WL#12098: MySQL system users
Affects: Server-8.0
—
Status: Complete
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
Copyright (c) 2000, 2025, Oracle Corporation and/or its affiliates. All rights reserved.