Overview
The mysql.default_roles view displays the roles that are enabled by default for users.
Columns
Column |
Type |
Is NULL |
Description |
|---|---|---|---|
| HOST | varchar(255) | NO | Account Host Name |
| USER | varchar(128) | NO | Account Name |
| DEFAULT_ROLE_HOST | varchar(255) | NO | Host Name of the Default Enabled Role |
| DEFAULT_ROLE_USER | varchar(128) | NO | Account name of the role enabled by default |
Sample query
Query the default enabled roles for user user001.
Create a role named
role1.obclient [oceanbase]> CREATE ROLE IF NOT EXISTS role1; Query OK, 0 rows affected, 1 warningGrant the
ALTER SYSTEMprivilege to the rolerole1.obclient [oceanbase]> GRANT ALTER SYSTEM ON *.* TO role1;Create a new user named
user001.obclient [oceanbase]> CREATE USER 'user001'@'%' IDENTIFIED BY '******';Grant the role
role1to the newly created useruser001.obclient [oceanbase]>GRANT role001 TO user001 WITH ADMIN OPTION;;Specify that the user
user001is granted all roles by default upon login.obclient > SET DEFAULT ROLE ALL TO user001;Query the roles that are enabled by default for user
user001.obclient > SELECT * FROM mysql.default_roles;The query result is as follows:
+------+---------+-------------------+-------------------+ | HOST | USER | DEFAULT_ROLE_HOST | DEFAULT_ROLE_USER | +------+---------+-------------------+-------------------+ | % | user001 | % | role001 | +------+---------+-------------------+-------------------+ 1 row in set (0.001 sec)
References
- View the grant relationships between roles and users: mysql.role_edges
- For more information about user roles, see View roles.