Create a user

This topic describes how to create a user.

Privilege 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 Modify user privileges.

Naming conventions for usernames

When you specify a name for a user, note the following:

• Unique username in a tenant

  Usernames are unique within a tenant, but users in different tenants can have the same username. Therefore, a user is uniquely identified globally in the system 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.

The SQL statement is as follows:

   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: specifies the username. If a user with the same name already exists, the system will report an error.

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

• IDENTIFIED BY: specifies a password for the user. This parameter is required in Oracle mode.

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

• PROFILE: specifies 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: specifies the default tablespace for the user. This parameter is generally meaningless.

• PRIMARY_ZONE: specifies 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 using 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.