Bug #35005 | Possible security flaw around trigger privileges | ||
---|---|---|---|
Submitted: | 3 Mar 2008 16:20 | Modified: | 9 Jul 2010 9:56 |
Reporter: | Matthias Leich | Email Updates: | |
Status: | Can't repeat | Impact on me: | |
Category: | MySQL Server: Security: Privileges | Severity: | S3 (Non-critical) |
Version: | 5.1 | OS: | Any |
Assigned to: | Georgi Kodinov | CPU Architecture: | Any |
[3 Mar 2008 16:20]
Matthias Leich
[3 Mar 2008 20:34]
Matthias Leich
test script
Attachment: ml051.test (application/octet-stream, text), 1.32 KiB.
[3 Mar 2008 20:36]
Matthias Leich
test script
Attachment: ml051.test (application/octet-stream, text), 1.32 KiB.
[4 Mar 2008 11:33]
Matthias Leich
The correct results I expect
Attachment: ml051.result (application/octet-stream, text), 1.60 KiB.
[4 Mar 2008 12:10]
Matthias Leich
Sorry I just detected if user4 executes a SHOW TRIGGERS FROM db_datadict; he sees the trigger like in SELECT ... FROM information_schema.triggers. That means - the behaviour of SHOW and SELECT on information_schema.triggers is consistent. - the security flaw is also within SHOW
[7 Mar 2008 13:35]
Martin Hansson
Ok Matthias, please set back to Verified when done. Best Regards Martin
[20 Mar 2008 19:24]
Matthias Leich
Hi, after some discussion I refined the test a bit and it looks like the hole is a bit more serious. My protocol (a bit shrinked and reformatted): --------------------------------------------- CREATE DATABASE db_datadict1; CREATE DATABASE db_datadict2; # CREATE USER 'user4'@'localhost'; GRANT ALL ON test.* TO 'user4'@'localhost'; GRANT TRIGGER ON db_datadict1.* TO 'user4'@'localhost'; # CREATE TABLE db_datadict1.t1 (f1 INT, f2 INT); CREATE TABLE db_datadict2.t1 (f1 INT, f2 INT); USE db_datadict1; CREATE TRIGGER trg1 AFTER UPDATE ON db_datadict1.t1 FOR EACH ROW UPDATE db_datadict2.t1 SET f1 = new.f1, f2 = new.f2; USE db_datadict2; CREATE TRIGGER trg1 AFTER UPDATE ON db_datadict2.t1 FOR EACH ROW UPDATE db_datadict1.t1 SET f1 = new.f1, f2 = new.f2; # # Establish connection user4 (user=user4) SHOW GRANTS FOR 'user4'@'localhost'; Grants for user4@localhost GRANT USAGE ON *.* TO 'user4'@'localhost' GRANT ALL PRIVILEGES ON `test`.* TO 'user4'@'localhost' GRANT TRIGGER ON `db_datadict1`.* TO 'user4'@'localhost' SHOW TABLES FROM db_datadict1; Tables_in_db_datadict1 t1 <----------------------------------------- (1) SHOW TABLES FROM db_datadict2; ERROR 42000: Access denied for user 'user4'@'localhost' ... # SHOW CREATE TABLE db_datadict1.t1; ERROR 42000: SELECT command denied to user 'user4'@'localhost' ... ... With short words: All is fine because I found no way via - SHOW CREATE TABLE - SHOW COLUMNS - SELECT on information_schema.columns - SELECT direct on my tables to get information about the layout or content of my two tables. And I expect that trigger related SHOWs and SELECTS also do not give such sensible information. # SHOW TRIGGERS FROM db_datadict1; Trigger trg1 Event UPDATE Table t1 Statement UPDATE db_datadict2.t1 SET f1 = new.f1, f2 = new.f2 Timing AFTER Created NULL sql_mode Definer root@localhost character_set_client latin1 collation_connection latin1_swedish_ci Database Collation latin1_swedish_ci <----------------------------------------- (1),(2),(3),(4) SHOW TRIGGERS FROM db_datadict2; ERROR 42000: Access denied for user 'user4'@'localhost' to ... SHOW CREATE TRIGGER db_datadict1.trg1; Trigger trg1 sql_mode SQL Original Statement CREATE DEFINER=`root`@`localhost` TRIGGER trg1 AFTER UPDATE ON db_datadict1.t1 FOR EACH ROW UPDATE db_datadict2.t1 SET f1 = new.f1, f2 = new.f2 character_set_client latin1 collation_connection latin1_swedish_ci Database Collation latin1_swedish_ci <----------------------------------------- (1),(2),(3),(4) SHOW CREATE TRIGGER db_datadict2.trg1; Trigger trg1 sql_mode SQL Original Statement CREATE DEFINER=`root`@`localhost` TRIGGER trg1 AFTER UPDATE ON db_datadict2.t1 FOR EACH ROW UPDATE db_datadict1.t1 SET f1 = new.f1, f2 = new.f2 character_set_client latin1 collation_connection latin1_swedish_ci Database Collation latin1_swedish_ci <----------------------------------------- (5) SELECT event_object_schema,event_object_table,action_statement FROM information_schema.triggers WHERE trigger_name = 'trg1'; event_object_schema db_datadict1 event_object_table t1 action_statement UPDATE db_datadict2.t1 SET f1 = new.f1, f2 = new.f2 <----------------------------------------- (1),(2),(3),(4) I accept at least that the TRIGGER priv ON db_datadict1.* implies to see the names of all triggers within db_datadict1. But user4 gets IMHO too much information: (1) db_datadict1 contains a table named t1 although he is not allowed to CREATE/DROP/MODIFY tables within that schema or SELECT/INSERT/... on any existing or future table etc. But of course somebody might say the TRIGGER privilege implies the right to know which tables exist in db_datadict1. (2) There is a trigger db_datadict1.trg1 (ok, TRIGGER priv) and this trigger is bound to an event on table t1. user4 has no privilege on t1 except TRIGGER privilege. So why does he need to know that the trigger db_datadict1.trg1 affects the table db_datadict1.t1? (3) The event activating the trigger is UPDATE. user4 has no privilege on t1 except TRIGGER privilege. So why does he need to know that the trigger db_datadict1.trg1 starts if an UPDATE is performed? (4) The presented trigger statement shows in action_statement other information - db_datadict1.t1 contains the columns f1 and f2 user4 has no ... column related rights on db_datadict1.t1. And the attempts to get layout information via SHOW COLUMNS or information_schema fail. Why is the content of the trigger statement presented if it contains this layout related information? - there is another database named db_datadict2 and there is also a table named t1 with columns .... And user4 has neither the trigger nor any other privilege on db_datadict2. (5) There is a trigger db_datadict2.trg1. Why this information? There is no TRIGGER priv granted ON db_datadict2. Proposed fixes: 1. (5) is a clear bug. user4 must not see triggers within databases where he has no trigger privilege. 2. Minimize the security issues (1) + (2) + (3) + (4) by 2.0 The TRIGGER privilege on db_datadict1.* gives access (See/Create/Drop trigger) to the name space of triggers within the schema db_datadict1. This means SELECT * FROM information_schema.triggers must contain one line per existing trigger with at least trigger_schema and trigger_name NOT NULL. = current implementation 2.1 I expect a behaviour similar like CREATE VIEW privilege only. Example: The layout of any view not created by the user itself will be not shown (empty result or command execution denied). My guess: The column view_definition is empty if the current_user() is neither root nor the definer of the view. This means at least action_statement should be empty if current_user() is neither root or the definer of the trigger. This fixes (4). 2.2 IMHO if current_user() - is neither root nor the definer of the trigger and - has no privileges except TRIGGER on the table than the presented content of event_object_schema,event_object_table, action_order,action_condition,action_orientation,action_timing should be NULL or empty. 2.3 IMHO if there is a user having no privilege at all (except TRIGGER) on a table than it would make sense to disallow for him the creation of triggers on this table. 2.4 Maybe additional refinements like a user having no UPDATE privilege on a table is not allowed to create an UPDATE trigger for this table make sense. 2.5 Maybe additional refinements like a user having no UPDATE privilege on a table gets in case of SHOW/SELECT for an UPDATE trigger on this table for action_order,action_condition, action_orientation,action_timing content NULL or empty make sense. I vote for at least 1. and 2.1. Regards, Matthias
[20 Mar 2008 19:56]
Matthias Leich
improved script
Attachment: ml051.test (application/octet-stream, text), 1.88 KiB.
[18 Jun 2010 9:50]
Konstantin Osipov
I don't think it's a bug. The fact that there is a discrepancy between SHOW and I_S is okay as long as both commands work as documented.
[18 Jun 2010 9:50]
Konstantin Osipov
Suggest closing as "Not a bug"
[18 Jun 2010 15:06]
Matthias Leich
Hi Kostja, taking into account that in MySQL - grants are mostly just name space related They allow to query, drop and create (of course following some defined syntax and maybe accompagnied by more or less checks if objects used exist) an object occupying some place in name space - existing stored functions/procedures, triggers and views are essentially just definitions. They can become at some point of time "ill" because the referenced objects were changed (drop table, change table layout etc.) I am willing (though it causes some pain) to accept that a users might see under some conditions such a definition even if he has no schema or table related privilege to check if the objects mentioned exist. As long as he has not sufficient additional privileges he cannot figure out if the stuff mentioned there is valid or just some outdated garbage. 1. There is a bug within the manual http://dev.mysql.com/doc/refman/5.5/en/grant.html because it does not specify under which conditions somebody is allowed to see triggers. It just says about trigger privilege "Enable triggers to be created or dropped". Without knowing which behavior was really intended in the server I can only guess "to be listed, created or dropped" would be correct. (see 2.) 2. http://dev.mysql.com/doc/refman/5.5/en/show-create-trigger.html does not mention privileges required for running this statement at all. Manual bug There seem to be restrictions within the server but again I do not know what the intention of the server is. 3. http://dev.mysql.com/doc/refman/5.5/en/triggers-table.html says: "You must have the TRIGGER privilege to access this table." http://dev.mysql.com/doc/refman/5.5/en/show-triggers.html says: "SHOW TRIGGERS lists the triggers currently defined for tables in a database (the default database unless a FROM clause is given). This statement requires the TRIGGER privilege." My addition: The grant syntax shows that the trigger privilege is bound to a database. This translates to I must not see a list of triggers for a database where I have no trigger privilege. My test shows that the system behaves different. IMHO there might be thinkable exceptions in case I am allowed to create/drop/insert/... the table where a trigger is defined for. But manual pages above do not mention exceptions. So there must be no one. Either the server or the manual has a bug. 4. Just assuming that there are indeed reasonable exceptions where someone without trigger priv for some database is allowed to see at least some triggers there. In my test 'user4'@'localhost' has the following privs only GRANT USAGE ON *.* TO 'user4'@'localhost' GRANT ALL PRIVILEGES ON `test`.* TO 'user4'@'localhost' GRANT TRIGGER ON `db_datadict1`.* TO 'user4'@'localhost' So why is he allowed to see SHOW CREATE TRIGGER db_datadict2.trg1; Trigger trg1 sql_mode SQL Original Statement CREATE DEFINER=`root`@`localhost` TRIGGER trg1 AFTER UPDATE ON db_datadict2.t1 FOR EACH ROW UPDATE db_datadict1.t1 SET f1 = new.f1, f2 = new.f2 character_set_client latin1 collation_connection latin1_swedish_ci Database Collation latin1_swedish_ci without any priv in db_datadict2 ?
[9 Jul 2010 8:54]
Georgi Kodinov
Mattias, Thanks for looking into this. You're completely correct that user4 should not be able to see the triggers in a database he doesn't have rights to. Thus the example below is a clear 5.1 bug and will be fixed. As for your other observations, I wouldn't change them in a release version, as the current behavior is something that people may rely upon. I suggest you open up separate bug(s) for the things that you think are incorrect so we can track and dispatch them to the correct version. CREATE DATABASE db_datadict1; CREATE DATABASE db_datadict2; CREATE USER 'user4'@'localhost'; GRANT TRIGGER ON db_datadict1.* TO 'user4'@'localhost'; # CREATE TABLE db_datadict1.t1 (f1 INT, f2 INT); CREATE TABLE db_datadict2.t1 (f1 INT, f2 INT); USE db_datadict1; CREATE TRIGGER trg1 AFTER UPDATE ON t1 FOR EACH ROW UPDATE db_datadict2.t1 SET f1 = new.f1, f2 = new.f2; USE db_datadict2; CREATE TRIGGER trg1 AFTER UPDATE ON db_datadict2.t1 FOR EACH ROW UPDATE db_datadict1.t1 SET f1 = new.f1, f2 = new.f2; SHOW CREATE TRIGGER db_datadict2.trg1; Trigger trg1 sql_mode SQL Original Statement CREATE DEFINER=`root`@`localhost` TRIGGER trg1 AFTER UPDATE ON db_datadict2.t1 FOR EACH ROW UPDATE db_datadict1.t1 SET f1 = new.f1, f2 = new.f2
[9 Jul 2010 9:54]
Georgi Kodinov
I can't repeat this with the latest 5.1-bugteam. Please find attached my .test and result file.
[9 Jul 2010 9:55]
Georgi Kodinov
bug.test
Attachment: bug.test (application/octet-stream, text), 706 bytes.
[9 Jul 2010 9:55]
Georgi Kodinov
bug.result
Attachment: bug.result (application/octet-stream, text), 626 bytes.
[9 Jul 2010 13:21]
Matthias Leich
I retested on current mysql-5.1-bugteam and can confirm that [18 Jun 17:06] Matthias Leich issue 4 disappeared. I will write a separate bug report for the remaining documentation related bugs.