Build your professional network on facebook via our app Go to app
 
 
Posted in Community :

Oracle Applications

 
Users, roles and privileges in Oracle by SACHIN SAXENA, Financial Analyst IKANOS Communication India Pvt. Ltd.

Users, roles and privileges in Oracle

By : SACHIN SAXENA, Financial Analyst IKANOS Communication India Pvt. Ltd.
Industry : IT Services Functional Area : Channel Management
Keywords :

Oracle

Roles

Users

Privileges

Activity:  0 comments  2414 views  last activity : 07 06 2010 20:18:04 +0000
Share
 
 
 

Privileges

A privilege is a right to execute an SQL statement or to access another user's object. In Oracle, there are two types of privileges: system privileges and object privileges. A privileges can be assigned to a user or a role
The set of privileges is fixed, that is, there is no SQL statement like create privilege xyz...

System privileges

There are quite a few system privileges: in Oracle 9.2, we count 157 of them, and 10g
Executing this statement, we find privileges like create session, drop user, alter database, see system privileges.
System privileges can be audited.
Arguably, the most important system privileges are:

Object privileges

privileges can be assigned to the following types of database objects:
  • Tables
    select, insert, update, delete, alter, debug, flashback, on commit refresh, query rewrite, references, all
  • Views
    select, insert, update, delete, under, references, flashback, debug
  • Sequence
    alter, select
  • Packeges, Procedures, Functions (Java classes, sources...)
    execute, debug
  • Materialized Views
    delete, flashback, insert, select, update
  • Directories
    read, write
  • Libraries
    execute
  • User defined types
    execute, debug, under
  • Operators
    execute
  • Indextypes
    execute
For a user to be able to access an object in another user's schema, he needs the according object privilege.
Object privileges can be displayed using all_tab_privs_made or user_tab_privs_made.

Public

If a privilege is granted to the special role public, this privilege can be executed by all other users. However, sysdba cannot be granted to public.

Users

to be finished ...

Roles

Predefined Roles

Along with the installation, more exactly with the creation of an oracle database, Oracle creates predefined roles. These are:
  • connect, resource, dba
    These might not be created anymore in future versions of Oracle.
    Oracle 9.2 grants create session, alter session, create synonym, create view, create database link, create table, create cluster and create sequence to connect.
    It also grants     create table , create cluster, create sequence, create trigger create procedure, create type, create indextype and create operator to resource.
    The role dba gets basically everything and that with admin option.
  • delete_catalog_role, execute_catalog_role, select_catalog_role
    Accessing data dictionary views (    v$ views and static dictionary views)
  • exp_full_database, imp_full_database
    This role is needed to     export objects found in another user's schema.
  • aq_user_role, aq_administrator_role, global_aq_user_role(?)
  • logstdby_administrator
  • snmpagent
  • recovery_catalog_owner
  • hs_admin_role
  • oem_monitor, oem_advisor
  • scheduler_admin
  • gather_system_statistics
  • plustrace
  • xdbadmin
  • xdbwebservices
  • ctxapp

Assigning privileges to users and roles

A privilege can be assigned to a user with the grant sql statment. On the other hand, revoke allows to take away such privileges from users and roles.
Oracle stores the granted privileges in its data dictionary.

Displaying the relationship between users, roles and privileges

Use this script to recursively list users, granted roles and privileges.
go to top
 
0 comments on "Users, roles and privileges in Oracle"
Sort by: Most Recent Top Rated
Add your comment on "Users, roles and privileges in Oracle"

Rate:
Submit
 Found the article "Users, roles and privileges in Oracle"  interesting ?  Share with your connections and communities  
Leading recruitment firm from Bangalore
  • Create a confidential Career Profile and Resume/C.V. online
  • Get advice for planning their career and for marketing of experience and skills
  • Maximize awareness of and access to the best career opportunities
Viewers also viewed
Hi ,i want to do certification in oracle but i don't have appropriate idea about the...
 
1 referals 1 answers, 94 views
salary vs job role
 
3635 referals 43 arguments, 1122 views
Now a days very good scope for Ph.D. in ENGINEERING,So that they will be SCIENTIST,RESEARCH...
 
0 referals 11 arguments, 328 views
more...  
Recent Knowledge (71)
Why is using email marketing services sometimes a better option than direct mail? There are many...
 
0 referals 4 comments, 221 views
I am not sure whether this is really happened or not, But I liked it and want to share with all...
 
63 referals 8 comments, 198 views
A Sufi Story Once upon a time, a fox that lost its legs lived in a forest. A forester who used...
 
294 referals 8 comments, 168 views
more...  
Sponsored Jobs
More From Author
Hi Gargi, very nice. Thanks for reffering and more
Not yet. we are growing faster but still 43% of our ppl are under poverty level & illiterate. Our money distribution system is one sided.
more...  
  • Popular Knowledge
  •  
  •  
  • Popular Jobs
  •