MySQL Bugs: #35005: Possible security flaw around trigger privileges

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:	None
Category:	MySQL Server: Security: Privileges	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
My protocol (a bit shrinked):
-----------------------------
CREATE DATABASE db_datadict;
CREATE USER 'user4'@'localhost';
GRANT TRIGGER ON *.* TO 'user4'@'localhost';
USE db_datadict;
CREATE TABLE db_datadict.t1 (f1 INT, f2 INT, f3 INT);
CREATE TRIGGER trg1 BEFORE INSERT
ON db_datadict.t1 FOR EACH ROW
SET @test_before = 2, new.f1 = @test_before;
# Establish connection user4 (user=user4)
# default database is "test".
SHOW GRANTS FOR 'user4'@'localhost';
Grants for user4@localhost
GRANT TRIGGER ON *.* TO 'user4'@'localhost'
SHOW TABLES FROM db_datadict;
Tables_in_db_datadict
t1
SHOW CREATE TABLE db_datadict.t1;
ERROR 42000: SELECT command denied to user 
             'user4'@'localhost' for table 't1'
SHOW COLUMNS FROM db_datadict.t1;
ERROR 42000: SELECT command denied to user
             'user4'@'localhost' for table 't1'
SELECT table_schema,table_name 
FROM information_schema.tables
WHERE table_schema = 'db_datadict';
table_schema	table_name
db_datadict	t1
SELECT table_schema,table_name,column_name
FROM information_schema.columns
WHERE table_schema = 'db_datadict';
table_schema	table_name	column_name
SELECT f1 FROM db_datadict.t1;
ERROR 42000: SELECT command denied to user
      'user4'@'localhost' for table 't1'
===========================================
So it looks like there is most probably no
way for "user4" to get information about
the layout or content of the table t1.

SHOW TRIGGERS LIKE 't1';
Trigger Event Table Statement .... 
<empty result set>
SELECT event_object_schema,event_object_table,action_statement
FROM information_schema.triggers
WHERE trigger_name = 'trg1';
event_object_schema event_object_table action_statement
db_datadict t1 SET @test_before = 2, new.f1 = @test_before

Why does the SELECT on information_schema.triggers
present more information to "user4" than the 
corresponding SHOW command?
He now knows that this tables has a numeric column with
the name f1.

My environment:
- mysql-5.1 ChangeSet@1.1810.3473.30, 2008-02-22
- compile-pentium-debug-max
- Linux (OpenSuSE 10.3) 64 Bit
- Intel Core2Duo (64 Bit)

How to repeat:
Please run the attached test.

Suggested fix:
1. The SHOW command and the corresponding SELECT on
   information_schema.triggers should show similar
   informations about triggers.
2. IMHO the TRIGGER should not allow to get information
   about columns of tables where the user has no
   privilege like SELECT,INSERT or UPDATE.
   This means in the current case the empty result
   set of the SHOW command looks more reasonable.

test script

Attachment: ml051.test (application/octet-stream, text), 1.32 KiB.

test script

Attachment: ml051.test (application/octet-stream, text), 1.32 KiB.

The correct results I expect

Attachment: ml051.result (application/octet-stream, text), 1.60 KiB.

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

Ok Matthias, please set back to Verified when done.

Best Regards

Martin

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

improved script

Attachment: ml051.test (application/octet-stream, text), 1.88 KiB.

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.

Suggest closing as "Not a bug"

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 ?

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

I can't repeat this with the latest 5.1-bugteam. Please find attached my .test and result file.

bug.test

Attachment: bug.test (application/octet-stream, text), 706 bytes.

bug.result

Attachment: bug.result (application/octet-stream, text), 626 bytes.

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.