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 thesysadmin
role besides the defaultpublic
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 theLoad
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:
- 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:
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
.