DB2最小化权限管理实现方案

#--创建角色 bsb_write_role：具有DML操作权限  bsb_read_role：只读权限
db2 create role bsb_write_role
db2 create role bsb_read_role

#-----------------------------------------------#
#--给角色 bsb_write_role 授权
db2 grant usage on workload sysdefaultuserworkload to role bsb_write_role
db2 grant connect on database                      to role bsb_write_role
db2 grant bindadd on database                      to role bsb_write_role

db2 grant load on database                           to role bsb_write_role

db2 grant create_external_routine on database      to role bsb_write_role
db2 grant createtab on database                    to role bsb_write_role
db2 grant use of tablespace userspace1             to role bsb_write_role
db2 grant implicit_schema on database              to role bsb_write_role
#db2 grant dataaccess on database                  to role bsb_write_role

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

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


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

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

#--授权模式bsbview的权限给角色bsb_write_role
db2 grant createin,alterin,dropin on schema bsbview to role bsb_write_role

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


#--给应用用户授权角色bsb_write_role
db2 grant role bsb_write_role to user bsbview

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


#----------------------------------------------#
# 设置应用要连接的实例的环境变量
#----------------------------------------------#
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
bsbview@sles11:~>  cat >> ~/.profile < if [ -f /home/db2inst2/sqllib/db2profile ]; then
    . /home/db2inst2/sqllib/db2profile
fi
EOF
#----------------------------------------------#
# END 设置应用要连接的实例的环境变量
#----------------------------------------------#


#----------------------------------------------------------------------------------#
# 最小化权限管理实验
#----------------------------------------------------------------------------------#
#--建库语句，必须用 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用户bsbview，执行如下操作报错的解决方法：

1)没有connect权限
bsbview@sles11:~> db2 connect to rest
SQL1060N  User "BSBVIEW " does not have the CONNECT privilege.  SQLSTATE=08004

解决方法：db2 grant connect on database to bsbview

bsbview@sles11:~> db2 connect to rest

   Database Connection Information

Database server        = DB2/LINUXX8664 10.1.3
SQL authorization ID   = BSBVIEW
Local database alias   = REST

2)列出模式bsbview的表，没有workload权限
bsbview@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 bsbview

3)没有执行包NULLID.SQLC2J25权限
bsbview@sles11:~> db2 list tables
SQL0551N  "BSBVIEW" does not have the required authorization or privilege to
perform operation "EXECUTE" on object "NULLID.SQLC2J25".  SQLSTATE=42501

解决方法：db2 GRANT EXECUTE ON PACKAGE NULLID.SQLC2J25 TO bsbview

4)没有视图syscat.tables的查询权限
bsbview@sles11:~> db2 list tables
SQL0551N  "BSBVIEW" 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 bsbview

通过以上4种权限：用户bsbview可以正常连接上rest，并可以列出模式bsbview下表：
bsbview@sles11:~> db2 list tables for schema bsbview

Table/View                      Schema          Type  Creation time            
------------------------------- --------------- ----- --------------------------

  0 record(s) selected.

5）没有create table权限
bsbview@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  "BSBVIEW" does not have the privilege to perform operation "CREATE
TABLE".  SQLSTATE=42502

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

6）没有隐式的创建模式权限：IMPLICIT CREATE SCHEMA
bsbview@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  "BSBVIEW" 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 bsbview 
2）使用DBADM的用户创建bsbview所需要的模式，然后授权
db2 create schema s1
db2 grant createin,alterin,dropin on schema s1 to user bsbview

7）没有表空间权限，若不指定表空间名字，默认使用表空间USERSPACE1
bsbview@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  "BSBVIEW" 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 bsbview

bsbview@sles11:~> db2 "create table s1.t1(id int) in userspace1"
DB20000I  The SQL command completed successfully.


8）没有存储过程执行的权限
bsbview@sles11:~> db2 "call s1.sleep(10)"
SQL0551N  "BSBVIEW" 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 bsbview

bsbview@sles11:~> db2 "call s1.sleep(2)"

  Return Status = 0
 
9）没有export权限
bsbview@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.
bsbview@sles11:~> ll
total 8
drwxr-xr-x 2 bsbview users 4096 Feb  1 17:04 bin
-rw-r--r-- 1 bsbview users  719 Feb  4 12:24 s1.t1.msg
bsbview@sles11:~> cat s1.t1.msg
SQL3015N  An SQL error "-551" occurred during processing.

SQL0551N  "BSBVIEW" 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  "BSBVIEW" 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  "BSBVIEW" 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 BSBVIEW
db2 grant execute on package nullid.sqlukj0b       to user BSBVIEW
db2 grant execute on package nullid.sqlupj00       to user BSBVIEW
db2 grant execute on package nullid.sqlucj05       to user BSBVIEW
db2 grant execute on package nullid.sqluaj20       to user BSBVIEW
db2 grant execute on function sysproc.base_table   to user BSBVIEW
db2 grant select on table SYSCAT.COLIDENTATTRIBUTES to user BSBVIEW
db2 grant select on table SYSCAT.INDEXCOLUSE       to user BSBVIEW
db2 grant select on table SYSCAT.SEQUENCES         to user BSBVIEW
db2 grant select on table SYSIBM.SYSTABLES         to user BSBVIEW
db2 grant select on table SYSIBM.SYSINDEXES        to user BSBVIEW
db2 grant select on table syscat.functions         to user BSBVIEW
db2 grant select on table sysibm.syscolumns        to user BSBVIEW

--授予上面的权限后，最终报错：
bsbview@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.



10）没有import权限
bsbview@sles11:~> db2 "import from s1.t1.ixf of ixf insert into s1.t1"
SQL0551N  "BSBVIEW" does not have the required authorization or privilege to
perform operation "EXECUTE" on object "NULLID.SQLUFJ14".  SQLSTATE=42501

bsbview@sles11:~> db2 "import from s1.t1.ixf of ixf insert into s1.t1"
SQL0551N  "BSBVIEW" does not have the required authorization or privilege to
perform operation "SELECT" on object "SYSIBMADM.DBCFG".  SQLSTATE=42501

bsbview@sles11:~> db2 "import from s1.t1.ixf of ixf insert into s1.t1"
SQL3015N  An SQL error "-551" occurred during processing.

SQL0551N  "BSBVIEW" does not have the required authorization or privilege to
perform operation "EXECUTE" on object "NULLID.SQLUOJ01".  SQLSTATE=42501

bsbview@sles11:~> db2 "import from s1.t1.ixf of ixf insert into s1.t1"
SQL3015N  An SQL error "-551" occurred during processing.

SQL0551N  "BSBVIEW" 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 BSBVIEW
db2 grant select on table SYSIBMADM.DBCFG          to user BSBVIEW
db2 grant execute on package NULLID.SQLUOJ01       to user BSBVIEW
db2 grant select on table SYSCAT.PACKAGES          to user BSBVIEW
db2 grant select on table SYSCAT.COLUMNS           to user BSBVIEW



bsbview@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.

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  "BSBVIEW" 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.

#----------------------------------------------------------------------------------#
# END 最小化权限管理实验
#----------------------------------------------------------------------------------#

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22661144/viewspace-1477190/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22661144/viewspace-1477190/