用户权限角色ppt课件.ppt

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 AccessControlling User Access( (控制用控制用户户概述概述) )DatabaseadministratorUsersUsername and passwordPrivilegesCreating Users(创建用户)The DBA creates users by using the CREATE USER statement.CREATE USER scottIDENTIFIED BY tiger;User created.CREATE USER user IDENTIFIED BY password;User System Privileges(用户的系统权限)•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 SESSION•CREATE TABLE•CREATE SEQUENCE•CREATE VIEW•CREATE PROCEDUREGRANT privilege [, privilege...]TO user [, user| role, 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.What is a Role?What is a Role?( (角色的概念角色的概念) )Allocating privilegeswithout a roleAllocating privilegeswith a rolePrivilegesUsersManagerCreating and Granting Privileges to a RoleCreating and Granting Privileges to a RoleCreating 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(赋予角色权限)•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 EMPLOYEES table.•Grant privileges to update specific columns to users and roles. GRANT selectON employeesTO sue, rich;Grant succeeded.GRANT update (department_name, location_id)ON departmentsTO scott, manager;Grant succeeded.Using the WITH GRANT OPTION and PUBLIC Keywords•Give a user authority to pass along privileges.(With grant option可传送权限)•Allow all users on the system to query data from Alice’s DEPARTMENTS table.(Public是一切用户)GRANT select, insertON departmentsTO scottWITH GRANT OPTION;Grant succeeded.GRANT selectON alice.departmentsTO PUBLIC;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...]|ALL}ON objectFROM {user[, user...]|role|PUBLIC}[CASCADE 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.Summary(Summary(小小结结) )StatementActionCREATE USERCreates a user (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 user’s passwordREVOKERemoves privileges on an object fromusersIn this lesson, you should have learned about DCL statements that control access to the database and database objects:。