DB2中特权/权限

77 阅读 0 评论 51 点赞

我是靠谱客的博主舒适彩虹，最近开发中收集的这篇文章主要介绍DB2中特权/权限，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

本文粗略讲述了DB2中的权限管理，包括特权/权限的分类、如何查看某个用户/组拥有的特权/权限，以及如何赋予特权/权限。

1. 特权/权限的分类

权限分类：

系统级别的权限(System-level authorization):
系统管理员（SYSADM）、系统控制（SYSCTRL）、系统维护（SYSMAINT）和系统监视（SYSMON）

数据库级别权限(Database-level authorization)
ACCESSCTRL, BINDADD, CONNECT, CREATETAB, CREATE_EXTERNAL_ROUTINE, CREATE_NOT_FENCED_ROUTINE, CREATE_SECURE_OBJECT, DATAACCESS, DBADM, EXPLAIN, IMPLICIT_SCHEMA, LOAD, QUIESCE_CONNECT, SECADM, SQLADM, WLMADM

特权(Privileges)
特权是对象级别的，比如某个表的增删改查权限

注意：系统级别和数据库级别的权限叫做authority，而对象级别的权限，叫做priviledge，为了区分，下文只使用英文描述。本文不解释每个特权和权限的作用和含义，有兴趣的，可以参考下面的链接
https://www.ibm.com/support/knowledgecenter/zh/SSEPGG_10.5.0/com.ibm.db2.luw.admin.sec.doc/doc/c0005524.html
https://www.ibm.com/support/knowledgecenter/zh/SSEPGG_10.5.0/com.ibm.db2.luw.admin.sec.doc/doc/c0005478.html
https://www.ibm.com/developerworks/cn/data/library/techarticle/dm-0601wasserman/

2. 查看系统或者数据库级别的authority

查看某个组、某个用户或者某个角色的authority，可以使用AUTH_LIST_AUTHORITIES_FOR_AUTHID表函数，它有两个参数，第一个是是名子，第二个是类型，G 表示Group，R 表示Role， U表示User

>>-AUTH_LIST_AUTHORITIES_FOR_AUTHID--(--authid--,--authidtype--)-><

例如，要查看用户 'MIAOQINGSONG'的所有authority，可以使用下面的命令：

$ db2 "SELECT substr(AUTHORITY,1,30) as AUTHORITY, D_USER, D_GROUP, D_PUBLIC, ROLE_USER, ROLE_GROUP, ROLE_PUBLIC, D_ROLE  FROM TABLE (SYSPROC.AUTH_LIST_AUTHORITIES_FOR_AUTHID ('MIAOQINGSONG', 'U') ) AS T ORDER BY AUTHORITY"

AUTHORITY                      D_USER D_GROUP D_PUBLIC ROLE_USER ROLE_GROUP ROLE_PUBLIC D_ROLE
------------------------------ ------ ------- -------- --------- ---------- ----------- ------
ACCESSCTRL                     Y      N       N        N         N          N           *
BINDADD                        N      N       Y        N         N          N           *
CONNECT                        N      N       Y        N         N          N           *
CREATETAB                      N      N       Y        N         N          N           *
CREATE_EXTERNAL_ROUTINE        N      N       N        N         N          N           *
CREATE_NOT_FENCED_ROUTINE      N      N       N        N         N          N           *
CREATE_SECURE_OBJECT           N      N       N        N         N          N           *
DATAACCESS                     Y      N       N        N         N          N           *
DBADM                          Y      N       N        N         N          N           *
EXPLAIN                        N      N       N        N         N          N           *
IMPLICIT_SCHEMA                N      N       Y        N         N          N           *
LOAD                           N      N       N        N         N          N           *
QUIESCE_CONNECT                N      N       N        N         N          N           *
SECADM                         Y      N       N        N         N          N           *
SQLADM                         N      N       N        N         N          N           *
SYSADM                         *      Y       *        *         *          *           *
SYSCTRL                        *      N       *        *         *          *           *
SYSMAINT                       *      N       *        *         *          *           *
SYSMON                         *      N       *        *         *          *           *
WLMADM                         N      N       N        N         N          N           *

  20 record(s) selected.

具体每个字段的含义，可以参考链接:
https://www.ibm.com/support/knowledgecenter/zh/SSEPGG_10.5.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0052898.html

3. 查看privileges

查看组db2users所有的privileges

$ db2 "select substr(AUTHID,1,30) as AUTHID, AUTHIDTYPE, PRIVILEGE, GRANTABLE, substr(OBJECTNAME,1,30) as OBJECTNAME, substr(OBJECTSCHEMA,1,20) as OBJECTSCHEMA, OBJECTTYPE from SYSIBMADM.PRIVILEGES where AUTHID='DB2USERS' "
AUTHID                         AUTHIDTYPE PRIVILEGE   GRANTABLE OBJECTNAME                     OBJECTSCHEMA         OBJECTTYPE
------------------------------ ---------- ----------- --------- ------------------------------ -------------------- ------------------------
DB2USERS                       G          SELECT      N         T1                             MIAOQINGSONG         TABLE
DB2USERS                       G          SELECT      N         T2                             MIAOQINGSONG         TABLE

  2 record(s) selected.

查看表T1上所有的privileges

$ db2 "select substr(AUTHID,1,30) as AUTHID, AUTHIDTYPE, PRIVILEGE, GRANTABLE, substr(OBJECTNAME,1,30) as OBJECTNAME, substr(OBJECTSCHEMA,1,20) as OBJECTSCHEMA, OBJECTTYPE from SYSIBMADM.PRIVILEGES where OBJECTNAME='T1' "

AUTHID                         AUTHIDTYPE PRIVILEGE   GRANTABLE OBJECTNAME                     OBJECTSCHEMA         OBJECTTYPE
------------------------------ ---------- ----------- --------- ------------------------------ -------------------- ------------------------
MIAOQINGSONG                   U          UPDATE      Y         T1                             MIAOQINGSONG         TABLE
MIAOQINGSONG                   U          REFERENCE   Y         T1                             MIAOQINGSONG         TABLE
DB2USERS                       G          SELECT      N         T1                             MIAOQINGSONG         TABLE
MIAOQINGSONG                   U          SELECT      Y         T1                             MIAOQINGSONG         TABLE
MIAOQINGSONG                   U          INSERT      Y         T1                             MIAOQINGSONG         TABLE
MIAOQINGSONG                   U          INDEX       Y         T1                             MIAOQINGSONG         TABLE
MIAOQINGSONG                   U          DELETE      Y         T1                             MIAOQINGSONG         TABLE
MIAOQINGSONG                   U          ALTER       Y         T1                             MIAOQINGSONG         TABLE
MIAOQINGSONG                   U          CONTROL     N         T1                             MIAOQINGSONG         TABLE

  9 record(s) selected.

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0021978.html

注意，通过这个表查询到的，只能是explict privileges, 例如上面两条查询结果表明，DB2USERS组对表T1是有SELECT privilege的，用户DB2TEST在组DB2USERS里，所以DB2TEST也对表T1有select privilege，但直接查询DB2TEST的privilege并不能看出来：

$ db2 "select substr(AUTHID,1,30) as AUTHID, AUTHIDTYPE, PRIVILEGE, GRANTABLE, substr(OBJECTNAME,1,30) as OBJECTNAME, substr(OBJECTSCHEMA,1,20) as OBJECTSCHEMA, OBJECTTYPE from SYSIBMADM.PRIVILEGES where AUTHID='DB2TEST' "

AUTHID                         AUTHIDTYPE PRIVILEGE   GRANTABLE OBJECTNAME                     OBJECTSCHEMA         OBJECTTYPE
------------------------------ ---------- ----------- --------- ------------------------------ -------------------- ------------------------

  0 record(s) selected.
  
$ db2 "terminate"
DB20000I  The TERMINATE command completed successfully.

$ db2 "connect to sample user db2test using db2test"

   Database Connection Information

 Database server        = DB2/NT64 10.5.6
 SQL authorization ID   = DB2TEST
 Local database alias   = SAMPLE

$ db2 "select * from MIAOQINGSONG.T1"

NAME                 ID
-------------------- -----------
miao                           1
qinbg                          2
sng                            3
ddd                          110
eee                          110

  5 record(s) selected.

4. authority和privilege的赋予与撤消

赋予系统级别的authority:
以SYSADM为例，需要配置实例配置参数 SYSADM_GROUP，指定其为一个用户组，若用户在该用户组中，则具有SYSADM authority。其他三个也类似

赋予数据库级别的authority
https://www.ibm.com/support/knowledgecenter/zh/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000958.html

对象级别的priviledge比较多，包括表、视图、别名、索引、表空间、包、sequence等等，不一一列举，可以参考链接：
https://www.ibm.com/support/knowledgecenter/zh/search/grant?scope=SSEPGG_10.5.0