MySQL Bugs: #45412: SHOW CREATE TRIGGER does not require privileges to disclose trigger data

Bug #45412	SHOW CREATE TRIGGER does not require privileges to disclose trigger data
Submitted:	9 Jun 2009 19:24	Modified:	21 Jul 2009 18:48
Reporter:	Chuck Bell	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S3 (Non-critical)
Version:	5.1.36-bzr, 6.0	OS:	Any
Assigned to:	Sergei Glukhov	CPU Architecture:	Any

Description:
The SHOW CREATE TRIGGER statement is not checking privileges and can reveal the intimate details of any trigger to a user without any rights. This is a security hole and could expose sensitive operations to prying eyes.

Note: May be somewhat related to BUG#17033, BUG#17597.

How to repeat:
Overview
--------
Create a user with absolutely no rights. (poor chap)
Create a database and a trigger in it.
Connect as the user with no rights.
SHOW CREATE TRIGGER <x> reveals the trigger details!

Sample test follows:

connect (root_user,localhost,root,,);

CREATE DATABASE trigger_priv_error;
CREATE TABLE trigger_priv_error.t1 (a char(30)) ENGINE=MEMORY;
INSERT INTO trigger_priv_error.t1 VALUES ("01 Test #1 - super privilege"); 
INSERT INTO trigger_priv_error.t1 VALUES ("02 Test #1 - super privilege"); 
INSERT INTO trigger_priv_error.t1 VALUES ("03 Test #1 - super privilege"); 
INSERT INTO trigger_priv_error.t1 VALUES ("04 Test #1 - super privilege"); 
INSERT INTO trigger_priv_error.t1 VALUES ("05 Test #1 - super privilege"); 
INSERT INTO trigger_priv_error.t1 VALUES ("06 Test #1 - super privilege"); 
INSERT INTO trigger_priv_error.t1 VALUES ("07 Test #1 - super privilege"); 
CREATE TRIGGER trigger_priv_error.trg AFTER INSERT ON trigger_priv_error.t1 FOR EACH ROW
 INSERT INTO trigger_priv_error.t1 VALUES('Some very sensitive data goes here');

CREATE USER 'no_rights'@'localhost';
REVOKE ALL ON *.* FROM 'no_rights'@'localhost';
FLUSH PRIVILEGES;

--echo #
--echo # Holy Moley, Badman! This shouldn't happen!
--echo #
connect (no_rights_user,localhost,no_rights,,);
SELECT trigger_name FROM INFORMATION_SCHEMA.TRIGGERS 
 WHERE trigger_schema = 'trigger_priv_error';
SHOW CREATE TRIGGER trigger_priv_error.trg;

connection root_user;
SELECT trigger_name FROM INFORMATION_SCHEMA.TRIGGERS 
 WHERE trigger_schema = 'trigger_priv_error';
SHOW CREATE TRIGGER trigger_priv_error.trg;

DROP USER 'no_rights'@'localhost';
DROP DATABASE trigger_priv_error;

The result will show this:
+#
+# Holy Moley, Badman! This shouldn't happen!
+#
+SELECT trigger_name FROM INFORMATION_SCHEMA.TRIGGERS
+WHERE trigger_schema = 'trigger_priv_error';
+trigger_name
+SHOW CREATE TRIGGER trigger_priv_error.trg;
+Trigger        sql_mode        SQL Original Statement  character_set_clientcoll
ation_connection        Database Collation
+trg            CREATE DEFINER=`root`@`localhost` TRIGGER trigger_priv_error.trg
 AFTER INSERT ON trigger_priv_error.t1 FOR EACH ROW
+INSERT INTO trigger_priv_error.t1 VALUES('Some very sensitive data goes here') latin1  latin1_swedish_ci       latin1_swedish_ci

As we can see, the selection from IS fails to return any triggers, but the SHOW CREATE TRIGGER reveals everything there is to know about the trigger. .oO

Suggested fix:
Add privilege checking to SHOW CREATE TRIGGER code.

Thank you for the bug report. Verified as described:

c:\dbs>6.0\bin\mysql -uno_rights --port=3600
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 6.0.12-alpha-Win X64-log Source distribution

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

mysql> SHOW CREATE TRIGGER trigger_priv_error.trg\G
*************************** 1. row ***************************
               Trigger: trg
              sql_mode:
SQL Original Statement: CREATE DEFINER=`root`@`localhost` TRIGGER trigger_priv_error.trg AFTER INSERT ON trigger_priv_error.t1 FOR EACH ROW
 INSERT INTO trigger_priv_error.t1 VALUES('Some very sensitive data goes here')
  character_set_client: latin1
  collation_connection: latin1_swedish_ci
    Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

mysql>

We have the same problem with recent 5.1.36 from bzr also.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/76787

2968 Sergey Glukhov	2009-06-22
      Bug#45412 SHOW CREATE TRIGGER does not require privileges to disclose trigger data
      Added privilege checking to SHOW CREATE TRIGGER code.
     @ mysql-test/r/trigger_notembedded.result
        test result
     @ mysql-test/t/trigger_notembedded.test
        test case
     @ sql/sql_show.cc
        Added privilege checking to SHOW CREATE TRIGGER code.

Patch is ok, but...
1. I think access checks should be isolated better. I recommend putting them outside of the show_create_trigger() function in SQLCOM_SHOW_CREATE_TRIGGER-case paragraph and before any other relevant call where an access check is required.
2. I think the functionality would be improved if the definer always could view his own tiggers, but this is a feature request.

I think the submitted patch is good enough as a minimal bug patch.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/77164

2977 Sergey Glukhov	2009-06-25
      Bug#45412 SHOW CREATE TRIGGER does not require privileges to disclose trigger data
      Added privilege checking to SHOW CREATE TRIGGER code.
     @ mysql-test/r/trigger_notembedded.result
        test result
     @ mysql-test/t/trigger_notembedded.test
        test case
     @ sql/sql_show.cc
        Added privilege checking to SHOW CREATE TRIGGER code.

Pushed into 5.1.37 (revid:joro@sun.com-20090708131116-kyz8iotbum8w9yic) (version source revid:staale.smedseng@sun.com-20090626084621-g6zteyvzehto95j0) (merge vers: 5.1.37) (pib:11)

Pushed into 5.1.37 (revid:joro@sun.com-20090708131116-kyz8iotbum8w9yic) (version source revid:staale.smedseng@sun.com-20090626084621-g6zteyvzehto95j0) (merge vers: 5.1.37) (pib:11)

Pushed into 5.4.4-alpha (revid:anozdrin@bk-internal.mysql.com-20090710111017-bnh2cau84ug1hvei) (version source revid:sergey.glukhov@sun.com-20090625105750-fenddjvwz8htr5x4) (merge vers: 5.4.4-alpha) (pib:11)

Noted in 5.1.37, 5.4.4 changelogs.

SHOW CREATE TRIGGER requires the TRIGGER privilege but was not
checking privileges.

Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.

Ignore previous comment about 5.4.2.

Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)

Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)

Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)

Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)

The 5.4 fix has been pushed to 5.4.2.