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 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 <<EOF
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 最小化許可權管理實驗
#----------------------------------------------------------------------------------#
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 <<EOF
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/26224914/viewspace-2122618/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- DB2許可權管理的理解DB2
- Pb中多使用者許可權管理實現方案 (轉)
- 基於RBAC實現許可權管理
- spring aop實現許可權管理Spring
- django開發之許可權管理(一)——許可權管理詳解(許可權管理原理以及方案)、不使用許可權框架的原始授權方式詳解Django框架
- 使用動態路由實現許可權管理路由
- DB2許可權與授權DB2
- MySQL許可權管理實戰MySql
- 如何用 Vue 實現前端許可權控制（路由許可權 + 檢視許可權 + 請求許可權）Vue前端路由
- NODE + JWT + Mongo（簡單實現許可權管理）JWTGo
- Java實現許可權管理-專案設計Java
- Linux-許可權管理（ACL許可權）Linux
- Laravel實現許可權控制Laravel
- Hyperf 使用 hyperf-permission 元件實現許可權管理元件
- 淺談許可權管理的設計與實現
- vue+elementUI實現許可權的部門管理VueUI
- ASP.Net實現使用者許可權管理ASP.NET
- 提問:使用spring aop實現許可權管理Spring
- DB2 public許可權相關DB2
- PostgreSQL：許可權管理SQL
- Mysql——許可權管理MySql
- Mysql 許可權管理MySql
- MySQL許可權管理MySql
- oracle 許可權管理Oracle
- sql許可權管理SQL
- 許可權管理策略
- 2 Day DBA-管理方案物件-關於方案物件管理許可權物件
- 2 Day DBA-管理方案物件-關於方案物件管理許可權-管理表物件
- Vue 專案實現按鈕級別許可權管理Vue
- 管理系統之許可權的設計和實現
- 基於Spring Security實現許可權管理系統Spring
- jQuery實現的管理員許可權左右移動效果jQuery
- Linux 許可權管理之目錄許可權限制Linux
- SpringSecurity許可權管理系統實戰—九、資料許可權的配置SpringGse
- Security 10：許可權管理
- SQL Server 許可權管理SQLServer
- 許可權管理[Linux]Linux
- MongoDB 3.0.8 許可權管理MongoDB