1、Lesson 14,Controlling User Access,Objectives,Explain the concept of the database security model. Describe system privileges. Set up and maintain database access by using roles. Identify object privileges. Change a password. Grant and revoke object privileges. Create synonyms for ease of table access
2、.,Control User Access,Database Administrator,Users,Username and Password Privileges,Privileges,Database security System security Data security System privileges Gain access to the database Object privileges Manipulate the content of the database objects Schema Collection of objects, such as tables,
3、views, and sequences,System Privileges,Over eighty privileges are available. The DBA has high level system privileges. Create new users Remove users Remove tables Backup tables The DBA creates users by using CREATE USER SQL command.,SQL CREATE USER scott2 IDENTIFIED BY tiger;,User System Privileges,
4、Once a user is created, the DBA can grant privileges to the user by executing the GRANT command. A typical user has the following system privileges: CREATE SESSION CREATE TABLE CREATE SEQUENCE CREATE VIEW CREATE PROCEDURE,Granting System Privileges,The DBA can grant a user specific system privileges
5、.,SQL GRANT create table, create sequence,2 create view3 TO scott; Grant succeeded.,What Is a Role?,Allocating privileges without a role,Allocating privileges with a role,Privileges,Users,Manager,Changing Your Password,When the user account is created, a password is initialized. Users can change the
6、ir password by using the ALTER USER command.,SQL ALTER USER scott IDENTIFIED BY lion; User altered.,Object Privileges,Object Table View Sequence Procedure Snapshot Privilege ALTER DELETE EXECUTE INDEX INSERT REFERENCES SELECT UPDATE ,Granting Object Privileges: Syntax,The DBA can allow users to perf
7、orm actions on specific objects. Object privileges vary by object type. An owner has all privileges on the object. An owner can give specific privileges on that owners object.,GRANT object_priv (columns) ON object TO user|role|PUBLIC WITH GRANT OPTION;,Granting Object Privileges: Examples,Grant quer
8、y privileges on your S_EMP table.Grant UPDATE privileges on specific columns to users and roles.,SQL GRANT select2 ON s_emp3 TO sue, rich; Grant succeeded.,SQL GRANT update (name, region_id)2 ON s_dept3 TO scott, manager; Grant succeeded.,WITH GRANT OPTION and PUBLIC Keywords: Examples,Give a user a
9、uthority to pass along the privileges.Grant SELECT privileges to all users on the S_ORD_ID sequence.,SQL GRANT select2 ON s_emp3 TO scott4 WITH GRANT OPTION; Grant succeeded.,SQL GRANT select2 ON s_ord_id3 TO PUBLIC; Grant succeeded.,Confirming Privileges Granted,Access the data dictionary views to
10、confirm the privileges granted to you or by you.,Data Dictionary Table Description ROLE_SYS_PRIVS System privileges granted to roles ROLE_TAB_PRIVS Table privileges granted to roles USER_ROLE_PRIVS Roles accessible by the user USER_TAB_PRIVS_MADE Object privileges granted on the users objects USER_T
11、AB_PRIVS_RECD Object privileges granted to the user USER_COL_PRIVS_MADE Object privileges granted on the columns of the users objects USER_COL_PRIVS_RECD Object privileges granted to the user on specific columns,Revoking Object Privileges,Use the REVOKE command to revoke privileges granted to other
12、users. Privileges granted to others through the WITH GRANT OPTION will also be revoked. As user Alice, revoke the SELECT and INSERT privileges given to user Scott on the S_DEPT table.,SQL REVOKE select, insert2 ON s_dept3 FROM scott; Revoke succeeded.,Creating a Synonym,Simplify access to objects by
13、 creating a synonym (another name for an object). Refer to a table owned by another user Shorten lengthy object names,SQL CREATE SYNONYM s_dept2 FOR alice.s_dept; Synonym created.,Creating a Synonym: Examples,Create a shortened name for the DEPT_SUM_VU view.Public synonyms are created and dropped by
14、 the DBA only.Remove a synonym.,SQL CREATE SYNONYM d_sum2 FOR dept_sum_vu; Synonym created.,SQL CREATE PUBLIC SYNONYM s_dept2 FOR alice.s_dept; Synonym created.,SQL DROP SYNONYM s_dept; Synonym dropped.,Summary,CREATE USER Allows the DBA to create a user. GRANT Allows the user to give other users privileges to access the users objects. CREATE ROLE Allows the DBA to create a collection of privileges. ALTER USER Allows users to change their password. REVOKE Removes privileges on an object from users. CREATE SYNONYM Provides an alternative name for objects.,
