WL#10895: INFORMATION_SCHEMA views for Roles
Affects: Server-8.0
—
Status: Complete
There's a community request for ease of use views which covers: A. List of roles enabled for the current session B. List of default roles C. List of global roles D. List of users for a given role The SQL standards defines following INFORMATION_SCHEMA (I_S) tables. These standard tables cover the A) and D) from above list. This WL aims to implement all/some of following tables plus some additional non-standard I_S tables, if SQL standard does not serve all the requirements listed above. INFORMATION_SCHEMA.APPLICABLE_ROLES INFORMATION_SCHEMA.ADMINISTRABLE_ROLE_AUTHORIZATIONS INFORMATION_SCHEMA.ENABLED_ROLES INFORMATION_SCHEMA.ROLE_TABLE_GRANTS INFORMATION_SCHEMA.ROLE_COLUMN_GRANTS INFORMATION_SCHEMA.ROLE_ROUTINE_GRANTS INFORMATION_SCHEMA.ROLE_USAGE_GRANTS [Following are few I_S table that are not feasible to be implemented as of now. Mainly because the concepts of TABLE_METHOD and User Defined Data Type (UDF) are not supported by MYSQL] INFORMATION_SCHEMA.ROLE_TABLE_METHOD_GRANTS INFORMATION_SCHEMA.ROLE_UDT_GRANTS
FR1 Implement INFORMATION_SCHEMA.APPLICABLE_ROLES, which
Show roles that are applicable for current user.
FR2 INFORMATION_SCHEMA.ADMINISTRABLE_ROLE_AUTHORIZATIONS to show
roles that are applicable for current user, which can be granted
to other users.
FR3 Implement INFORMATION_SCHEMA.ENABLED_ROLES to show roles that
are enabled for current user.
FR4 Implement INFORMATION_SCHEMA.ROLE_TABLE_GRANTS to show table
grants for roles that are enabled for current user.
FR5 Implement INFORMATION_SCHEMA.ROLE_COLUMN_GRANTS to show
column grants for roles that are enabled for current user.
FR6 Implement INFORMATION_SCHEMA.ROLE_ROUTINE_GRANTS to
show routine grants for roles that are enabled for current user.
FR7 Implement INFORMATION_SCHEMA.APPLICABLE_ROLES such that
it show roles dependencies.
FR8 MySQL upgrade from older database should be able to create
new I_S views introduced by this WL.
I. INTRODUCTION:
````````````````
a) This WL aim to implement following I_S views as per the SQL
standard notations.
1 INFORMATION_SCHEMA.APPLICABLE_ROLES
- Show roles that are applicable for current user.
2 INFORMATION_SCHEMA.ADMINISTRABLE_ROLE_AUTHORIZATIONS
- Show roles that are applicable for current user, which
can be granted to other users.
3 INFORMATION_SCHEMA.ENABLED_ROLES
- Show roles that are enabled for current user.
4 INFORMATION_SCHEMA.ROLE_TABLE_GRANTS
- Show table grants for roles that are enabled for current user.
5 INFORMATION_SCHEMA.ROLE_COLUMN_GRANTS
- Show column grants for roles that are enabled for current user.
6 INFORMATION_SCHEMA.ROLE_ROUTINE_GRANTS
- Show routine grants for roles that are enabled for current user.
b) Following are few I_S table defined in SQL standard that are
not feasible to be implemented as of now in MySQL . Mainly
because the concepts of TABLE_METHOD and User Defined Data
Type (UDF) are not supported by MYSQL.
- INFORMATION_SCHEMA.ROLE_TABLE_METHOD_GRANTS
- INFORMATION_SCHEMA.ROLE_UDT_GRANTS
Also it is not feasible to implement INFORMATION_SCHEMA.ROLE_USAGE_GRANTS
table because SQL standards says following about it. And MySQL
does not support the listed database objects.
...
A privilege descriptor with an of USAGE is called a
usage privilege descriptor and describes a privilege on an object
of one of the following kinds:
— A domain.
— A user-defined type.
— A character set.
— A collation.
— A transliteration.
— A sequence generator.
- INFORMATION_SCHEMA.ROLE_USAGE_GRANTS
II. DESIGN:
```````````
This section discuss design of I_S tables listed in above section.
The phrase 'SQL Standard' used below refers to IWD 9075-11:2016(E).
All the GRANTEE/ROLE_NAME below will be of the form 'user@hostname'.
This is a "MySQL" way of representing a user/role. The following
I_S tables will show both user and hostname component in a separate
I_S column. E.g.,
-> GRANTEE & GRANTEE_HOST
-> ROLE_NAME & ROLE_HOST
Where GRANTEE_HOST and ROLE_HOST would be a MySQL specific
I_S column definition.
See LLD for low level information of I_S view definition.
1) INFORMATION_SCHEMA.APPLICABLE_ROLES
SQL Standard: Identifies the applicable roles for the current
user.
- It also tells is the applicable role is grantable to other
users.
- The information required for this view is present in
mysql.role_edges.
- Role names that are granted to role are also listed. The
role dependency graph with cycle could be displayed.
- This I_S table does not deal with PRIVILEGE objects.
- Columns exposed are:
USER, /* SQL Standard */ to which the role_name belongs to.
HOST, /* MySQL Specific */ to which the role_name belongs to.
GRANTEE, /* SQL Standard */ to whom the role_name is granted to.
GRANTEE_HOST, /* MySQL Specific */ to whom to which the role_name is granted to.
ROLE_NAME, /* SQL Standard */ Role name which is granted.
ROLE_HOST, /* MySQL Specific */ Role host which is granted.
IS_GRANTABLE, /* SQL Standard */ Is the role grantable to others ?
IS_DEFAULT, /* MySQL Specific */ Is this a default role ?
IS_MANDATORY /* MySQL Specific */ Is this a mandatory role ?
EXPECTED OUTPUT:
$ SELECT * FROM INFORMATION_SCHEMA.APPLICABLE_ROLES;
USER HOST GRANTEE GRANTEE_HOST ROLE_NAME ROLE_HOST IS_GRANTABLE IS_DEFAULT IS_MANDATORY
user2 % updater % reader % Yes No No
user2 % user2 % hr_rules % No No No
user2 % user2 % updater % No No No
2) INFORMATION_SCHEMA.ADMINISTRABLE_ROLE_AUTHORIZATIONS
SQL Standard: Identify role authorizations for which the
current user or role has WITH ADMIN/GRANT OPTION.
- The information required for this view is present in
output of I_S.APPLICABLE_ROLES defined in 1) above.
- Columns exposed are:
USER, /* SQL Standard */ to which the role_name belongs to.
HOST, /* MySQL Specific */ to which the role_name belongs to.
GRANTEE, /* SQL Standard */ to whom the role_name is granted to.
GRANTEE_HOST, /* MySQL Specific */ to whom to which the role_name is granted to.
ROLE_NAME, /* SQL Standard */ Role name which is granted.
ROLE_HOST, /* MySQL Specific */ Role host which is granted.
IS_GRANTABLE, /* SQL Standard */ Is the role grantable to others ?
IS_DEFAULT, /* MySQL Specific */ Is this a default role ?
IS_MANDATORY /* MySQL Specific */ Is this a mandatory role ?
EXPECTED OUTPUT:
$ SELECT * FROM INFORMATION_SCHEMA.ADMINISTRABLE_ROLE_AUTHORIZATIONS;
USER HOST GRANTEE GRANTEE_HOST ROLE_NAME ROLE_HOST IS_GRANTABLE IS_DEFAULT IS_MANDATORY
user2 % updater % reader % Yes No No
3) INFORMATION_SCHEMA.ENABLED_ROLES
SQL Standard: Identify the enabled roles for the current
SQL-session. This implements A) in HLD.
- The function CURRENT_ROLE() provides the information required
by ENABLED_ROLES.
- Columns exposed are:
ROLE_NAME, /* SQL Standard */ Role name which is granted.
ROLE_HOST, /* MySQL Specific */ Role host which is granted.
IS_DEFAULT, /* MySQL Specific */ Is this a default role ?
IS_MANDATORY /* MySQL Specific */ Is this a mandatory role ?
EXPECTED OUTPUT:
$ SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES;
ROLE_NAME ROLE_HOST IS_DEFAULT IS_MANDATORY
hr_rules % No No
4) INFORMATION_SCHEMA.ROLE_TABLE_GRANTS
SQL Standard: Identifies the privileges on tables defined in
this catalog that are available to or granted by the currently
applicable roles.
- The information required for this view is present in
mysql.role_edges and mysql.tables_priv metadata tables.
- Columns exposed are:
GRANTOR /* SQL Standard */ Who granted the role ?
GRANTOR_HOST /* MySQL Standard */ Who granted the role ?
GRANTEE /* SQL Standard */ to whom the role_name is granted to.
GRANTEE_HOST /* MySQL Standard */ to whom the role_name is granted to.
TABLE_CATALOG, /* SQL Standard */
TABLE_SCHEMA, /* SQL Standard */
TABLE_NAME, /* SQL Standard */
PRIVILEGE_TYPE /* SQL Standard */
IS_GRANTABLE /* SQL Standard */
EXPECTED OUTPUT:
$ SELECT * FROM INFORMATION_SCHEMA.ROLE_TABLE_GRANTS;
GRANTOR GRANTOR_HOST GRANTEE GRANTEE_HOST TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE
root localhost hr_rules % def test hr_ledger1 Select NO
root localhost reader % def test taskA_ledger1 Select NO
5) INFORMATION_SCHEMA.ROLE_COLUMN_GRANTS
SQL Standard: Identifies the privileges on table columns
defined in this catalog that are available to or granted by the
currently enabled roles.
- The information required for this view is present in
mysql.role_edges, mysql.tables_priv and mysql.column_priv
metadata tables.
- Columns exposed are:
GRANTOR /* SQL Standard */ Who granted the role ?
GRANTOR_HOST /* MySQL Standard */ Who granted the role ?
GRANTEE /* SQL Standard */ to whom the role_name is granted to.
GRANTEE_HOST /* MySQL Standard */ to whom the role_name is granted to.
TABLE_CATALOG, /* SQL Standard */
TABLE_SCHEMA, /* SQL Standard */
TABLE_NAME, /* SQL Standard */
COLUMN_NAME, /* SQL Standard */
PRIVILEGE_TYPE /* SQL Standard */
IS_GRANTABLE /* SQL Standard */
Column in MySQL is not Grantable.
The Table's IS_GRANTABLE property is inherited in this case.
EXPECTED OUTPUT:
$ SELECT * FROM INFORMATION_SCHEMA.ROLE_COLUMN_GRANTS;
GRANTOR GRANTOR_HOST GRANTEE GRANTEE_HOST TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
root localhost reader % def test taskA_ledger2 f1 Select NO
6) INFORMATION_SCHEMA.ROLE_ROUTINE_GRANTS
SQL Standard: Identify the privileges on SQL-invoked routines
defined in this catalog that are available to or granted by the
currently enabled roles.
- The information required for this view is present in
mysql.role_edges, and mysql.procs_priv metadata tables.
- Columns exposed are:
GRANTOR /* SQL Standard */ Who granted the role ?
GRANTOR_HOST /* MySQL Standard */ Who granted the role ?
GRANTEE /* SQL Standard */ to whom the role_name is granted to.
GRANTEE_HOST /* MySQL Standard */ to whom the role_name is granted to.
SPECIFIC_CATALOG, /* SQL Standard */
SPECIFIC_SCHEMA, /* SQL Standard */
SPECIFIC_NAME, /* SQL Standard */
ROUTINE_CATALOG, /* SQL Standard */
ROUTINE_SCHEMA, /* SQL Standard */
ROUTINE_NAME, /* SQL Standard */
PRIVILEGE_TYPE, /* SQL Standard */
IS_GRANTABLE /* SQL Standard */
EXPECTED OUTPUT:
$ SELECT * FROM INFORMATION_SCHEMA.ROLE_ROUTINE_GRANTS;
GRANTOR GRANTOR_HOST GRANTEE GRANTEE_HOST SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME PRIVILEGE_TYPE IS_GRANTABLE
root localhost taskLeaderA % def test dailyReport def test dailyReport Execute NO
7) New internal user defined function are added to support above implementation.
1 INTERNAL_GET_ENABLED_ROLE_JSON()
- Get the role names formated as a JSON conforming string.
2 INTERNAL_GET_USERNAME()
- Get the current user name from session. OR Retrieve the
username from the given parameter.
3 INTERNAL_GET_HOSTNAME()
- Get the current user hostname from session. OR Retrieve the
hostname from the given parameter.
4 INTERNAL_IS_ENABLED_ROLE(user, host)
- Check if the given user and host name is active in the session.
Returns true or false.
5 INTERNAL_IS_MANDATORY_ROLE()
- Check if the given user and host name is a mandatory role.
Returns true or false.
III. UPGRADE:
`````````````
As we are introducing new I_S system views, the I_S_VERSION
number of the server needs to be increased so that the upgrade
creates the new I_S system view.
IV. PERFORMANCE:
````````````````
These are new I_S system views, so there is not impact on
existing behavior.
V. DOCUMENTATION:
`````````````````
Update documents explaining newly added I_S tables by this work log.
Copyright (c) 2000, 2025, Oracle Corporation and/or its affiliates. All rights reserved.