Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




Oracle Privileges and Roles
Oracle Tips by Burleson

The Oracle database incorporates a number of security features, two of which are privileges and roles. Both of these features are explained in some detail in the next few sections of this chapter. The most important thing that you need to know about roles and privileges is that all stored PL/SQL objects execute with the rights of the user who created the object.

For instance, let’s say there is a table, EMPLOYEE_COMPENSATIONS, which contains the salary and other benefits for each employee. While an individual user should never be able to modify the salaries of employees, occasionally employees get raises and benefits change. If a stored procedure owned by the system references this table, the stored procedure has the same rights as the system, despite the rights of the user who executes the stored procedure.

What Is A Privilege?

Oracle7 has two levels of privileges:

  • System level privileges give users the ability to create, modify, and drop objects (tables, indexes, views, synonyms, and so on), as well as the ability to execute stored PL/SQL objects.

  • Table level privileges give users the ability to perform SELECT, INSERT, UPDATE, and DELETE operations on tables.

These privileges can be granted to all system users or to individual users as needed. There is no list of privileges included in this book, because that knowledge is rarely needed by application developers.

What Is A Role?

Oracle7 introduced roles as a way to make user maintenance less of a chore for DBAs. Privileges can be granted to roles, and roles can be granted to other roles. Roles are then assigned to system users and application developers, and even to DBAs. Rather than grant privileges on tens or hundreds of tables to each individual user, these privileges can be granted to a role, which is then granted to each user.

TIP:  Looking Up Existing Roles In Your Database

You can get a listing of all the roles that exist in your system by executing the query shown in Listing 2.4.

Listing 2.4 Finding the existing roles in your database.

SELECT AR.role, ARP.granted_role

Understanding which roles exist and what each role does within the system will help you gain a better understanding of system design and, consequently, improve your ability to contribute to the success of your project.

This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter