MySQL Bugs: #56866: TRIGGER/SUPER priv *not* required as docs state

Bug #56866	TRIGGER/SUPER priv not required as docs state
Submitted:	20 Sep 2010 8:28	Modified:	22 Sep 2010 19:45
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0, 5.1, 5.5	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	qc

Description:
http://dev.mysql.com/doc/refman/5.0/en/show-triggers.html
"You must have the SUPER privilege to execute SHOW TRIGGERS."

It does not seem to be the case.  It would also be inconsistent with "SELECT from I_S.TRIGGERS" where SUPER never was required.

Additionally for SHOW CREATE TRIGGER no privilege requirements are listed. I'd assume that some database-level privileges to the database, where the trigger resides, would be required here?

How to repeat:
SHOW GRANTS;
/*
Grants for peter@localhost                                               
-------------------------------------------------------------------------
GRANT USAGE ON *.* TO 'peter'@'localhost'                                
GRANT ALL PRIVILEGES ON `test`.* TO 'peter'@'localhost' WITH GRANT OPTION
*/

SHOW TRIGGERS; -- empty set; not 'access denied'

SELECT * FROM information_schema.TRIGGERS; -- another empty set

Suggested fix:
Fix doc's - don't change server. An please reply ASAP what the intention and actual state is.

Sorry, but had you tested that with 5.0.x or with 5.1.50? 

Manual for 5.1 describes 5.1 behavior a bit differently at http://dev.mysql.com/doc/refman/5.1/en/show-triggers.html. There is TRIGGER table level privilege in 5.1...

OK .. my mistake as regards the documentation page. I was using 5.1.50.

But in my example I do not have TRIGGER privilege either. And I am able to execute SHOW TRIGGERS.

Also
http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_trigger
says "The TRIGGER privilege enables you to create and drop triggers. You must have this privilege for a table to create or drop triggers for that table. This privilege was added in MySQL 5.1.6. (Prior to MySQL 5.1.6, trigger operations required the SUPER privilege.)".  Note: It does not mention TRIGGER-privilege as a requirement for SHOW TRIGGERS.

So I'd still say there is a bug in this page: 
http://dev.mysql.com/doc/refman/5.1/en/show-triggers.html
(and probably in the corresponding 5.0 page too)

updated synopsis as per discussion!

OK, manual pages say nothing about SHOW TRIGGERS and should probably be improved with clarification.

It *does say* something, but what it says is wrong!

It says that SUPER/TRIGGER privileges are requirements for execution of "SHOW TRIGGERS", but it is not so.

By the way, in 5.1 TRIGGER is a table level privilege, so you grant it on all tables in the test database with this:

GRANT ALL PRIVILEGES ON `test`.* TO 'peter'@'localhost' WITH GRANT OPTION;

OK .. you had a point.  But still after REVOKING the TRIGGER privilege to the database:

SHOW GRANTS;
/*
Grants for peter@localhost                                                                                                                                                                                                                    
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GRANT USAGE ON *.* TO 'peter'@'localhost'                                                                                                                                                                                                     
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT ON `test`.* TO 'peter'@'localhost' WITH GRANT OPTION
*/

USE test; 

SHOW TRIGGERS; -- succeeds

And on 5.0.90 (with no SUPER privilege):

SHOW GRANTS;
/*
Grants for peter@localhost                                               
-------------------------------------------------------------------------
GRANT USAGE ON *.* TO 'peter'@'localhost'                                
GRANT ALL PRIVILEGES ON `test`.* TO 'peter'@'localhost' WITH GRANT OPTION
*/

USE test;

SHOW TRIGGERS; -- succeeds

Let me add that we have been believing in that page and for that reason omitted use of the statement (and did SELECT FROM I_S instead) where we could have used it with advantage (as SHOW - at least on some server versions - is faster than SELECT FROM I_S. Actually SELECT FROM I_S.TRIGGERS can be so slow that our application seems to 'hang').

Not surprisingly same issue exists with 5.5 docs.  Updating version affected to "5.0, 5.1, 5.5"

You also have a good point. 

Indeed, even for other database but test you do NOT need TRIGGER privilege to execute SHOW TRIGGERS:

mysql> create database db1;
Query OK, 1 row affected (0.00 sec)

mysql> create user valeriy@localhost identified by 'test';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE
    -> TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER
    -> ROUTINE, EVENT ON db1.* to valeriy@localhost WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye
macbook-pro:5.1 openxs$ bin/mysql -uvaleriy -ptest test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.51-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> show grants;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for valeriy@localhost                                                                                                                                                                                                                    |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'valeriy'@'localhost' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29'                                                                                                                                  |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT ON `db1`.* TO 'valeriy'@'localhost' WITH GRANT OPTION |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show triggers;
Empty set (0.00 sec)

mysql> exit

very most simple test case:

SHOW GRANTS;
/*
Grants for pl@localhost                     
--------------------------------------------
GRANT USAGE ON *.* TO 'pl'@'localhost'      
GRANT SELECT ON `test`.* TO 'pl'@'localhost' -- (a privilege on 'test' is required to USE it at all)
*/
USE test; 
SHOW TRIGGERS; -- success

Sorry, I've used test database instead of db1 in my last comment. 

Anyway, you can execute SHOW TRIGGERS statement without TRIGGER privilege, BUT you will get empty result set (same as with I_S):

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.51-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> REVOKE TRIGGER ON db1.* FROM valeriy@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
macbook-pro:5.1 openxs$ bin/mysql -uvaleriy -ptest db1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.1.51-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> show triggers\G
Empty set (0.01 sec)

mysql> exit
Bye
macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.1.51-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> GRANT TRIGGER ON db1.* to valeriy@localhost WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
macbook-pro:5.1 openxs$ bin/mysql -uvaleriy -ptest db1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.1.51-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> show triggers\G
*************************** 1. row ***************************
             Trigger: tr1
               Event: INSERT
               Table: t1
           Statement: begin set new.c1 = 2; end
              Timing: BEFORE
             Created: NULL
            sql_mode: 
             Definer: root@localhost
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.01 sec)

A colleague informs me that SELECT FROM I_S.TRIGGERS returns an empty result set (also if there are TRIGGERS) if user has no TRIGGER privilege and it is also documented.

So MySQL doc's statement for SHOW TRIGGERS what is now "you need TRIGGER privilege to execute SHOW TRIGGERS" should be rather "SHOW TRIGGERS will only return results from databases and tables where you have TRIGGER privilege."

If so, it seems it is not *execution* that is managed by TRIGGER privilege but *retrieval of results*.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Updated SHOW TRIGGERS (and INFORMATION_SCHEMA.TRIGGERS) description with suggested revised statement.