分配数据库访问级别的示例

访问级别报告

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 用RNAME = 'DBA' to see what privileges the DBA user has. Or you may want to add WHERE clause criteria 用RNAME <> 'DBA' to report on all users except the DBA user.

可以为示例2-4创建视图。看 创建视图.

例1

创建一个用户定义的函数,可转换用于报告的多个访问级别。

每个用户或角色的单个数据库对象的多个访问级别存储为二进制文件。按位运算符可用于转换这些报告。这允许通过使用每个对象为用户或角色使用一行来轻松报告单个数据库对象的多个访问级别。

/* 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 */选择 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 */删除功能 IF EXISTS fnc_dbobject_privs;
SET DELIMITER @
创建功能 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 ;

选择 fnc_dbobject_privs(10) FROM dual;
 FNC_DBOBJECT_PRIVS
 -------------------
    SELECT,UPDATE

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

例2.

此示例显示已授予角色的访问级别。

选择 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    选择,,UPDATE,删除,ALTER,执行,TRIGGERS,PROCEDURES
  HOCKEY FAN_ROLE       TABLE         HOCKEY        HOCKEY    选择
  HOCKEY PLAYER_ROLE    TABLE         HOCKEY        PLAYERS   选择,UPDATE

例3.

访问级别也可以直接授予用户。

选择 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        用R         FUNCTION      HOCKEY     FNC_DBOBJECT_PRIVS/1 执行
 COACH_USER        用R         TABLE         HOCKEY     PLAYERS              选择,,UPDATE,删除
 FAN_USER          用R         TABLE         HOCKEY     TEAMS                选择
 PLAYER_USER       用R         TABLE         HOCKEY     SCORING              选择,,UPDATE

例4.

用户还可以从已授予它们的角色继承访问级别。此示例包括前面示例的权限以及用户继承的权限。

选择 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
选择 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
选择 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           用R      COACH_ROLE     FALSE        TRUE         ACTIVE      TABLE         HOCKEY     HOCKEY               选择,,UPDATE,删除,ALTER,执行,TRIGGERS,PROCEDURES
 FAN_USER        ROLE           用R      FAN_ROLE       FALSE        TRUE         ACTIVE      TABLE         HOCKEY     HOCKEY               选择
 PLAYER_USER     ROLE           用R      PLAYER_ROLE    FALSE        TRUE         ACTIVE      TABLE         HOCKEY     PLAYERS              选择,UPDATE
 COACH_USER      用R           <null>    <null>         <null>       <null>       <null>      FUNCTION      HOCKEY     FNC_DBOBJECT_PRIVS/1 执行
 COACH_USER      用R           <null>    <null>         <null>       <null>       <null>      TABLE         HOCKEY     PLAYERS              选择,,UPDATE,删除
 FAN_USER        用R           <null>    <null>         <null>       <null>       <null>      TABLE         HOCKEY     TEAMS                选择
 PLAYER_USER     用R           <null>    <null>         <null>       <null>       <null>      TABLE         HOCKEY     SCORING              选择,,UPDATE

例5.

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

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