用户权限角色ppt课件

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(授予和回收权限)Databaseadministrato

2、rUsersUsername and passwordPrivilegesCreating 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 privil

3、eges 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

4、 a user specific system privileges.GRANT create session, create table, create sequence, create viewTO scott;Grant succeeded.Allocating privilegeswithout a roleAllocating privilegeswith a rolePrivilegesUsersManagerCREATE ROLE manager;Role created. GRANT create table, create view TO manager; Grant suc

5、ceeded. 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 Granting Object Privileges(例子) Grant query privileges o

6、n 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 KeywordsGive a user authority

7、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 selectON alice.departmentsTO PUBLIC;Grant succeeded.How to Revoke Object Privilege

8、s(回收权限) 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;Revoking Object Pri

9、vileges(回收权限例子)As user Alice, revoke the SELECT and INSERT privileges given to user Scott on the DEPARTMENTS table.REVOKE select, insertON departmentsFROM scott;Revoke succeeded.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 pr