Previous Next JavaScript must be enabled to correctly display this content

## 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().

To start Admin CLI, see Start the Administration CLI.

### 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

### Granting Authorization Access to Namespaces

You can manage permission for users or roles to access namespaces and tables. These are the applicable permissions given to the developers and other users:

Table 9-1 Namespace Privileges and Permissions

Privilege | Description
--- | ---
`CREATE_ANY_NAMESPACE` `DROP_ANY_NAMESPACE` | Grant permission to a user or to a role to create or drop any namespace.
    GRANT CREATE_ANY_NAMESPACE TO <User|Role>;
    GRANT DROP_ANY_NAMESPACE TO <User|Role>;
`CREATE_TABLE_IN_NAMESPACE` `DROP_TABLE_IN_NAMESPACE` `EVOLVE_TABLE_IN_NAMESPACE` | Grant permission to a user or to a role to create, drop or evolve tables in a specific namespace. You can evolve tables to update table definitions, add or remove fields, or change field properties, such as a default value. You may even add a particular kind of column, like an IDENTITY column, to increment some value automatically. Only tables that already exist in the store are candidates for table evolution. For more details, see Alter Table.
    GRANT CREATE_TABLE_IN_NAMESPACE ON NAMESPACE namespace_name TO <User|Role>;
    GRANT DROP_TABLE_IN_NAMESPACE ON NAMESPACE  namespace_name TO <User|Role>;
    GRANT EVOLVE_TABLE_IN_NAMESPACE ON NAMESPACE namespace_name TO <User|Role>user_role;
`CREATE_INDEX_IN_NAMESPACE` `DROP_INDEX_IN_NAMESPACE` | Grant permission to a user or to a role to create or drop an index in a specific namespace.
    GRANT CREATE_INDEX_IN_NAMESPACE ON NAMESPACE namespace_name TO <User|Role>;
    GRANT DROP_INDEX_IN_NAMESPACE ON NAMESPACE namespace_name TO <User|Role>;
`READ_IN_NAMESPACE` `INSERT_IN_NAMESPACE` `DELETE_IN_NAMESPACE` | Grant permission to a role to read, insert, or delete items in a specific namespace.
    GRANT READ_IN_NAMESPACE ON NAMESPACE namespace_name TO <User|Role>;
    GRANT INSERT_IN_NAMESPACE ON NAMESPACE  namespace_name TO <User|Role>;
    GRANT DELETE_IN_NAMESPACE ON NAMESPACE namespace_name TO <User|Role>;
`MODIFY_IN_NAMESPACE` | Helper label for granting or revoking permissions to all DDL privileges for a specific namespace to a user or role.
    GRANT MODIFY_IN_NAMESPACE ON NAMESPACE namespace_name TO <User|Role>;
    REVOKE MODIFY_IN_NAMESPACE ON NAMESPACE namespace_name TO <User|Role>;

Grant privileges on a namespace

You can grant permissions to a role or a user on a namespace. Following is the syntax for granting permissions on a namespace:

    GRANT {Namespace-scoped privileges} ON NAMESPACE namespace_name TO <User|Role>
    Namespace-scoped privileges ::= namespace_privilege [, namespace_privilege]

where,

- namespace_privilege

  The namespace privilege that can be granted to a user or a role. For more information on the applicable privileges, see the Privilege column in the Namespace Privileges and Permissions table.

- namespace_name

  The namespace that the user wishes to access.

- <User|Role>

  The name of the KVStore user or the role of a user.

For example, you can grant read access to a user for all the tables in the namespace.

    Example:

    GRANT READ_IN_NAMESPACE ON NAMESPACE ns1 TO Kate;

Here, ns1 is the namespace and Kate is the user.

Note:

The label MODIFY_IN_NAMESPACE can be used as a helper for granting or revoking permissions to all DDL privileges for a specific namespace to a user or role.

Revoke privileges on a namespace

You can revoke the permissions from a role or a user on a namespace. Following is the syntax for revoking the permissions on a namespace.

    REVOKE {Namespace-scoped privileges} ON NAMESPACE namespace_name FROM <User|Role>
    Namespace-scoped privileges ::= namespace_privilege [, namespace_privilege]

where,

- namespace_privilege

  The namespace privilege that can be revoked from a user or a role. For more information on the applicable privileges, see the Privilege column in the Namespace Privileges and Permissions table.

- namespace_name

  The namespace that the user wishes to access.

- <User|Role>

  The name of the KVStore user or the role of a user.

For example, you can revoke the read access from a user for all the tables in the namespace.

    Example:

    REVOKE READ_IN_NAMESPACE ON NAMESPACE ns1 FROM Kate;

Here, ns1 is the namespace and Kate is the user.

Note:

The label MODIFY_IN_NAMESPACE can be used as a helper for granting or revoking permissions to all DDL privileges for a specific namespace to a user or role.
The following example shows:

1. Creation of a namespace and a table.
2. Revocation of the privilege to create any other new tables in the namespace, but allow the table to be dropped.

    Example: Namespace Scoped Privileges

    CREATE NAMESPACE IF NOT EXISTS ns1;
    GRANT MODIFY_IN_NAMESPACE ON NAMESPACE ns1 TO usersRole;
    CREATE TABLE ns1:t (id INTEGER, name STRING, primary key (id));
    INSERT INTO ns1:t VALUES (1, 'Smith');
    SELECT * FROM ns1:t;
    REVOKE CREATE_TABLE_IN_NAMESPACE ON NAMESPACE ns1 FROM usersRole;
    DROP NAMESPACE ns1 CASCADE;

Note:

You can save all of the above commands as a sql script and execute it in a single command. If you want to execute any of the above commands outside of a SQL prompt, remove the semi colon at the end.