Create a role

This topic describes how to create roles by using the CREATE ROLE statement and how to manage privileges by role.

Prerequisites

• You have the global CREATE USER privilege.

  For more information about how to view your privileges, see View user privileges. If you do not have the required privilege, contact the administrator to obtain the privilege. For more information about how to grant privileges to a user, see Grant direct privileges.

• If the system variable read_only is set to 1 for the current tenant, which indicates that the tenant is in read-only mode, you must also have the SUPER privilege to execute the CREATE ROLE statement.

  The system variable read_only specifies whether to enable the read-only mode for a tenant. The default value is 0, which indicates that the tenant is in read/write mode. For more information, see read_only.

Example

To create a role named role1, execute the following statement:

obclient [oceanbase]> CREATE ROLE role1;

Alternatively, execute the following statement:

obclient [oceanbase]> CREATE ROLE IF NOT EXISTS role1;

In the preceding example, if you execute the statement without the IF NOT EXISTS clause, and the role1 role already exists in the tenant, an error is reported. If the IF NOT EXISTS clause is used, a warning message is returned if the error occurs.

obclient [oceanbase]> CREATE ROLE IF NOT EXISTS role1;
Query OK, 0 rows affected, 1 warning

You can query the warning message by using the SHOW WARNINGS; statement. The result is as follows:

+-------+------+---------------------------------------------+
| Level | Code | Message                                     |
+-------+------+---------------------------------------------+
| Note  | 3163 | Authorization ID 'role1'@'%' already exists |
+-------+------+---------------------------------------------+
1 row in set

What to do next

After the role is created, you can grant privileges or other roles to the role. For more information, see the following topics:

• Grant privileges to a role

• Grant a role to a user or another role

References

For more information about the CREATE ROLE statement, see CREATE ROLE.