WL#12098: MySQL system users

Affects: Server-8.0   —   Status: Complete   —   Priority: Medium

Executive Summary
=================
The aim of the worklog is:

A-1: Introduce two categories for users: Power users and regular users. In turn 
     regular users can not modify any properties of power users - even if DDL 
     privileges are granted.

    - The worklog will introduce a new dynamic privilege 'SYSTEM_USER'.
      The users granted this privilege will be power users.
    
A-2: Introduce a way to restrict DDL/DML operations on one or more databases
     even if user has required global privileges.
  
    - The worklog will introduce a new feature 'partial_revokes' to restrict
      access to one or more databases. A new global system variable with the 
      same name will be introduce in order to turn ON/OFF this feature.

High-Level Description
======================
At present 'CREATE USER' privilege grants us the ability to drop, create and 
modify any user account, including root. That means a user who has 
'CREATE USER' privilege can modify or drop any account including root.
If the user has the UPDATE privilege then (s)he can modify the record in
the grant table.
If the user has DELETE privilege then (s)he can modify the record in the grant
tables. 

For instance-  
mysql@root> CREATE USER foo;
mysql@root> GRANT CREATE USER,UPDATE,DELETE ON *.* TO foo WITH GRANT OPTION;
mysql@root> GRANT SELECT ON mysql.* TO foo with grant option;

Now, foo has the ability to do the following:

mysql@foo>CREATE USER bar;
mysql@foo>ALTER USER root@localhost IDENTIFIED BY 'gibberish';
mysql@foo>DROP USER root@localhost;
mysql@foo>DELETE FROM mysql.user WHERE user = 'root';
mysql@foo>UPDATE mysql.user SET authentication_string = 'gibberish'
          WHERE user='root';

User foo will not be able to perform above last four operations
(Unless authorized to do so) once this worklog will be implemented.
Definitions
===========

- Global Privileges: DDL/DML privileges that allow object manipulation on all
                     databases. This includes administrative privileges,
                     dynamic privileges.

- Database Privileges: Restricted to a one (or more) databases.
                       They provide ability to manipulate objects and
                       data within database.

- Restrictions_list: List of tuples - (user, database, privileges).
                   Each entry in the list represents operations prohibited on
                   a given database for given user. Restrictions list implies
                   that even if user is granted GLOBAL privileges, if
                   revocation list prevents the operation, user can not perform
                   it for given database.

Functional Requirements
=======================
There are following two hard requirements:

R1: Prevent modifications to user accounts through ACL DDLs.
R2: Prevent modifications to database objects through DDL or DML.

For the sake of clarity, the functional requirements are segregated
in two groups accordingly.
-------------------------------------------------------------------------------
FR1-01: A new new dynamic privilege 'system_user' must be created.

FR1-02: User accounts with SYSTEM_USER privilege must not be
        modifiable through other user accounts who does not have SYSTEM_USER.

FR1-03: Impact on Roles :

    FR1-3.1: While granting one or more roles to grantees, if any of the roles
             has SYSTEM_USER privilege, then grantor must already have
             SYSTEM_USER privilege either directly or through a role.

    FR1-3.2: IF a role has 'SYSTEM_USER' privilege then it must not be allowed
             to SET [PERSIST[_ONLY]] as mandatory roles.

FR1-04: Impact on replication: There is no impact on replication.

FR1-05: Impact on upgrade:
    
    FR1-5.1: 'mysql_upgrade' must grant the SYSTEM_USER dynamic privilege to
             all of the users who have the SET_USER_ID privilege iff none of
             the users already have SYSTEM_USER privilege .

    FR1-5.2: If none of the user have SET_USER_ID dynamic privilege. In that
             case 'mysql_upgrade' must grant the SYSTEM_USER dynamic privilege
             to all of the users who have the SUPER privilege provided if none
             of the user already have SYSTEM_USER privilege.
    
    FR1-5.3  Upgrades from logical backups must not grant SYSTEM_USER privilege
             to the restored users even if the restored users have SUPER OR
             SET_USER_ID privilege.

FR1-06: 'SYSTEM_USER' privilege must be granted to the root user during
        database initialization.

FR1-07: Output of SHOW GRANTS must show the newly introduced privilege
        if granted.
-------------------------------------------------------------------------------
FR2-01: It must be possible to deny privileged operations on certain databases
        even if user has global privileges to perform the task. We shall call
        this partial revoke feature from here onwards.

    FR2-01.1: We must be able to configure(enable or disable) partial revoke
              feature through a system variable 'partial_revokes'.

    FR2-01.2: The default value of system variable must be OFF indicating that
              feature is turned off. This provides backward compatible
              behavior.

Note - Following requirements will be applicable when 'partial_revokes'
       feature is ON.

FR2-02: A new JSON object 'Restrictions' must be added to the 'user_attributes'
        column in the mysql.user table to store restrictions list persistently.

FR2-03: Partial revokes are scoped only at database level through this worklog.

        For instance:

        mysql@root>
            CREATE USER foo;
            GRANT INSERT ON *.* TO foo;
            --Error, not implemented yet.
            REVOKE INSERT ON mysql.user FROM foo;
            --Error, not implemented yet.
            REVOKE INSERT(USER) ON mysql.user FROM foo;

FR2-04: A user U must not be able to grant privilege P on database D
       (or any of the object within it) if there exists an entry (U, D, P) in
       restrictions list.

FR2-05: Revoking a non-existing database privilege must create a restrictions
        list entry.

        For instance :

        mysql@root>
            CREATE USER foo;
            GRANT INSERT ON *.* TO foo;
            REVOKE INSERT ON mysql.* FROM foo; -- Create partial revoke for foo
        mysql@foo>
            INSERT INTO mysql.user VALUES (...); -- Error, access denied.

FR2-06: Partial revokes must not support DB name with patterns in
        the GRANT/REVOKE statement.

FR2-07: A user must be able to grant those privileges to other users that he
        himself has. In other words, if grantor has a restrictions list
        attached, it must be passed to grantee unless grantee already has more
        privilege.

        For instance :

        mysql@root>
            CREATE USER foo, bar, baz;
            GRANT INSERT ON *.* TO foo WITH GRANT OPTION;
            GRANT INSERT ON *.* TO bar;
            -- create restrictions_list (foo, mysql, INSERT)
            REVOKE INSERT ON mysql.* FROM foo;
        mysql@foo>
            -- baz will get a restrictions_list (baz, mysql, INSERT)
            GRANT INSERT ON *.* to baz;
            -- bar will not get a restrictions_list since bar already has
            -- unrestricted ability.
            GRANT INSERT ON *.* to bar;

FR2-08: Restrictions_list of a grantee will be cleared, if a grantor without
        restrictions_list grants the privilege to the grantee.

        For instance:

        mysql@root>
            CREATE USER foo, bar;
            GRANT INSERT ON *.* TO foo WITH GRANT OPTION;
            GRANT INSERT ON *.* TO bar;
            REVOKE INSERT ON mysql.* FROM bar;
        mysql@foo>
            -- Either of the following two statements will clear the
            -- restrictions_list(bar, mysql, insert)
            GRANT INSERT ON *.* to bar;
            GRANT INSERT ON mysql.* to bar;

FR2-09: Grantee must have aggregated restrictions_list if grantor and grantee
        both have restrictions_list.

        For instance:

        mysql@root>
            CREATE USER foo, bar;
            GRANT UPDATE ON *.* TO foo WITH GRANT OPTION;
            GRANT INSERT ON *.* TO bar;
            -- create a restrictions_list (foo, mysql, update)
            REVOKE UPDATE ON mysql.* FROM foo;
            -- create a restrictions_list (bar, mysql, insert)
            REVOKE INSERT ON mysql.* FROM bar;
        mysql@foo>
            -- restrictions_list is updated with another entry
            -- { (bar, mysql, insert), (bar, mysql, update) }
            GRANT INSERT ON *.* to bar;

FR2-10: Impact on Roles :

    FR2-10.1: The restrictions_lists of the role and users must be
              aggregated in case there is difference in them(similar to FR2-09)

FR2-11: Impact on replication:

        FR2-11.1:  If grantor has restrictions list, all nodes must execute
                   corresponding grant using grantor's context. This is to
                   ensure that restrictions list propagation is consistent
                   across different nodes.

        FR2-11.2: partial_revokes feature must be turned ON on all the nodes.
                  If it is not ON in any node then REVOKE on non-existent
                  privilege will throw error on that node.

        FR2-11.3: A new binlog event flag shall be added in binlog query
                  header to propagate active roles information to secondary
                  node. If invoker information is being recorded in binlog
                  event, active roles information shall be recorded as well.

        FR2-11.4: A new binlog event flag shall be added in binlog query
                  header to prevent mysqlbinlog from generating SQL for
                  statements that require INVOKER context.

FR2-12: Impact on upgrade:

    FR2-12.1: 'mysql_upgrade' program must add the new JSON column in
              the mysql.user table with NULL as the default value.

    FR2-12.2: Server must throw error if user tries to use partial revoke
              feature while partial_revokes system variable is ON but JSON
              column is absent.

FR2-13: Output of SHOW GRANTS must show the partial revokes.
        For instance:
        GRANT INSERT ON *.* TO `foo`@`%` WITH GRANT OPTION
        REVOKE INSERT ON mysql.* from `foo`@`%`

FR2-14: --partial_revokes system variable must not be allowed to be set to OFF
        if there exists one or more entries in any restrictions list.

    FR2-14.1: --partial-revokes system variable must be turned ON automatically
              with appropriate warning in the log if there exists at least
              one restriction_list in the user_attributes column.

FR2-15: Table and column grants must work on a partially revoked database.

    For instance : SELECT on table t1 and column c1 must work even if there
                   exists a partial revoke on the database.
    mysql@root>GRANT SELECT ON *.* TO u1;
    mysql@root>REVOKE SELECT ON partial_revokes_db.* FROM u1;
    mysql@root>GRANT SELECT ON partial_revokes_db.t1 TO u1;
    mysql@root>GRANT SELECT(c1) ON partial_revokes_db.t2 TO u1;

    mysql@u1>SELECT * FROM partial_revokes_db.t1;
    mysql@u1>SELECT c1 FROM partial_revokes_db.t2;

-------------------------------------------------------------------------------
Interface Specification
=======================
1. Following global server variable will be added. Either of the
   SUPER/SYSTEM_VARIABLES_ADMIN privilege is require in order to change it.

    Name             : partial_revokes
    Scope            : Global
    Variable type    : Boolean
    Dynamic          : Yes
    Default          : OFF

2. A 'Restrictions' JSON array will be added in the user_attributes column in
   mysql.user table. It will have nested JSON objects with the information
   of privileges being revoked on databases.

   For instance:

   {
    "Restrictions" : [
          {"Database" : "ABC", "Privileges" : ["priv_A", "priv_B",...]},
          {"Database" : "XYZ", "Privileges" : ["priv_A", "priv_C",...]},
          ...
          ...
      ]
   }

High-Level Specification
========================

S-1: Restrict the user account modifications through ACL DDLs:
    ----------------------------------------------------------
    - Introduce 'system_user' privilege.

    - An account with system_user privilege can only be modified by another
      account with same privilege. This covers modifications through DDLs:
        ALTER | RENAME | DROP USER, GRANT, REVOKE

    - system_user privilege does NOT grant ability to perform any other
      operations. Respective privileges must be granted to perform various
      database operations.

    - For an existing database
        - 'mysql_upgrade' will grant 'SYSTEM_USER' privilege all users who
           are granted  'SET_USER_ID' privilege.

        - 'mysql_upgrade' will not grant 'SYSTEM_USER' privilege to any of the
          users if none of them have 'SET_USER_ID' privilege.

    - To CREATE/ALTER/DROP objects(views/procedures) with a DEFINER set to
      another user with system_user privilege, one must have system_user
      privilege herself.

S-2: Restrict the database_object modifications through DDLs/DMLs:
    --------------------------------------------------------------
    - Introduce a global boolean system variable to control the partial revoke
      behavior.
    - Add a 'restrictions' JSON array in the user_attributes column in
      mysql.user table to store the partial revokes information.
    - Turn ON the partial revoke behavior.
    - While loading ACL caches : Create new cache/Update the
      restriction_list cache.
    - GRANT: Grantor is granting the privA
                    OR
             user(grantee) activating a Role(grantor)
        - If it is an identical non-restricted grant
            - remove all existing restrictions on grantee
        - Else If it is an identical restricted grant
            - If grantee is unrestricted
                - grants after dropping the attached restrictions. (no-op)
            - Else
                - grants as it is. (no-op)
        - Else If there is difference in the restriction_list of both
            - Aggregate the restriction_list of grantor and grantee.
        - Else
            - create the default restriction object which does not restrict
              anything.

    - REVOKE: The DB level priv A from grantee
        - If grantee has the global priv A
            - If restriction_list does not exist
                - Create a restriction_list entry
            - Else
                - update the restriction_list entry
        - Else if grantee does not have global level priv A
            - If grantee does have DB level priv A
                - If grantee has restriction for priv A.
                    - Error. This is not possible.
            - Else
                - Revoke the DB level priv A.

    - REVOKE : The user level privilege(role) from grantee
        - If role has restriction_list
            - Negate the restriction_list entries of role from grantee.

    - Now, if user executes the DML/DDL on the system table
        - Global priv check
            - If user does not have priv
                - Return priv not granted
            - Else
                - If db is present in restriction list
                    - Return priv not granted
                - Else
                    - Return priv granted
        - DB priv check
            - If user has priv
                - If db is present in restriction list : DBUG ASSERT
                - Else
                    - Return priv granted
            - Else
                - Return priv not granted
        - Table priv check
            - Should be as before - because we do not check db priv
             - In case we do, see db priv check
        - Column priv check
            - Same as table priv check

Note: Refer the other design alternatives at https://tinyurl.com/y76865sn
      which were discussed before finalizing above specifications.
      [Courtesy to Harin].

S-3: Upgrade:
    ---------

    (1) system_user privilege -

        - We need at least a user who can be granted the system_user privilege.

        - If the mysql_upgrade program creates a user and grant it the
          system_user privilege by default then there is possibility of name
          collision.

        - We could choose existing users who are granted specific
          administrative privileges as a starting point.
            - We can grant the system_user privilege to root user as well
              but root is already granted privileges to CREATE_USER so
              it might well be seen as conflicting privileges are assigned to
              root user. Also, it could be possible that root user is renamed.

            - We can grant the system_user privilege to the users who are
              granted SET_USER_ID privilege for example.
              If none of the users are granted the chosen privilege then
              system_user privilege cannot be used.

    (2) partial_revokes -

        If the server is running with the old database that does not have the
        JSON column. Server will throw error if user tries partial revoke
        feature with partial_revokes system variable ON.

S-4: Replication:
    -------------
    (1) system_user privilege -
        SQL thread on slave will have the system_user privilege by default so
        that it can execute the statements that were executed on master with
        system_user privilege.

    (2) partial_revokes -

        Propagation of restrictions_lists from grantor to grantee requires
        the grantor's security context. But, SQL thread on slave has its own
        security_context(i.e. 'skip-grants user'). As a result on slave the
        grantor's (i.e. SQL thread) security context will differ from grantor
        as it was on master. Hence, restrictions_lists will not be able to
        propagate from grantor to grantee on slave.

        In order for above to work on slave, we shall set the grantor as
        invoker in the binary log. Slave will execute the GRANTs with
        invoker (i.e. grantor's security context). Thereby, restrictions_list
        will be propagated from grantor to grantee uniformly across master
        and slave.

        Further, apart from invoker information, active roles' information
        must also be available to SQL thread on slave. At present there is
        no mechanism to do so. Hence, a new binlog event flag - Q_ACTIVE_ROLES
        will be added in binlog query event header. If invoker information is
        to be recorded, active roles information will also be added. On slave
        side, SQL thread will set corresponding value in THD. GRANT/REVOKE will
        make use of this information while performing the operation.

        mysqlbinlog utility generates .sql file from binary log file. If
        --partial_revokes=ON, then outcome of GRANT/REVOKE depends on INVOKER
        user. This can not be reliably put in SQL form and hence, mysqlbinlog
        will detect such case using new flag Q_CANT_REPLAY_FOR_MYSQLBINLOG and
        stop.

S-5: Roles:
    -------
    (1) System_user privilege

      - If SYSTEM_USER is granted through role(s), it does not make grantee
        immutable from users who do not have SYSTEM_USER privilege.

        The user gets the capabilities of roles only if the former activates
        the later. The roles activation is also session specific.
        Therefore, granting a role(with SYSTEM_USER) to grantee does not make
        latter immutable from users who do not have SYSTEM_USER privilege.

      - Users with System_users privilege are not modifiable through other
        users who do not have the system_user privilege. It is likely that
        some of the roles specified in the mandatory roles may not have
        the system_user privilege. We also do not want the user root from
        promoting itself via grants of mandatory roles. Therefore, we are
        left with the following options:

        - Do not activate the mandatory roles to the users with system_user
          privilege.
        - A role that is granted SYSTEM_USER privilege can not be made
          Mandatory role.
        - Grant the mandatory roles to the users who have system_user
          privileges only if all of the roles in the mandatory roles also have
          system_user privilege.
        - Pick the roles from the mandatory roles list who are granted the
          system_user privilege and then apply only selected roles to the users
          who have system_user privilege.

        Second option is easy to implement without sacrificing anything.
        Hence we decided to go with that option.

    (2) Partial revoke

        Granting roles continues as it does now. It is when user set the active
        roles that time partial grants(if any) applies to the user with the
        usual steps as described in S-2.

        Once the roles are activated by the user, the restriction_list for the
        user will be aggregated in the ACL cache only.

S-6: Partial revoke use cases:
    -------------------------
    Following matrix evaluates the possible outcome with various combinations
    when a grantor is executing the *Statement* with the *Level* of privilege
    to the grantee *bar*. Grantee might already have been granted Global/DB
    level privilege as well as partial revoke.

    Note1 - The examples below use INSERT privilege just for understanding
            purpose. It could be any other global/db privilege.

    Note2 - The system variable 'partial_revoke' is ON.

-------------------------------------------------------------------------------
S.No| Global | DB   |Partial | Statement | Level  | Outcome
    |  priv  | priv |revoke  |           |        |
-------------------------------------------------------------------------------
01  | No     | No   | No     | GRANT     | Global | Grant global privilege
02  | No     | Yes  | No     | GRANT     | Global | Grant global privilege
03  | Yes    | NO   | Yes    | GRANT     | Global | Remove partial revoke
04  | No     | No   | No     | GRANT     |  DB    | Grant DB level privilege
05  | Yes    | No   | No     | GRANT     |  DB    | Grant DB level privilege
06  | Yes    | NO   | Yes    | GRANT     |  DB    | Remove partial revoke
07  | Yes    | NO   | Yes    | REVOKE    | Global | Revoke global privilege and
    |        |      |        |           |        | remove partial revoke
08  | Yes    | Yes  | No     | REVOKE    | Global | Revoke global privilege
09  | Yes    | No   | No     | REVOKE    | Global | Revoke global privilege
10  | No     | Yes  | No     | REVOKE    |  DB    | Revoke DB level privilege
11  | Yes    | Yes  | No     | REVOKE    |  DB    | Revoke DB level privilege
12  | Yes    | No   | No     | REVOKE    |  DB    | Create Partial revoke

13  | Yes    | Yes  | No     | GRANT     | Global | No-op
14  | Yes    | No   | No     | GRANT     | Global | No-op
15  | Yes    | Yes  | No     | GRANT     |  DB    | No-op
16  | No     | Yes  | No     | GRANT     |  DB    | No-op
17  | No     | Yes  | No     | REVOKE    | Global | No-op
18  | No     | No   | No     | REVOKE    | Global | No-op
19  | Yes    | No   | Yes    | REVOKE    |  DB    | No-op
20  | No     | No   | No     | REVOKE    |  DB    | No-op

21  | Yes    | Yes  | Yes    | GRANT     | Global | Invalid case. Error
22  | No     | Yes  | Yes    | GRANT     | Global | Invalid case. Error
23  | No     | No   | Yes    | GRANT     | Global | Invalid case. Error
24  | No     | Yes  | Yes    | GRANT     |  DB    | Similar to #22
25  | No     | No   | Yes    | GRANT     |  DB    | Similar to #23
26  | Yes    | Yes  | Yes    | GRANT     |  DB    | Similar to #21
27  | No     | No   | Yes    | REVOKE    | Global | Similar to #23
28  | No     | Yes  | Yes    | REVOKE    | Global | Similar to #22
29  | Yes    | Yes  | Yes    | REVOKE    | Global | Similar to #21
30  | No     | No   | Yes    | REVOKE    |  DB    | Similar to #23
31  | No     | Yes  | Yes    | REVOKE    |  DB    | Similar to #22
32  | Yes    | Yes  | Yes    | REVOKE    |  DB    | Similar to #21
------------------------------------------------------------------------------

1.
GRANT INSERT ON *.* TO bar;         // Grant global INSERT privilege

2.
GRANT INSERT ON mysql.* to bar;     // Grant partial privilege on a db
GRANT INSERT ON *.* TO bar;         // Grant global INSERT privilege

3.
GRANT INSERT ON *.* TO bar;         // Grant global INSERT priv
REVOKE INSERT ON mysql.* FROM bar;  // Create partial revoke
GRANT INSERT ON *.* TO bar;         // Remove partial revoke

4.
GRANT INSERT ON mysql.* TO bar;     // Grant DB level privilege

5.
GRANT INSERT ON *.* TO bar;         // Grant global INSERT priv
GRANT INSERT ON mysql.* TO bar;     // Grant DB level privilege

6.
GRANT INSERT ON *.* TO bar;         // Grant global INSERT priv
REVOKE INSERT ON mysql.* FROM bar;  // Create partial revoke
GRANT INSERT ON mysql.* TO bar;     // Remove partial revoke and
                                    // grant DB level privilege

7.
GRANT INSERT ON *.* TO bar;         // Grant global INSERT priv
REVOKE INSERT ON mysql.* FROM bar;  // Create partial revoke
REVOKE INSERT ON *.* FROM bar;      // Revoke global privilege and
                                    // remove partial revoke

8.
GRANT INSERT ON *.* TO bar;         // Grant global INSERT priv
GRANT INSERT ON mysql.* TO bar;     // Grant DB level privilege
REVOKE INSERT ON *.* FROM bar;      // Revoke global privilege

9.
GRANT INSERT ON *.* TO bar;         // Grant global INSERT priv
REVOKE INSERT ON *.* FROM bar;      // Revoke global privilege

10.
GRANT INSERT ON mysql.* TO bar;     // Grant DB level privilege
REVOKE INSERT ON mysql.* from bar;  // Revoke DB level privilege


11.
GRANT INSERT ON *.* TO bar;         // Grant global INSERT priv
GRANT INSERT ON mysql.* TO bar;     // Grant DB level privilege
REVOKE INSERT ON mysql.* from bar;  // Revoke DB level privilege

12.
GRANT INSERT ON *.* TO bar;         // Grant global INSERT priv
REVOKE INSERT ON mysql.* from bar;  // Create partial revoke

13.
GRANT INSERT ON *.* TO bar;         // Grant global INSERT priv
GRANT INSERT ON mysql.* TO bar;     // Grant DB level privilege
GRANT INSERT ON *.* TO bar;         // No-op

14.
GRANT INSERT ON *.* TO bar;         // Grant global INSERT priv
GRANT INSERT ON *.* TO bar;         // No-op

15.
GRANT INSERT ON *.* TO bar;         // Grant global INSERT priv
GRANT INSERT ON mysql.* TO bar;     // Grant DB level privilege
GRANT INSERT ON mysql.* TO bar;     // No-op

16.
GRANT INSERT ON mysql.* TO bar;     // Grant DB level privilege
GRANT INSERT ON mysql.* TO bar;     // No-op

17.
GRANT INSERT ON mysql.* TO bar;     // Grant DB level privilege
REVOKE INSERT ON *.* from bar;      // No-op

18.
REVOKE INSERT ON *.* from bar;      // No-op

19.
GRANT INSERT ON *.* TO bar;         // Grant global INSERT priv
REVOKE INSERT ON mysql.* from bar;  // Create partial revoke
REVOKE INSERT ON mysql.* from bar;  // No-op

20.
REVOKE INSERT ON mysql.* from bar;  // No-op

21.
GRANT INSERT ON *.* TO bar;         // Grant global INSERT priv
REVOKE INSERT ON mysql.* from bar;  // Create partial revoke
UPDATE mysql.db SET Insert_priv='Y' WHERE User LIKE 'bar'; // Invalid case.
GRANT INSERT ON *.* TO bar;         // Throw error. Invalid grant on mysql.* db

22.
GRANT INSERT ON *.* TO bar;         // Grant global INSERT priv
REVOKE INSERT ON mysql.* from bar;  // Create partial revoke
UPDATE mysql.user SET Insert_priv='N' WHERE user LIKE 'bar'; // Invalid case
GRANT INSERT ON *.* TO bar;         // Throw error. Invalid grant on mysql.* db

23.
GRANT INSERT ON *.* TO bar;         // Grant global INSERT priv
REVOKE INSERT ON mysql.* from bar;  // Create partial revoke
UPDATE mysql.user SET Insert_priv='N' WHERE user LIKE 'bar'; // Invalid case
GRANT INSERT ON *.* TO bar;         // Throw error. Invalid grant on mysql.* db

S-7: Representation of restriction_list structure
     --------------------------------------------
     We can represent the JSON array in following two manners:

    (1)
    "Restrictions" : [
          {"Database" : "ABC", "Privileges" : ["priv_A", "priv_B",...]},
          {"Database" : "XYZ", "Privileges" : ["priv_A", "priv_C",...]},
          ...
          ...
    ]

    Above could be represented with
    unorderd_map>
    data structure.

    Pros:
        (a) Search by database will require a single lookup.
    Cons:
        (a) Search by the db and privilege will require two lookups
        (b) Search by the privilege will require full scan.

    (2)
    "restrictions" : [
        { Database:"mysql", Privilege:"Select" },
        { Database:"accounting", Privilege: "insert" },
        { Database:"accounting", Privilege: "update" },
    ....
    ]

    Above could be represented with
    unorderd_set> data structure.
        OR
    unorderd_set> data structure.

    Pros:
        (a) Search by the db and privilege will require a single lookup.
    Cons:
        (a) Search by database or privilege will require full scan.


    Internally a bit mask is used to represent the DB level privileges.
    It will be easier to do the bit operations if we prepare the
    restriction_list bit mask as well. We can prepare the restriction_list bit
    mask with rep#1 in a single lookup on database then a scan on privilege set
    The rep#2 will require full scan (in worst case) to prepare this bit mask.
    Hence, we shall go with the rep#1.

S-8: Granting the SYSTEM_USER privilege to internal users
     ----------------------------------------------------
     Server creates three internal users mysql.session, mysql.infoschema
     and mysql.sys. Among these three users mysql.session is granted the SUPER
     and other administrative privileges. This user should not be modified
     inadvertently therefore, server will grant the SYSTEM_USER privilege to
     this user at the time of initialization or upgrade.
     mysql.infoschema and mysql.sys users are the owners of certain schema
     objects. These two users will not be granted the SYSTEM_USER privilege.

S-9: Toggling of the partial_revokes system-variable
     -----------------------------------------------
     There could be scenarios when --partial-revokes is toggled while
     some user(s) have restrictions_list attached to them.

     For instance -  Consider the following situation:

     1. --partial_revokes is set to ON.
     2. Restrictions list is attached to one or more user
     3. --partial_revokes is set to OFF.
     4. One of the users that has restrictions list attached, tries to
        grant the privilege to other user(s).

     We have 3 choices for action 4 above:

     a. Do not propagate restrictions list if --partial_revokes=OFF.

     b. Propagate restrictions list, regardless of --partial_revokes value BUT 
        do not consider restrictions list when it comes to privilege checking 
        if --partial_revokes=OFF.

     c. Do not allow --partial_revokes to be set to OFF if there exists one or
        more entries in any restrictions list - Thus avoiding 4.

     Each has pros & cons:

     For a
     ------
     Pros: Simple to understand. We can stop caring about
           mysql.user.user_attributes as far as restrictions list is concerned.

     Cons: Depending on --partial_revokes there may be behavior difference.
           E.g. grantee may end up with more privileges than grantor if grant
           was done when --partial_revokes=OFF and then --partial_revokes is
           set to ON.

     For b
     ------
     Pros: Always a consistent state regardless of --partial_revokes value.
           This way, even if --partial_revokes is set to ON later, restriction
           of grantors are passed to grantees.

     Cons: We have to maintain partial revokes in memory and in tables.
           Unnecessary overheard for customers who will not be using this
           feature.

     For c
     ------
     Pros:
         1. Simple to understand.
         2. Protects against accidental changes.
           (ON to OFF and suddenly bunch of users get more power).
         3. It will be easy to transition when we decide to turn ON the
            partial-revokes by default and deprecate system variable in future.

     Cons: May be bit more restrictive. But it is unlikely to have a use case
           where we --partial-revokes would be changed repeatedly.

    Summary: After evaluating above options we decided to go with option (c).

S-10: Wildcard Grants with partial_revokes
      ------------------------------------
 
      In case partial_revokes are ON then wild card characters in the grant 
      will not get special treatment. They will be treated literally. 
      (RB#21264)

S-11: Change in behavior:
      ------------------
    (1) A user account who is granted system_user privilege loses the ability
        to drop other users with system_user privilege if the former deletes
        itself. System_user is a dynamic_privilege and latter are updated
        instantly in the ACL map.

        mysql@root> CREATE USER foo, bar, baz;
        mysql@root> GRANT ALL ON *.* TO foo, bar;
        mysql@bar> DROP USER bar;
        mysql@bar> DROP USER foo;
        ERROR 1227 (42000): Access denied; you need (at least one of)
                            the SYSTEM_USER privilege(s) for this operation
        mysql@bar> DROP USER baz;

    (2) An user must have the SYSTEM_USER privilege in addition to SET_USER_ID 
        privilege in case the user wants to specify DEFINER attribute of a view 
        or a stored program and definer user has SYSTEM_USER privilege.
Legend
 ~~~~~~
 Added  : ──────
 Future : ------

(1) Partial Revokes
    ===============

                                                     ┌──────────────┐
                                   ┌─────────────────│Mem_root_base │
                                   │                 └──────────────┘
                                   ♦
                      ┌─────────────────────┐
                      │     {interface}     │
                      │Abstract_restrictions├────────────────────────────────┐
                      ├─────────────────────┤                                │
                      ├─────────────────────┤                                │
                      │ is_empty()          │                                │
                      │ clear()             │                                │
                      │ size()              │                                │
                      └─────────────────────┘                                │
                               ∆                                             │
                               │                                             │
       ┌───────────────────────┴---┬--------------------------┐              │
       │                           |                          |              │
       │                           |                          |              │
┌──────┴────────┐         ┌--------┴-----------┐     ┌--------┴------------┐ │
│               │         |                    |     |                     | │
│DB_restrictions│         | Table_restrictions |     | Column_restrictions | │
└─┬─────┬───────┘         └--------┬-----------┘     └---------┬-----------┘ │
  │     │ 0..1               0..1  |                      0..1 |             │
  │     │                          |                           |             │
  │     │                          ├---------------------------┘             │
  │     │                          |                                         │
  │     │                        1 |                                         │
  │     │                          ♦                                         │
  │     │                   ┌──────────────┐                                 │
  │     │              1    │              │                                 │
  │     └─────────────────◄►│ Restrictions │                                 │
  │                         └──────────────┘                                 │
  │                                                                          │
  │             ┌────────────────────────┐                                   │
  │             │   {interface}          │ *..0                              │
  │             │Restrictions_aggregator │←──────────────────────────────────┘
  │             ├────────────────────────┤ Generates aggregated restrictions
  │             ├────────────────────────┤
  │             │                        │←──────────────┐
  │             │ generate()             │   ┌────────────────────────────────┐
  │             │ require_next_level_op()│   │Restrictions_aggregator_factory │
  │             └────────────────────────┘   ├────────────────────────────────┤
  │*..0                   ∆                  ├────────────────────────────────┤
  │                       │                  │     create()                   │
  │                       │                  └────────────────────────────────┘
  │    ┌──────────────────┘--------┬-------------------┐
  │    │                           |                   |
  ↓    │                           |                   |
┌──────┴───────────────────┐       |     ┌-------------+------------------┐
│  {abstract}              │       |     |                                |
│DB_restrictions_aggregator│       |     | Column_restrictions_aggregator |
└──────────────────────────┘       |     └--------------------------------┘
        ∆                          |
        │                          |
        │               ┌----------┴--------------------┐
        │               |                               |
        │               | Table_restrictions_aggregator |
        │               └-------------------------------┘
        │
        └───────┬────────────────────────────────┐
                │                                │
                │                                │
┌───────────────┴───────────────────────┐        │
│                                       │        │
│DB_restrictions_aggregator_global_grant│        │
└───────────────────────────────────────┘        │
                ┌────────────────────────────────┤
                │                                │
┌───────────────┴────────────────────────┐       │
│                                        │       │
│DB_restrictions_aggregator_global_revoke│       │
└────────────────────────────────────────┘       │
                                                 │
                ┌────────────────────────────────┤
                │                                │
┌───────────────┴────────────────────────────┐   │
│                                            │   │
│DB_restrictions_aggregator_global_revoke_all│   │
└────────────────────────────────────────────┘   │
                                                 │
                ┌────────────────────────────────┤
                │                                │
┌───────────────┴────────────────────────┐       │
│                                        │       │
│  DB_restrictions_aggregator_db_grant   │       │
└────────────────────────────────────────┘       │
                ┌────────────────────────────────┤
                │                                │
┌───────────────┴────────────────────────┐       │
│                                        │       │
│ DB_restrictions_aggregator_db_revoke   │       │
└────────────────────────────────────────┘       │
                                                 │
                ┌────────────────────────────────┘
                │
┌───────────────┴────────────────────────┐
│                                        │
│ DB_restrictions_aggregator_set_role    │
└────────────────────────────────────────┘

  Abstract_restrictions :
  ---------------------
  A restriction_list represents a type of partial revokes on a database object.
  At present partial revokes can only be created on databases. It might be
  extended to create partial revokes on other database objects as well.
  For instance - Table_restrictions, Column_restrictions.

  Abstract_restrictions could represent either of such restrictions object.
  All restrictions object are created either on user specified mem_root object
  or self contained mem_root object.

  Db_restrictions:
  ----------------
  A concrete implement that represents the Partial revokes on databases.
  It provides some of the useful methods pertaining to Db_restrictions.

  Restrictions_aggregator:
  ------------------------
  An interface that requires requested privileges and current privileges
  of grantor as well as grantee to generate the aggregated restriction list.

  It is tightly coupled with the Abstract_restrictions interface so does the
  concrete implementations of bother interfaces.

  Aggregator fetches the grantor and grantee's restrictions list from the
  ACL_USER, it then validates both restrictions list for the current SQL
  statement. If it detects there is something pertaining to partial
  revokes then it generates the aggregated restrictions otherwise it passes
  the current statement for next level to handle.

  The concrete objects of this interface must be created through the factory
  class 'Restrictions_aggregator_factory'.

  DB_restrictions_aggregator :
  --------------------------
  An interface that implements methods which are common across the different
  types of DB restrictions aggregators.

  DB_restrictions_aggregator_global_grant:
  ----------------------------------------
  Concrete implementation that handles the aggregation of restrictions for the
  following global grant SQL statement.
  
  GRANT INSERT ON *.* TO grantee;

  DB_restrictions_aggregator_global_revoke:
  ----------------------------------------
  Concrete implementation that handles the aggregation of restrictions for the
  following global revoke SQL statement.

  REVOKE INSERT ON *.* FROM grantee;

  DB_restrictions_aggregator_global_revoke_all:
  ---------------------------------------------
  Concrete implementation that handles the aggregation of restrictions for the
  following Global revoke all SQL statement.

  REVOKE ALL INSERT ON *.* FROM grantee;

  DB_restrictions_aggregator_db_grant:
  ------------------------------------
  Concrete implementation that handles the aggregation of restrictions for the
  following DB level grant SQL statement.

  GRANT INSERT ON test.* TO grantee;

  DB_restrictions_aggregator_db_revoke:
  ------------------------------------
  Concrete implementation that handles the aggregation of restrictions for the
  following DB level revoke SQL statement.

  REVOKE INSERT ON test.* FROM grantee;

  DB_restrictions_aggregator_set_role:
  ------------------------------------
  SET ROLE statement grants the capabilities of roles to the Auth_id.
  This concrete implementation handles the aggregation of restrictions
  due to following SQL statement.
  
  SET ROLE r1, r2, ...;  
    
  Restrictions_aggregator_factory:
  -------------------------------
  A factory class that is solely responsible for creating the objects from
  Restrictions_aggregator hierarchy. It is a friend class in the concrete
  implementations of Restrictions_aggregator interfaces. Hence, only factory
  class can only create the required aggregator.

  Restrictions :
  -------------
  Composites all restrictions objects. Entities within the authorization system
  must fetch  the object of this class to know or set a specific restrictions.
  This class could implement supporting methods once it contain more than one
  type of Restrictions objects.

  For instance :
  is_exist() -  Will probe all restrictions member variables. If there exists
                at least one restriction in any of the restrictions member
                variable then return true otherwise false.

  As of now it provides limited access to DB_restrictions.

(2) SYSTEM_USER privilege:
    ======================  
    Add following two capabilities to the Seccurity_context class.
    
    - Security_context::has_gloabl_grant()
      ------------------------------------
      Checks if an authorization is granted the specified dynamic privilege
      either directly, or cumulatively through the roles hierarchy.
    
    - Security_context::can_operate_with()
      ------------------------------------
      Checks if the specified authorization id with given privileges can work
      with the current user or not.  If the authorization id has the specified 
      privilege then current user must also have the same privilege.
  
Likely code changes
===================

1. New files
   - partial_revokes.cc
   - partial_revokes.h
   - auth_util.h
   - auth_acls.cc
   - auth_acls.cc