Bug #27145 EXTRA_ACL troubles
Submitted: 14 Mar 2007 17:59 Modified: 6 Mar 2010 19:57
Reporter: Sergei Golubchik Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S3 (Non-critical)
Version:any OS:Any
Assigned to: Kristofer Pettersson CPU Architecture:Any
Triage: D1 (Critical) / R5 (Severe) / E3 (Medium)

[14 Mar 2007 17:59] Sergei Golubchik
Description:
EXTRA_ACL is documented in sql_parse.cc as

 The idea of EXTRA_ACL is that one will be granted access to the table if
 one has the asked privilege on any column combination of the table; For
 example to be able to check a table one needs to have SELECT privilege on
 any column of the table.

It is used according to the description above, e.g.:

  case SQLCOM_CHECK:
    if (check_table_access(thd, SELECT_ACL | EXTRA_ACL , all_tables, 0))
      goto error; /* purecov: inspected */

Same in SQLCOM_CHECKSUM, and many other places.

In fact EXTRA_ACL grants access to the table if one has *any* privileges on any column combination - not *asked* privileges.

Either the description or the implementation is wrong.
Ask Monty to be sure.
Take care when fixing the implementation - it'll break de-facto behaviour that MySQL had for ages.
I failed to find what privileges should apply to CHECK and CHECKSUM in the manual.

How to repeat:
CREATE TABLE t1 (a int, b int);
GRANT INSERT (a) ON test.t1 TO user@localhost;

-- connect as user@localhost
INSERT t1 (a) VALUES (1);
CHECK TABLE t1;
[30 Nov 2007 11:00] Sergei Golubchik
Consequences, commands that require less privileges than intended:

CHECKSUM TABLE
CHECK TABLE
SHOW COLUMNS
SHOW KEYS
SHOW CREATE TABLE
CREATE TABLE ... LIKE
[30 Nov 2007 11:04] Sergei Golubchik
bug#32826 was marked a duplicate of this bug
[30 Nov 2007 17:03] Sergei Golubchik
After discussion with Monty: we'll fix it by making EXTRA_ACL behave as documented. To preserve the old de-facto behavior we'll use TABLE_ACL | EXTRA_ACL instead SELECT_ACL | EXTRA_ACL for those statements where we want to preserve it.

I think that for CHECK TABLE we can preserve the old behavior (any privilege is sufficient), for other statements we will preserve the intended behavior (SELECT privilege is required).

The manual need to list explicitly the required privileges for all the affected statements.
[30 Nov 2007 17:49] Sergei Golubchik
because of inevitable behavior changes, we'll fix it in 5.1+
[2 Dec 2007 1:40] Peter Gulutzan
CREATE TABLE ... LIKE is closest to the standard's
CREATE TABLE ... (LIKE table), where "LIKE table"
is a <like clause>. The requirement for <like clause> is
(SQL:200n Part 2, 11.3 <table definition>, access rules)
"2) If a <like clause> is contained in a <table definition>,
then the applicable privileges for A shall include
SELECT privilege on the table identified in the <like clause>."

SHOW COLUMNS is closest to the standard's
SELECT ... FROM INFORMATION_SCHEMA.COLUMNS.
The requirement for selecting from the COLUMNS view is
(SQL:200n Part 11, 5.21 COLUMNS view)
"WHERE ( C.TABLE_CATALOG, C.TABLE_SCHEMA, C.TABLE_NAME, C.COLUMN_NAME ) IN
( SELECT CP.TABLE_CATALOG, CP.TABLE_SCHEMA, CP.TABLE_NAME, CP.COLUMN_NAME
FROM DEFINITION_SCHEMA.COLUMN_PRIVILEGES AS CP
WHERE ( CP.GRANTEE IN
( 'PUBLIC', CURRENT_USER )
OR
CP.GRANTEE IN
( SELECT ROLE_NAME
FROM ENABLED_ROLES ) ) )"

In other words, in standard SQL, CREATE TABLE ... LIKE
requires SELECT, while SHOW COLUMNS requires any privilege.

See also
https://intranet.mysql.com/worklog/Server-Sprint/?tid=173
https://intranet.mysql.com/secure/mailarchive/mail.php?folder=104&mail=99596
[11 Jan 2008 9:52] Kristofer Pettersson
Update on the conclusions made while working on this:

CREATE TABLE ... LIKE => Requires SELECT on the table.
SHOW COLUMNS => Requires any privileges on any column combination.
 (Note: only columns which actually have privileges are shown.)
CHECK TABLE => Any privilege on any column combination
CHECKSUM => SELECT privileges on the table.
SHOW OPEN TABLES - same as SHOW TABLES which is any privilege on any column combination.

Hence, the need for EXTRA_ACL behaving as "any privilege from a subset of privileges on any column combination" (= any requested privilege on any column combination) is not needed.

Further more I argue that using the EXTRA_ACL 'escape channel' adds unnecessary complexity, and that it should be removed completely.
[1 Feb 2008 14:38] Bugs System
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/41570

ChangeSet@1.2681, 2008-02-01 15:44:26+01:00, thek@adventure.(none) +16 -0
  Bug#27145 EXTRA_ACL troubles
  
  The flag EXTRA_ACL is used in conjugation with our access checks, yet it is
  not clear what impact this flag has.
  This is a code clean up which replaces use of EXTRA_ACL with an explicit
  function parameter.
  The patch also fixes privilege checks for:
  - SHOW CREATE TABLE: The new privilege requirement is any privilege on
    the table-level.
  - CHECKSUM TABLE: Requires SELECT on the table level.
  - SHOW CREATE VIEW: Requires SHOW_VIEW and SELECT on the table level
    (just as the manual claims)
  - SHOW INDEX: Requires any privilege on any column combination.
[1 Feb 2008 15:46] Bugs System
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/41572

ChangeSet@1.2681, 2008-02-01 16:51:47+01:00, thek@adventure.(none) +16 -0
  Bug#27145 EXTRA_ACL troubles
  
  The flag EXTRA_ACL is used in conjugation with our access checks, yet it is
  not clear what impact this flag has.
  This is a code clean up which replaces use of EXTRA_ACL with an explicit
  function parameter.
  The patch also fixes privilege checks for:
  - SHOW CREATE TABLE: The new privilege requirement is any privilege on
    the table-level.
  - CHECKSUM TABLE: Requires SELECT on the table level.
  - SHOW CREATE VIEW: Requires SHOW_VIEW and SELECT on the table level
    (just as the manual claims)
  - SHOW INDEX: Requires any privilege on any column combination.
[4 Mar 2008 11:05] Bugs System
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/43361

ChangeSet@1.2681, 2008-03-04 12:12:17+01:00, thek@adventure.(none) +20 -0
  Bug#27145 EXTRA_ACL troubles
  
  The flag EXTRA_ACL is used in conjugation with our access checks, yet it is
  not clear what impact this flag has.
  This is a code clean up which replaces use of EXTRA_ACL with an explicit
  function parameter.
  The patch also fixes privilege checks for:
  - SHOW CREATE TABLE: The new privilege requirement is any privilege on
    the table-level.
  - CHECKSUM TABLE: Requires SELECT on the table level.
  - SHOW CREATE VIEW: Requires SHOW_VIEW and SELECT on the table level
    (just as the manual claims)
  - SHOW INDEX: Requires any privilege on any column combination.
[4 Mar 2008 16:19] Bugs System
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/43400

ChangeSet@1.2681, 2008-03-04 17:26:59+01:00, thek@adventure.(none) +20 -0
  Bug#27145 EXTRA_ACL troubles
  
  The flag EXTRA_ACL is used in conjugation with our access checks, yet it is
  not clear what impact this flag has.
  This is a code clean up which replaces use of EXTRA_ACL with an explicit
  function parameter.
  The patch also fixes privilege checks for:
  - SHOW CREATE TABLE: The new privilege requirement is any privilege on
    the table-level.
  - CHECKSUM TABLE: Requires SELECT on the table level.
  - SHOW CREATE VIEW: Requires SHOW_VIEW and SELECT on the table level
    (just as the manual claims)
  - SHOW INDEX: Requires any privilege on any column combination.
[20 Mar 2008 9:47] Alexander Nozdrin
Pushed into 6.0-runtime.
[20 Apr 2008 13:01] Bugs System
Pushed into 6.0.6-alpha
[2 May 2008 2:10] Paul Dubois
Noted in 6.0.6 changelog.

Access privileges for several statements are more accurately checked:

CHECK TABLE requires some privilege for the table.
          
CHECKSUM TABLE requires SELECT for the table. 
        
CREATE TABLE ... LIKE requires SELECT for the source table and CREATE
for the destination table. 
          
SHOW COLUMNS displays information only for those columns you have
some privilege for. 
          
SHOW CREATE TABLE requires some privilege for the table (previously
required SELECT). 

SHOW CREATE VIEW requires SHOW VIEW and SELECT for the view. 

SHOW INDEX requires some privilege for any column.

SHOW OPEN TABLES displays only tables for which you have some 
privilege on any table column.
[12 Oct 2009 13:35] Bugs System
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/86581

2888 Kristofer Pettersson	2009-10-12
      Bug#27145 EXTRA_ACL troubles
      
      The flag EXTRA_ACL is used in conjugation with our access checks, yet it is
      not clear what impact this flag has.
      This is a code clean up which replaces use of EXTRA_ACL with an explicit
      function parameter.
      The patch also fixes privilege checks for:
      - SHOW CREATE TABLE: The new privilege requirement is any privilege on
        the table-level.
      - CHECKSUM TABLE: Requires SELECT on the table level.
      - SHOW CREATE VIEW: Requires SHOW_VIEW and SELECT on the table level
        (just as the manual claims)
      - SHOW INDEX: Requires any privilege on any column combination.
     @ mysql-test/r/grant.result
        * Error message now shows correct command (SHOW instead of SELECT)
     @ mysql-test/r/grant2.result
        * Error message now shows correct command (SHOW instead of SELECT)
     @ mysql-test/r/grant4.result
        * This test file tests privilege requirements for
          SHOW COLUMNS
          CREATE TABLE .. LIKE
          SHOW CREATE TABLE
          SHOW INDEX
          CHECKSUM TABLE
          SHOW CREATE VIEW
     @ mysql-test/r/information_schema_db.result
        * Added SELECT privilege to testdb_2 as
          SHOW CREATE VIEW now demands this privilege
          as well as SHOW VIEW.
     @ mysql-test/r/outfile.result
        * Changed error code
     @ mysql-test/r/view_grant.result
        * Additional SELECT privilege is now needed
          for SHOW CREATE VIEW
     @ mysql-test/t/grant4.test
        * This test file tests privilege requirements for
          SHOW COLUMNS
          CREATE TABLE .. LIKE
          SHOW CREATE TABLE
          SHOW INDEX
          CHECKSUM TABLE
          SHOW CREATE VIEW
     @ mysql-test/t/information_schema_db.test
        * Added SELECT privilege to testdb_2 as
          SHOW CREATE VIEW now demands this privilege
          as well as SHOW VIEW.
     @ mysql-test/t/outfile.test
        * Changed error code
     @ mysql-test/t/view_grant.test
        * Additional SELECT privilege is now needed
          for SHOW CREATE VIEW
     @ sql/mysql_priv.h
        * Replaced EXTRA_ACL with a parameter.
     @ sql/sp_head.cc
        * Replaced EXTRA_ACL with a parameter.
     @ sql/sql_acl.cc
        * Converted function documentation to doxygen and clarified some behaviors.
        * Changed value from uint to bool to better reflect its meaning.
        * Removed pointless variable orig_want_access
        * Added function has_any_table_level_privileges to help with requirements
          checks during SHOW CREATE TABLE.
     @ sql/sql_acl.h
        * changed signature of check_grant()
        * introduced access control function has_any_table_leevl_privileges()
     @ sql/sql_base.cc
        * Check_table_access has new signature
     @ sql/sql_cache.cc
        * Check_table_access has new signature
     @ sql/sql_parse.cc
        * Rewrote function documentation in doxygen comments for: check_access,
          check_table_acces, check_grant.
        * Removed EXTRA_ACL flag where it doesn't hold any meaningful purpose anymore
          and replaced it with a function parameter where any privileges on any column
          combination would satisfy the requirement.
        * Fixed privilege check for SHOW COLUMNS and SHOW INDEX
        * Modified check_table_access to gain clarity in what EXTRA_ACL actually does.
        * Modified check_access to gain clarity in what EXTRA_ACL actually does.
        * Fixed privilege check for CREATE TABLE .. LIKE .. ; It now requires SELECT
          privileges on the table.
        * Fixed privilege check for SHOW CREATE TABLE ..; It now requires any privilege
          on the table level.
     @ sql/sql_plugin.cc
        * check_table_access has new signature
     @ sql/sql_prepare.cc
        * Check_table_access has new signature
     @ sql/sql_show.cc
        * check_table_access has new signature
     @ sql/sql_trigger.cc
        * check_table_access has new signature
     @ sql/sql_update.cc
        * check grant has new signature
     @ sql/sql_view.cc
        * check_table_access has new signature
[12 Oct 2009 13:35] Kristofer Pettersson
Patch is now ported to next-mr-runtime as per request. New review is needed because of complexity.
[19 Oct 2009 12:59] Bugs System
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/87298

2888 Kristofer Pettersson	2009-10-19
      Bug#27145 EXTRA_ACL troubles
      
      The flag EXTRA_ACL is used in conjugation with our access checks, yet it is
      not clear what impact this flag has.
      This is a code clean up which replaces use of EXTRA_ACL with an explicit
      function parameter.
      The patch also fixes privilege checks for:
      - SHOW CREATE TABLE: The new privilege requirement is any privilege on
        the table-level.
      - CHECKSUM TABLE: Requires SELECT on the table level.
      - SHOW CREATE VIEW: Requires SHOW_VIEW and SELECT on the table level
        (just as the manual claims)
      - SHOW INDEX: Requires any privilege on any column combination.
     @ mysql-test/r/grant.result
        * Error message now shows correct command (SHOW instead of SELECT)
     @ mysql-test/r/grant2.result
        * Error message now shows correct command (SHOW instead of SELECT)
     @ mysql-test/r/grant4.result
        * This test file tests privilege requirements for
          SHOW COLUMNS
          CREATE TABLE .. LIKE
          SHOW CREATE TABLE
          SHOW INDEX
          CHECKSUM TABLE
          SHOW CREATE VIEW
     @ mysql-test/r/information_schema_db.result
        * Added SELECT privilege to testdb_2 as
          SHOW CREATE VIEW now demands this privilege
          as well as SHOW VIEW.
     @ mysql-test/r/outfile.result
        * Changed error code
     @ mysql-test/r/view_grant.result
        * Additional SELECT privilege is now needed
          for SHOW CREATE VIEW
     @ mysql-test/t/grant4.test
        * This test file tests privilege requirements for
          SHOW COLUMNS
          CREATE TABLE .. LIKE
          SHOW CREATE TABLE
          SHOW INDEX
          CHECKSUM TABLE
          SHOW CREATE VIEW
     @ mysql-test/t/information_schema_db.test
        * Added SELECT privilege to testdb_2 as
          SHOW CREATE VIEW now demands this privilege
          as well as SHOW VIEW.
     @ mysql-test/t/outfile.test
        * Changed error code
     @ mysql-test/t/view_grant.test
        * Additional SELECT privilege is now needed
          for SHOW CREATE VIEW
     @ sql/mysql_priv.h
        * Replaced EXTRA_ACL with a parameter
     @ sql/sp_head.cc
        * Replaced EXTRA_ACL with a parameter
     @ sql/sql_acl.cc
        * Converted function documentation to doxygen and clarified some behaviors.
        * Changed value from uint to bool to better reflect its meaning.
        * Removed pointless variable orig_want_access
        * Added function has_any_table_level_privileges to help with requirements
          checks during SHOW CREATE TABLE.
     @ sql/sql_acl.h
        * changed signature of check_grant()
        * introduced access control function has_any_table_leevl_privileges()
     @ sql/sql_base.cc
        * Check_table_access has new signature
     @ sql/sql_cache.cc
        * Check_table_access has new signature
     @ sql/sql_parse.cc
        * Rewrote function documentation in doxygen comments for: check_access,
          check_table_acces, check_grant.
        * Removed EXTRA_ACL flag where it doesn't hold any meaningful purpose anymore
          and replaced it with a function parameter where any privileges on any column
          combination would satisfy the requirement.
        * Fixed privilege check for SHOW COLUMNS and SHOW INDEX
        * Modified check_table_access to gain clarity in what EXTRA_ACL actually does.
        * Modified check_access to gain clarity in what EXTRA_ACL actually does.
        * Fixed privilege check for CREATE TABLE .. LIKE .. ; It now requires SELECT
          privileges on the table.
        * Fixed privilege check for SHOW CREATE TABLE ..; It now requires any privilege
          on the table level.
     @ sql/sql_plugin.cc
        * check_table_access has new signature
     @ sql/sql_prepare.cc
        * check_table_access has new signature
     @ sql/sql_show.cc
        * check_table_access has new signature
     @ sql/sql_trigger.cc
        * check_table_access has new signature
     @ sql/sql_update.cc
        * check grant has new signature
     @ sql/sql_view.cc
        * check_table_access has new signature
[20 Oct 2009 13:40] Bugs System
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/87479

2890 Kristofer Pettersson	2009-10-20
      Bug#27145 EXTRA_ACL troubles
      
      Post merge fix for embedded server.
     @ sql/sql_acl.h
        * Added definition for has_any_table_level_privileges must be defined in embedded mode.
[2 Nov 2009 8:18] Kristofer Pettersson
Doc info: Bug is backported to mysql-next-mr-runtime, hence new status 'Patched Queued'
[3 Nov 2009 7:16] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091102151658-j9o4wgro47m5v84d) (version source revid:alik@sun.com-20091023064702-2f8jdmny61bdl94u) (merge vers: 6.0.14-alpha) (pib:13)
[3 Nov 2009 17:57] Paul Dubois
Already fixed in earlier 6.0.x release.

Setting report to NDI pending push to 5.5.x.
[10 Nov 2009 14:57] Bugs System
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/89972

2913 Kristofer Pettersson	2009-11-10
      Bug#27145 EXTRA_ACL troubles
      
      Correction of backport patch:
      * Fixed signature of check_access_table() for embedded build
      * Fixed typo for last argument in a check_access() call from UINT_MAX to 0.
[12 Nov 2009 8:18] Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091110093229-0bh5hix780cyeicl) (version source revid:mikael@mysql.com-20091103113702-p61dlwc6ml6fxg18) (merge vers: 5.5.0-beta) (pib:13)
[12 Nov 2009 20:25] Paul Dubois
Noted in 5.5.0 changelog.
[20 Nov 2009 12:54] Bugs System
Pushed into 5.6.0-beta (revid:davi.arnaut@sun.com-20091119234808-xbjpkwaxjt5x5c0b) (version source revid:jon.hauglid@sun.com-20090928163426-2lg1gofzz44xzzxf) (merge vers: 6.0.14-alpha) (pib:13)
[20 Nov 2009 12:57] Bugs System
Pushed into 6.0.14-alpha (revid:kostja@sun.com-20091120124947-yi6h2jbgw0kbciwm) (version source revid:jon.hauglid@sun.com-20090928163426-2lg1gofzz44xzzxf) (merge vers: 6.0.14-alpha) (pib:13)
[22 Nov 2009 0:11] Paul Dubois
Already fixed in 5.5.x, 6.0.x. Re-closing.
[6 Mar 2010 10:56] Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:jon.hauglid@sun.com-20090928163426-2lg1gofzz44xzzxf) (merge vers: 6.0.14-alpha) (pib:16)