《12用户权限角色教材课程》由会员分享，可在线阅读，更多相关《12用户权限角色教材课程（14页珍藏版）》请在金锄头文库上搜索。
1、Controlling User Access(控制用户访问),Quality is not an act! Its a habit.,Objectives,After completing this lesson, you should be able to do the following: Create users(创建用户) Create roles to ease setup and maintenance of the security model(创建角色) Use the GRANT and REVOKE statements to grant and revoke objec
2、t privileges(授予和回收权限),Quality is not an act! Its a habit.,Controlling User Access(控制用户概述),Database administrator,Users,Username and password Privileges,Quality is not an act! Its a habit.,User System Privileges(用户的系统权限),Once a user is created, the DBA can grant specific system privileges to a user.(
3、DBA赋予用户系统权限) An application developer, for example, may have the following system privileges:(最常用的用户系统权限) CREATE SESSION CREATE TABLE CREATE SEQUENCE CREATE VIEW CREATE PROCEDURE,GRANT privilege , privilege. TO user , user| role, PUBLIC.;,Quality is not an act! Its a habit.,Granting System Privilege
4、s(赋权限),The DBA can grant a user specific system privileges.,GRANT create session, create table, create sequence, create view TO scott; Grant succeeded.,Quality is not an act! Its a habit.,What is a Role?(角色的概念),Allocating privileges without a role,Allocating privileges with a role,Privileges,Users,M
5、anager,Quality is not an act! Its a habit.,Creating and Granting Privileges to a Role(创建角色，赋予权限),CREATE ROLE manager; Role created.,GRANT create table, create view TO manager; Grant succeeded.,GRANT manager TO DEHAAN, KOCHHAR; Grant succeeded.,Create a role(创建角色) Grant privileges to a role(赋予角色权限) G
6、rant a role to users(赋予用户角色),Quality is not an act! Its a habit.,Object Privilege TableViewSequenceProcedure ALTER DELETE EXECUTE INDEX INSERT REFERENCES SELECT UPDATE ,Object Privileges(对象权限),Quality is not an act! Its a habit.,Granting Object Privileges(例子),Grant query privileges on the EMPLOYEES
7、table. Grant privileges to update specific columns to users and roles.,GRANT select ON employees TO sue, rich; Grant succeeded.,GRANT update (department_name, location_id) ON departments TO scott, manager; Grant succeeded.,Quality is not an act! Its a habit.,Using the WITH GRANT OPTION and PUBLIC Ke
8、ywords,Give a user authority to pass along privileges.(With grant option可传递权限) Allow all users on the system to query data from Alices DEPARTMENTS table.(Public是所有用户),GRANT select, insert ON departments TO scott WITH GRANT OPTION; Grant succeeded.,GRANT select ON alice.departments TO PUBLIC; Grant s
9、ucceeded.,Quality is not an act! Its a habit.,How to Revoke Object Privileges(回收权限),You use the REVOKE statement to revoke privileges granted to other users. Privileges granted to others through the WITH GRANT OPTION clause are also revoked.(With grant option权限也同时回收),REVOKE privilege , privilege.|AL
10、L ON object FROM user, user.|role|PUBLIC CASCADE CONSTRAINTS;,Quality is not an act! Its a habit.,Revoking Object Privileges(回收权限例子),As user Alice, revoke the SELECT and INSERT privileges given to user Scott on the DEPARTMENTS table.,REVOKE select, insert ON departments FROM scott; Revoke succeeded.
11、,Quality is not an act! Its a habit.,Summary(小结),StatementAction CREATE USERCreates a user (usually performed by a DBA) GRANTGives other users privileges to access the your objects CREATE ROLECreates a collection of privileges (usually performed by a DBA) ALTER USERChanges a users password REVOKERemoves privileges on an object fromusers,In this lesson, you should have learned about DCL statements that control access to the database and database objects:,
