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.

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".


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.


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


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

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

grant all privileges and grant any object

Two special privileges


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

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


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

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'