WL#8131: Pluggable dynamic privileges

Affects: Server-8.0   —   Status: Complete

Currently privileged operations of various unrelated kinds are all abusing the
SUPER privilege for access control, e.g. both the firewall administration and
the replication slave administration require SUPER.
This makes granting the SUPER privilege a security risk as it's too coarse and
thus too powerful.

Also it's currently very hard for developers to be adding new privileges instead
since it would require altering the mysql.user table and recompiling the server
code. Thus components, plugins and server code developers are not adding
specific fine grained privileges and tend to keep abusing the SUPER privilege by
overloading it with even more meaning.

This worklog will provide a service to easily register new, granular global
privileges with the server at runtime. It will provide an easy way to GRANT and
revoke these privileges through SQL and programmatically check if they're
granted or not.

The worklog will also define a set of new granular privileges for various
aspects of what SUPER is used for today. 

D-0 A grant is a ordered tuple (privilege identifier, SQL object, authorization

D-0.1 An SQL object can be the server instance, a database, a table, a column, a
stored program, a view.

D-1 A dynamic privilege is a grant of one privilege identifier to an
authorization identifier represented as a row in a table. The privilege
identifier is called dynamic because it's loaded in runtime either by the server
component or by plugins or other components. The opposite is static privileges
which are always hard coded into the server and represented as columns rather
than rows. From a user perspective both dynamic privileges and static privileges
will be part of a static security model and be perceived as doing the same 

D-2 An access control list (ACL) or GRANT-list is a list of rows in a
system table mapping USER, HOST to a privilege identifier.

NOTE: In Oracle DB USER, HOST is refereed to as principal. Principals and
privilege IDs are related in a declarative way by defining ACL.

D-3 A role is an authorization ID which specify how ACLs can be aggregated
into an effective ACL. Roles can relate to each other in hierarchies but are not
aggregated themselves.

D-4 Effective privileges, or effective ACLs are a set of grants which
will be checked before a statement is executed. The statement SET ROLE, or
default roles, can effect the effective privileges for an authenticated user.

D-5 ACLs can be applied to different SQL object aggregation levels sometimes
referred to as security levels. There are several different categories or
security levels: Global, db, table, column, stored program.

Example: Global SELECT is specified as SELECT ON *.*
Table level SELECT is specified as SELECT ON db1.t1
Column level SELECT is specified as SELECT (c1) ON db1.t1

R-1 A plugin or component MAY be able to specify a list of dynamic
privileges as string identifiers after the plugin or component is initialized.

R-2 All ACLs created from GRANT-statements involving dynamic privileges
will be stored in the system table mysql.global_grants. Any
REVOKE-statement will subsequently remove the corresponding entry.

R-3 *removed *

R-4.1 New dynamic privileges will be introduced for server operations which
currently require the SUPER_ACL. The privileges will be group according to
plugin, code area or approximate future component structure

  - administer firewall rules for any user

  - For updating the users own firewall rules

  - Configure the audit log

  - Change system variables

  - Rotate the innodb encryption keys

  - purge binlog

  - Administer version token commands


  - Connect/disconnect to master, start replication

  - Privilege for setting variables related to setting or circumventing
privileges related to client connections.
  - This privilege replace the SUPER privilege for the following server variables:

  - Privilege for starting and stopping group replication

  - Privilege for changing effective authid when executing view, trigger,
    event or routine

CONNECTION_ADMIN, SET_USER_ID will be registered when the mysqld server
component starts. All other privilege ids will be registered with their
corresponding plugins, ie FIREWALL_* is registered when the firewall plugin is
loaded or if the privilege ID already is in a grant entry in mysql.global_grants.

R-4.3 Privilege IDs registered by plugins won't be unregistered when the plugins
are uninstalled, and the syntax will still work.

R-4.4 All privilege IDs in the mysql.global_grants table will be registered
when the FLUSH PRIVILEGE command is issued.

R-5 Only global security level ACLs of dynamic privileges should be supported.

R-6 *removed*

R-7 information_schema.user_privileges will show dynamic privilege grants

R-8 It must be possible to list effective dynamic privileges by using SHOW
GRANTS FOR x [USING y] just as any other privilege.

R-9 DROPing a authorization ID MUST also REVOKE all dynamic privileges from that
authorization ID.

R-10 SHOW PRIVILEGES will list all registered dynamic privileges as Admin

R-11 GRANT ALL statements will grant all /currently/ available privileges. GRANT
ALL will not grant access to future dynamic privileges on INSTALL PLUGIN 

R-12 SHOW GRANTS FOR x [USING y] must not show GRANT ALL as a valid grant.
Instead every single granted privilege must be listed explicitly.

R-13 The SUPER_ACL will be deprecated. Whenever the SUPER_ACL is used explicitly
in a GRANT or REVOKE statement a warning will be printed stating that the
privilege ID is deprecated.

R-14 A dynamic privilege ID will be limited to a length of 32 characters.
I-1 New semantics GRANT

    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user_specification [, user_specification] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH {GRANT OPTION | resource_option} ...]
user_specification := authid[, authid]

When ever priv_type can't be identified as a statically defined privilege
(example SELECT, UPDATE etc), it will be assumed that this is a dynamic
privilege and a dictionary lookup will be made against the dynamic privilege
identifier. If no dynamic privilege identifier can be found an error is thrown
to indicate an unknown privilege identifier.

Dynamic privileges will only be checked if the priv_level is *.*.

NOTE: GRANT ALL will only grant currently available privilege identifiers.
Future privilege identifiers won't be automatically granted because of a
previous GRANT ALL statement.

SHOW GRANTS will not show "GRANT ALL" for all global grants as it's difficult to
know what this means in an environment where privileges IDs can be loaded or
unloaded in runtime.
Instead SHOW GRANTS will list every privilege ID granted.

I-2 New semantics REVOKE

    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user [, user] ...

    FROM user [, user] ...

When ever priv_type can't be identified as a statically defined privilege
(example SELECT, UPDATE etc), it will be assumed that this is a dynamic
privilege and a dictionary lookup will be made against the dynamic privilege
identifier. If no dynamic privilege identifier can be found an error is thrown
to indicate an unknown privilege identifier.

Dynamic privileges will only be checked if the priv_level is *.*.

In case ALL PRIVILEGES is specified, all the corresponding dynamic privileges
will also be revoked.


Since GRANT OPTION is per dynamic privilege ID instead of per entire global
grant level, it wouldn't make sense to revoke all GRANT OPTION with this
statement as is the case for static global privileges. The only way to revoke a
GRANT OPTION for a dynamic privilege is to first REVOKE the privilege ID then
GRANT it again without GRANT OPTION.

There's no change for static privileges.
S-0 Two new services: dynamic_privilege_register and global_grants_check is
introduced in the mysql_server component.

S-1 New method in the dynamic_privilege_register service in the mysql_server
component [1]:
dynamic_privilege_impl::register_privilege(const char *str, size_t len)

This will store a new entry in a global dynamic privilege dictionary and reload
the system table mysql.global_grants for any ACLs which contain the
privilege id. As the storage will be a separate entity independent of the plugin
registering the new privilege.

S-2 New method in the global_grants_check service in the service API for the
  (Security_context_handle sctx, const char *str, size_t len)

Check if the currently logged in user, as specified by the sctx, has the dynamic
privilege id and return true.

S-3 New method in the dynamic_privilege_register service in the service API for the
  (const char *str, size_t len)

Unregister a previously registered privilege ID.

S-4 As a reference implementation, the firewall plugin will use the register
service to register and use a new privilege, FIREWALL_ADMIN, using S-{1,2}.

S-5 The Security_context must provide a method for checking the existence
of a dynamic privilege in the effective ACLs. It's up to the plugin or component
author to call this method at appropriate places.