文稿教学讲稿les

1、Controlling User AccessObjectivesAfter completing this lesson, you should be able to do the following:Differentiate system privileges from object privilegesGrant privileges on tablesGrant rolesDistinguish between privileges and rolesLesson AgendaSystem privilegesCreating a roleObject privilegesRevok

2、ing object privilegesControlling User AccessDatabaseadministratorUsersUsername and passwordPrivilegesPrivilegesDatabase security:System securityData securitySystem privileges: Performing a particular action within the databaseObject privileges: Manipulating the content of the database objectsSchemas

3、: Collection of objects such as tables, views, and sequencesSystem PrivilegesMore than 100 privileges are available.The database administrator has high-level system privileges for tasks such as:Creating new usersRemoving usersRemoving tablesBacking up tablesCreating UsersThe database administrator (

4、DBA) creates users with the CREATE USER statement.CREATE USER demoIDENTIFIED BY demo;CREATE USER user IDENTIFIED BY password;User System PrivilegesAfter a user is created, the DBA can grant specific system privileges to that user.An application developer, for example, may have the following system p

5、rivileges:CREATE SESSIONCREATE TABLECREATE SEQUENCECREATE VIEWCREATE PROCEDUREGRANT privilege , privilege.TO user , user| role, PUBLIC.;Granting System PrivilegesThe DBA can grant specific system privileges to a user.GRANT create session, create table, create sequence, create viewTO demo;Lesson Agen

6、daSystem privilegesCreating a roleObject privilegesRevoking object privilegesWhat Is a Role?Allocating privilegeswithout a roleAllocating privilegeswith a rolePrivilegesUsersManagerCreating and Granting Privileges to a RoleCreate a role:Grant privileges to a role:Grant a role to users:CREATE ROLE ma

7、nager; GRANT create table, create view TO manager; GRANT manager TO BELL, KOCHHAR; Changing Your PasswordThe DBA creates your user account and initializes your password.You can change your password by using the ALTER USER statement.ALTER USER demo IDENTIFIED BY employ;Lesson AgendaSystem privilegesC

8、reating a roleObject privilegesRevoking object privilegesObject privilege Table View SequenceObject PrivilegesALTERDELETEINDEXINSERTREFERENCESSELECT UPDATEObject PrivilegesObject privileges vary from object to object.An owner has all the privileges on the object.An owner can give specific privileges

9、 on that owners object. GRANTobject_priv (columns) ONobject TOuser|role|PUBLIC WITH GRANT OPTION;Granting Object PrivilegesGrant query privileges on the EMPLOYEES table:Grant privileges to update specific columns to users and roles:GRANT selectON employeesTO demo;GRANT update (department_name, locat

10、ion_id)ON departmentsTO demo, manager;Passing On Your PrivilegesGive a user authority to pass along privileges:Allow all users on the system to query data from Alices DEPARTMENTS table:GRANT select, insertON departmentsTO demoWITH GRANT OPTION;GRANT selectON alice.departmentsTO PUBLIC;Confirming Gra

11、nted PrivilegesData Dictionary ViewDescriptionROLE_SYS_PRIVSSystem privileges granted to rolesROLE_TAB_PRIVSTable privileges granted to rolesUSER_ROLE_PRIVSRoles accessible by the userUSER_SYS_PRIVSSystem privileges granted to the userUSER_TAB_PRIVS_MADEObject privileges granted on the users objects

12、USER_TAB_PRIVS_RECDObject privileges granted to the userUSER_COL_PRIVS_MADEObject privileges granted on the columns of the users objectsUSER_COL_PRIVS_RECDObject privileges granted to the user on specific columnsLesson AgendaSystem privilegesCreating a roleObject privilegesRevoking object privileges

13、Revoking Object PrivilegesYou use the REVOKE statement to revoke privileges granted to other users.Privileges granted to others through the WITH GRANT OPTION clause are also revoked.REVOKE privilege , privilege.|ALLON objectFROM user, user.|role|PUBLICCASCADE CONSTRAINTS;Revoking Object PrivilegesRe

14、voke the SELECT and INSERT privileges given to the demo user on the DEPARTMENTS table.REVOKE select, insertON departmentsFROM demo;SummaryIn this lesson, you should have learned about statements that control access to the database and database objects.StatementActionCREATE USERCreates a user (usually performed by a DBA)GRANTGives other users privileges to access the objectsCREATE ROLECreates a collection of privileges (usually performed by a DBA)ALTER USERChanges a users passwordREVOKERem