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".
Granting | grant create session 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 |
SYSDBA |
All the SYSOPER privileges use the create database command |
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
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
if you grant a role using with admin option the grantee can do the following:
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 ( |
enable specific role for user | delete from product_user_profile |