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 SQL CLI. To start SQL CLI, see Starting the SQL shell.

sql-> create ROLE role_name;

sql-> 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:

sql-> create USER Ken IDENTIFIED BY "<password>";

sql-> 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. For more information, see Password Complexity Policies.

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

sql->create ROLE manager;
sql->grant WRITE_ANY to manager;
sql->create ROLE employee;
sql->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.

sql->GRANT employee TO ROLE manager;
sql->GRANT manager TO USER Kate;

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

sql->show USER Kate;
KVStoreUser[id=u2 name=Kate] enabled=true auth-type=LOCAL current-passwd-expiration=2026-05-04 11:23:48 UTC retain-passwd=inactive granted-roles=[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:

sql->drop ROLE manager;
sql->show USER Kate;
KVStoreUser[id=u2 name=Kate] enabled=true auth-type=LOCAL current-passwd-expiration=2026-05-04 11:23:48 UTC retain-passwd=inactive granted-roles=[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.