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 rules for usernames

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

• The username must be unique 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 rules:

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

  • When you create a user in 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

If you have the CREATE USER privilege, we recommend that you adhere to the principle of minimum privileges when you create users, that is, grant only the minimum privileges required by the users 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

Notes:

• user_name: the username. If a user with the same name already exists, the system returns an error.

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

• IDENTIFIED BY: This parameter is required in Oracle mode to provide the user with a password.

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

• PROFILE: the profile used by the user. If this parameter is not specified, the DEFAULT configuration file is used. By default, all settings in the DEFAULT configuration file are not limited.

• DEFAULT TABLESPACE table_space: the default tablespace of the user. This parameter is generally meaningless.

• PRIMARY_ZONE: the primary zone of the user.

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.