the tool, it is the job of the Oracle manager to understand the uses of
these security mechanisms and their appropriate use within an Oracle
environment. At this point, it's very important to note that all of the
Oracle security tools have significant overlapping functionality. When the
security administrator mixes these tools, it is not easy to tell which
specific end users have access to what part of the database.
For example, the
end user who's been granted execution privileges against a stored procedure
will have access to certain database entities, but this will not be readily
apparent from any specific role-based privileges that that user has been
granted. Conversely, an individual end user can be granted privileges for a
specific database role, but that role can be bypassed by the use of Oracle's
Virtual Private Database (VPD) technique.
In sum, each
of the three Oracle security methods provides access control to the Oracle
database, but they each do it in very different ways. The concurrent use of
any of these products can create a nightmarish situation whereby an Oracle
security auditor can never know exactly who has access to what specific
by reviewing traditional role-based Oracle security.
Traditional Oracle Security
security is generally implemented by associating a user with a "role" or a
"subschema" view of the database. These roles are profiles of acceptable
data items and operations, and the role profiles are checked by the database
engine at data request time (refer to figure 1).
traditional role-based security comes from the standard relational database
model. In all relational databases, specific object- and system-level
privileges can be created, grouped together into roles, and then assigned to
individual users. This method of security worked very well in the 1980s and
1990s, but has some significant shortcomings for individuals charged with
managing databases with many tens of thousands of users, and many hundreds
of data access requirements.
Traditional relational security.
roles, each individual user would need to be granted specific access to
every table that they need. To simplify security, Oracle allows for the
bundling of object privileges into roles that are created and then
associated with users. Below is a simple example:
create role cust_role;
grant select on customer to cust_role;
grant select, update on orders to cust_role;
grant cust_role to
fall into two categories, system privileges and object privileges. System
privileges can be very broad in scope because they grant the right to
perform an action, or perform an action on a particular TYPE of object. For
example, "grant select any table to scott" invokes a system-level privilege.
are a collection of privileges, roles can be organized in a hierarchy, and
different user can be assigned roles according to their individual needs.
New roles can be created from existing roles, from system privileges, from
object privileges, or any combination of roles (refer to figure 2).
Figure 2: A
sample hierarchy for role-based Oracle security.
hierarchical model for roles may appear simple, there are some important
caveats that must be considered.
About Role-based Security
several areas in which administrators get into trouble. These are granting
privileges using the WITH ADMIN option, granting system-level privileges,
and granting access to the special PUBLIC user. One confounding feature of
role-based security is the cascading ability of GRANT privileges. For
example, consider this simple command:
grant select any table
to JONES with GRANT OPTION;
Here we see
that the JONES user has been given a privilege with the "GRANT OPTION," and
JONES gains the ability to grant any of their privileges to any other Oracle
grant-based security, there is a method to negate all security for a
specific object. Security can be explicitly turned off for an object by
using "PUBLIC" as the receiver of the grant. For example, to turn off all
security for the CUSTOMER table, we could enter:
grant select on customer
now effectively turned off for the CUSTOMER table, and restrictions may not
be added with the REVOKE command. Even worse, all security can be negated
with a single command:
Grant select any table
Closing the Back Doors
As we know,
granting access to a table allows the user to access that table anywhere,
including ad-hoc tools such as ODBC, iSQL, and SQL*Plus.
Session-level security can be enforced within external Oracle tools as well
as within the database.
provides their PRODUCT_USER_PROFILE table to enforce tool-level security,
and the user may be disabled from updating in SQL*Plus by making an entry
into this table: For example, to disable updates for user JONES, the DBA
(product, userid, attribute, char_value)
("SQL*Plus", "JONES", "UPDATE", "DISABLED");
could still performs updates within the application, but would be prohibited
from updating while in the SQL*Plus tool. To disable unwanted commands for
end-users, a wildcard can be used in the attribute column. To disable the
DELETE command for all users of SQL*Plus, you could enter:
(product, userid, attribute, char_value)
("SQL*Plus", "%", "DELETE", "DISABLED");
Unfortunately, while this is great for excluding all users, we cannot alter
the tables to allow the DBA staff to have DELETE authority.
examine an alternative to role-based security, Oracle's Virtual Private
Virtual Private Databases
latest foray into Oracle security management is a product with several
names. Oracle has two official names for this product, virtual private
databases, or VPD, which as also known as fine-grained access control. To
add to the naming confusion, it is also commonly known as Row Level Security
and the Oracle packages have RLS in the name. Regardless of the naming
conventions, VPD security is a very interesting new component of Oracle
high-level, VPD security adds a WHERE clause predicate to every SQL
statement that is issued on behalf of an individual and user. Depending upon
the end users access, the WHERE clause constrains information to specific
rows a within the table, hence the name row-level security.
But we can
also do row-level security with views. It is possible to restrict SELECT
access to individual rows and columns within a relational table. For
example, assume that a person table contains confidential columns such as
SALARY. Also assume that this tables contains a TYPE column with the values
EXEMPT, NON_EXEMPT and MANAGER. We want our end-users to have access to the
person table, but we wish to restrict access to the SALARY columns and the
MANAGER rows. A relational view could be created to isolate the columns and
rows that are allowed:
department = 'FINANCE';
We may now
grant access to this view to anyone:
grant select on
FINANCE_VIEW to scott;
Let's take a
look at how VPD works. When users access a table (or view) that has a
1. The Oracle
server calls the policy function, which returns a "predicate." A predicate
is a WHERE clause that qualifies a particular set of rows within the table.
The heart of VPD security is the policy transformation of SQL statements. At
runtime, Oracle produces a transient view with the text:
SELECT * FROM scott.emp
then dynamically rewrites the query by appending the predicate to the users'
methodology is used widely for Oracle systems on the Web, where security
must be maintained according to instantiated users, but at the same time
provide a method whereby the data access can be controlled through more
procedural methods. Please note that the VPD approach to Oracle security
requires the use of PL/SQL functions to define the security logic.
several benefits to VPD security:
how VPD works, let's take a closer look at the emp_sec procedure below. Here
we see that the emp_sec function returns a SQL predicate, in this case
"ENAME=xxxx," in which XXX is the current user (in Oracle, we can get a
current user ID by calling the sys_context function). This predicate is
appended to the WHERE clause of every SQL statement issued by the user when
they reference the EMP table.
CREATE OR REPLACE FUNCTION
(schema IN varchar2, tab IN varchar2)
RETURN VARCHAR2 AS
function is created, we call the dbms_rls (row-level security) package. To
create a VPD policy, we invoke the add_policy procedure, and figure 3 shows
an example of the invocation of the add_policy procedure. Take a close look
at this policy definition:
Invoking the add_policy Procedure.
example, the policy dictates that:
the EMP table is referenced
2. In a SELECT query
3. A policy called EMP_POLICY will be invoked
4. Using the SECUSR PL/SQL function.
Oracle treats the EMP table as a view and does the view expansion just like
the ordinary view, except that the view text is taken from the transient
view instead of the data dictionary. If the predicate contains subqueries,
then the owner (definer) of the policy function is used to resolve objects
within the subqueries and checks security for those objects.
words, users who have access privilege to the policy-protected objects do
not need to know anything about the policy. They do not need to be granted
object privileges for any underlying security policy. Furthermore, the users
also do not require EXECUTE privileges on the policy function, because the
server makes the call with the function definer's right.
In figure 4
we see the VPD policy in action. Depending on who is connected to the
database, different row data is display from identical SQL statements.
Internally, Oracle is rewriting the SQL inside the library cache, appending
the WHERE clause to each SQL statement.
Figure 4: The
VPD Policy in Action.
While the VPD
approach to Oracle security works great, there are some important
considerations. The foremost benefit of VPD is that the database server
automatically enforces these security policies, regardless of the how the
data is accessed, through the use of variables that are dynamically defined
within the database user's session. The downsides to VPD security are that
VPD security policies are required for every table accessed inside the
schema, and the user still must have access to the table via traditional
examine a third type of Oracle security, the grant execute
method of security.
Now we visit
the third main area of Oracle security, the ability to grant execution
privileges on specific database procedures. Under the grant execute
model, and individual needs nothing more than connect privileges to attach
to the Oracle database. Once attached, execution privileges on any given
stored procedure, package, or function can be directly granted to each end
user. At runtime, the end-user is able to execute the STORE procedure,
taking on the privileges of the owner of the STORE procedure.
As we know,
one shortcoming of traditional role-based security is that end users can
bypass their application screens, and access their Oracle databases through
SQL*Plus or iSQL. One benefit of the grant execute model is
that you ensure that your end users are only able to use their privileges
within the scope of your predefined PL/SQL or Java code. In many cases, the
grant execute security method provides tighter control access
security because it controls not only those database entities that a person
is able to see, but what they're able to do with those entities.
execute security model fits in very nicely with the logic consolidation
trend over the decade. By moving all of the business logic into the database
management system, it can be tightly coupled to the database and at the same
time have the benefit of additional security. The Oracle9i database is now
the repository not only for the data itself, but for all of the SQL and
stored procedures and functions that transform the data. By consolidating
both the data and procedures in the central repository, the Oracle security
manager has much tighter control over the entire database enterprise.
execute security can give much tighter control over security than
data-specific security. The DBA can authorize the application owners with
the proper privileges to perform their functions, and all of the end-users
will not have any explicit GRANTS against the database. Instead, they are
granted EXECUTE on the procedure, and the only way that the user will be
able to access the data is though the procedure.
owner of the procedure governs the access rights to the data. There is no
need to create huge GRANT scripts for each any every end-user, and there is
no possibility of end users doing an "end-run" and accessing the tables from
within other packages.
grant execute access method has its greatest benefit in the coupling
of data access security and procedural security. When an individual end-user
is granted execute privileges against a store procedure or package, the end
user may use those packages only within the context of the application
itself. This has the side benefit of enforcing not only table-level
security, but column-level security. Inside the PL/SQL package, we can
specify individual WHERE predicates based on the user ID and very tightly
control their access to virtually any distinct data item within our Oracle
confounding problem with procedures and packages is that their security is
managed in an entirely different fashion from other GRANT statements. When a
user is given execution privileges on a package, they will be operating
under the security domain of the owner of the procedure, and not their
defined security domain. In other words, a user who does not have privileges
to update employee rows can get this privilege by being authorized to use a
procedure that updates employees. From the DBA's perspective, their database
security audits cannot easily reveal this update capability.
themselves, each Oracle security mechanism does an excellent job of
controlling access to data. However, it can be quite dangerous (especially
from an auditing perspective) to mix and manage between the three security
modes. For example, an Oracle shop using role-based security that also
decided to use virtual private databases would have a hard time reconciling
what users had specific access to what data tables and rows.
example would be mixing the grant execute security with either
VPD security. The grant execute security takes those specific
privileges off the owner of the procedure, such that each user who has been
granted access to a store procedure may (or may not) be seeing all off the
database entities that are allowed by the owner of the procedure. In other
words, only a careful review of the actual PL/SQL or Java code will tell us
exactly what a user is allowed to view inside the database.
security continues to evolve, we will no doubt see more technical advances
in data control methods. For now, it is the job of the Oracle DBA to ensure
that all access to data is tightly controlled and managed.