用户权限角色.pptx

Quality is not an act!Its a habit.Quality is not an act!Its a habit.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(授予和回收权限)Quality is not an act!Its a habit.Quality is not an act!Its a habit.Controlling User AccessControlling User Access(控制用户概述控制用户概述)DatabaseadministratorUsersUsername and passwordPrivilegesQuality is not an act!Its a habit.Quality is not an act!Its a habit.Creating 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;Quality is not an act!Its a habit.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.(DBA赋予用户系统权限)An application developer,for example,may have the following system privileges:(最常用的用户系统权限)CREATE SESSIONCREATE TABLECREATE SEQUENCECREATE VIEWCREATE PROCEDUREGRANT privilege,privilege.TO user,user|role,PUBLIC.;Quality is not an act!Its a habit.Quality is not an act!Its a habit.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.Quality is not an act!Its a habit.Quality is not an act!Its a habit.What is a Role?What is a Role?(角色的概念角色的概念)Allocating privilegeswithout a roleAllocating privilegeswith a rolePrivilegesUsersManagerQuality is not an act!Its a habit.Quality is not an act!Its a habit.Creating 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.Grant succeeded.Create a role(创建角色)Grant privileges to a role(赋予角色权限)Grant a role to users(赋予用户角色)Quality is not an act!Its a habit.Quality is not an act!Its a habit.Object Privilege TableViewSequenceProcedureALTER DELETE EXECUTE INDEX INSERT REFERENCES SELECT UPDATE Object Privileges(Object Privileges(对象权限对象权限)Quality is not an act!Its a habit.Quality is not an act!Its a habit.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 succeeded.GRANT update(department_name,location_id)ON departmentsTO scott,manager;Grant succeeded.Grant succeeded.Quality is not an act!Its a habit.Quality is not an act!Its a habit.Using the WITH GRANT OPTION and PUBLIC KeywordsGive 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 succeeded.Grant succeeded.Quality is not an act!Its a habit.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.|ALLON objectFROM user,user.|role|PUBLICCASCADE CONSTRAINTS;Quality is not an act!Its a habit.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,insertON departmentsFROM scott;Revoke succeeded.Revoke succeeded.Quality is not an act!Its a habit.Quality is not an act!Its a habit.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 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: