--权限相关的视图
db2 "select substr(tabname,1,30) as tabname
from syscat.tables
where tabschema='SYSCAT' and tabname like '%AUTH'
"

--查看数据库用户
db2 " select char(authid,30) as authid, authidtype
from sysibmadm.authorizationids
"

--查看public的数据库权限
db2 "select substr(grantee,1,32) as grantee,granteetype,
bindaddauth,dbadmauth,connectauth,createtabauth,implschemaauth,loadauth,dataaccessauth
from syscat.dbauth
where grantee='PUBLIC'
"

--管理权限，所有对象的的所有特权
db2 " SELECT substr(AUTHID,1,20) as AUTHID,
   AUTHIDTYPE,
   PRIVILEGE,
   GRANTABLE,
   substr(OBJECTNAME,1,30) as OBJECTNAME,
   substr(OBJECTSCHEMA,1,30) as OBJECTSCHEMA,
   OBJECTTYPE
FROM SYSIBMADM.PRIVILEGES
where authid = 'PUBLIC' order by 1,7,5,3
"

--特权privilege
db2 " SELECT substr(AUTHORITY,1,32) as Authority, D_USER, D_GROUP, D_PUBLIC, ROLE_USER, ROLE_GROUP, ROLE_PUBLIC, D_ROLE
FROM TABLE (SYSPROC.AUTH_LIST_AUTHORITIES_FOR_AUTHID ('DB2INST1', 'U') ) AS T ORDER BY AUTHORITY
"

db2 " select substr(grantee,1,32) as grantee,granteetype,substr(tbspace,1,20) as tbspace,useauth
from syscat.tbspaceauth
"

--回收特权
db2 "revoke use of tablespace userspace1 from public"
db2 "revoke createtab on database from public"
db2 "revoke implicit_schema on database from public"

--当前连接的用户所拥有的管理权限
db2 get authorizations

--windows syscat授权给谁应查看的视图
db2 list tables for schema syscat | find /i "AUTH"

--linux syscat授权给谁应查看的视图
db2 list tables for schema syscat | grep -i auth

db2 "select char(grantee,20) as grantee,char(tabname,20) as tabname,
controlauth,alterauth,deleteauth,insertauth,selectauth,updateauth
from syscat.tabauth
where tabname='EMPLOYEE'
"

--workload 权限查询
db2 "select char(workloadname,30) from syscat.workloads"
db2 "select workloadid,char(workloadname,30),usageauth,char(grantee,30) from syscat.workloadauth"

-----------------------------------------------------------------------

数据库权限

connect
bindadd
createtab
create_not_fenced
implicit_schema 允许用户在尚不存在的模式中创建对象
quiesce_connect 允许用户连接处于quiesced状态的数据库
create_external_routine 允许用户注册外部例程

表空间特权
use 允许用户在指定表空间中创建表

模式特权
createin 允许用户在模式中创建对象
alterin
dropin

表和视图特权
control 授予用户在表和视图上的所有特权，以及将这些特权（除了control）授予别人
alter   允许用户在表中添加列，在表和列上添加或修改注释，添加主键或唯一约束，以及创建或删除表检查约束
delete 允许用户从表或视图删除行
index   允许用户在表上创建索引
insert
references 允许创建和删除外键
select   允许用户从表或视图中检索行，在表上创建视图，以及运行EXPORT实用程序
update

索引特权
control 允许用户删除索引

包特权
control 允许用户重新绑定、删除或执行包，以及将这些特权（除了CONTROL）授予别人
bind   允许用户重新绑定现有的包
execute 允许用户执行包
bindadd 新包绑定需要的权限

例程特权
execute 允许用户调用例程，从例程创建函数（只应用于函数），以及在任何DDL语句中引用例程

序列特权
usage 允许用户对序列使用nextval和prevval表达式
alter 允许用户使用alter sequence语句修改序列属性

db2 grant dbadm on database to user user1
db2 grant dbadm on database to group db2grp1
db2 grant load on database to user user1
db2 grant load on database to group grp1

--向用户或组授予权限和特权时，DB2允许授予不存在的账号。如果以后创建了同名的账号，则这个账号就获得了权限和特权。

db2 grant insert on table t1 to user nxz

db2 grant select on table t1 to group grp1

db2 grant delete on table t1 to group grp1

with grant option 只能用于包、例程、模式、表、视图和表空间的GRANT语句。

CONTROL特权只能由具有SYSADM或DBADM权限的用户执行。

--drop 表必须具有模式上的权限

db2 grant alterin,createin,dropin on schema s1 to group grp1 with grant option

db2 grant dropin on schema s1 to all

db2 revoke alter on table t1 from user u1

db2 revoke all privileges on table t1 from u1

从组中撤销特权不一定会从这个组的所有成员撤销该特权，

db2 revoke all on table s1.t1 from group grp1

但是，test1用户仍然拥有对这个表的特权（如select），因为test1是被直接授予这个特权的。

--nxz 执行以下命令，则nxz需要这个包的动态SQL的特权，运行动态SQL的每个SQL所需的特权，以及包上的EXECUTE特权。
db2 BIND sampleapp.bnd QUALIFIER u1 OWNER u2 DYNAMIC RULES RUN

--nxz执行以下命令，检查用户U2的特权，不是检查nxz
db2 BIND sampleapp.bnd QUALIFIER u1 OWNER u2 DYNAMIC RULES BIND

DB2关于特权的信息存储在7个系统编目视图中：模式syscat
dbauth      数据库特权
colauth     列特权
indexauth   索引特权
packageauth 包特权
schemaauth 模式特权
tabauth     表和视图特权
tbspaceauth 表空间特权

GTYPE 被授权者类型 U:用户账号 G:组账号
其他列 Y:拥有此特权 N:不拥有特权 G:拥有此特权并可以授予其他人

--模式特权

db2 "select char(grantee,20) grantee, char(schemaname,20) schema ,alterinauth ,createinauth,dropinauth

from syscat.schemaauth where grantee='PUBLIC'

"

--角色特权
db2 "select char(rolename,20),roleid
from syscat.ROLEAUTH where grantee='PUBLIC'
"

--执行计划包特权
db2 "select char(pkgschema,20), char(pkgname,20), CONTROLAUTH,BINDAUTH,EXECUTEAUTH
from syscat.packageauth where grantee='PUBLIC'
"

--序列特权

db2 "select char(seqschema,20),char(seqname,20) from syscat.SEQUENCEAUTH where grantee='PUBLIC' "

--查询public能访问的模式

db2 "select distinct char(objectschema,40) as "ObjectSchema" from sysibmadm.privileges where authid='PUBLIC'"

--查询public权限和特权

db2 "select char(objectschema,30) as "ObjectSchema" ,char(objectname,50) "ObjectName",objecttype,privilege
from sysibmadm.privileges where authid='PUBLIC' order by 1,2 "

ITPub博客

DB2权限与授权

最新文章