User Management

You can create, modify, or remove users in the Oracle NoSQL Database through the SQL CLI, where the commands for manipulating users are exposed in SQL format through DDL API. To start the SQL CLI, see Starting the SQL shell. You can also display information about a specific user account, as well as get a summary list of registered users. For more information, see the next sections describing each user management operation.

All user passwords should follow the password security policies. For more information see Password Complexity Policies.

User Creation

To create a user, use the following command: 

CREATE USER user_name 
 (IDENTIFIED BY password
  [PASSWORD EXPIRE | PASSWORD LIFETIME duration_time_unit])
  [ACCOUNT LOCK|UNLOCK] 
  [ADMIN]

where:

  • user_name

    Assigns a name to identify a user. If you are creating a Kerberos user, the user_name must match the fully qualified principal name created in the Key Distribution Center (KDC) at your site. A username is an ID, just as a table name. The formal definition for each ID is as follows: 
    ALPHA (ALPHA | DIGIT | UNDER)* ;
    Each ID must start with a letter (a-z, A-Z), followed by other letters, numerical values (0 - 9), and underscore (_) characters. There is no ID size limit for the number of characters it contains. An an ID can consist of as many characters as the memory required to accommodate its length. In practice, most sites have name length recommendations, but they are not checked or enforced by Oracle NoSQL Database.

    Kerberos users must have different names from existing users, since you cannot change the authentication type of an existing user.

  • IDENTIFIED BY "password"

    Indicates that Oracle NoSQL Database authenticates the new user by the password you assign. The new user must log on using that password.

    Note:

    You must specify a user password with quotation marks, for example, "password".

  • PASSWORD EXPIRE

    Specifies that the assigned password has already expired. With this setting the user is forced to change the given password as soon as they initially login. They must enter a password of their choice (which meets any site requirements) before accessing Oracle NoSQL Database.

  • PASSWORD LIFETIME {INT duration_time_unit}

    Indicates the password duration unit, which is required for using the assigned password. Enter the integer time_unit as follows: 
    
time_unit : (SECONDS | MINUTES | HOURS | DAYS)

    Using zero (0) with any time unit specifies that the password never expires. Entering a negative value causes an error. If you do not specify a PASSWORD LIFETIME time_unit, the lifetime from the global configuration is used. The default for this parameter is 180 days.

    Following is a basic example of creating new user Kate, IDENTIFIED BY a password you assign to her, represented here as "password", with a PASSWORD LIFETIME duration specifying the integer unit of time as 30 DAYS. 

    sql->create USER Kate IDENTIFIED BY "<password>" PASSWORD LIFETIME:30 DAYS;

  • ACCOUNT {LOCK | UNLOCK}

    Specify ACCOUNT LOCK to lock a user's account to disable access. An Admin can use this option to remove access from a user, but retain the account. Then, as required, reinstate the user account specifying ACCOUNT UNLOCK.

  • ADMIN Clause

    Specify ADMIN to grant the user sysadmin role automatically.

User Modification

To alter a user, use the following command: 

ALTER USER user_name [IDENTIFIED BY password
[RETAIN CURRENT PASSWORD]] [CLEAR RETAINED PASSWORD] [PASSWORD EXPIRE]
[PASSWORD LIFETIME duration] [ACCOUNT UNLOCK|LOCK]

where:

  • user_name

    Name of user to alter. If specifying a Kerberos user, you can only alter the ACCOUNT clause options.

  • IDENTIFIED Clause

    Specify BY password to specify a new password for the user.

  • RETAIN CURRENT PASSWORD

    Used with BY password clause. If specified, causes the current password defined for the user to be remembered as a valid alternate password for a limited duration (24 hours by default), or until the password is explicitly cleared. Only one alternate password may be retained at a time. This option allows a password to be changed while an application is still running without affecting its operation.

  • CLEAR RETAINED PASSWORD Clause

    Erases the current alternate retained password.

  • PASSWORD EXPIRE

    Causes the user's password to expire immediately, then the user or the user having sysadmin role must change the password before attempting to log in to the database following the expiration.

  • PASSWORD LIFETIME duration

    Specify the duration that current password can be used for authentication. 

    duration: [0-9]+ unit
unit: S | M | H | SECONDS | MINUTES | HOURS | DAYS

    Note that specifying 0 time unit for PASSWORD LIFETIME will make the password as "non-expiring".

  • ACCOUNT Clause

    Specify ACCOUNT LOCK to lock the user's account and disable access. Specify ACCOUNT UNLOCK to enable the user.

If you are updating the password of an existing user, the new password should comply with the password security policies. For more information see Password Complexity Policies.

User Removal

DROP USER user_name [CASCADE]

Use the DROP USER user_name command to remove the specified user account (users cannot remove themselves), where user_name is the name of the user to drop.

If the user has existing tables, drop each of the tables first, and then drop the user. Alternatively, use the optional CASCADE option, which drops the user tables along with the user.

For example: 

sql->drop USER Kate cascade;

Dropping a user occurs immediately. If another user was accessing tables that the user owned, the tables are no longer available for DML or DDL operations.

User Status

SHOW [AS JSON] USERS| USER user_name

For example: 


sql->show users;
user:KVStoreUser[id=u3 name=Alice]
user:KVStoreUser[id=u2 name=Kate]
user:KVStoreUser[id=u1 name=Ken]

Note:

The User ID values are incremented sequentially as you add each user. They are an internal mechanism for ensuring each user is unique.

To view detailed information about a specific user: 

sql-> show user Kate;
KVStoreUser[id=u2 name=Kate] enabled=true auth-type=LOCAL current-passwd-expiration=2026-05-04 11:20:56 UTC retain-passwd=inactive granted-roles=[public, readwrite]

User Login

You can use either the -username <user> or the -security <path to security file> argument to login to the SQL CLI. For more details on logging in to the SQL client, see Starting the SQL shell.