Note
- In OceanBase Database V4.3.x, this view is introduced since V4.3.1.
- In OceanBase Database V4.2.x, this view is introduced since V4.2.3.
Purpose
The mysql.default_roles view displays the roles that are enabled for a user by default.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| HOST | varchar(255) | NO | The host name of the user. |
| USER | varchar(128) | NO | The username of the user. |
| DEFAULT_ROLE_HOST | varchar(255) | NO | The host name of the role that is enabled by default. |
| DEFAULT_ROLE_USER | varchar(128) | NO | The username of the role that is enabled by default. |
Sample query
Query the roles that are enabled by default for the user001 user.
- Create a role named
role1.
obclient [oceanbase]> CREATE ROLE IF NOT EXISTS role1;
Query OK, 0 rows affected, 1 warning
- Grant the
ALTER SYSTEMprivilege torole1.
obclient [oceanbase]> GRANT ALTER SYSTEM ON *.* TO role1;
- Create a user named
user001.
obclient [oceanbase]> CREATE USER 'user001'@'%' IDENTIFIED BY '******';
- Grant
role1touser001.
obclient [oceanbase]>GRANT role001 TO user001 WITH ADMIN OPTION;;
- Specify to enable all roles granted to the
user001user at login.
obclient > SET DEFAULT ROLE ALL TO user001;
- Query the roles that are enabled by default for the
user001user.
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)