Create a user

This topic describes how to create a user.

Privileges required to create a user

You may need to create users and grant them privileges in OceanBase Database as needed. To create a user, you must have the CREATE USER privilege.

By default, only cluster and tenant administrators have the CREATE USER privilege. Other users can create a user only after they are granted the CREATE USER privilege. For more information, see Grant direct privileges.

Naming conventions for usernames

When you specify a name for a user, note that:

• Unique username in a tenant

  Users in the same tenant must have unique names. However, users in different tenants can have the same name. You can globally and uniquely identify a tenant user by specifying the username in the username@tenant name format.

• Naming conventions

  • When you create a user through the OBClient or OceanBase Developer Center (ODC), the username cannot exceed 64 bytes in length.

  • When you create a user through the OceanBase Cloud Platform (OCP) console, the username must be 2 to 64 characters in length and start with a letter, and can contain letters, digits, and underscores (_).

Create a user with the minimum database privileges by using an SQL statement

You can use the CREATE USER statement to create a user. Creating a user requires the system privilege CREATE USER. When creating a user, it is recommended to adhere to the principle of least privilege, which means that each user should only have the minimum privileges necessary to perform their tasks.

Execute the following statement to create a user:

   CREATE USER user_name [host_name] IDENTIFIED BY password [REQUIRE {NONE | SSL | X509 | tls_option_list}]
 [PROFILE user_profile] [DEFAULT TABLESPACE table_space] [PRIMARY_ZONE 'zone_name']

password:
    STR_VALUE

tls_option_list:
      tls_option
    | tls_option_list, tls_option

tls_option:
      CIPHER STR_VALUE
    | ISSUER STR_VALUE
    | SUBJECT STR_VALUE

where:

• user_name indicates the username. If a user with the same name already exists, the system will report an error.

• host_name indicates the IP address of the host to which the user belongs, in the @xx.xx.xx.xx format.

• IDENTIFIED BY indicates the password for the user. This parameter is required in Oracle mode.

• REQUIRE indicates the encryption protocol for the user. Valid values: NONE, SSL, X509, and tls_option_list.

• PROFILE indicates the profile for the user. If this parameter is not specified, the DEFAULT configuration file will be used. By default, there is no limit to all settings in the DEFAULT configuration file.

• DEFAULT TABLESPACE table_space indicates the default tablespace for the user. This parameter is generally meaningless.

• PRIMARY_ZONE indicates the primary zone for the user.

  For more information about the primary zone, see Primary zone.

Create a user named test2 and grant the user only the database connection privilege.

1. Log on to an Oracle tenant of the cluster as the SYS user.

2. Execute the following statement to create a user named test2:

   obclient> CREATE USER test2 IDENTIFIED BY ******;
   

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

3. Execute the following statement to grant the database connection privilege to the test2 user:

   obclient>GRANT CREATE SESSION TO test2;
   

   For more information about the GRANT statement, see GRANT.