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.

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

One or more of the examples below make use of tables defined in the HOCKEY schema, which is a schema defined in the test database. The test database is created when the QuickStart process is run. See Running the SQL QuickStart.

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 fnc_dbobject_privs;
SET DELIMITER @
CREATE FUNCTION 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 fnc_dbobject_privs(10) FROM dual;
 FNC_DBOBJECT_PRIVS 
 -------------------
    SELECT,UPDATE   
 
/* GRANT EXECUTE on the function to users or roles */
GRANT EXECUTE ON FUNCTION fnc_dbobject_privs TO coach_user;
COMMIT;

Example 2

This example shows access levels that have been granted to roles.

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'
     end as OBJECT_TYPE
  ,rp.objectschema
  ,rp.objectname
  ,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        PLAYERS   SELECT,UPDATE

Example 3

Access levels can also be granted directly to a 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'
     END AS object_type
  ,p.objectschema
  ,p.objectname
  ,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 object_type <> 'USER'
  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         FUNCTION      HOCKEY     FNC_DBOBJECT_PRIVS/1 EXECUTE                    
 COACH_USER        USER         TABLE         HOCKEY     PLAYERS              SELECT,INSERT,UPDATE,DELETE
 FAN_USER          USER         TABLE         HOCKEY     TEAMS                SELECT                     
 PLAYER_USER       USER         TABLE         HOCKEY     SCORING              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'
     END AS object_type
  ,p1.objectschema
  ,p1.objectname
  ,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 object_type <> 'USER'
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'
     END AS OBJECT_TYPE
  ,p2.objectschema
  ,p2.objectname
  ,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' 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           USER      COACH_ROLE     FALSE        TRUE         ACTIVE      TABLE         HOCKEY     HOCKEY               SELECT,INSERT,UPDATE,DELETE,ALTER,EXECUTE,TRIGGERS,PROCEDURES
 FAN_USER        ROLE           USER      FAN_ROLE       FALSE        TRUE         ACTIVE      TABLE         HOCKEY     HOCKEY               SELECT                                                       
 PLAYER_USER     ROLE           USER      PLAYER_ROLE    FALSE        TRUE         ACTIVE      TABLE         HOCKEY     PLAYERS              SELECT,UPDATE                                                
 COACH_USER      USER           <null>    <null>         <null>       <null>       <null>      FUNCTION      HOCKEY     FNC_DBOBJECT_PRIVS/1 EXECUTE                                                      
 COACH_USER      USER           <null>    <null>         <null>       <null>       <null>      TABLE         HOCKEY     PLAYERS              SELECT,INSERT,UPDATE,DELETE                                  
 FAN_USER        USER           <null>    <null>         <null>       <null>       <null>      TABLE         HOCKEY     TEAMS                SELECT                                                       
 PLAYER_USER     USER           <null>    <null>         <null>       <null>       <null>      TABLE         HOCKEY     SCORING              SELECT,INSERT,UPDATE

Example 5

The following query shows how to determine who has CREATE privileges for a particular schema:

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