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)
init_connect
is ignored byroot
.SP_Warnings
, but the procedure you've created is namedWarnings
.CALL
stored in theinit_connect
runs ok (tested with copy-paste +;
)SHOW CREATE PROCEDURE Warnings
because you haven't listed theSQL SECURITY
characteristic. What isSHOW CREATE USER Tk3
and `SHOW GRANTS FOR TK3'