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.

The syntax for creating a user 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

Some notes about this:

• 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.

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

For example, create a user named user2 with the minimum database privilege, which is the database connection privilege.

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

2. Execute the following statement to create the user2 user:

   obclient> CREATE USER user2 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 user2:

   obclient>GRANT CREATE SESSION TO user2;
   

   For more information about the GRANT statement, see GRANT.

Create a user with the minimum database privileges in the OCP console

You can create a user in Oracle mode in the OCP console.

Prerequisites

Before you create a user, ensure that:

• You have the permissions to modify a tenant. For more information about user permissions in the OCP console, see the OCP User Guide of the corresponding version.

• Your password box contains the password of the SYS user under the tenant. For more information about the password box of an OCP user, see the OCP User Guide of the corresponding version.

Procedure

1. Log on to the OCP console.

2. In the left-side navigation pane, click Tenants to go to the Tenants page.

3. In the tenant list, click a tenant whose Tenant Mode is Oracle to go to the Overview page.

4. In the left-side navigation pane, click User Management.

5. In the upper-right corner, click Create User.

6. In the Create User dialog box, set the information about the user.

   The following table describes the parameters for setting the user.

   Parameter	Description
   User Name	The username must be 2 to 64 characters in length and start with a letter. The username can contain uppercase letters, lowercase letters, digits, and underscores (_).
   Password	The password is a string of 8 to 32 characters and contains at least two digits, two uppercase letters, two lowercase letters, and two special characters. The supported special characters are . _ + @ # $ %
   Confirm Password	Enter your password again in this field.
   Save to Password Box	Specify whether to save the user password in the password box.
   System Permissions	Optional. The parameter specifies the privileges that apply to all users.
   Roles	Optional. If you specify a role, the user obtains all privileges of the role.

   For information about the privileges specified by the System Permissions parameter, see Users and privileges.

7. Click Submit.