用戶權(quán)限角色

Controlling User Access(控制用戶訪問) ObjectivesAfter completing this lesson, you should be able to do the following: Create users(創(chuàng)建用戶) Create roles to ease setup and maintenance of the security model(創(chuàng)建角色) Use the GRANT and REVOKE statements to grant and revoke object privileges(授予和回收權(quán)限) Controlling User Access(控制用戶概述)DatabaseadministratorUsersUsername and passwordPrivileges Creating Users(創(chuàng)建用戶)The DBA creates users by using the CREATE USER statement.CREATE USER scottIDENTIFIED BY tiger;CREATE USER user IDENTIFIED BY password; User System Privileges(用戶的系統(tǒng)權(quán)限) Once a user is created, the DBA can grant specific system privileges to a user.(DBA賦予用戶系統(tǒng)權(quán)限) An application developer, for example, may have the following system privileges:(最常用的用戶系統(tǒng)權(quán)限)CREATE SESSIONCREATE TABLE CREATE SEQUENCECREATE VIEWCREATE PROCEDUREGRANT privilege , privilege.TO user , user| role, PUBLIC.; Granting System Privileges(賦權(quán)限)The DBA can grant a user specific system privileges.GRANT create session, create table, create sequence, create viewTO scott; What is a Role?(角色的概念)Allocating privileges without a role Allocating privilegeswith a rolePrivilegesUsers Manager Creating and Granting Privileges to a Role(創(chuàng)建角色，賦予權(quán)限)CREATE ROLE manager; GRANT create table, create view TO manager; GRANT manager TO DEHAAN, KOCHHAR; Create a role(創(chuàng)建角色) Grant privileges to a role(賦予角色權(quán)限) Grant a role to users(賦予用戶角色) Object Privilege Table View Sequence ProcedureALTER DELETE EXECUTE INDEX INSERT REFERENCES SELECT UPDATE Object Privileges(對(duì)象權(quán)限) 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 update (department_name, location_id)ON departmentsTO scott, manager; Using the WITH GRANT OPTION and PUBLIC Keywords Give a user authority to pass along privileges.(With grant option可傳遞權(quán)限) Allow all users on the system to query data from Alices DEPARTMENTS table.(Public是所有用戶)GRANT select, insertON departmentsTO scottWITH GRANT OPTION; GRANT selectON alice.departmentsTO PUBLIC; How to Revoke Object Privileges(回收權(quán)限) 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權(quán)限也同時(shí)回收)REVOKE privilege , privilege.|ALL ON objectFROM user, user.|role|PUBLICCASCADE CONSTRAINTS; Revoking Object Privileges(回收權(quán)限例子)As user Alice, revoke the SELECT and INSERT privileges given to user Scott on the DEPARTMENTS table.REVOKE select, insertON departmentsFROM scott; Summary(小結(jié))Statement ActionCREATE 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 password REVOKERemoves privileges on an object fromusersIn this lesson, you should have learned about DCL statements that control access to the database and database objects: