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


 

HTML Text

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
FROM   ALL_ROLES AR, ALL_ROLE_PRIVS ARP;

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