MySQL Forums
Forum List  »  Quality Assurance

Variable is not interpreted
Posted by: Vitali Graf
Date: July 03, 2008 12:50AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Variable is not interpreted
2779
July 03, 2008 12:50AM
1992
July 03, 2008 02:37AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.