—创建角色 test_write_role：具有DML操作权限 test_read_role：只读权限db2 create role test_write_roledb2 create role test_read_role
———————————————————————-#—给角色 test_write_role 授权db2 grant usage on workload sysdefaultuserworkload to role test_write_roledb2 grant connect on database to role test_write_roledb2 grant bindadd on database to role test_write_roledb2 grant load on database to role test_write_roledb2 grant create_external_routine on database to role test_write_roledb2 grant createtab on database to role test_write_roledb2 grant use of tablespace userspace1 to role test_write_roledb2 grant implicit_schema on database to role test_write_roledb2 grant dataaccess on database to role test_write_role
问题:如何确定用户需要什么package执行权限？db2 grant execute on package nullid.sqlc2j25 to role test_write_roledb2 grant execute on package nullid.syssh200 to role test_write_roledb2 grant execute on package nullid.sqlubj05 to role test_write_roledb2 grant execute on package nullid.sqlukj0b to role test_write_roledb2 grant execute on package nullid.sqlupj00 to role test_write_roledb2 grant execute on package nullid.sqlucj05 to role test_write_roledb2 grant execute on package nullid.sqluaj20 to role test_write_roledb2 grant execute on package nullid.sqlufj14 to role test_write_roledb2 grant execute on package nullid.sqluoj01 to role test_write_roledb2 grant execute on function sysproc.base_table to role test_write_role
db2 grant select on table syscat.colidentattributes to role test_write_roledb2 grant select on table sysibmadm.dbcfg to role test_write_roledb2 grant select on table sysibm.systables to role test_write_roledb2 grant select on table sysibm.sysindexes to role test_write_roledb2 grant select on table sysibm.syscolumns to role test_write_roledb2 grant select on table sysibm.dual to role test_write_roledb2 grant select on table syscat.packages to role test_write_roledb2 grant select on table syscat.columns to role test_write_roledb2 grant select on table syscat.indexcoluse to role test_write_roledb2 grant select on table syscat.sequences to role test_write_roledb2 grant select on table syscat.functions to role test_write_roledb2 grant select on table syscat.tables to role test_write_roledb2 grant select on table syscat.tabauth to role test_write_roledb2 grant select on table syscat.tbspaceauth to role test_write_roledb2 grant select on table syscat.views to role test_write_roledb2 grant select on table syscat.schemaauth to role test_write_roledb2 grant select on table syscat.sequences to role test_write_roledb2 grant select on table syscat.sequenceauth to role test_write_roledb2 grant select on table syscat.roles to role test_write_roledb2 grant select on table syscat.roleauth to role test_write_roledb2 grant select on table syscat.procedures to role test_write_roledb2 grant select on table syscat.references to role test_write_roledb2 grant select on table syscat.packages to role test_write_roledb2 grant select on table syscat.packageauth to role test_write_role
———————————————————————-#—给角色 test_read_role 授权db2 grant connect on database to role test_read_roledb2 grant select on table syscat.tables to role test_read_roledb2 grant select on table syscat.tabauth to role test_read_roledb2 grant select on table syscat.tbspaceauth to role test_read_roledb2 grant select on table syscat.views to role test_read_roledb2 grant select on table syscat.schemaauth to role test_read_roledb2 grant select on table syscat.sequences to role test_read_roledb2 grant select on table syscat.sequenceauth to role test_read_roledb2 grant select on table syscat.roles to role test_read_roledb2 grant select on table syscat.roleauth to role test_read_roledb2 grant select on table syscat.procedures to role test_read_roledb2 grant select on table syscat.references to role test_read_roledb2 grant select on table syscat.packages to role test_read_roledb2 grant select on table syscat.packageauth to role test_read_roledb2 grant select on table sysibm.dual to role test_read_role
———————————————————————-#—创建模式 1)没有隐藏模权限(IMPLICIT_SCHEMA)用户必须显示创建模式 2)没有DBADM用户的权限testview与用户名同名的模式可以创建testviewtestview@sles11:~> db2 “create schema testview”DB20000I The SQL command completed successfully.
—授权模式testview角色的权限test_write_roledb2 grant createin,alterin,dropin on schema testview to role test_write_role
—了解以下两个概念很重要！ 1)对象的创建者自动拥有对象的所有权。 2)用户有模式DML在这种模式下，有权创建对象。 3)对象包括:表、视图、索引、序列、触发器、存储过程、函数
—给应用用户授权角色test_write_roledb2 grant role test_write_role to user testview
———————————————————————-#回收创建表的权限后，使用表空间的权限也将默认回收：db2 revoke CREATETAB on DATABASE from testview
———————————————————————# 设置应用要连接的实例的环境变量———————————————————————#1)DB2实例查看方法cd /opt/IBM/db2/V10.1/instance./db2ilistdb2inst1 #DB2的实例名其实是操作系统的一个用户名2)查看实例 db2inst1 家目录cat /etc/passwd|grep db2inst1db2inst1:x:1001:1000::/home/db2inst1:/bin/bash3)修改应用用户的 .profiletestview@sles11:~> cat >> ~/.profile < if [ -f /home/db2inst2/sqllib/db2profile ]; then . /home/db2inst2/sqllib/db2profilefiEOF———————————————————————# END 设置应用要连接的实例的环境变量———————————————————————#https://www.cndba.cn/hbhe0316/article/103903
—————————————————————————————————————————# 最小化权限管理实验—————————————————————————————————————————#—建库语句，必须用 RESTRICTIVE 参数db2 “create database test2 on /db2data1,/db2data2,/db2data3 using codeset UTF-8 territory cn RESTRICTIVE”
—DB2数据库rest为restrict模式db2inst2@sles11:~> db2 get db cfg |grep -i restrictRestrict access = YES
对于没有任何权限的OS用户testview，执行如下操作报错的解决方法：
1)没有connect权限testview@sles11:~> db2 connect to restSQL1060N User “testVIEW “ does not have the CONNECT privilege. SQLSTATE=08004
解决方法：db2 grant connect on database to testview
testview@sles11:~> db2 connect to rest
Database Connection Information
Database server = DB2/LINUXX8664 10.1.3SQL authorization ID = testVIEWLocal database alias = REST
2)列出模式testview的表，没有workload权限testview@sles11:~> db2 list tablesSQL5193N The current session user does not have usage privilege on anyenabled workloads. SQLSTATE=42524
解决方法：db2 grant usage on workload sysdefaultuserworkload to user testview
3)没有执行包NULLID.SQLC2J25权限testview@sles11:~> db2 list tablesSQL0551N “testVIEW” does not have the required authorization or privilege toperform operation “EXECUTE” on object “NULLID.SQLC2J25”. SQLSTATE=42501https://www.cndba.cn/hbhe0316/article/103903
解决方法：db2 GRANT EXECUTE ON PACKAGE NULLID.SQLC2J25 TO testview
4)没有视图syscat.tables的查询权限testview@sles11:~> db2 list tablesSQL0551N “testVIEW” does not have the required authorization or privilege toperform operation “SELECT” on object “SYSCAT.TABLES”. SQLSTATE=42501
解决方法：db2 grant select on table syscat.tables to user testview
通过以上4种权限：用户testview可以正常连接上rest，并可以列出模式testview下表：testview@sles11:~> db2 list tables for schema testview
Table/View Schema Type Creation time
0 record(s) selected.
5)没有create table权限testview@sles11:~> db2 “create table t1(id int)”DB21034E The command was processed as an SQL statement because it was not avalid Command Line Processor command. During SQL processing it returned:SQL0552N “testVIEW” does not have the privilege to perform operation “CREATETABLE”. SQLSTATE=42502
解决方法：db2 grant CREATETAB ON DATABASE to testview
6)没有隐式的创建模式权限：IMPLICIT CREATE SCHEMAtestview@sles11:~> db2 “create table t1(id int)”DB21034E The command was processed as an SQL statement because it was not avalid Command Line Processor command. During SQL processing it returned:SQL0552N “testVIEW” does not have the privilege to perform operation “IMPLICITCREATE SCHEMA”. SQLSTATE=42502
解决方法：对于没有IMPLICIT_SCHEMA权限的用户，有两种解决办法：1)直接授予IMPLICIT_SCHEMA权限：db2 grant IMPLICIT_SCHEMA ON DATABASE to user testview2)使用DBADM的用户创建testview所需要的模式，然后授权db2 create schema s1db2 grant createin,alterin,dropin on schema s1 to user testview
7)没有表空间权限，若不指定表空间名字，默认使用表空间USERSPACE1testview@sles11:~> db2 “create table s1.t1(id int) in userspace1”DB21034E The command was processed as an SQL statement because it was not avalid Command Line Processor command. During SQL processing it returned:SQL0551N “testVIEW” does not have the required authorization or privilege toperform operation “CREATE TABLE” on object “USERSPACE1”. SQLSTATE=42501
解决方法： db2 grant use of TABLESPACE USERSPACE1 to testview
testview@sles11:~> db2 “create table s1.t1(id int) in userspace1”DB20000I The SQL command completed successfully.
8)没有存储过程执行的权限testview@sles11:~> db2 “call s1.sleep(10)”SQL0551N “testVIEW” does not have the required authorization or privilege toperform operation “EXECUTE” on object “NULLID.SYSSH200”. SQLSTATE=42501
解决方法： db2 grant execute on package NULLID.SYSSH200 to user testview
testview@sles11:~> db2 “call s1.sleep(2)”
Return Status = 0https://www.cndba.cn/hbhe0316/article/103903
9)没有export权限testview@sles11:~> db2 “export to s1.t1.ixf of ixf messages s1.t1.msg select * from s1.t1”SQL3020N The user does not have the authority to run the specified EXPORTcommand.testview@sles11:~> lltotal 8drwxr-xr-x 2 testview users 4096 Feb 1 17:04 bin-rw-r—r— 1 testview users 719 Feb 4 12:24 s1.t1.msgtestview@sles11:~> cat s1.t1.msgSQL3015N An SQL error “-551” occurred during processing.
SQL0551N “testVIEW” does not have the required authorization or privilege toperform operation “EXECUTE” on object “NULLID.SQLUBJ05”. SQLSTATE=42501
SQL3015N An SQL error “-551” occurred during processing.
SQL0551N “testVIEW” does not have the required authorization or privilege toperform operation “EXECUTE” on object “NULLID.SQLUKJ0B”. SQLSTATE=42501
SQL3015N An SQL error “-551” occurred during processing.
SQL0551N “testVIEW” does not have the required authorization or privilege toperform operation “EXECUTE” on object “NULLID.SQLUPJ00”. SQLSTATE=42501
SQL3020N The user does not have the authority to run the specified EXPORTcommand.
解决方法：db2 grant execute on package nullid.sqlubj05 to user testVIEWdb2 grant execute on package nullid.sqlukj0b to user testVIEWdb2 grant execute on package nullid.sqlupj00 to user testVIEWdb2 grant execute on package nullid.sqlucj05 to user testVIEWdb2 grant execute on package nullid.sqluaj20 to user testVIEWdb2 grant execute on function sysproc.base_table to user testVIEWdb2 grant select on table SYSCAT.COLIDENTATTRIBUTES to user testVIEWdb2 grant select on table SYSCAT.INDEXCOLUSE to user testVIEWdb2 grant select on table SYSCAT.SEQUENCES to user testVIEWdb2 grant select on table SYSIBM.SYSTABLES to user testVIEWdb2 grant select on table SYSIBM.SYSINDEXES to user testVIEWdb2 grant select on table syscat.functions to user testVIEWdb2 grant select on table sysibm.syscolumns to user testVIEW
—授予上面的权限后，最终报错：testview@sles11:~> cat s1.t1.msgSQL3104N The Export utility is beginning to export data to file “s1.t1.ixf”.
SQL27981W The utility could not verify presence of attached or detached datapartitions in the target table or the source table.
SQL0551N “” does not have the required authorization or privilege to performoperation “” on object “”.
SQL3105N The Export utility has finished exporting “1” rows.https://www.cndba.cn/hbhe0316/article/103903
10)没有import权限testview@sles11:~> db2 “import from s1.t1.ixf of ixf insert into s1.t1”SQL0551N “testVIEW” does not have the required authorization or privilege toperform operation “EXECUTE” on object “NULLID.SQLUFJ14”. SQLSTATE=42501
testview@sles11:~> db2 “import from s1.t1.ixf of ixf insert into s1.t1”SQL0551N “testVIEW” does not have the required authorization or privilege toperform operation “SELECT” on object “SYSIBMADM.DBCFG”. SQLSTATE=42501
testview@sles11:~> db2 “import from s1.t1.ixf of ixf insert into s1.t1”SQL3015N An SQL error “-551” occurred during processing.https://www.cndba.cn/hbhe0316/article/103903https://www.cndba.cn/hbhe0316/article/103903
SQL0551N “testVIEW” does not have the required authorization or privilege toperform operation “EXECUTE” on object “NULLID.SQLUOJ01”. SQLSTATE=42501
testview@sles11:~> db2 “import from s1.t1.ixf of ixf insert into s1.t1”SQL3015N An SQL error “-551” occurred during processing.
SQL0551N “testVIEW” does not have the required authorization or privilege toperform operation “SELECT” on object “SYSCAT.PACKAGES”. SQLSTATE=42501
SQL3015N An SQL error “” occurred during processing.
解决方法：db2 grant execute on package nullid.SQLUFJ14 to user testVIEWdb2 grant select on table SYSIBMADM.DBCFG to user testVIEWdb2 grant execute on package NULLID.SQLUOJ01 to user testVIEWdb2 grant select on table SYSCAT.PACKAGES to user testVIEWdb2 grant select on table SYSCAT.COLUMNS to user testVIEWhttps://www.cndba.cn/hbhe0316/article/103903
testview@sles11:~> db2 “import from s1.t1.ixf of ixf insert into s1.t1”SQL27981W The utility could not verify presence of attached or detached datapartitions in the target table or the source table.https://www.cndba.cn/hbhe0316/article/103903
SQL3150N The H record in the PC/IXF file has product “DB2 02.00”, date“20150204”, and time “124100”.
SQL3153N The T record in the PC/IXF file has name “s1.t1.ixf”, qualifier “”,and source “ “.
SQL3015N An SQL error “-551” occurred during processing.
SQL0551N “testVIEW” does not have the required authorization or privilege toperform operation “SELECT” on object “SYSCAT.COLUMNS”. SQLSTATE=42501
SQL3110N The utility has completed processing. “0” rows were read from theinput file.
版权声明：本文为博主原创文章，未经博主允许不得转载。
DB2