CODEWITHSUNDEEP
mysqlstored-procedurespermissionssql-grant
2mac
2mac

Reputation: 1689

Allow GRANT in procedures?

I have created a procedure that will prepare a statement to grant permission to a user with a password. I have created this procedure to avoid having to give explicit GRANT permission to admin users, instead letting them use the procedure which does exactly what I expect them to do.

The procedure is as follows:

CREATE PROCEDURE grantPermission (perm VARCHAR(30), target VARCHAR(30), id VARCHAR(8), host VARCHAR(45), passwd VARCHAR(45))
  BEGIN
    SET @setPermissionCmd = CONCAT('GRANT ', perm, ' ON ', target, ' TO ''', id, '''@''', host, ''' IDENTIFIED BY ''', passwd, ''';');
    PREPARE setPermissionStmt FROM @setPermissionCmd;
    EXECUTE setPermissionStmt;
    DEALLOCATE PREPARE setPermissionStmt;
    FLUSH PRIVILEGES;
  END

When I CALL this procedure, I get an error code 1142 that I don't have permission to GRANT. There are two problems here. One, I'm executing this as root which should have all permissions (I have not taken any away, and root is the one who created the procedure). Two, having permission to execute the procedure should imply permission to do whatever is inside it.

Upvotes: 0

Views: 211

Answers (1)

wchiquito
wchiquito

Reputation: 16569

My test:

$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.35-1ubuntu1 (Ubuntu)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;
Database changed

mysql> CREATE TABLE `tableTest`(`id` INT UNSIGNED);
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'userTest'@'localhost' IDENTIFIED BY 'mypass';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR 'userTest'@'localhost';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for userTest@localhost                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'userTest'@'localhost' IDENTIFIED BY PASSWORD '*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4' |
+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> DELIMITER //

mysql> DROP PROCEDURE IF EXISTS `grantPermission`//
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE PROCEDURE `grantPermission` (`perm` VARCHAR(30),
    ->                                     `target` VARCHAR(30),
    ->                                     `id` VARCHAR(8),
    ->                                     `host` VARCHAR(45),
    ->                                     `passwd` VARCHAR(45))
    -> BEGIN
    ->     SET @`setPermissionCmd` = CONCAT(
    ->     'GRANT ', `perm`, ' ON `', `target`, '`
     >      TO ''', `id`, '''@''', `host`, ''' 
     >     IDENTIFIED BY ''', `passwd`, ''';');
    ->     PREPARE `setPermissionStmt` FROM @`setPermissionCmd`;
    ->     EXECUTE `setPermissionStmt`;
    ->     DEALLOCATE PREPARE `setPermissionStmt`;
    ->     FLUSH PRIVILEGES;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> CALL `grantPermission`('SELECT', 'tableTest', 'userTest', 'localhost', 'mypass');
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR 'userTest'@'localhost';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for userTest@localhost                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'userTest'@'localhost' IDENTIFIED BY PASSWORD '*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4' |
| GRANT SELECT ON `test`.`tableTest` TO 'userTest'@'localhost'                                                    |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Upvotes: 1

Related Questions