《控制用戶權(quán)限》PPT課件

13Copyright Oracle Corporation, 2001. All rights reserved.控制用戶權(quán)限 13-2 Copyright Oracle Corporation, 2001. All rights reserved. 目標(biāo)通過本章學(xué)習(xí)，您將可以:創(chuàng)建用戶創(chuàng)建角色使用GRANT 和 REVOKE 語句賦予和回收權(quán)限創(chuàng)建數(shù)據(jù)庫聯(lián)接 13-3 Copyright Oracle Corporation, 2001. All rights reserved. 控制用戶權(quán)限數(shù)據(jù)庫管理員用戶用戶名和密碼權(quán)限 13-4 Copyright Oracle Corporation, 2001. All rights reserved. 權(quán)限數(shù)據(jù)庫安全性:系統(tǒng)安全性數(shù)據(jù)安全性系統(tǒng)權(quán)限: 對(duì)于數(shù)據(jù)庫的權(quán)限對(duì)象權(quán)限: 操作數(shù)據(jù)庫對(duì)象的權(quán)限方案: 一組數(shù)據(jù)庫對(duì)象集合, 例如表, 視圖,和序列 13-5 Copyright Oracle Corporation, 2001. All rights reserved. 系統(tǒng)權(quán)限超過一百多種 100 有效的權(quán)限數(shù)據(jù)庫管理員具有高級(jí)權(quán)限以完成管理任務(wù)，例如:創(chuàng)建新用戶刪除用戶刪除表備份表 13-6 Copyright Oracle Corporation, 2001. All rights reserved. 創(chuàng)建用戶DBA 使用 CREATE USER 語句創(chuàng)建用戶CREATE USER scottIDENTIFIED BY tiger;CREATE USER user IDENTIFIED BY password; 13-7 Copyright Oracle Corporation, 2001. All rights reserved. 用戶的系統(tǒng)權(quán)限用戶創(chuàng)建之后, DBA 會(huì)賦予用戶一些系統(tǒng)權(quán)限以應(yīng)用程序開發(fā)者為例, 一般具有下列系統(tǒng)權(quán)限:CREATE SESSION（創(chuàng)建會(huì)話）CREATE TABLE（創(chuàng)建表）CREATE SEQUENCE（創(chuàng)建序列）CREATE VIEW（創(chuàng)建視圖）CREATE PROCEDURE（創(chuàng)建過程）GRANT privilege , privilege.TO user , user| role, PUBLIC.; 13-8 Copyright Oracle Corporation, 2001. All rights reserved. 賦予系統(tǒng)權(quán)限 DBA 可以賦予用戶特定的權(quán)限GRANT create session, create table, create sequence, create viewTO scott; 13-9 Copyright Oracle Corporation, 2001. All rights reserved. 角色不使用角色分配權(quán)限使用角色分配權(quán)限權(quán)限用戶Manager 13-10 Copyright Oracle Corporation, 2001. All rights reserved. 創(chuàng)建角色并賦予權(quán)限CREATE ROLE manager; GRANT create table, create view TO manager; GRANT manager TO DEHAAN, KOCHHAR; 創(chuàng)建角色為角色賦予權(quán)限將角色賦予用戶 13-11 Copyright Oracle Corporation, 2001. All rights reserved. 修改密碼 DBA 可以創(chuàng)建用戶和修改密碼用戶本人可以使用ALTER USER 語句修改密碼ALTER USER scott IDENTIFIED BY lion; 13-12 Copyright Oracle Corporation, 2001. All rights reserved. 對(duì)象權(quán)限 表視圖 序列過程修改 刪除 執(zhí)行 索引 插入 關(guān)聯(lián) 選擇 更新 對(duì)象權(quán)限 13-13 Copyright Oracle Corporation, 2001. All rights reserved. 對(duì)象權(quán)限不同的對(duì)象具有不同的對(duì)象權(quán)限對(duì)象的擁有者擁有所有權(quán)限對(duì)象的擁有者可以向外分配權(quán)限 GRANTobject_priv (columns) ONobject TOuser|role|PUBLIC WITH GRANT OPTION; 13-14 Copyright Oracle Corporation, 2001. All rights reserved. 分配對(duì)象權(quán)限分配表 EMPLOYEES 的查詢權(quán)限分配表中各個(gè)列的更新權(quán)限GRANT selectON employeesTO sue, rich;GRANT update (department_name, location_id) ON departmentsTO scott, manager 13-15 Copyright Oracle Corporation, 2001. All rights reserved. WITH GRANT OPTION 和 PUBLIC 關(guān)鍵字WITH GRANT OPTION 使用戶同樣具有分配權(quán)限的權(quán)利向數(shù)據(jù)庫中所有用戶分配權(quán)限GRANT select, insertON departmentsTO scottWITH GRANT OPTION;GRANT select ON alice.departmentsTO PUBLIC; 13-16 Copyright Oracle Corporation, 2001. All rights reserved. 查詢權(quán)限分配情況 數(shù)據(jù)字典視圖描述ROLE_SYS_PRIVS角色擁有的系統(tǒng)權(quán)限ROLE_TAB_PRIVS角色擁有的對(duì)象權(quán)限USER_ROLE_PRIVS用戶擁有的角色USER_TAB_PRIVS_MADE用戶分配的關(guān)于表對(duì)象權(quán)限USER_TAB_PRIVS_RECD用戶擁有的關(guān)于表對(duì)象權(quán)限USER_COL_PRIVS_MADE用戶分配的關(guān)于列的對(duì)象權(quán)限USER_COL_PRIVS_RECD用戶擁有的關(guān)于列的對(duì)象權(quán)限USER_SYS_PRIVS用戶擁有的系統(tǒng)權(quán)限 13-17 Copyright Oracle Corporation, 2001. All rights reserved. 收回對(duì)象權(quán)限使用 REVOKE 語句收回權(quán)限使用 WITH GRANT OPTION 子句所分配的權(quán)限同樣被收回REVOKE privilege , privilege.|ALLON objectFROM user, user.|role|PUBLICCASCADE CONSTRAINTS; 13-18 Copyright Oracle Corporation, 2001. All rights reserved. 收回對(duì)象權(quán)限舉例REVOKE select, insertON departmentsFROM scott; 13-19 Copyright Oracle Corporation, 2001. All rights reserved. 數(shù)據(jù)庫聯(lián)接數(shù)據(jù)庫聯(lián)接使用戶可以在本地訪問遠(yuǎn)程數(shù)據(jù)庫 本地?cái)?shù)據(jù)庫遠(yuǎn)程數(shù)據(jù)庫 SELECT * FROM empHQ_ACME.COM;HQ_ACME.COMdatabaseEMP Table 13-20 Copyright Oracle Corporation, 2001. All rights reserved. 數(shù)據(jù)庫聯(lián)接創(chuàng)建數(shù)據(jù)庫聯(lián)接使用SQL 語句訪問遠(yuǎn)程數(shù)據(jù)庫CREATE PUBLIC DATABASE LINK USING sales;Database link created.SELECT * FROM empHQ.ACME.COM; 13-21 Copyright Oracle Corporation, 2001. All rights reserved. 總結(jié)語句功能CREATE USER創(chuàng)建用戶 (通常由 DBA 完成)GRANT分配權(quán)限CREATE ROLE創(chuàng)建角色 (通常由 DBA 完成)ALTER USER修改用戶密碼REVOKE收回權(quán)限通過本章學(xué)習(xí),您已經(jīng)可以使用 DCL 控制數(shù)據(jù)庫權(quán)限，創(chuàng)建數(shù)據(jù)庫聯(lián)接: 13-22 Copyright Oracle Corporation, 2001. All rights reserved. 13-23 Copyright Oracle Corporation, 2001. All rights reserved. 13-24 Copyright Oracle Corporation, 2001. All rights reserved. 13-25 Copyright Oracle Corporation, 2001. All rights reserved.