26.oracleocp題庫dba經(jīng)典輔助學(xué)習(xí)

1、教學(xué)目標(biāo)控制用戶學(xué)完本節(jié)課之后,你會(huì)明白如下知識(shí):區(qū)別對(duì)象權(quán)限和系統(tǒng)權(quán)限的區(qū)別在表上授予權(quán)限授予角色區(qū)別權(quán)限和角色數(shù)據(jù)1數(shù)據(jù)2系統(tǒng)權(quán)限可用的權(quán)限有100多個(gè)數(shù)據(jù)庫管理員有次的系統(tǒng)權(quán)限，可以完成下面的任務(wù):創(chuàng)建一個(gè)新用戶刪除用戶刪除表備份表數(shù)據(jù)6權(quán)限數(shù)據(jù)庫安全:系統(tǒng)安全數(shù)據(jù)安全系統(tǒng)權(quán)限:數(shù)據(jù)庫中執(zhí)行一個(gè)特定操作對(duì)象權(quán)限:數(shù)據(jù)庫的對(duì)象的內(nèi)容數(shù)據(jù)5控制用戶Database administratorUsers數(shù)據(jù)4Username and passwordPrivileges課程安排系統(tǒng)創(chuàng)建一個(gè)角色對(duì)象撤銷對(duì)象數(shù)據(jù)3創(chuàng)建用戶用戶的系統(tǒng)權(quán)限D(zhuǎn)BA使用CREATE USER語句創(chuàng)建用戶用戶被創(chuàng)建后,DB

2、A可以對(duì)那個(gè)用戶授予特殊的系統(tǒng)權(quán)限一個(gè)應(yīng)用程序開發(fā)者, 舉例來說, 可能有下列的系統(tǒng)CREATE SESCREATE TABLECREATE SEQUENCECREATE VIEWCREATE PROCEDURE:數(shù)據(jù)7數(shù)據(jù)8創(chuàng)建和賦予一個(gè)用戶權(quán)限創(chuàng)建一個(gè)角色:賦予給角色一個(gè)權(quán)限:賦予一個(gè)角色給用戶:數(shù)據(jù)12GRANT manager TO BELL, KOCHHAR;GRANT create table, create view TO manager;CREATE ROLE manager;角色?用戶Manager權(quán)限不使用角色分配權(quán)限使用角色分配權(quán)限數(shù)據(jù)11課程安排系統(tǒng)創(chuàng)建一個(gè)角色對(duì)象撤銷

3、對(duì)象數(shù)據(jù)10賦予系統(tǒng)權(quán)限D(zhuǎn)BA能賦予某的系統(tǒng)權(quán)限給用戶數(shù)據(jù)9GRANT create ses, create table, create sequence, create viewTOdemo;GRANT privilege , privilege. TO user , user| role, PUBLIC.;CREATE USER demo IDENTIFIED BY demo;CREATE USER userIDENTIFIED BYpassword;修改你的DAB創(chuàng)建你的用戶帳號(hào)和初始化你的你能使用ALTER USER 語句改變你的數(shù)據(jù)13傳遞授予用戶給其他用戶的權(quán)限:允許系統(tǒng)上的所有用

4、戶從Alice的DEPARTMENTS表中查詢數(shù)據(jù):數(shù)據(jù)18GRANT selectONalice.departments TOPUBLIC;GRANT select, insert ONdepartmentsTOdemoWITHGRANT OPTION;賦予對(duì)象權(quán)限在EMPLOYEES 表中賦予查詢:賦予更新某列的給用戶和角色:數(shù)據(jù)17GRANT update (department_name, location_id) ONdepartmentsTOdemo, manager;GRANT select ONemployeesTOdemo;對(duì)象每種對(duì)象的權(quán)限都不同對(duì)象宿主擁有在對(duì)象上全部的權(quán)

5、限對(duì)象宿主能授予對(duì)象權(quán)限給其他用戶。數(shù)據(jù)16GRANTobject_priv (columns) ONobjectTOuser|role|PUBLIC WITH GRANT OPTION;對(duì)象數(shù)據(jù)15Object privilegeTableViewSequenceALTERDELETEINDEXINSERTREFERENSELECTUPDATE課程安排系統(tǒng)創(chuàng)建一個(gè)角色對(duì)象撤銷對(duì)象數(shù)據(jù)14ALTER USER demo IDENTIFIED BY employ;檢查賦予的天才數(shù)據(jù)19練: 回顧這個(gè)練習(xí)包括如下:給你的表賦予其他用戶權(quán)限通過給你賦予權(quán)限修改其他用戶的表創(chuàng)建一個(gè)同義字 （synon

6、ym）天才數(shù)據(jù)24總結(jié)在這課中,你學(xué)到了關(guān)于語句對(duì)數(shù)據(jù)庫和數(shù)據(jù)庫對(duì)象控制天才數(shù)據(jù)23語句活動(dòng)CREATE USERCreates a user (usually performed by a DBA)GRANTGives other users privileges to ac s the objectsCREATE ROLECreates a collection of privileges (usually performed by a DBA)ALTER USERChanges a users passwordREVOKERemoves privileges on an object f

7、rom users撤消對(duì)象權(quán)限在demo 用戶的DEPARTMENTS表中，撤消SELECT 和 INSERT 權(quán)限天才數(shù)據(jù)22REVOKE select, insert ONdepartmentsFROMdemo;撤消對(duì)象權(quán)限使用REVOKE 語句來撤消曾賦予給用戶的權(quán)限通過WITH GRANT OPTION子句被傳遞給其他用戶的權(quán)限也被撤消天才數(shù)據(jù)21REVOKE privilege , privilege.|ALL ONobjectFROMuser, user.|role|PUBLIC CASCADE CONSTRAS;課程安排系統(tǒng)創(chuàng)建一個(gè)角色對(duì)象撤銷對(duì)象天才數(shù)據(jù)20數(shù)據(jù)字典視圖描述ROLE_SYS_PRIVS授予角色的系統(tǒng)權(quán)限ROLE_TAB_PRIVS授予角色的表權(quán)限USER_ROLE_PRIVS授予用戶的角色USER_SYS_PRIVS授予用戶的系統(tǒng)權(quán)限USER