Previous Next JavaScript must be enabled to correctly display this content

## Roles

In Oracle NoSQL Database a role is a set of privileges that defines the authority and responsibility of users assigned to the role. Oracle NoSQL Database provides a set of system built-in roles. Users can create new roles to group together privileges or other roles.

### System Built-in Roles

The following system roles are predefined:

- `readonly`

  Contains the READ_ANY privilege. Users with this role can read all data in the KVStore.

- `writeonly`

  Contains the WRITE_ANY privilege. Users with this role can write to the entire KVStore.

- `readwrite`

  Contains both the READ_ANY and WRITE_ANY privileges. Users with this role can both read and write the entire KVStore.

- `dbadmin`

  Contains the SYSDBA privilege. Users with this role can execute data definition operations, including table and index administration.

- `sysadmin`

  Contains the SYSDBA, SYSVIEW and SYSOPER privileges. Users with this role can execute the same operations as `dbadmin`, and have the ability of executing all Oracle NoSQL Database management tasks. A user created with the `-admin` option is granted with the `sysadmin` role besides the default `public` role.

- `writesystable`

  Contains the WRITE_SYSTEM_TABLE privilege. Users with this role can modify system tables if they have the necessary read and write privileges. The multi-region table agent is the intended user of this role. The `WRITE_SYSTEM_TABLE` privilege is needed when you need to use the `Load` program to restore records into a secure data store. See Using the Load Program for more details. Typically, normal users should not modify system tables.

- `public`

  Contains the USRVIEW and DBVIEW privileges. A default role for all Oracle NoSQL Database users, which cannot be revoked. Users with this role can login to database, view and change their own user information, as well as check and operate the plans owned by them. Users with this role can also obtain a read-only view of the data object information, for example, table names, indices, and others. Users with this role can view current topology of store using Oracle NoSQL Database Java direct driver.

### User-Defined Roles

Oracle NoSQL Database allows the user to create new roles using kvstore built-in privileges, and add new privilege groups to users by assigning defined roles to the users. To perform role and privilege granting and revocation operations, the user must have a role having SYSOPER privilege, for example, the sysadmin role.

To manage user-defined roles, use the following commands from the Admin CLI:

    kv-> execute 'CREATE ROLE role_name'

    kv-> execute 'DROP ROLE role_name'

Note:

The names of user-defined roles are case-insensitive, and are not the same as any existing privilege names or names of system built-in roles. Also, a reserved keyword cannot be used as a role name. For a list of reserved keywords, see Reserved Words in the SQL Reference Guide.

The following example shows how to create user-defined roles and grant them to, or revoke them from users:

Create two users with the following commands:

    kv-> execute 'CREATE USER Ken IDENTIFIED BY \"password\" '

    kv-> execute 'CREATE USER Kate IDENTIFIED BY \"password\" '

Note:

Use the following guidelines to define a password :

- Password must have at least 9 characters
- Password must have at least 2 upper case letters
- Password must have at least 2 special characters

Now, create two roles – `manager` with the `write_any` privilege and `employee` with the `read_any` privilege:

    kv-> execute 'CREATE ROLE manager'
    kv-> execute 'GRANT WRITE_ANY TO manager'
    kv-> execute 'CREATE ROLE employee'
    kv-> execute 'GRANT READ_ANY TO employee'

The next example shows granting role `employee` to role `manager` (sub-role of manager), and then grants role `manager` to user `Kate`. User Kate then has both `manager` and `employee` role, with both of their privileges, to `write_any` data to the store, and `read_any` data.

    kv-> execute 'GRANT employee TO ROLE manager'
    kv-> execute 'GRANT manager TO USER Kate'

Note:

Make sure the security feature is enabled for the store. Else you get the following error.

    Error: User error in query: GrantRoles failed for: Cannot grant or revoke roles. Please make sure  the security
              feature is enabled.

See Configuring Security with Securityconfig for more details.

Use the following command to see the user’s role status:

    kv-> execute 'SHOW USER Kate'
    id=u2 name=Kate enabled=true type=LOCAL retain-passwd=inactive
    granted-role=[public, manager]

Once the user drops a role, this role and its sub-roles will be revoked automatically from any users and user-defined roles having this role. However, all of its sub-roles will not be removed from the Oracle NoSQL Database.

For example:

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

Now, the `show roles` command will list the roles in the system without the 'manager' role.

If the administrator decides to drop the `manager` role, the system revokes the manager role from user Kate automatically, as well as the `employee` role. In the above example, Kate cannot perform any read or write operations.

Note:

Granting circular roles is not allowed. For example, role `manager` cannot be granted to role `employee` if role `employee` has previously been granted to role `manager`.