--查看public拥有的表或视图的权限
db2 " select char(grantee,20) as grantee, char(tabname,40) as tabname,
controlauth, alterauth, deleteauth , insertauth, selectauth, updateauth
from syscat.tabauth where grantee='PUBLIC' and tabname like '%AUTH%'
"
db2 " select char(grantee,20) as grantee, char(tabname,40) as tabname,
controlauth, alterauth, deleteauth , insertauth, selectauth, updateauth
from syscat.tabauth where grantee='PUBLIC' and tabname like '%AUTH%'
"
---------------------------------------------------------------
数据库在 Restrict 模式下,授权用户:test
db2 grant use of TABLESPACE USERSPACE1 to test
db2 grant IMPLICIT_SCHEMA ON DATABASE to test
db2 grant CREATETAB ON DATABASE to test
db2 grant dataaccess on DATABASE to test
db2 grant connect on database to test
db2 grant usage on workload sysdefaultuserworkload to user test
数据库在 Restrict 模式下,授权用户:test
db2 grant use of TABLESPACE USERSPACE1 to test
db2 grant IMPLICIT_SCHEMA ON DATABASE to test
db2 grant CREATETAB ON DATABASE to test
db2 grant dataaccess on DATABASE to test
db2 grant connect on database to test
db2 grant usage on workload sysdefaultuserworkload to user test
db2 revoke connect ,bindadd,createtab on database from public;
--restrictive 为 YES,不把相关权限授予public
db2 get db cfg for testdb | grep -i restrict
--------------------------------------------------------------------------------------------------------
REVOKE BINDADD ON DATABASE FROM PUBLIC;
REVOKE CREATETAB ON DATABASE FROM PUBLIC;
REVOKE CONNECT ON DATABASE FROM PUBLIC;
REVOKE IMPLICIT_SCHEMA ON DATABASE FROM PUBLIC;
REVOKE USE OF TABLESPACE USERSPACE1 FROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.COLAUTH FROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.DBAUTH FROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.INDEXAUTH FROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.PACKAGEAUTH FROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.PASSTHRUAUTH FROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.ROUTINEAUTH FROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.SCHEMAAUTH FROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.SECURITYLABELACCESS FROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.SECURITYPOLICYEXEMPTIONS FROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.SEQUENCEAUTH FROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.SURROGATEAUTHIDSFROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.TABAUTH FROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.TBSPACEAUTH FROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.XSROBJECTAUTHFROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.AUTHORIZATIONIDS FROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.OBJECTOWNERS FROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.PRIVILEGES FROM PUBLIC;
Grant DBADM ON DATABASE FROM USER jeff
REVOKE BINDADD ON DATABASE FROM PUBLIC;
REVOKE CREATETAB ON DATABASE FROM PUBLIC;
REVOKE CONNECT ON DATABASE FROM PUBLIC;
REVOKE IMPLICIT_SCHEMA ON DATABASE FROM PUBLIC;
REVOKE USE OF TABLESPACE USERSPACE1 FROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.COLAUTH FROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.DBAUTH FROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.INDEXAUTH FROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.PACKAGEAUTH FROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.PASSTHRUAUTH FROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.ROUTINEAUTH FROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.SCHEMAAUTH FROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.SECURITYLABELACCESS FROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.SECURITYPOLICYEXEMPTIONS FROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.SEQUENCEAUTH FROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.SURROGATEAUTHIDSFROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.TABAUTH FROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.TBSPACEAUTH FROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.XSROBJECTAUTHFROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.AUTHORIZATIONIDS FROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.OBJECTOWNERS FROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.PRIVILEGES FROM PUBLIC;
Grant DBADM ON DATABASE FROM USER jeff
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22661144/viewspace-1477191/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22661144/viewspace-1477191/