DB2創(chuàng)建用戶、模式、賦權(quán)舉例

1、DB2創(chuàng)建用戶、模式、賦權(quán)舉例來(lái)了一個(gè)需求，比較簡(jiǎn)單，需要在db2數(shù)據(jù)庫(kù)上創(chuàng)建一個(gè)用戶給開(kāi)發(fā)人員， 只授予database相關(guān)操作權(quán)限，instance權(quán)限不給，其他數(shù)據(jù)庫(kù)操作權(quán)限不給。 常用操作主要為創(chuàng)建各種數(shù)據(jù)庫(kù)對(duì)象，比如表，視圖等。發(fā)現(xiàn)DB2數(shù)據(jù)庫(kù)里根本就沒(méi)有用戶這個(gè)概念，只有模式。那db2怎么進(jìn)行權(quán) 限分離和最小化管理？再深入地查資料時(shí)，發(fā)現(xiàn)db2自己在數(shù)據(jù)庫(kù)體系結(jié)構(gòu)內(nèi)不 使用用戶的概念，但是它還是變相引用，它使用的是操作系統(tǒng)層面創(chuàng)建的用戶， 在數(shù)據(jù)庫(kù)場(chǎng)面對(duì)創(chuàng)建模式，然后對(duì)操作系統(tǒng)用戶、模式、database進(jìn)行綁定即 可。與oracle不一樣的是，db2這里模式名字和用戶名字可以不一

2、樣。小結(jié)一下： 一個(gè)instance下可以有多個(gè)database，一個(gè)database下可以有 多個(gè)schema， 1個(gè)schema可以被多個(gè)user使用。操作步驟如下：1、操作系統(tǒng)下創(chuàng)建用戶2、db2數(shù)據(jù)庫(kù)下創(chuàng)建模式（也可以不創(chuàng)建。不創(chuàng)建的情況下，DB2會(huì)使用你連接 的用戶名作為默認(rèn)的模式名字）3、賦權(quán)給用戶授權(quán)用戶訪問(wèn)數(shù)據(jù)庫(kù)權(quán)限GRANT BINDADD ON DATABASE TO USER dstuser;GRANT CONNECT ON DATABASE TO USER dstuser;GRANT LOAD ON DATABASE TO USER dstuser;授予用戶訪問(wèn)表空間的

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

4、OMMODITY TO USER dstuser;創(chuàng)建各種對(duì)象：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;測(cè)試：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查看當(dāng)前 schema 命令： db2 values( current schema)設(shè)置當(dāng)前 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 視圖結(jié)構(gòu)db2inst1xifenfei $ db2 describe table syscat.schemataColumn nameData typeschemaColumnData typenameLengthScale NullsSCHEMANAMESYSIBMVARCHAR1280 NoOWNERSYSIBMVARCHAR1280 NoOWNERTYPESYSIBMCHARACTER10 NoDEFINERSYSIBMVARC

8、HAR1280 NoDEFINERTYPESYSIBMCHARACTER10 NoCREATE_TIMESYSIBMTIMESTAMP100 NoREMARKS2.查詢當(dāng) 前存在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.顯示創(chuàng)建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.隱式創(chuàng)建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.隱式創(chuàng)建schema的所屬用戶會(huì)是SYSIBM(存放系統(tǒng)數(shù)據(jù)字典表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關(guān)鍵字，而且該schema中無(wú)對(duì)象存在.在DB2中的schema的概念和ORACLE中的概念有著本質(zhì)的區(qū)別：在ORACLE中schema 和用戶是同一個(gè)在DB2中schema不一定是用戶，因?yàn)閐b2內(nèi)部沒(méi)有用戶的概念，連接用戶必 須是操作系統(tǒng)用戶目錄：顯式建立schema隱式建立schema查詢現(xiàn)有的schema刪除 schema顯式建立schema執(zhí)行create sc

21、hema需要有DBADM權(quán)限建立某個(gè)schema需要有SYSADM和DBAMIN權(quán)限總的來(lái)說(shuō)就是需要SYSADM和DBAMIN權(quán)限Sql代碼語(yǔ)法CREATE SCHEMA name AUTHORIZATION name-如果不輸入AUTHORIZATION，就是執(zhí)行命令的本人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 如果你沒(méi)有SYSADM,DBADMIN權(quán)限，但是你有IMPLICIT_SCHEMA，那么你也可以建立schemaSql代碼巨空-查詢用戶沒(méi)有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-顯式創(chuàng)立失敗db2 = create schema db2user11DB21034E The command was processed as an SQL statement because it was not avalid Command Line