0

From this question I was able to make my init_connect = "CALL mydb.Warnings()", but a user login in does not triggers the SP.

the SP is like:

CREATE DEFINER=`root`@`localhost` PROCEDURE `Warnings`()
BEGIN
select * from mydb.log;
END

The user 'Tk3' is able to call the SP with success, but it ain't allowed to do Selects.
I did check with show global variables like 'init_%'; and the correct value is set.
I'm missing the reason for this failure, could you help?

1st Edit:
After the @danblack and @Rick-James suggestions I altered the SECURITY to:
CREATE DEFINER='tk3'@'localhost' PROCEDURE 'warnings'()
but the behavior is the same.
:( I'm lost ):

2nd Edit:

    MariaDB [(none)]> show create procedure logdatabase.warnings;
    +-----------+--------------------------------------------+----------------------+
    | Procedure | sql_mode    
    |Create Procedure  
    |character_set_client | collation_connection | Database Collation |
    +-----------+--------------------------------------------+----------------------+
    | warnings  | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

    CREATE DEFINER=`tk3`@`localhost` PROCEDURE `warnings`()
    BEGIN

    SELECT * FROM logdatabase.log_activity;
    END 
    | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
    +-----------+--------------------------------------------+----------------------+
    1 row in set (0.00 sec)


MariaDB [(none)]> show grants for 'tk3'@'localhost';
+------------------------------------------------------------------------------+
| Grants for tk3@localhost                                                     |                           
+------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tk3'@'localhost'                                      |   
| GRANT SELECT, CREATE, EXECUTE, CREATE ROUTINE, ALTER ROUTINE ON `logdatabase`.* TO 'tk3'@'localhost' |
+------------------------------------------------------------------------------+
2 rows in set (0.00 sec)


I'm not showing the SHOW CREATE USER because it's returning an error:

MariaDB [(none)]> CREATE USER 'mytest'@'localhost';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> show CREATE USER 'mytest'@'localhost';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'USER 'mytest'@'localhost'' at line 1

(this print was made with 'root' - I will deal with this strange thing later)

5
  • 1
    Check the spelling of "tigers". Do you have "connection pooling". init_connect is ignored by root.
    – Rick James
    Mar 16, 2019 at 14:42
  • You seem to be trying to call a procedure called SP_Warnings, but the procedure you've created is named Warnings.
    – dbdemon
    Mar 16, 2019 at 18:26
  • @rick-james: thanks for the notice. I think I don't have pooling. This is a Xampp vanilla installation, and the login is made in the cmd with mysql.exe -utk3.... @dbemon: The naming error was just a consequent of a bad renaming. the CALL stored in the init_connect runs ok (tested with copy-paste + ;)
    – Ezeq
    Mar 16, 2019 at 23:21
  • What's the full SHOW CREATE PROCEDURE Warnings because you haven't listed the SQL SECURITY characteristic. What is SHOW CREATE USER Tk3 and `SHOW GRANTS FOR TK3'
    – danblack
    Mar 18, 2019 at 2:48
  • Hi @danblack; Sorry for my late reply. I must have misinterpreted the SQL Security characteristic. I have added the information, all but the SHOW CREATE USER .
    – Ezeq
    Mar 22, 2019 at 22:59

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.