Managing Roles, Privileges and Users
Oracle NoSQL Database provides a set of security operations, including commands to create, drop, show, grant or revoke roles to or from users, and to grant or revoke privileges to or from roles. All these statements can be executed through the SQL CLI or the Admin CLI execute
command, or the API of KVStore.execute() or KVStore.executeSync().
Role Creation
CREATE ROLE role_name
Where, role_name
is the case insensitive name of the role.
For example,
kv-> execute 'CREATE ROLE administrator'
Statement completed successfully
Role Removal
DROP ROLE role_name
Where, role_name
is the name of the role, which is case insensitive.
For example,
kv-> execute 'DROP ROLE administrator'
Statement completed successfully
Role Status
SHOW [AS JSON] ROLES | ROLE role_name
Where, role_name
is the name of the role.
List all available role names by running 'SHOW ROLES', or view the detailed information of a role if the role name is specified.
For example,
kv->execute 'SHOW ROLES'
role:name=dbadmin
role:name=public
role:name=readonly
role:name=readwrite
role:name=sysadmin
role:name=writeonly
The detailed information of a role can be viewed by specifying the role name:
kv->execute 'SHOW ROLE dbadmin'
name=dbadmin assignable=true readonly=true
granted-privileges=[SYSDBA, DBVIEW]
Note:
Assignable indicates whether this role can be explicitly granted to or revoked from a user.
Object privileges will appear in the form of PRIVILEGE(obj). For example, privilege of READ_TABLE on table 'emp' will appear as:
kv->execute 'CREATE ROLE emptablereader'
kv->execute 'GRANT READ_TABLE ON emp TO emptablereader'
kv->execute 'SHOW ROLE emptablereader'
name=emptablereader assignable=true readonly=false
granted-privileges=[READ_TABLE(emp)]
Grant Roles or Privileges
GRANT { grant_roles | grant_system_privileges
| grant_object_privileges }
grant_roles ::= role [, role]... TO { USER user | ROLE role }
grant_system_privileges ::=
{system_privilege | ALL PRIVILEGES}
[,{system_privilege | ALL PRIVILEGES}]...
TO role
grant_object_privileges ::=
{object_privileges| ALL [PRIVILEGES]}
[,{object_privileges| ALL [PRIVILEGES]}]...
ON object TO role
where:
-
role
The role that is granted.
-
user
The user to which the privileges are granted.
-
system_privileges
The system privileges that are granted.
-
object_privileges
The object privileges that are granted.
-
object
The object on which the privilege is granted. Currently only table privileges are supported.
-
ALL PRIVILEGES
Grants all of the system privileges. This is a shortcut for specifying all system privileges.
-
ALL [PRIVILEGES]
Grants all object privileges defined for the object. The keyword PRIVILEGES is provided for semantic clarity and is optional.
For example, you can grant a role with fewer privileges to one with more privileges, such as employee
to role manager
:
kv-> execute 'GRANT EMPLOYEE TO ROLE manager'
kv-> execute 'GRANT MANAGER TO USER Kate'
Statement completed successfully
If you try to grant the same role in the other direction, an error occurs:
kv-> execute 'GRANT employee to ROLE manager'
You will receive an error of "Could not recursively grant role employee to role manager" because this would lead to a cyclic definition of role manager.
The user can now add new privileges to their defined role. For example:
kv-> execute 'GRANT READ_ANY TO Kate'
For example, to grant read permission on table T1 to Kate:
kv-> execute 'GRANT READ_TABLE on T1 TO Kate'
See also notes on granting table privileges in Table Ownership.
Revoke Roles or Privileges
REVOKE { revoke_roles | revoke_system_privileges
| revoke_object_privileges}
revoke_roles ::= role [, role]... FROM { USER user | ROLE role }
revoke_system_privileges ::=
{ system_privilege | ALL PRIVILEGES }
[, {system_privilege | ALL PRIVILEGES}]...
FROM role
revoke_object_privileges ::=
{ object_privileges| ALL [PRIVILEGES] }
[, { object_privileges | ALL [PRIVILEGES] }]...
ON object FROM role
where:
-
role
The role to revoke.
-
user
The user from which the privileges are revoked.
-
system_privileges
The system privileges to revoke.
-
object_privileges
The object privileges to revoke.
-
object
The table from which the privileges are revoked. Currently, the only objects supported are tables.
-
ALL PRIVILEGES
Revokes all of the system privileges that have been granted to the revokee.
-
ALL [PRIVILEGES]
Revokes all object privileges defined on the object from the revokee. The keyword PRIVILEGES is provided for semantic clarity and is optional.
For example, to revoke role 'employee' from role 'manager':
kv-> execute 'REVOKE employee FROM ROLE manager'
Statement completed successfully
To revoke the role 'manager' from user 'Kate':
kv-> execute 'REVOKE manager FROM USER Kate'
Statement completed successfully