DB2最小化权限问题-CSDN博客

本文链接：https://blog.csdn.net/hbhe0316/article/details/122701218

—创建角色 test_write_role：具有DML操作权限 test_read_role：只读权限
db2 create role test_write_role
db2 create role test_read_role

———————————————————————-#
—给角色 test_write_role 授权
db2 grant usage on workload sysdefaultuserworkload to role test_write_role
db2 grant connect on database to role test_write_role
db2 grant bindadd on database to role test_write_role
db2 grant load on database to role test_write_role
db2 grant create_external_routine on database to role test_write_role
db2 grant createtab on database to role test_write_role
db2 grant use of tablespace userspace1 to role test_write_role
db2 grant implicit_schema on database to role test_write_role
db2 grant dataaccess on database to role test_write_role

问题：如何确定应用用户需要哪些package的执行权限？
db2 grant execute on package nullid.sqlc2j25 to role test_write_role
db2 grant execute on package nullid.syssh200 to role test_write_role
db2 grant execute on package nullid.sqlubj05 to role test_write_role
db2 grant execute on package nullid.sqlukj0b to role test_write_role
db2 grant execute on package nullid.sqlupj00 to role test_write_role
db2 grant execute on package nullid.sqlucj05 to role test_write_role
db2 grant execute on package nullid.sqluaj20 to role test_write_role
db2 grant execute on package nullid.sqlufj14 to role test_write_role
db2 grant execute on package nullid.sqluoj01 to role test_write_role
db2 grant execute on function sysproc.base_table to role test_write_role

db2 grant select on table syscat.colidentattributes to role test_write_role
db2 grant select on table sysibmadm.dbcfg to role test_write_role
db2 grant select on table sysibm.systables to role test_write_role
db2 grant select on table sysibm.sysindexes to role test_write_role
db2 grant select on table sysibm.syscolumns to role test_write_role
db2 grant select on table sysibm.dual to role test_write_role
db2 grant select on table syscat.packages to role test_write_role
db2 grant select on table syscat.columns to role test_write_role
db2 grant select on table syscat.indexcoluse to role test_write_role
db2 grant select on table syscat.sequences to role test_write_role
db2 grant select on table syscat.functions to role test_write_role
db2 grant select on table syscat.tables to role test_write_role
db2 grant select on table syscat.tabauth to role test_write_role
db2 grant select on table syscat.tbspaceauth to role test_write_role
db2 grant select on table syscat.views to role test_write_role
db2 grant select on table syscat.schemaauth to role test_write_role
db2 grant select on table syscat.sequences to role test_write_role
db2 grant select on table syscat.sequenceauth to role test_write_role
db2 grant select on table syscat.roles to role test_write_role
db2 grant select on table syscat.roleauth to role test_write_role
db2 grant select on table syscat.procedures to role test_write_role
db2 grant select on table syscat.references to role test_write_role
db2 grant select on table syscat.packages to role test_write_role
db2 grant select on table syscat.packageauth to role test_write_role

———————————————————————-#
—给角色 test_read_role 授权
db2 grant connect on database to role test_read_role
db2 grant select on table syscat.tables to role test_read_role
db2 grant select on table syscat.tabauth to role test_read_role
db2 grant select on table syscat.tbspaceauth to role test_read_role
db2 grant select on table syscat.views to role test_read_role
db2 grant select on table syscat.schemaauth to role test_read_role
db2 grant select on table syscat.sequences to role test_read_role
db2 grant select on table syscat.sequenceauth to role test_read_role
db2 grant select on table syscat.roles to role test_read_role
db2 grant select on table syscat.roleauth to role test_read_role
db2 grant select on table syscat.procedures to role test_read_role
db2 grant select on table syscat.references to role test_read_role
db2 grant select on table syscat.packages to role test_read_role
db2 grant select on table syscat.packageauth to role test_read_role
db2 grant select on table sysibm.dual to role test_read_role

———————————————————————-#
—创建模式
1)没有隐式模式权限(IMPLICIT_SCHEMA)的用户必须显示创建模式
2)没有DBADM权限的应用用户testview可以创建与用户名同名的模式testview
testview@sles11:~> db2 “create schema testview”
DB20000I The SQL command completed successfully.

—授权模式testview的权限给角色test_write_role
db2 grant createin,alterin,dropin on schema testview to role test_write_role

—理解以下两个概念很重要！
1)对象的创建者自动拥有了该对象的所有权限。
2)用户拥有模式的DML权限后，在该模式上就拥有了创建对象的权限。
3)对象包含：表，视图，索引，序列，触发器，存储过程，函数

https://www.cndba.cn/hbhe0316/article/103903

—给应用用户授权角色test_write_role
db2 grant role test_write_role to user testview

———————————————————————-#
回收创建表的权限后，使用表空间的权限也将默认回收：
db2 revoke CREATETAB on DATABASE from testview

———————————————————————#
设置应用要连接的实例的环境变量
———————————————————————#
1)DB2实例查看方法
cd /opt/IBM/db2/V10.1/instance
./db2ilist
db2inst1 #DB2的实例名其实是操作系统的一个用户名
2)查看实例 db2inst1 家目录
cat /etc/passwd|grep db2inst1
db2inst1:x:1001:1000::/home/db2inst1:/bin/bash
3)修改应用用户的 .profile
testview@sles11:~> cat >> ~/.profile < if [ -f /home/db2inst2/sqllib/db2profile ]; then
. /home/db2inst2/sqllib/db2profile
fi
EOF
———————————————————————#
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 restrict
Restrict access = YES

对于没有任何权限的OS用户testview，执行如下操作报错的解决方法：

1)没有connect权限
testview@sles11:~> db2 connect to rest
SQL1060N 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.3
SQL authorization ID = testVIEW
Local database alias = REST

2)列出模式testview的表，没有workload权限
testview@sles11:~> db2 list tables
SQL5193N The current session user does not have usage privilege on any
enabled workloads. SQLSTATE=42524

解决方法：db2 grant usage on workload sysdefaultuserworkload to user testview

3)没有执行包NULLID.SQLC2J25权限
testview@sles11:~> db2 list tables
SQL0551N “testVIEW” does not have the required authorization or privilege to
perform 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 tables
SQL0551N “testVIEW” does not have the required authorization or privilege to
perform 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 a
valid Command Line Processor command. During SQL processing it returned:
SQL0552N “testVIEW” does not have the privilege to perform operation “CREATE
TABLE”. SQLSTATE=42502

解决方法：db2 grant CREATETAB ON DATABASE to testview

6)没有隐式的创建模式权限：IMPLICIT CREATE SCHEMA
testview@sles11:~> db2 “create table t1(id int)”
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0552N “testVIEW” does not have the privilege to perform operation “IMPLICIT
CREATE SCHEMA”. SQLSTATE=42502

解决方法：
对于没有IMPLICIT_SCHEMA权限的用户，有两种解决办法：
1)直接授予IMPLICIT_SCHEMA权限：
db2 grant IMPLICIT_SCHEMA ON DATABASE to user testview
2)使用DBADM的用户创建testview所需要的模式，然后授权
db2 create schema s1
db2 grant createin,alterin,dropin on schema s1 to user testview

https://www.cndba.cn/hbhe0316/article/103903

7)没有表空间权限，若不指定表空间名字，默认使用表空间USERSPACE1
testview@sles11:~> db2 “create table s1.t1(id int) in userspace1”
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N “testVIEW” does not have the required authorization or privilege to
perform 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 to
perform 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 EXPORT
command.
testview@sles11:~> ll
total 8
drwxr-xr-x 2 testview users 4096 Feb 1 17:04 bin
-rw-r—r— 1 testview users 719 Feb 4 12:24 s1.t1.msg
testview@sles11:~> cat s1.t1.msg
SQL3015N An SQL error “-551” occurred during processing.

SQL0551N “testVIEW” does not have the required authorization or privilege to
perform 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 to
perform 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 to
perform operation “EXECUTE” on object “NULLID.SQLUPJ00”. SQLSTATE=42501

SQL3020N The user does not have the authority to run the specified EXPORT
command.

解决方法：
db2 grant execute on package nullid.sqlubj05 to user testVIEW
db2 grant execute on package nullid.sqlukj0b to user testVIEW
db2 grant execute on package nullid.sqlupj00 to user testVIEW
db2 grant execute on package nullid.sqlucj05 to user testVIEW
db2 grant execute on package nullid.sqluaj20 to user testVIEW
db2 grant execute on function sysproc.base_table to user testVIEW
db2 grant select on table SYSCAT.COLIDENTATTRIBUTES to user testVIEW
db2 grant select on table SYSCAT.INDEXCOLUSE to user testVIEW
db2 grant select on table SYSCAT.SEQUENCES to user testVIEW
db2 grant select on table SYSIBM.SYSTABLES to user testVIEW
db2 grant select on table SYSIBM.SYSINDEXES to user testVIEW
db2 grant select on table syscat.functions to user testVIEW
db2 grant select on table sysibm.syscolumns to user testVIEW

—授予上面的权限后，最终报错：
testview@sles11:~> cat s1.t1.msg
SQL3104N The Export utility is beginning to export data to file “s1.t1.ixf”.

SQL27981W The utility could not verify presence of attached or detached data
partitions in the target table or the source table.

SQL0551N “” does not have the required authorization or privilege to perform
operation “” 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 to
perform 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 to
perform 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/103903 https://www.cndba.cn/hbhe0316/article/103903

SQL0551N “testVIEW” does not have the required authorization or privilege to
perform 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 to
perform operation “SELECT” on object “SYSCAT.PACKAGES”. SQLSTATE=42501

SQL3015N An SQL error “” occurred during processing.

解决方法：
db2 grant execute on package nullid.SQLUFJ14 to user testVIEW
db2 grant select on table SYSIBMADM.DBCFG to user testVIEW
db2 grant execute on package NULLID.SQLUOJ01 to user testVIEW
db2 grant select on table SYSCAT.PACKAGES to user testVIEW
db2 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 data
partitions 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 to
perform operation “SELECT” on object “SYSCAT.COLUMNS”. SQLSTATE=42501

SQL3110N The utility has completed processing. “0” rows were read from the
input file.

DB2