Oracle Data Access

Oracle uses several means to control data access and the best way is to assign privileges and roles to users. You can assign individual privileges to users but this can become overwhelming when you have many users, this is were roles comes in to play as privileges can be assigned to the role then the role assigned to the user.

There are two basic privileges system and object, using the commands grant and revoke privileges can be given and taken away from a user.

System Privileges

Here are some common system privileges, be careful to whom you grant system privileges too as these can have devastating impact on your database.

advisor
alter database
alter system
audit system
create database link
create table
create any index
create session
create tablespace
create, alter and drop user
insert any table

It is possible to allow a user to also grant the same system privilege he/she has to other users, when granting the system privilege, use the option "with admin option".

Granting	grant create session to vallep; grant create tablespace to vallep; grant create user, alter user, drop user to vallep;
Revoking	revoke create session from vallep; revoke create tablespace from vallep;
Allow user to also grant this privilege	grant create session to vallep with admin option; Note: now vallep can also grant this privilege
Useful Views
SYSTEM_PRIVILEGE_MAP	table to list all system privileges
DBA_USERS	provides information about users
DBA_SYS_PRIVS	see who has system privileges

There are two very powerful system privileges sysdba and sysoper, you cannot grant this privilege to a role and you cannot use with admin option.

SYSOPER

perform startup and shutdown operations
mount/dismount and open/close the database
use alter database commands (BACKUP, ARCHIVE, LOG AND RECOVER)
perform archiving and recovery operations
create a spfile

SYSDBA

All the SYSOPER privileges

use the create database command
all system privileges with admin option

Object Privileges

Object privileges are privileges on database objects which allows a user to perform some action on a specific table, view, sequence, etc. You can use the following SQL statements when you grant object privileges

alter
select
delete
execute
insert
references
index

Some of the possible object privileges on the following are possible, it is also possible to allow column only privileges

table	select, insert, update, delete, alter, debug, index and references
views	select, insert, update, delete, debug and references
sequences	select and alter
Functions, procedures, packages	debug and execute

As with the system privilege you can allow other users to grant privileges to other users using the option "with grant admin option".

Grant	grant select, insert, delete, update on employees to vallep; grant select on employees to public; grant update (product_id) on products to vallep;
Revoke	revoke select, insert, delete, update on employees from vallep; revoke select on employees from public; revoke update (product_id) on products from vallep;
Allow user to also grant this privilege	grant select on employees to vallep with grant option;
Useful Views
DBA_TAB_PRIVS	show users table privileges
DBA_COL_PRIVS	show users columns privileges

with admin and with grant options

There is something to remember when a users privilege is revoked the following will happen

with admin option - the revoke does not cascade, so any user that the user gave the privilege too remains
with grant option - the revoke does cascade, so any user that the user gave the privilege to will also be revoked.

grant all privileges and grant any object

Two special privileges

grant all privileges (system) - this is not a privilege but a simple way to grant all privileges in one go.
grant any object (object) - is a special system privilege that lets the grantee grant or revoke object privileges for object in any schema.

Roles

It can be very difficult to keep track of each users privilege, Oracle addresses this problem by using roles, which are named sets of privileges that can be assigned to users. Roles are a set of privileges that can be set or taken away in one go, using grant or revoke. A user by default, will use the default role unless he/she is assigned another role, you can assign more than one role to a user and he/she can switch roles during a session.

A role can also be made up of other roles and when revoking roles it does not cascade down. Probably the most well know role is the DBA role which is a very privilege account, becareful who you give this out too. Here are a few well know roles

dba - all system privileges with admin option
connect role - this now only has the create session privilege
resource role - create cluster, index type, operator, procedure, sequence, table, trigger and type
exp_full_database - used for data pump export
imp_full_database - used for data pump import

if you grant a role using with admin option the grantee can do the following:

grant the role to or revoke it from any user or other role in the database
grant the role with admin option
alter or drop the role

creating	create role test_role identified by <password>; Note: the password is optional, you can also use externally or globally authentication
removing	drop role test_role;
adding privileges to role	grant select on HR.employees to test_role; grant exp_full_database to test_role;
removing privileges from role	revoke select on HR.employees from test_role; revoke exp_full_database from test_role;
adding a role to a role	grant dba to test_role; Note: the dba is a very powerful role be careful giving this out to anyone
granting a role to a user	grant test_role to valle; grant test_role to vallep with admin option;
revoking a role from a user	revoke test_role from vallep;
list roles/privileges	select * from session_roles; select * from session_privs;
setting session role	set role test_role identified by <password>;
set default	alter user vallep default role test_role
Useful Views
DBA_ROLES	list all the roles
DBA_ROLE_PRIVS	lists the users granted roles Note: useful columns are with admin option, default role
ROLE_SYS_PRIVS	lists the roles system privileges and what roles have other roles within them
ROLE_TAB_PRIVS	lists the roles table privileges
ROLE_ROLE_PRIVS	lists what other roles the role has (roles within roles)
SESSION_ROLES	lists current role in use.
SESSION_PRIVS	show privileges currently enabled for the user

You can disable a users role by inserting a row within the table product_user_profile in the sys schema.

disable specific role for user	insert into product_user_profile ( product, userid, attribute, char_value) values ('SQL*Plus', 'VALLEP', 'ROLES', 'TEST_ROLE') ;
enable specific role for user	delete from product_user_profile where userid = 'VALLEP', and char_value = 'TEST_ROLE' ;