Examples of Assigning Database Access Levels

Access Level Reporting

NuoDB provides system metadata tables that can be used to report database access levels for both user level access and/or role level access. The following are some examples of using this system metadata to report on access levels. In the following examples you may want to add WHERE clause criteria to report on USERNAME = 'DBA' to see what privileges the DBA user has. Or you may want to add WHERE clause criteria USERNAME <> 'DBA' to report on all users except the DBA user.

Views can be created for Examples 2-4. See CREATE VIEW.

Setup for Examples

Create a table named HOCKEY using the following command:
```
CREATE TABLE Hockey (Id BIGINT, Name STRING);
```
If the table already exists, skip to the next step.

Define Roles and Users.

/* The roles */
CREATE ROLE coach_role;
CREATE ROLE fan_role;
CREATE ROLE player_role;

/* The users */
CREATE USER coach_user PASSWORD 'coach';
CREATE USER fan_user PASSWORD 'fan';
CREATE USER player_user PASSWORD 'player';

Grant access to the Roles and Users.

/* Assign access privileges to each role for the Hockey table */
GRANT ALL ON hockey TO ROLE coach_role;
GRANT SELECT ON hockey TO ROLE fan_role;
GRANT SELECT,update ON hockey TO ROLE player_role;

/* Grant a role to each user */
GRANT coach_role TO coach_user;
GRANT fan_role TO fan_user;
GRANT player_role TO player_user;

Example 1

Create a user defined function that translates multiple access levels for reporting.

Multiple access levels for a single database object per user or role are stored as binary. The bitwise operator can be used to translate these for reporting. This allows multiple access levels for a single database object to be easily reported by using one row per object for a user or role.

/* A user or role having multiple access levels to a database object is assigned a
  privmask. For example if a user or role has both SELECT and UPDATE access to a
  table, they would be assigned a privmask value of 10 in the system.privileges
  table for that database object */

SELECT pt.name AS privname FROM system.privtypes pt WHERE (pt.id & 10) > 0;

 PRIVNAME
 ---------
  SELECT
  UPDATE

/* For reporting purposes, these access levels are translated best when they can
  be returned as one row per database object for the user or role being reported */
DROP FUNCTION IF EXISTS user.fnc_dbobject_privs;
SET DELIMITER @
Delimiter is now [@]

CREATE FUNCTION user.fnc_dbobject_privs (i_privmask INTEGER)
RETURNS STRING
AS
    VAR l_priv_list STRING='';
    FOR SELECT pt.name AS privname FROM system.privtypes pt
            WHERE (pt.id & i_privmask) > 0;
        l_priv_list=l_priv_list||','||privname;
    END_FOR;
    RETURN substr(l_priv_list,2,length(l_priv_list)-1);
END_FUNCTION;
@
SET DELIMITER ;

SELECT user.fnc_dbobject_privs(10) FROM dual;

 USER.FNC_DBOBJECT_PRIVS
 -------------------
    SELECT,UPDATE

 /* GRANT EXECUTE on the function to users or roles */
GRANT EXECUTE ON FUNCTION user.fnc_dbobject_privs TO coach_user;
COMMIT;

Example 2

This example shows the access levels assigned to roles for various database objects.

SELECT
  r.schema,
  r.rolename,
  CASE WHEN rp.objecttype = 0 THEN 'TABLE'
       WHEN rp.objecttype = 1 THEN 'VIEW'
       WHEN rp.objecttype = 2 THEN 'PROCEDURE'
       WHEN rp.objecttype = 3 THEN 'USER'
       WHEN rp.objecttype = 4 THEN 'ROLE'
       WHEN rp.objecttype = 5 THEN 'ZONE'
       WHEN rp.objecttype = 6 THEN 'SEQUENCE'
       WHEN rp.objecttype = 7 THEN 'DOMAIN'
       WHEN rp.objecttype = 8 THEN 'TEMPTABLE'
       WHEN rp.objecttype = 9 THEN 'FUNCTION'
       WHEN rp.objecttype = 10 THEN 'SCHEMA'
       ELSE cast(rp.objecttype AS STRING)
       END AS OBJECT_TYPE,
  rp.objectschema,
  rp.objectname,
  user.fnc_dbobject_privs(rp.privilegemask) AS access_level
FROM
  SYSTEM.roles r,
  SYSTEM.privileges rp
WHERE
  r.schema = rp.holderschema
  AND r.rolename = rp.holdername
  AND rp.holdertype = 4 /* ROLE */
  AND rp.privilegemask > 0
  AND r.SCHEMA <> 'SYSTEM' /* ignore the SYSTEM metadata schema */
ORDER BY
  r.schema,
  r.rolename,
  rp.objectschema,
  rp.objectname;

 SCHEMA   ROLENAME   OBJECT_TYPE  OBJECTSCHEMA  OBJECTNAME                          ACCESS_LEVEL
 ------- ----------- ------------ ------------- ----------- -------------------------------------------------------------

 HOCKEY  COACH_ROLE     TABLE        HOCKEY       HOCKEY    SELECT,INSERT,UPDATE,DELETE,ALTER,EXECUTE,TRIGGERS,PROCEDURES
 HOCKEY  FAN_ROLE       TABLE        HOCKEY       HOCKEY    SELECT
 HOCKEY  PLAYER_ROLE    TABLE        HOCKEY       HOCKEY    SELECT,UPDATE

Example 3

Access levels can also be granted directly to a user. This example lists the database users and the privileges they have for various database objects.

/* First grant access directly to each user */
GRANT SELECT,INSERT,UPDATE,DELETE ON hockey TO coach_user;
GRANT SELECT ON hockey TO fan_user;
GRANT SELECT,INSERT,UPDATE on hockey to player_user;

SELECT
  u.username,
  'USER' AS privilege_level,
  CASE WHEN p.objecttype = 0 THEN 'TABLE'
       WHEN p.objecttype = 1 THEN 'VIEW'
       WHEN p.objecttype = 2 THEN 'PROCEDURE'
       WHEN p.objecttype = 3 THEN 'USER'
       WHEN p.objecttype = 4 THEN 'ROLE'
       WHEN p.objecttype = 5 THEN 'ZONE'
       WHEN p.objecttype = 6 THEN 'SEQUENCE'
       WHEN p.objecttype = 7 THEN 'DOMAIN'
       WHEN p.objecttype = 8 THEN 'TEMPTABLE'
       WHEN p.objecttype = 9 THEN 'FUNCTION'
       WHEN p.objecttype = 10 THEN 'SCHEMA'
       ELSE cast(p.objecttype AS STRING)
       END AS object_type,
  p.objectschema,
  p.objectname,
  user.fnc_dbobject_privs(p.privilegemask) AS access_level
FROM
  system.users u,
  system.privileges p
WHERE
  u.username = p.holdername
  AND p.holdertype = 3 /* USER */
  AND objecttype <> 3 /* Every user can manage their own login, so ignore */
  AND u.username <> 'DBA'
  AND p.privilegemask > 0
ORDER BY
  u.username,
  p.objectschema,
  p.objectname;

 USERNAME   PRIVILEGE_LEVEL  OBJECT_TYPE  OBJECTSCHEMA       OBJECTNAME             ACCESS_LEVEL
 ----------- ---------------- ------------ ------------- -------------------- ---------------------------

 COACH_USER        USER         TABLE         HOCKEY     HOCKEY               SELECT,INSERT,UPDATE,DELETE
 COACH_USER        USER         FUNCTION      USER       FNC_DBOBJECT_PRIVS/1 EXECUTE
 FAN_USER          USER         TABLE         HOCKEY     HOCKEY               SELECT
 PLAYER_USER       USER         TABLE         HOCKEY     HOCKEY               SELECT,INSERT,UPDATE

Example 4

Users can also inherit access levels from roles that have been granted to them. This example includes privileges from the previous example along with privileges that the user inherited from roles.

SELECT
  u1.username,
  'USER' AS PRIVILEGE_LEVEL,
  NULL AS ROLESCHEMA,
  NULL AS ROLENAME,
  NULL AS GRANT_PRIVS,
  NULL AS DEFAULT_ROLE,
  NULL AS ROLE_STATE,
  CASE WHEN p1.objecttype = 0 THEN 'TABLE'
       WHEN p1.objecttype = 1 THEN 'VIEW'
       WHEN p1.objecttype = 2 THEN 'PROCEDURE'
       WHEN p1.objecttype = 3 THEN 'USER'
       WHEN p1.objecttype = 4 THEN 'ROLE'
       WHEN p1.objecttype = 5 THEN 'ZONE'
       WHEN p1.objecttype = 6 THEN 'SEQUENCE'
       WHEN p1.objecttype = 7 THEN 'DOMAIN'
       WHEN p1.objecttype = 8 THEN 'TEMPTABLE'
       WHEN p1.objecttype = 9 THEN 'FUNCTION'
       WHEN p1.objecttype = 10 THEN 'SCHEMA'
       ELSE cast(p1.objecttype AS STRING)
       END AS object_type,
  p1.objectschema,
  p1.objectname,
  user.fnc_dbobject_privs(p1.privilegemask) AS access_level
FROM
  system.users u1,
  system.privileges p1
WHERE
  u1.username = p1.holdername
  AND p1.holdertype = 3 /* USER */
  AND u1.username <> 'DBA'
  AND p1.privilegemask > 0
  AND objecttype <> 3
UNION
SELECT
  u2.username,
  'ROLE' AS PRIVILEGE_LEVEL,
  ur2.roleschema,
  ur2.rolename,
  CASE WHEN ur2.options = 1 THEN 'TRUE' ELSE 'FALSE' END AS GRANT_PRIVS,
  CASE WHEN ur2.defaultrole = 1 THEN 'TRUE' ELSE 'FALSE' END AS DEFAULT_ROLE,
  CASE WHEN ur2.active = 1 THEN 'ACTIVE' ELSE 'INACTIVE' END AS ROLE_STATE,
  CASE WHEN p2.objecttype = 0 THEN 'TABLE'
       WHEN p2.objecttype = 1 THEN 'VIEW'
       WHEN p2.objecttype = 2 THEN 'PROCEDURE'
       WHEN p2.objecttype = 3 THEN 'USER'
       WHEN p2.objecttype = 4 THEN 'ROLE'
       WHEN p2.objecttype = 5 THEN 'ZONE'
       WHEN p2.objecttype = 6 THEN 'SEQUENCE'
       WHEN p2.objecttype = 7 THEN 'DOMAIN'
       WHEN p2.objecttype = 8 THEN 'TEMPTABLE'
       WHEN p2.objecttype = 9 THEN 'FUNCTION'
       WHEN p2.objecttype = 10 THEN 'SCHEMA'
       ELSE cast(p2.objecttype AS STRING)
       END AS OBJECT_TYPE,
  p2.objectschema,
  p2.objectname,
  user.fnc_dbobject_privs(p2.privilegemask) AS access_level
FROM
  SYSTEM.users u2,
  SYSTEM.userroles ur2,
  SYSTEM.privileges p2
WHERE
  u2.username = ur2.username
  AND ur2.rolename = p2.holdername
  AND p2.holdertype = 4 /* ROLE */
  AND p2.holderschema = ur2.roleschema
  AND p2.privilegemask > 0
  AND ur2.roleschema <> 'SYSTEM'
  AND ur2.rolename <> 'DBA'
UNION
SELECT
  u3.username,
  'DBA_ROLE' AS PRIVILEGE_LEVEL,
  ur3.roleschema,
  ur3.rolename,
  CASE WHEN ur3.options = 1 THEN 'TRUE' ELSE 'FALSE' END AS GRANT_PRIVS,
  CASE WHEN ur3.defaultrole = 1 THEN 'TRUE' ELSE 'FALSE' END AS DEFAULT_ROLE,
  CASE WHEN ur3.active = 1 THEN 'ACTIVE' ELSE 'INACTIVE' END AS ROLE_STATE,
  'ALL' AS OBJECT_TYPE,
  'SYSTEM' AS objectschema,
  'ALL' AS objectname,
  'N/A' AS access_level
FROM
  SYSTEM.users u3,
  SYSTEM.userroles ur3
WHERE
  u3.username = ur3.username
  AND ur3.roleschema = 'SYSTEM'
  AND ur3.rolename = 'DBA'
ORDER BY
  privilege_level,
  username,
  roleschema,
  rolename,
  objectschema,
  objectname;

  USERNAME   PRIVILEGE_LEVEL  ROLESCHEMA   ROLENAME   GRANT_PRIVS  DEFAULT_ROLE  ROLE_STATE  OBJECT_TYPE  OBJECTSCHEMA       OBJECTNAME                              ACCESS_LEVEL
 ----------- ---------------- ----------- ----------- ------------ ------------- ----------- ------------ ------------- -------------------- -------------------------------------------------------------

 DBA             DBA_ROLE       SYSTEM    DBA            TRUE         TRUE         ACTIVE      ALL           SYSTEM     ALL                  N/A
 COACH_USER      ROLE           HOCKEY    COACH_ROLE     FALSE        TRUE         ACTIVE      TABLE         HOCKEY     HOCKEY               SELECT,INSERT,UPDATE,DELETE,ALTER,EXECUTE,TRIGGERS,PROCEDURES
 FAN_USER        ROLE           HOCKEY    FAN_ROLE       FALSE        TRUE         ACTIVE      TABLE         HOCKEY     HOCKEY               SELECT
 PLAYER_USER     ROLE           HOCKEY    PLAYER_ROLE    FALSE        TRUE         ACTIVE      TABLE         HOCKEY     HOCKEY               SELECT,UPDATE
 COACH_USER      USER           <null>    <null>         <null>       <null>       <null>      TABLE         HOCKEY     HOCKEY               SELECT,INSERT,UPDATE,DELETE
 COACH_USER      USER           <null>    <null>         <null>       <null>       <null>      FUNCTION      USER       FNC_DBOBJECT_PRIVS/1 EXECUTE
 FAN_USER        USER           <null>    <null>         <null>       <null>       <null>      TABLE         HOCKEY     HOCKEY               SELECT
 PLAYER_USER     USER           <null>    <null>         <null>       <null>       <null>      TABLE         HOCKEY     HOCKEY               SELECT,INSERT,UPDATE

Example 5

The following query shows how to determine who has CREATE privileges for the USER schema:

SELECT
  holdername
FROM
  system.privileges p,
  system.privtypes t
WHERE
  objectname = 'USER'
  AND t.name = 'CREATE'
  AND p.privilegemask & t.id = t.id;

HOLDERNAME
-----------
     DBA