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

Bug #35005	Possible security flaw around trigger privileges
Submitted:	3 Mar 2008 17:20	Modified:	20 Mar 2008 20:57
Reporter:	Matthias Leich
Status:	Verified
Category:	Server: Privileges	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:	Martin Hansson	Target Version:
Triage:	Triaged: D3 (Medium)

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.