Privilege A privilege is a right to execute

Privilege • A privilege is a right to execute a particular type of SQL statement or to access another user's object. For example – Connect to the database (create a session) – Create a table – Select rows from another user's table – Execute another user's stored procedure

Privilege • You grant privileges to users so these users can accomplish tasks required for their job. • You should grant a privilege only to a user who absolutely requires the privilege to accomplish necessary work. • Excessive granting of unnecessary privileges can compromise security.

Privilege • A user can receive a privilege in two different ways: • You can grant privileges to users explicitly. • For example, you can explicitly grant the privilege to insert records into the employees table to the user SCOTT.

Privilege • You can also grant privileges to a role (a named group of privileges), and then grant the role to one or more users. • For example, you can grant the privileges to select, insert, update, and delete records from the employees table to the role named clerk, which in turn you can grant to the users scott and brian.

Privilege • Because roles allow for easier and better management of privileges, you should normally grant privileges to roles and not to specific users.

Privilege • There are two distinct categories of privileges: – System privileges – Schema object privileges

System Privilege • A system privilege is the right to perform a particular action, or to perform an action on any schema objects of a particular type. • For example, – the privileges to create tablespaces – to delete the rows of any table in a database are system privileges. • There are over 60 distinct system privileges.

System Privilege • A system privilege is the right to perform a particular action, or to perform an action on any schema objects of a particular type. • For example, – the privileges to create tablespaces – to delete the rows of any table in a database are system privileges. • There are over 60 distinct system privileges.

System Privilege • Only users who have been granted a specific system privilege with – the ADMIN OPTION – users with the system privileges GRANT ANY PRIVILEGE – GRANT ANY OBJECT PRIVILEGE can grant or revoke system privileges to other users.

Schema Object Privileges • A schema object privilege is a privilege or right to perform a particular action on a specific schema object: – Table – View – Sequence – Procedure – Function – Package

Schema Object Privileges • Different object privileges are available for different types of schema objects. • For example, the privilege to delete rows from the departments table is an object privilege.

Schema Object Privileges • For example, assume there is a table student 1. emp and the user student 1 issues the following statement: GRANT SELECT ON student 1. emp TO student 2; The user student 2 can query student 1. emp by referencing the table by name SELECT * FROM student 1. emp;

GRANT The basic syntax of GRANT for object privileges is GRANT privilege ON object TO grantee [WITH GRANT OPTION];

GRANT For example, GRANT SELECT ON emp TO rollno 1;

GRANT For example, GRANT SELECT ON emp TO rollno 1; GRANT UPDATE, DELETE ON emp TO rollno 1;

GRANT For system privileges, the syntax is GRANT privilege TO grantee [WITH ADMIN OPTION]

GRANT For system privileges, the syntax is GRANT privilege TO grantee [WITH ADMIN OPTION] • Where privilege is the system privilege to be granted, • and grantee is the user receiving privilege. • If WITH ADMIN OPTION is specified then grantee can grant the privileges to other user as well.

GRANT CREATE TABLE TO rollno 1;

REVOKE privileges ON object FROM grantee;

Object Privilege ALTER Objects Tables, sequences DELETE Tables, views EXECUTE Procedure, functions, packages INDEX Tables INSERT Tables, views SELECT Tables, sequences UPDATE Tables, views Description Allows grantee to issue an ALTER statement on the object. Allows grantee to issue a DELETE statement against the object. Allows grantee to execute the stored PL/SQL object. Allows grantee to create an index on the table. Allows grantee to use INSERT statement Allow grantee to issue to SELECT statement. Allow grantee to issue an UPDATE

Role • Oracle provides for easy and controlled privilege management through roles. • Roles are named groups of related privileges that you grant to users or other roles. • Roles are designed to ease the administration of end-user system and schema object privileges.

Role • Reduced privilege administration – Rather than granting the same set of privileges explicitly to several users, you can grant the privileges for a group of related users to a role, and then only the role needs to be granted to each member of the group.

Role • Dynamic privilege management – If the privileges of a group must change, only the privileges of the role need to be modified. – The security domains of all users granted the group's role automatically reflect the changes made to the role.

Role • Selective availability of privileges – You can selectively enable or disable the roles granted to a user. – This allows specific control of a user's privileges in any given situation.

Who can Grant or Revoke Role? • Any user with the GRANT ANY ROLE system privilege can grant or revoke any role except a global role to or from other users or roles of the database. • You should grant this system privilege conservatively because it is very powerful.

Pre-defined Oracle Roles • Oracle Database provides some predefined roles to help in database administration. • These roles are automatically defined for Oracle databases when you run the standard scripts that are part of database creation. • You can grant privileges and roles to, and revoke privileges and roles from, these predefined roles in the same way as you do with any role you define.

Pre-defined Oracle Roles • Each installation should create its own roles and assign only those privileges that are needed. • This principle enables the organization to retain detailed control of its roles and privileges.

Pre-defined Oracle Roles: CONNECT • Includes the following system privileges: ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW

Pre-defined Oracle Roles: RESOURCE • Includes the following system privileges: CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, CREATE TYPE

Pre-defined Oracle Roles: DBA • All system privileges WITH ADMIN OPTION

Create Role The following statement creates the clerk role, which is authorized by the database using the password bicentennial: CREATE ROLE clerk IDENTIFIED BY bicentennial;