Hi,
I would like to implement a kind of rolemanagement into our database and want to realise this with stored procedures/functions:
drop procedure if exists grant_manager_privileges;
drop procedure if exists grant_staff_privileges;
delimiter |
create procedure grant_manager_privileges(in benutzer varchar(81))
BEGIN
#The next line causes an error when the user is created...
# and doesn't have any rights
#revoke all privileges, grant option from benutzer;
grant select, insert, update, delete on new_data.* to benutzer;
#return benutzer ; <-- wenn es als function realisiert werden soll
END |
delimiter ;
The variable "benutzer" is NOT interpreted! see the following example (from the console):
mysql> create user 'new_user' identified by 'password';
Query OK, 0 rows affected (0.00 sec)
mysql> call grant_manager_privileges('new_user');
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'new_user';
+---------------------------------------------------------------------------------------------------------+
| Grants for new_user@% |
+---------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'new_user'@'%' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
+---------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show grants for 'benutzer';
+------------------------------------------------------------------------+
| Grants for benutzer@% |
+------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'benutzer'@'%' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `new_data`.* TO 'benutzer'@'%' |
+------------------------------------------------------------------------+
2 rows in set (0.00 sec)
The user "new_user" didn't get any rights, but "benutzer" although he didn't exist that time!
So what am I doing wrong? Can I use REVOKE and GRANT with variables in stored procedures/functions?
PS: I use 5.0.32-Debian_7etch5_log Version.