DB2 里查询是否具有权限的全部SQL

V10适用。

--查看数据库里现有的用户、组、ROLE

SELECT substr(AUTHID,1,16) as AUTHID,AUTHIDTYPE FROM SYSIBMADM.AUTHORIZATIONIDS

--查询某一个用户、组、ROLE的数据库级别的权限

SELECT 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

--查询对象层面的权限

SELECT substr(AUTHID,1,16) as AUTHID, PRIVILEGE, substr(OBJECTSCHEMA,1,20) as OBJECTSCHEMA, substr(OBJECTNAME,1,50) as OBJECTNAME, substr(OBJECTTYPE,1,20) as OBJECTTYPE FROM SYSIBMADM.PRIVILEGES                                   

--查询字段级别的权限

SELECT substr(GRANTEE,1,16) as AUTHID,substr(TABSCHEMA,1,20) as TABSCHEMA,substr(TABNAME,1,50) as TABNAME,substr(COLNAME,1,50) as COLNAME,PRIVTYPE from SYSCAT.COLAUTH        

--查询用户、组、ROLE所被赋予的ROLE

SELECT substr(GRANTEE,1,16) as AUTHID,GRANTEETYPE as AUTHID_TYPE,substr(ROLENAME,1,20) as ROLENAME,ADMIN from SYSCAT.ROLEAUTH