Roles

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 the grantor's point of view
• Grantor is logged in
• SELECT grantee, table_name, privilege
• FROM user_tab_privs_made
• Check privileges from the grantee'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 the CREATE ROLE
  statement, but you must have the CREATE ROLE
  system 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)