Previous Next JavaScript must be enabled to correctly display this content

## User Management

You can create, modify, or remove users in the Oracle NoSQL Database through the Admin CLI, where the commands for manipulating users are exposed in SQL format through DDL API. 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 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'