sql12 - 14 14 Controlling User Access Objectives After...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: 14 14 Controlling User Access Objectives After completing this lesson, you should After be able to do the following: be • Create users • Create roles to ease setup and maintenance of the security model • GRANT and REVOKE object privileges 14-2 Controlling User Access Database administrator Username and password privileges Users 14-3 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, views, and sequences 14-4 System Privileges • More than 80 privileges are available. • The DBA has high-level system privileges. – Create new users – Remove users – Remove tables – Backup tables 14-5 Creating Users The DBA creates users by using the The CREATE USER statement. CREATE CREATE USER IDENTIFIED BY user password; SQL> CREATE USER scott 2 IDENTIFIED BY tiger; User created. 14-6 User System Privileges • Once a user is created, the DBA can grant specific system privileges to a user. GRANT privilege [, privilege...] TO user [, user...]; • An application developer may have the following system privileges: – CREATE SESSION – CREATE TABLE – CREATE SEQUENCE – CREATE VIEW – CREATE PROCEDURE 14-7 Granting System Privileges The DBA can grant a user specific system The privileges. privileges. SQL> GRANT create table, create sequence, create view 2 TO scott; Grant succeeded. 14-8 What Is a Role? Users Manager Privileges Allocating privileges without a role 14-9 Allocating privileges with a role What Is a Role? What • A role is a named group of related privileges that can be granted to the user. This method makes granting and revoking privileges easier to perform and maintain. • A user can have access to several roles, and several users can be assigned the same role. Roles typically are created for a database application. Creating and Assigning a Role Creating • First, the DBA must create the role. Then the DBA can assign privileges to the role and users to the role. 14-10 Creating and Granting Privileges to a Role SQL> CREATE ROLE manager; Role created. SQL> GRANT create table, create view 2 to manager; Grant succeeded. Grant SQL> GRANT manager to BLAKE, CLARK; Grant succeeded. Grant 14-11 Changing Your Password • When the user account is created, a password is initialized. • Users can change their password by using the ALTER USER statement. SQL> ALTER USER scott 2 IDENTIFIED BY lion; User altered. 14-12 Object Privileges Object Privilege Table ALTER √ DELETE √ View Sequence Procedure √ √ √ EXECUTE INDEX INSERT √ REFERENCES √ SELECT √ √ UPDATE 14-13 √ √ √ √ √ Object Privileges • Object privileges vary from object to object. • An owner has all the privileges on the object. • An owner can give specific privileges on that owner’s object. GRANT ON TO [WITH GRANT 14-14 object_priv [(columns)] object {user|role|PUBLIC} OPTION]; Granting Object Privileges • Grant query privileges on the EMP table. SQL> GRANT select 2 ON emp 3 TO sue, rich; Grant succeeded. • Grant privileges to update specific columns to users and roles. SQL> GRANT update (dname, loc) 2 ON dept 3 TO scott, manager; Grant succeeded. 14-15 Guidelines Guidelines – To grant privileges on an object, the object must be in your own schema or you must have been granted the object privileges WITH GRANT OPTION. – An object owner can grant any object privilege on the object to any other user or role of the database. – The owner of an object automatically acquires all object privileges on that object. • The first example above grants users Sue and Rich the privilege to query your EMP table. The second example grants UPDATE privileges on specific columns in the DEPT table to Scott and to the manager role. • Note: DBAs generally allocate system privileges; any user who owns an object can grant object privileges. 14-16 Using WITH GRANT OPTION and PUBLIC Keywords • Give a user authority to pass along the privileges. SQL> GRANT select, insert 2 ON dept 3 TO scott 4 WITH GRANT OPTION; Grant succeeded. • Allow all users on the system to query data from Alice’s DEPT table. SQL> GRANT select 2 ON alice.dept 3 TO PUBLIC; Grant succeeded. 14-17 Confirming Privileges Granted 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 user's objects USER_TAB_PRIVS_RECD Object privileges granted to the user USER_COL_PRIVS_MADE Object privileges granted on the columns of the user's objects USER_COL_PRIVS_RECD Object privileges granted to the user on specific columns 14-18 How to Revoke Object Privileges • You use the REVOKE statement to revoke privileges granted to other users. • Privileges granted to others through the WITH GRANT OPTION will also be revoked. REVOKE {privilege [, privilege...]|ALL} ON object FROM {user[, user...]|role|PUBLIC} [CASCADE CONSTRAINTS]; 14-19 Revoking Object Privileges As user Alice, revoke the SELECT and As INSERT privileges given to user Scott on the DEPT table. the SQL> REVOKE select, insert 2 ON dept 3 FROM scott; Revoke succeeded. 14-20 Summary CREATE USER Allows the DBA to create a user GRANT Allows the user to give other users privileges to access the user's 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 14-21 Practice Overview • Granting other users privileges to your table • Modify another user’s table through the privileges granted to you • Creating a synonym • Querying the data dictionary views related to privileges 14-22 LOCKS Locks are the mechanisms used to Locks prevent destructive interaction between users accessing the same resource simultaneoulsy. simultaneoulsy. A resource can either be an entire table or resource a specific row in a table. specific Thus, locks provide a high degree of data Thus, concurrency concurrency 14-23 Locks on two different levels •Row level lock A row is locked exclusively so that other users cannot modify the row until the transaction holding the lock is committed or rolled back. or Row level locks are acquired Row automatically by oracle as a result of insert,update,delete and select ….for update clause statement. update 14-24 Select….for update clause Select * from emp where deptno=20 for Select update of hiredate,ename update users can perform manipulations other than updat row that has been locked until the lock is released he rows are locked by the ‘for update’ clause no o an update that particular row. 14-25 Table level lock: Table A table level lock will protect table data table thereby guaranteeing data integrity when data is being accessed concurrently by multiple users. multiple 14-26 Modes of table level lock •Share lock •Share update lock •Exclusive lock Syntax: >llock table <table_name> in <share or share update or ock exclusive mode> exclusive 14-27 Share Lock: Locks the table allowing other users to only query but not insert,update or delete rows in a table. table. Share update lock: It locks rows that are to be updated in a table. It It permits other users to concurrently query,insert,update or even lock other rows in the same table. It prevents the other users from updating the row that has been locked. updating 14-28 Exclusive lock Exclusive Most restrictive of table locks. When issued by Most one user, it allows the other user to only query but no insert, delete or update rows in a table. but Similar to a share lock, but only one user can Similar place an exclusive lock on a table at a time. Whereas, many users can place a share lock on the same table at the same time. the 14-29 Nowait… Specify NOWAIT if you want Oracle to return Specify NOWAIT control to you immediately if the specified table (or specified partition or subpartition) is already locked by another user. In this case, Oracle returns a message indicating that the table, partition, or subpartition is already locked by another user. another If you omit this clause, then Oracle waits If until the table is available, locks it, and returns control to you. returns 14-30 14-31 14-32 ...
View Full Document

Ask a homework question - tutors are online