26.oracleocp题库dba经典辅助学习les

1、Controlling User AccessObjectivesAfter completing this lesson, you should be able to do the following:Differentiate system privileges from object privilegesGrant privileges on tablesView privileges in the data dictionaryGrant rolesDistinguish between privileges and rolesControlling User AccessDataba

2、seadministratorUsersUsername and passwordPrivilegesPrivilegesDatabase security:System securityData securitySystem privileges: Gaining access to the databaseObject privileges: Manipulating the content of the database objectsSchemas: Collection of objects such as tables, views, and sequencesSystem Pri

3、vilegesMore 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 DBA creates users with the CREATE USER statement.CREATE USER HRIDENTIFIED BY HR;User created.C

4、REATE 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 privileges:CREATE SESSIONCREATE TABLECREATE SEQUENCECREATE VIEWCREATE PROCEDUREGRANT pr

5、ivilege , 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 scott;Grant succeeded.What Is a Role?Allocating privilegeswithout a roleAllocating privilegeswith a ro

6、lePrivilegesUsersManagerCreating and Granting Privileges to a RoleCreate a roleGrant privileges to a roleGrant a role to usersCREATE ROLE manager;Role created. GRANT create table, create view TO manager; Grant succeeded. GRANT manager TO DE HAAN, KOCHHAR; Grant succeeded. Changing Your PasswordThe D

7、BA creates your user account and initializes your password.You can change your password by using the ALTER USER statement.ALTER USER HR IDENTIFIED BY employ;User altered.Object PrivilegesObject Privilege TableViewSequenceProcedureALTER DELETE EXECUTE INDEX INSERT REFERENCES SELECT UPDATE Object Priv

8、ilegesObject privileges vary from object to object.An owner has all the privileges on the object.An owner can give specific privileges on that owners object. GRANTobject_priv (columns) ONobject TOuser|role|PUBLIC WITH GRANT OPTION;Granting Object PrivilegesGrant query privileges on the EMPLOYEES tab

9、le.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.Passing On Your PrivilegesGive a user authority to pass along privileges.Allow all users

10、on the system to query data from Alices DEPARTMENTS table.GRANT select, insertON departmentsTO scottWITH GRANT OPTION;Grant succeeded.GRANT selectON alice.departmentsTO PUBLIC;Grant succeeded.Confirming Privileges Granted Data Dictionary ViewDescriptionROLE_SYS_PRIVSSystem privileges granted to role

11、sROLE_TAB_PRIVSTable privileges granted to rolesUSER_ROLE_PRIVSRoles accessible by the userUSER_TAB_PRIVS_MADEObject privileges granted on the users objectsUSER_TAB_PRIVS_RECDObject privileges granted to the userUSER_COL_PRIVS_MADEObject privileges granted on thecolumns of the users objectsUSER_COL_

12、PRIVS_RECDObject privileges granted to the user on specific columnsUSER_SYS_PRIVSSystem privileges granted to the userRevoking 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.

13、REVOKE privilege , privilege.|ALLON objectFROM user, user.|role|PUBLICCASCADE CONSTRAINTS;Revoking Object PrivilegesAs user Alice, revoke the SELECT and INSERT privileges given to user Scott on the DEPARTMENTS table.REVOKE select, insertON departmentsFROM scott;Revoke succeeded.SummaryIn this lesson

14、, 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 theobjectsCREATE ROLECreates a collection of privileges (usually performed by a DBA)ALTER USERChanges a users passwordREVOKERemoves privileges on an object fromusersPractice 1: OverviewTh