Bug #35384 Revoke grant fails upon automatic privilege assignment to users on procedures
Submitted: 18 Mar 2008 9:10 Modified: 25 Mar 2008 11:47
Reporter: Rizwan Maredia Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S3 (Non-critical)
Version:5.1.22, 5.1.24-rc OS:Windows (also Linux)
Assigned to: CPU Architecture:Any
Tags: automatic_sp_privileges, disabled, grant, revoke

[18 Mar 2008 9:10] Rizwan Maredia
Description:
If a user which is not a super user creates a procedure and then disconnects. Upon revoking grants from that user without deleting the procedure, the revoke statement causes an error i.e ER_NONEXISTING_PROC_GRANT which is invalid according to the context.

How to repeat:
We can test this bug in mysql test environment.

CREATE TABLE t1 (a varchar(200));
INSERT INTO t1 VALUES('Procedure Executed.');

# Creating test user
CREATE USER 'userTest'@'localhost';
SET GLOBAL automatic_sp_privileges = TRUE;

connect (conUser,localhost,userTest,,);
connection conUser;

delimiter |;
CREATE PROCEDURE testProc ()
BEGIN
SELECT * FROM t1;
END;|
delimiter ;|

connection default;
disconnect conUser;

SHOW GRANTS FOR 'userTest'@'localhost';
+Grants for userTest@localhost
+GRANT USAGE ON *.* TO 'userTest'@'localhost'
+GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `test`.`testproc` TO 'userTest'@'localhost'

--error ER_NONEXISTING_PROC_GRANT
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'userTest'@'localhost';

Suggested fix:
The grants should get revoked instead of causing an error as the grants are shown in the show grants statement result.
[20 Mar 2008 11:51] Susanne Ebrecht
Many thanks for writing a bug report.

Verified as described by using 5.1 bk tree.

mysql> select version();
5.1.24-rc-debug

mysql> CREATE TABLE t1 (a varchar(200));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t1 VALUES('Procedure Executed.');
Query OK, 1 row affected (0.00 sec)

mysql> CREATE USER 'test'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL automatic_sp_privileges = TRUE;
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter //
mysql> CREATE PROCEDURE testProc () BEGIN SELECT * FROM t1; END;//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE miracee.`testproc` TO test@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR test@'localhost';

GRANT USAGE ON *.* TO 'test'@'localhost'
GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `miracee`.`testproc` TO 'test'@'localhost'

mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'userTest'@'localhost';
ERROR 1269 (HY000): Can't revoke all privileges for one or more of the requested users
[20 Mar 2008 17:55] Timothy Smith
Hello,

Thanks for the report, but in this situation the server is behaving correctly.

The user does not have any real privileges associated with it.  The SHOW GRANTS command is correct because the user *can* ALTER/EXECUTE procedures based on the global variable setting.  However, no such privileges are associated directly with the user, and they can't be revoked just for that user.

It is correct for REVOKE to give an error, because there is nothing to revoke from the user.