用户权限角色.ppt

《用户权限角色.ppt》由会员分享，可在线阅读，更多相关《用户权限角色.ppt（14页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Controlling User Access(控制用户访问)ObjectivesAfter 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 object privileges(授予和回收权限)Controlling User Acce

2、ssControlling User Access(控制用户概述控制用户概述)DatabaseadministratorUsersUsername and passwordPrivilegesCreating Users(创建用户)The DBA creates users by using the CREATE USER statement.CREATE USER scottIDENTIFIED BY tiger;User created.User created.CREATE USER user IDENTIFIED BY password;User System Privileges(用

3、户的系统权限)Once a user is created,the DBA can grant specific system privileges to a user.(DBA赋予用户系统权限)An application developer,for example,may have the following system privileges:(最常用的用户系统权限)CREATE SESSIONCREATE TABLECREATE SEQUENCECREATE VIEWCREATE PROCEDUREGRANT privilege,privilege.TO user,user|role,

4、PUBLIC.;Granting System Privileges(赋权限)The DBA can grant a user specific system privileges.GRANT create session,create table,create sequence,create viewTO scott;Grant succeeded.Grant succeeded.What is a Role?What is a Role?(角色的概念角色的概念)Allocating privilegeswithout a roleAllocating privilegeswith a ro

5、lePrivilegesUsersManagerCreating and Granting Privileges to a RoleCreating and Granting Privileges to a Role(创建角色，赋予权限创建角色，赋予权限)CREATE ROLE manager;Role created.Role created.GRANT create table,create view TO manager;Grant succeeded.Grant succeeded.GRANT manager TO DEHAAN,KOCHHAR;Grant succeeded.Gran

6、t succeeded.Create a role(创建角色)Grant privileges to a role(赋予角色权限)Grant a role to users(赋予用户角色)Object Privilege TableViewSequenceProcedureALTER DELETE EXECUTE INDEX INSERT REFERENCES SELECT UPDATE Object Privileges(Object Privileges(对象权限对象权限)Granting Object Privileges(例子)Grant query privileges on the

7、 EMPLOYEES table.Grant privileges to update specific columns to users and roles.GRANT selectON employeesTO sue,rich;Grant succeeded.Grant succeeded.GRANT update(department_name,location_id)ON departmentsTO scott,manager;Grant succeeded.Grant succeeded.Using the WITH GRANT OPTION and PUBLIC KeywordsG

8、ive 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,insertON departmentsTO scottWITH GRANT OPTION;Grant succeeded.Grant succeeded.GRANT selectON alice.departmentsTO PUBLIC;Grant succ

9、eeded.Grant succeeded.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.|ALLON objectFROM user,user.|rol

10、e|PUBLICCASCADE CONSTRAINTS;Revoking Object Privileges(回收权限例子)As user Alice,revoke the SELECT and INSERT privileges given to user Scott on the DEPARTMENTS table.REVOKE select,insertON departmentsFROM scott;Revoke succeeded.Revoke succeeded.Summary(Summary(小结小结)StatementActionCREATE USERCreates a use

11、r(usually performed by a DBA)GRANTGives other users privileges to access the your objectsCREATE ROLECreates a collection of privileges(usually performed by a DBA)ALTER USERChanges a users passwordREVOKERemoves privileges on an object fromusersIn this lesson,you should have learned about DCL statements that control access to the database and database objects: