Roles & privileges

1. Roles & privileges A user privilege is a right to execute a particular type of SQL statement, or a right to access another user's object. The types of privileges are defined by Oracle. Roles, on the other hand, are created by users (usually administrators) and are used to group together privileges or other roles. They are a means of facilitating the granting of multiple privileges or roles to users.

2. System Privileges Over 100 system privileges System privileges can be very powerful Oracle recommends that you configure your database to prevent regular (non-DBA) users exercising ANY system privileges System privileges providing access to objects in other schemas do not give other users access to objects in the SYS schema: For example, the SELECT ANY TABLE privilege allows users to access views and tables in other schemas, but NOT in SYS schema

3. Accessing Objects in the SYS Schema The SYS users and those who connect with administrative privileges (SYSDBA) can access objects in the SYS schema. Other means of allowing access to objects in the SYS schema is by granting users any of the following roles SELECT_CATALOG_ROLE This role can be granted to users to allow SELECT privileges on all data dictionary views. EXECUTE_CATALOG_ROLE This role can be granted to users to allow EXECUTE privileges for packages and procedures in the data dictionary.

4. Object Privileges Each type of object has different privileges associated with it Grant A Single Privilege CREATE TABLE test (testcol VARCHAR2(20)); GRANT SELECT ON test TO abc; Grant Multiple Privileges GRANT INSERT, DELETE ON test TO abc; Grant All Privileges GRANT ALL ON test TO abc; Grant Execute (procedures, functions, packages) GRANT EXECUTE ON getosuser TO abc;

5. Object Privileges Each type of object has different privileges associated with it Revoke A Single Privilege REVOKE SELECT ON test FROM abc; Revoke Multiple Privileges REVOKE INSERT, DELETE ON test FROM abc; Revoke All Privileges REVOKE ALL ON test FROM abc; Revoke Execute (procedures, functions, packages) REVOKE EXECUTE ON getosuser FROM abc;

6. Object Privileges Each type of object has different privileges associated with it Check privileges from thegrantor's point of view Grantor is logged in SELECT grantee, table_name, privilege FROM user_tab_privs_made Check privileges from thegrantee's point of view Grantee is logged in SELECT grantor, table_name, privilege FROM user_tab_privs_recd;

7. User Roles A role groups several privileges and roles, so that they can be granted to and revoked from users simultaneously. A role must be enabled for a user before it can be used by the user. Crucial role = CONNECT Includes the following system privileges: ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW

8. Creating a role You can create a role using theCREATE ROLEstatement, but you must have theCREATE ROLEsystem privilege to do so. Typically, only security administrators have this system privilege CREATE ROLE manager IDENTIFIED BY morework; Password 'morework' is then needed in the SET ROLE statement There are other means how to authorize a role than password by an Application by an External Source by the Operating System

9. Granting a role GRANT new_dba TO michael [WITH ADMIN OPTION]; WITH ADMIN OPTION rarely granted

10. Auditing Auditing is a method of "crime detection" not "crime prevention." It simply allows the site to keep a record of actions taken with respect to the database. The level of detail can be controlled by specifying auditing BY [SESSION | ACCESS]. default is to audit an action only once per session. an action can be audited WHENEVER (NOT) SUCCESSFUL. Depending on the setting, you can request successes only, failures only, or both. 3 – level auditing

11. 1)Statement level tracks usage of any of a related group of commands. For example the "table" statement would cause any of the following DDL commands to be audited: CREATE TABLE DROP TABLE TRUNCATE TABLE. This level can be imposed across selected UserID's only, or the entire instance.

12. 2)Privilege level tracks any command that relies on the specified system privilege. This level can also be imposed across selected UserID's only, or the entire instance

13. 2)Object level tracks specified references to a given object (such as one table) will be imposed across all UserID's

14. Auditing options Assuming that the "fireid" user is to be audited CONNECT sys/password AS SYSDBA AUDIT ALL BY fireid BY ACCESS; AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY fireid BY ACCESS; AUDIT EXECUTE PROCEDURE BY fireid BY ACCESS;

15. Auditing data View DBA_AUDIT_TRAIL Username : Oracle Username. Terminal : Machine that the user performed the action from. Timestamp : When the action occured. Object Owner : The owner of the object that was interacted with. Object Name : The name of the object that was interacted with. Action Name : The action that occured against the object. (INSERT, UPDATE, DELETE, SELECT, EXECUTE)