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

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.

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

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

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

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