User Management

User Creation

To create a user, use the following command:

CREATE USER user_name 
 (IDENTIFIED EXTERNALLY | 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 EXTERNALLY

Indicates that Oracle NoSQL Database will use an external mechanism to authenticate the user. Currently, Oracle NoSQL Database supports only Kerberos as an external authentication service.
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. We do not recommend using this practice.
```
kv-> execute '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 "never expired".
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:

kv-> execute '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 USER[S] [-name user_name] [-json | -json-v1]

Notice that the S on the SHOW USERS command is optional for the Admin CLI, except for DDL statements you call with execute. For DDL "SHOW USER -name user_name" shows information about a single user, and SHOW USERS displays information about all users.

Note:

The SHOW USERS command differs between the Admin CLI and the SQL CLI. This section describes the differences wherever possible. For example, the JSON options are not available for all the Admin CLI commands.

From the SQL CLI, add one of the -json flags to output the information in JSON.

From the Admin CLI, use the SHOW USERS without further qualification to list all existing users in the system. For example, here are three users in the system:


kv-> execute 'SHOW USERS'
user: id=u1 name=Ken
user: id=u2 name=Kate
user: id=u3 name=Alice

Note:

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

From the Admin CLI, specify SHOW USERS -name user_name to view detailed information about a specific user:

kv-> execute 'SHOW USERS -name Kate'
id=u2 name=kate enabled=true auth-type=LOCAL retain-passwd=inactive
granted-role=[public]

From the SQL CLI, you can omit the -name flag:

sql-> show user Kate

From this CLI, entering SHOW USER name automatically interprets name as a user_name.

To specify a Kerberos user from the Admin CLI, the returned auth-type value is EXTERNAL:

kv-> execute 'SHOW USERS krbuser@EXAMPLE.COM'
user: id=u4 name=krbuser@EXAMPLE.COM enabled=true auth-type=EXTERNAL
retain-passwd=inactive granted-roles=[readwrite, public, sysadmin]

User Login

You can use either the -username <user> or the -security <path to security file> runadmin argument to login to the admin CLI:

-username <user>

Specifies the username to log in as. This option is used in conjunction with security properties like oracle.kv.transport.
-security <path-to-security-file>

Specifies the security file that contains property settings for the login. Relative filename references within the security file are interpreted relative to the location of the security properties file. For example, if a security properties file contains the setting oracle.kv.ssl.truststore=client.trust then, the client.trust file should be in the same directory as the security properties file. If the file is named with an absolute path then it can be anywhere in the file system.

The following properties can be set in the file in addition to any of the SSL communication properties documented in the previous chapter:
```
oracle.kv.auth.username
oracle.kv.auth.wallet.dir
oracle.kv.auth.pwdfile.file 
```
where the oracle.kv.auth.wallet.dir and oracle.kv.auth.pwdfile.file properties in this file indicate the location of an EE wallet directory or CE password store file, respectively.

Note:

The oracle.kv.security Java system property can be used as an alternative mechanism for providing a security file path. Setting this system property is equivalent to adding the -security option to the command line for the Admin CLI . This property is supported by all tools as well as by the KVStore client library.

Password Management

The Admin can configure the lifetime of users’ passwords for various units of time, or make them expire immediately. When a password expires, the user needs to renew it to log in Oracle NoSQL Database successfully. All user passwords should follow the password security policies. For more information see Password Complexity Policies.

The two ways to manage passwords from expiring are as follows:

Explicit Expiration

It makes the current password expire immediately as well as the retained password, if it exists. For this user, the user must change the password before attempting to log in the database.

For example:
```
kv-> execute 'CREATE USER John IDENTIFIED BY \”password\” PASSWORD EXPIRE'
```
Password Lifetime Configuration

If a user logs into the database with John’s account, the user must input the new password for John.
```
Logged in admin as John
The password of John has expired, it is required to change the 
password.
Enter the new password:
Re-enter the new password:
```
Password lifetime limits the duration that current password can be used for authentication.

Note:

This configuration only works for the current password but not the retained one.

For example:
```
kv-> execute 'ALTER USER John PASSWORD LIFETIME 15 days'
```
In the example above, the current password for user John will expire after 15 days. After expiration, if the user John attempts to log into the database, the system displays a notification to change the password.

A retained password is used to allow a password to be changed while an application is still running without affecting its operation. It is only saved by the system for a limited duration (24 hours) and there is no way to specify individual duration for each user. For retained password, only explicit expiration is supported using the following command:
```
kv->execute 'ALTER USER John CLEAR RETAINED PASSWORD' 
```