DB2 managing user & privilege

最新推荐文章于 2023-08-15 15:10:40 发布

原创最新推荐文章于 2023-08-15 15:10:40 发布 · 1.6k 阅读

1 ·

CC 4.0 BY-SA版权

DB2 Administrator 专栏收录该内容

8 篇文章

订阅专栏

本文介绍了IBM DB2数据库系统的权限管理机制，包括实例/数据库权限和对象权限。详细讲解了如何使用DB2命令来查看和管理不同类型的用户权限，如数据库连接权限、表操作权限等。

DB2用户默认为操作系统用户，权限管理主要分为以下两类
实例/数据库权限
对象权限
对DBA而言一般需要掌握实例/数据库的管理权限，而对普通应用用户或应用管理员，需要对象权限即可。
DB2的权限控制
查看当前用户的系统权限
db2 get authorizations

查看DB2有哪些用户sysibmadm.authorizationids

db2 select char(authid,30) as authid,authidtype from sysibmadm.authorizationids

AUTHID                         AUTHIDTYPE
------------------------------ ----------
PUBLIC                         G
DB2ADMIN                       U

2 record(s) selected.
查看用户的数据库权限syscat.dbauth
db2 select char(grantee,20) as grantee,granteetype,bindaddauth,connectauth,createtabauth,dbadmauth,security
admauth from syscat.dbauth

GRANTEE              GRANTEETYPE BINDADDAUTH CONNECTAUTH CREATETABAUTH DBADMAUTH SECURITYADMAUTH
-------------------- ----------- ----------- ----------- ------------- --------- ---------------
DB2ADMIN             U           Y           Y           Y             Y         N
PUBLIC               G           Y           Y           Y             N         N

2 record(s) selected.
查看用户的对象权限sysibmadm.privileges
db2 select char(authid,15) as authid,substr(objectschema,1,20) as objectschema,substr(objectname,1,32) as objectname,objecttype,privilege from sysibmadm.privileges where authid='PUBLIC'|more

查看某个表对象授权给哪些账户，哪些权限？syscat.tabauth
db2 select char(grantee,25) as grantee,char(tabname,20) as tabname,controlauth,alterauth,deleteauth,insertauth,selectauth,updateauth from syscat.tabauth where tabname='ODS_T1'

GRANTEE                   TABNAME              CONTROLAUTH ALTERAUTH DELETEAUTH INSERTAUTH SELECTAUTH UPDATEAUTH
------------------------- -------------------- ----------- --------- ---------- ---------- ---------- ----------
DB2ADMIN                  ODS_T1               Y           G         G          G          G          G

1 record(s) selected.
PUBLIC权限的回收
connect
createtab
bindadd           指创建包
implicit_schema   指创建schema
db2 revoke connect,bindadd,createtab,implicit_schema on database from public

创建数据库和实例权限账户组
C:\Users\Administrator>db2 get dbm config|find /i "group"
SYSADM group name                        (SYSADM_GROUP) =
SYSCTRL group name                      (SYSCTRL_GROUP) = DB2SYSCTRLS
SYSMAINT group name                    (SYSMAINT_GROUP) =
SYSMON group name                        (SYSMON_GROUP) =
Group Plugin                             (GROUP_PLUGIN) =

C:\Users\Administrator>db2 update dbm cfg using SYSCTRL_GROUP db2sysctrls
DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.