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: | |
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
[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.