記錄的一部份權限管理相關的東東。
實例級權限: SYSADM 最高管理權限,通常供DBA使用 SYSCTRL 最高系統控制權限,有SYSMAINT 和SYSMON 的全部權限 SYSMAINT 次級系統 控制權限 SYSMON 最小的實例級管理權限DB2實例級的管理權限是和操作系統用戶綁定的.所以需先創建操作系統用戶. 例子: 假設有用戶: db2ctlusr 組: db2ctlgrp Linux: groupadd db2ctlgrp useradd -g db2ctlgrp -m -d /home/db2ctlusr db2ctlusr AIX: groupadd -g 996 db2ctlgrp mkuser id=1005 pgrp=db2ctlgrp home/home/db2ctlusr db2ctlusr
[root@O11g64 bin]# id db2ctlusr uid=1053(db2ctlusr) gid=1021(db2ctlgrp) groups=1021(db2ctlgrp)
對比db2inst1: $ id db2inst1 uid=110(db2inst1) gid=104(db2grp1) groups=1(staff),101(dasadm1) db2inst1 db2iadm1 DB2實例用戶,用戶組 db2fenc1 db2fadm1 受防護的用戶,用戶組. 運行自定義函數和過程 dasusr1 dasadm1 創建DB2管理服務器用戶,用戶組.
1. 參數中指定用戶組: a. [root@O11g64 bin]# ./db2 get dbm cfg|grep "SYSCTRL"
SYSCTRL group name (SYSCTRL_GROUP) =
2.授與用戶權限 db2 connect to xcldb
b. [db2inst1@O11g64 ~]$ db2 update dbm cfg using SYSCTRL_GROUP db2ctlgrp DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully. SQL1362W One or more of the parameters submitted for immediate modification were not changed dynamically. Client changes will not be effective until the next time the application is started or the TERMINATE command has been issued. Server changes will not be effective until the next DB2START command.
c. db2stop d. db2start
e. [db2inst1@O11g64 ~]$ db2 get dbm cfg|grep "SYSCTRL" SYSCTRL group name (SYSCTRL_GROUP) = DB2CTLGRP
db2 "grant dbadm on database to user db2ctlusr"
db2 connect reset
3.收回權限
db2 connect to xcldb db2 " revoke dbadm on database from user db2ctlusr " db2 connect reset
還有更細粒度的訪問控制,基於標簽的訪問控制(LBAC)
常見問題 : 報SQL5001N錯誤: 原因是需要SYSADM權限才能更改數據庫管理器配置文件 使用下面命令查出組 [db2inst1@O11g64 ~]$ db2 get dbm cfg|grep "SYSADM" SYSADM group name (SYSADM_GROUP) = DB2IADM1 進入此組下的用戶,再去執行更新權限 [root@O11g64 bin]# id db2inst1 uid=1051(db2inst1) gid=1010(db2iadm1) groups=1010(db2iadm1),1020(db2fadm1)
表授權命令: -- public是全部用戶 grant select on emp to group db2ctlgrp2
查詢所有與權限相關的系統表: db2 " select substr(tabname,1,20) from syscat.tables where tabschema='SYSCAT' and tabname like '%AUTH' "; db2 " select * from DBAUTH"
角色 create role myrole grant select on table vacation to rle myrole grant role myrole to user myusr1,user myus2 revoke role myrole from user myusr1;
-- 用戶可以授予或撤消其它人角色,這個到和Oracle一個樣 grant role myrole to user myusr1 with admin option
DB2實例級的管理權限是和操作系統用戶綁定的.所以需先創建操作系統用戶. 例子: 假設有用戶: db2ctlusr 組: db2ctlgrp Linux: groupadd db2ctlgrp useradd -g db2ctlgrp -m -d /home/db2ctlusr db2ctlusr AIX: groupadd -g 996 db2ctlgrp mkuser id=1005 pgrp=db2ctlgrp home/home/db2ctlusr db2ctlusr
[root@O11g64 bin]# id db2ctlusr uid=1053(db2ctlusr) gid=1021(db2ctlgrp) groups=1021(db2ctlgrp)
db2inst1 db2iadm1 DB2實例用戶,用戶組 db2fenc1 db2fadm1 受防護的用戶,用戶組. 運行自定義函數和過程 dasusr1 dasadm1 創建DB2管理服務器用戶,用戶組.
1. 參數中指定用戶組: [root@O11g64 bin]# ./db2 get dbm cfg|grep "SYSCTRL"
SYSCTRL group name (SYSCTRL_GROUP) =
2.授與用戶權限 db2 connect to xcldb
[db2inst1@O11g64 ~]$ db2 update dbm cfg using SYSCTRL_GROUP db2ctlgrp DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully. SQL1362W One or more of the parameters submitted for immediate modification were not changed dynamically. Client changes will not be effective until the next time the application is started or the TERMINATE command has been issued. Server changes will not be effective until the next DB2START command.
db2stop db2start
[db2inst1@O11g64 ~]$ db2 get dbm cfg|grep "SYSCTRL" SYSCTRL group name (SYSCTRL_GROUP) = DB2CTLGRP
db2 "grant dbadm on database to user db2ctlusr"
db2 connect reset
3.收回權限
db2 connect to xcldb db2 " revoke dbadm on database from user db2ctlusr " db2 connect reset
常見問題 : 報SQL5001N錯誤: 原因是需要SYSADM權限才能更改數據庫管理器配置文件 使用下面命令查出組 [db2inst1@O11g64 ~]$ db2 get dbm cfg|grep "SYSADM" SYSADM group name (SYSADM_GROUP) = DB2IADM1 進入此組下的用戶,再去執行更新權限 [root@O11g64 bin]# id db2inst1 uid=1051(db2inst1) gid=1010(db2iadm1) groups=1010(db2iadm1),1020(db2fadm1)
[root@O11g64 bin]# ./db2 update dbm cfg using SYSCTL_GROUP db2ctlgrp SQL0104N An unexpected token "SYSCTL_GROUP" was found following "USING". Expected tokens may include: "AGENTPRI". SQLSTATE=42601
其它的 -- public是全部用戶 grant select on emp to group db2ctlgrp2
查詢 -- 所有與權限相關的系統表 db2 " select substr(tabname,1,20) from syscat.tables where tabschema='SYSCAT' and tabname like '%AUTH' "; db2 " select * from DBAUTH"
角色 create role myrole grant select on table vacation to rle myrole grant role myrole to user myusr1,user myus2 revoke role myrole from user myusr1;
-- 用戶可以授予或撤消其它人角色,這個到和Oracle一個樣 grant role myrole to user myusr1 with admin option
還有更細粒度的訪問控制,基於標簽的訪問控制(LBAC) 工作原理是在 行級,列級或行列同時設置 安全性標簽,以便特定用戶 是否可以訪問一個表中的某些行或列.
這個好像沒用上,不深究了.
