1、DB2創建用戶、模式、賦權舉例來了一個需求，比較簡單，需要在db2數據庫上創建一個用戶給開發人員， 只授予database相關操作權限，instance權限不給，其他數據庫操作權限不給。 常用操作主要為創建各種數據庫對象，比如表，視圖等。發現DB2數據庫里根本就沒有用戶這個概念，只有模式。那db2怎么進行權 限分離和最小化管理？再深入地查資料時，發現db2自己在數據庫體系結構內不 使用用戶的概念，但是它還是變相引用，它使用的是操作系統層面創建的用戶， 在數據庫場面對創建模式，然后對操作系統用戶、模式、database進行綁定即 可。與oracle不一樣的是，db2這里模式名字和用戶名字可以不一

2、樣。小結一下： 一個instance下可以有多個database，一個database下可以有 多個schema， 1個schema可以被多個user使用。操作步驟如下：1、操作系統下創建用戶2、db2數據庫下創建模式（也可以不創建。不創建的情況下，DB2會使用你連接 的用戶名作為默認的模式名字）3、賦權給用戶授權用戶訪問數據庫權限GRANT BINDADD ON DATABASE TO USER dstuser;GRANT CONNECT ON DATABASE TO USER dstuser;GRANT LOAD ON DATABASE TO USER dstuser;授予用戶訪問表空間的

3、權限GRANT USE OF TABLESPACE GD_MAIN_TBS TO USER dstuser;GRANT USE OF TABLESPACE GD_IDX_TBS TO USER dstuser;授予用戶操作模式的權限GRANT ALTERIN ON SCHEMA dstuser TO USER dstuser;GRANT CREATEIN ON SCHEMA dstuser TO USER dstuser;GRANT DROPIN ON SCHEMA dstuser TO USER dstuser;授予用戶讀取表權限GRANT SELECT ON TABLE ECGD.CL_C

4、OMMODITY TO USER dstuser;創建各種對象：groupadd -g 5000 appgroupuseradd -g appgroup -u 5001 dstuserpasswd dstuserdb2connect to DSTDB create schema dstuserGRANT DBADM ON DATABASE TO USER dstuser;GRANT ALTERIN ON SCHEMA dstuser TO USER dstuser; GRANT CREATEIN ON SCHEMA dstuser TO USER dstuser; GRANT DROPIN O

5、N SCHEMA dstuser TO USER dstuser;測試：connect to DSTDB user dstuser create table test(id char(10)insert into test values(T)insert into test values(2) delete from test where id=1 select * from testdrop table test查看當前 schema 命令： db2 values( current schema)設置當前 schema 命令： set current schema shemanameDB2中

6、schema管理 HYPERLINK /2012/04/db2%E4%B8%ADschema%E7%AE%A1%E7%90%86.html /2012/04/db2%E4%B8%ADschema%E7%AE%A1%E7%90%86.htmlDB2版本信息 db2inst1xifenfei $ db2levelDB21085I Instance db2inst1uses 32 bits and DB2 code release SQL09050with level identifier 03010107.Informational tokens are DB2 v, s071001, LINUX

7、IA3295, and Fix Pack0.Product is installed at /opt/db2/V9.5.1.顯示syscat.schemata 視圖結構db2inst1xifenfei $ db2 describe table syscat.schemataColumn nameData typeschemaColumnData typenameLengthScale NullsSCHEMANAMESYSIBMVARCHAR1280 NoOWNERSYSIBMVARCHAR1280 NoOWNERTYPESYSIBMCHARACTER10 NoDEFINERSYSIBMVARC

8、HAR1280 NoDEFINERTYPESYSIBMCHARACTER10 NoCREATE_TIMESYSIBMTIMESTAMP100 NoREMARKS2.查詢當 前存在schemaSYSIBMVARCHAR2540 Yesdb2inst1xifenfei $ db2SCHEMANAMEselect SCHEMANAME,owner,CREATE_TIME fromOWNERCREATE_TIMEsyscat.schemata SYSIBMSYSIBM2012-03-25-15.07.07.196612SYSCATSYSIBM2012-03-25-15.07.07.196612SYSF

9、UNSYSIBM2012-03-25-15.07.07.196612SYSSTATSYSIBM2012-03-25-15.07.07.196612SYSPROCSYSIBM2012-03-25-15.07.07.196612SYSIBMADMSYSIBM2012-03-25-15.07.07.196612SYSIBMINTERNALSYSIBM2012-03-25-15.07.07.196612SYSIBMTSSYSIBM2012-03-25-15.07.07.196612NULLIDSYSIBM2012-03-25-15.07.23.011671SQLJSYSIBM2012-03-25-15

10、.07.54.575637SYSTOOLSDB2INST12012-03-25-15.09.01.964744record(s) selected.顯示創建schemadb2inst1xifenfei $ db2 create schema xifenfeiDB20000I The SQL command completed successfully.db2inst1xifenfei $ db2 select SCHEMANAME,owner,CREATE_TIME from syscat.schemataSCHEMANAMEOWNERCREATE_TIMESYSIBMSYSIBM2012-0

11、3-25-15.07.07.196612SYSCATSYSIBM2012-03-25-15.07.07.196612SYSFUNSYSIBM2012-03-25-15.07.07.196612SYSSTATSYSIBM2012-03-25-15.07.07.196612SYSPROCSYSIBM2012-03-25-15.07.07.196612SYSIBMADMSYSIBM2012-03-25-15.07.07.196612SYSIBMINTERNALSYSIBM2012-03-25-15.07.07.196612SYSIBMTSSYSIBM2012-03-25-15.07.07.19661

12、2NULLIDSYSIBM2012-03-25-15.07.23.011671SQLJSYSIBM2012-03-25-15.07.54.575637SYSTOOLSDB2INST12012-03-25-15.09.01.964744XIFENFEIDB2INST12012-04-03-12.01.12.724932record(s) selected.4.隱式創建schemadb2inst1xifenfei $ db2createtable xff.t_xifenfei(id int,name varchar(100)DB20000I The SQL command completedsuc

13、cessfully.db2inst1xifenfei $ db2selectSCHEMANAME,owner,CREATE_TIME from syscat.schemataSCHEMANAMEOWNERCREATE_TIME SYSIBMSYSIBM-2012-03-25-15.07.07.196612SYSCATSYSIBM2012-03-25-15.07.07.196612SYSFUNSYSIBM2012-03-25-15.07.07.196612SYSSTATSYSIBM2012-03-25-15.07.07.196612SYSPROCSYSIBM2012-03-25-15.07.07

14、.196612SYSIBMADMSYSIBM2012-03-25-15.07.07.196612SYSIBMINTERNALSYSIBM2012-03-25-15.07.07.196612SYSIBMTSSYSIBM2012-03-25-15.07.07.196612NULLIDSYSIBM2012-03-25-15.07.23.011671SQLJSYSIBM2012-03-25-15.07.54.575637SYSTOOLSDB2INST12012-03-25-15.09.01.964744XIFENFEIDB2INST12012-04-03-12.01.12.724932XFFSYSIB

15、M2012-04-03-12.03.12.581260record(s) selected.隱式創建schema的所屬用戶會是SYSIBM(存放系統數據字典表SCHEMA)刪除 schemadb2inst1xifenfei $ db2 drop schema xffDB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0104N An unexpe

16、cted token END-OF-STATEMENT was found following drop schema xff. Expected tokens may include: RESTRICT. SQLSTATE=42601db2inst1xifenfei $ db2 drop schema xff restrictDB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing

17、 it returned: SQL0478N DROP, ALTER, TRANSFER OWNERSHIP or REVOKE on object typeSCHEMA cannot be processed because there is an object XFF.T_XIFENFEI, of typeTABLE, which depends on it. SQLSTATE=42893db2inst1xifenfei $ db2 drop table xff.t_xifenfeiDB20000I The SQL command completed successfully.db2ins

18、t1xifenfei $ db2 drop schema xff restrictDB20000I The SQL command completed successfully.db2inst1xifenfei $ db2 select SCHEMANAME,owner,CREATE_TIME from syscat.schemataSCHEMANAMEOWNERCREATE_TIMESYSIBMSYSIBM2012-03-25-15.07.07.196612SYSCATSYSIBM2012-03-25-15.07.07.196612SYSFUNSYSIBM2012-03-25-15.07.0

19、7.196612SYSSTATSYSIBM2012-03-25-15.07.07.196612SYSPROCSYSIBM2012-03-25-15.07.07.196612SYSIBMADMSYSIBM2012-03-25-15.07.07.196612SYSIBMINTERNALSYSIBM2012-03-25-15.07.07.196612SYSIBMTSSYSIBM2012-03-25-15.07.07.196612NULLIDSYSIBM2012-03-25-15.07.23.011671SQLJSYSIBM2012-03-25-15.07.54.575637SYSTOOLSDB2IN

20、ST12012-03-25-15.09.01.964744XIFENFEIDB2INST12012-04-03-12.01.12.72493212 record(s) selected.刪除schema需要使用restrict關鍵字，而且該schema中無對象存在.在DB2中的schema的概念和ORACLE中的概念有著本質的區別：在ORACLE中schema 和用戶是同一個在DB2中schema不一定是用戶，因為db2內部沒有用戶的概念，連接用戶必 須是操作系統用戶目錄：顯式建立schema隱式建立schema查詢現有的schema刪除 schema顯式建立schema執行create sc

21、hema需要有DBADM權限建立某個schema需要有SYSADM和DBAMIN權限總的來說就是需要SYSADM和DBAMIN權限Sql代碼語法CREATE SCHEMA name AUTHORIZATION name-如果不輸入AUTHORIZATION，就是執行命令的本人db2 = create schema db2user12DB20000I The SQL command completed successfully.db2 = select SCHEMANAME,OWNER from syscat.schemata where schemaname=DB2USER12SCHEMANA

22、ME OWNERDB2USER12 DB2INST11 record(s) selected.隱式建立schema 如果你沒有SYSADM,DBADMIN權限，但是你有IMPLICIT_SCHEMA，那么你也可以建立schemaSql代碼巨空-查詢用戶沒有DBADMIN，但是有IMPLICIT_SCHEMAdb2 = GET AUTHORIZATIONSAdministrative Authorizations for Current UserDirect SYSADM authority = NODirect SYSCTRL authority = NODirect SYSMAINT aut

23、hority = NODirect DBADM authority = NODirect CREATETAB authority = NODirect BINDADD authority = NODirect CONNECT authority = NODirect CREATE_NOT_FENC authority = NODirect IMPLICIT_SCHEMA authority = NODirect LOAD authority = NODirect QUIESCE_CONNECT authority = NODirect CREATE_EXTERNAL_ROUTINE autho

24、rity = NODirect SYSMON authority = NOIndirectSYSADM authority = YESIndirectSYSCTRL authority = NOIndirectSYSMAINT authority = NOIndirectDBADM authority = NOIndirectCREATETAB authority = YESIndirectBINDADD authority = YESIndirectCONNECT authority = YESIndirectCREATE_NOT_FENC authority=NOIndirectIMPLICIT_SCHEMA authority=YESIndirectLOAD authority = NOIndirectQUIESCE_CONNECT authority=NOIndirect CREATE_EXTERNAL_ROUTINE authority = NOIndirect SYSMON authority = NO-顯式創立失敗db2 = create schema db2user11DB21034E The command was processed as an SQL statement because it was not avalid Command Line