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