Introduction

This is the second of many articles on new Oracle8i release 8.1 database
features. I will be discussing in some depth many of the 150+ new features
contained in the Oracle database over the next couple of months. In each of
these articles we will explore some new feature or set of features found in the
database and explain/provide you with:

This week we will take a look at 2 new features. Fine Grained Access Controls
and Secure Application Contexts. These two features, when used together, add a
new dimension to your ability to secure your data in the database.

You will see Fine Grained Access Control referred to with various names in
different publications. The following are synonymous terms for this feature:

Fine Grained Access Control (technical name)

Virtual Private Database (marketing name)

Row Level Security (technical name based on the PL/SQL packages that
implement this feature)

Fine Grained Access Control and Application Contexts

In a nutshell, the Fine Grained Access Control in Oracle8i is the ability for
you to dynamically attach, at runtime, a predicate (where clause) to any and all
queries issued against a database table or view. You now have the ability to
procedurally modify the query at runtime. You may evaluate who is running
the query, where they are running the query from, when they are
running the query and develop a predicate given those circumstances. With the
use of Application Contexts, your may securely add additional information to the
environment (such as an application role the user may have) and access this in
your procedure or predicate as well.

As an example of Fine Grained Access Control, you might have a security
policy that determines what rows different groups of people may see. Your
security policy will develop a predicate based on who is logged in and what
group they are in. Fine Grained Access Control allows the query "select * from
emp" when submitted by different users to be evaluated as:

HrRep may see anyone in a given department. This introduces the syntax
for retrieving variables from an application context – the SYS_CONTEXT()
builtin function.

Why use this feature?

There are many reasons to use this feature. Some of the most prevalent ones
are:

Ease of Maintenance

. Fine Grained Access Control allows you to have 1
table and 1 stored procedure to manage what used to take many views. This
multiple view approach typically leads to a proliferation of database objects
as you end up creating a separate view for each group of people. For example,
in the above example with employees, managers and HR representatives, a
typical system would have had 3 database views created. If you need to add
another group of people – you would have to add another set of views that you
manage and maintain. If you change the security policy (e.g. you want managers
to see not only their direct reports but also 2 levels down), you would have
to recreate the view in the database – invalidating all objects that refer to
it.

It’s done in the server

. Many times, given the complexity of managing
and maintaining so many views, developers will encode the application logic
into the application itself. The application will look at who is logged in and
what they are requesting and submit the appropriate query. This protects the
data but only when the data is accessed via the application.
This hampers your ability to use ad-hoc query tools, report generation tools,
and the like on your data. This increases the probability that the data will
be compromised at some point since all one needs to do is log into the
database with some other tool, other then your application, and query the
data. By placing the security logic, the logic that determines what data the
user should see, in the database – you insure the data is protected,
regardless of the tool used to access it and you make it so any tool can in
fact access the data.

It avoids the approach of using common users to log in. Using Fine
Grained Access Control, each user should log in as themselves. This supplies
complete accountability – you can audit actions at the user level. In the
past, many applications when faced with having different views of the data for
different users, make the choice to have a database user per set of data. For
example, using the employee/manager/HR rep example from above, an application
would set up 3 accounts. Every employee would use the employee account. Every
manager would use the manager account. Every HR Rep would use the HR Rep
account. This removes the ability to audit actions at the user level.

It allows for easier application development.

Fine Grained Access
Control takes the security logic out of the application logic. The application
developer can concentrate on the application itself, not the logic of
accessing the underlying data to keep it secure. Since Fine Grained Access
Control is done entirely in the server – the applications immediately inherit
this logic. In the past, the application developers had to encode the logic
into the application – making the applications harder to develop initially and
making them especially hard to maintain. If the application is responsible for
mediating access to the data and you access the same data from many locations
in the application, a simple change to your security policy may affect dozens
of application modules. Using Fine Grained Access Control, a change in a
security policy affects no application modules.

It allows for evolutionary application development.

In many
environments, security policies are not well defined initially and change over
time. As companies merge, or as health care providers tighten access to
patient databases, or as privacy laws are introduced – security policies will
necessarily change. By placing the access control as close to the data as
possible, you allow for this evolution with minimal impact on applications and
tools. There is one place to go to implement the new logic and all
applications and tools that access the database automatically inherit the new
logic.

How to use this feature

Fine grained access control is implemented in Oracle8i with 2 constructs:

An application "context

". This is a namespace with a corresponding set
of variable/value pairs. For example, in the context named ‘OurApp’, we could
access variables ‘DeptNo’, ‘Mgr’, etc. An application context is always bound
to some PL/SQL package. This package is the only method for setting values
in the context. For example, to get the ‘DeptNo’ variable set to a value
in the ‘OurApp’ context – you must call a specific package, the one bound to
the ‘OurApp’ context. This package is trusted to set values correctly in the
‘OurApp’ context (you wrote it, that’s why it is trusted to set the context
correctly). This prevents users with malicious intent from setting values in
an application context that would give them access to information they should
not have access to. Anyone can read the values of an application context, only
one package may set these values.

A security policy.

A security policy is simply a function you develop
that will return a predicate used to filter data dynamically at query
execution time. Typically this function will make use of values in an
application context to determine the correct predicate to return (e.g. it will
look at ‘who’ is logged in, ‘what’ they are trying to do and validate they
have the privilege to do that). It should be noted that the user SYS (or
INTERNAL) never have security policies applied to them – that user will be
able to see all of the data.

In order to use this feature, the developer will need the following
privileges in addition to the standard connect and resource roles:

EXECUTE_CATALOG_ROLE.

This allows the developer to execute the
dbms_rls package. Alternatively, you may just grant execute on dbms_rls
to the account when connected as SYS.

CREATE ANY CONTEXT:

This allows the developer to create application
contexts.

An application context is created using a simple SQL command

SQL> create context OurApp using Our_Context_Pkg;

Here OurApp is the name of the context and Our_Context_Pkg is the PL/SQL
package that is allowed to set values in the context. Application Contexts are
an important feature in the Fine Grained Access Control implementation for two
reasons:

It supplies you with a trusted way to set variables in a namespace. Only
the PL/SQL package associated with a context may set values in that context.
This ensures the integrity of the values in this context. Since you will use
this context to restrict or permit access to data, the integrity of the values
in the context must be assured.

References to the Application Context values in a SQL query are treated as
bind variables would be. For example, if you set a ‘DeptNo’ variable in the
context ‘OurApp’ and implemented a policy to return a where clause "deptno =
SYS_CONTEXT(‘OurApp’,’DeptNo’)", it will not impact shared sql usage since the
SYS_CONTEXT reference is similar to "deptno = :b1". Everyone might use
different values for ‘Deptno’, but they will all reuse the same parsed,
optimized query plans.

Example 1; Implementing a security policy

If you wanted a security policy that would allow the user to see only rows
they ‘owned’ unless you were the RLS_ADMIN user, you might code:

The predicate "where owner = USER" will be dynamically appended to all
queries against the table to which this function is bound, effectively
restricting the number of rows that would be available to the user. Only if the
currently logged in user is RLS_ADMIN will a NULL (empty) predicate be returned.
Returning an empty predicate is like returning "1=1" or "TRUE".

To tie this function to a table, one uses the PL/SQL procedure
"dbms_rls.add_policy". For example we have the following table setup:

Now, all DML against the EMP table will have the predicate returned by
my_security_function applied to it – regardless of the environment submitting
the DML operation (i.e. regardless of the application accessing the data). To
see this in action:

So, that shows that we have effectively filtered the rows – the current user
RLS can only see its rows – the ones it owns. The row owned by SCOTT is no
longer visible. Let’s connect as the RLS_ADMIN account now:

That shows that the RLS_ADMIN account can see all of the data as we desired.
Logging back in as the RLS account – we will see what happens when we attempt to
create data we cannot ‘see’ (not owned by us):

The error ORA-28115 is raised since when we added the policy we specified

…
9 update_check => TRUE );
…

This is analogous to creating a view with the "CHECK OPTION" enabled. This
makes it so we can only create data we can also select. The default is to allow
you to create data you cannot select.

Important Caveat

One important implementation feature of our security predicate function above
is the fact that during a given session, this function returns a constant
predicate – this is critical. If we look at the function we used above once
more we see the logic is:

This predicate function returns either no predicate or "owner = USER". During
a given session it will consistently return the same predicate. There is
no chance that we would retrieve the predicate "owner = USER" and later in that
same session retrieve the empty predicate "". To understand why this is
absolutelycritical to a correctly designed Fine Grained Access
Control application, we must understand when the predicate is associated with a
query and how different environments handle this.

Lets say we wrote a predicate function that looked something like this:

That says that if the attribute "x" is set in the context, the predicate
should be "x > 0". If the context attribute "x" is not set, the predicate is
"1=0". If we create a table T, put data into it and add the policy and context
as follows:

So, it would appear that we are set to go. The dynamic predicate is
working as we expected. In fact, if we use PL/SQL (or Pro*C, or well coded OCI
applications, as well as many other execution environments) we find that the
above does not hold true. For example, lets code a small PL/SQL routine:

This routine simply issues a "select * from T" once in the procedure if no
inputs are passed, two times in the procedure if some input is passed. Let’s
execute this procedure and observe the outcome:

SQL> -- Make it so dbms_output.put_line works

SQL> set serveroutput on
SQL> -- unset the context -- make X have a NULL value
SQL> exec set_ctx( NULL )
PL/SQL procedure successfully completed.
SQL> -- lets run the procedure. Note that we are letting
SQL> -- some_input default to NULL. Only 1 select * from t
SQL> -- will execute.
SQL> --
SQL> -- As expected we get ZERO rows since the predicate 1=0
SQL> -- we used
SQL> exec dump_t
*** Output from SELECT * FROM T
PL/SQL procedure successfully completed.
SQL> -- Now, lets reset the context to have a non-null value.
SQL> exec set_ctx( 1 )
PL/SQL procedure successfully completed.
SQL> -- Since the table t contains 1 row with 1234 in it and
SQL> -- the predicate should be "x > 0" when this attribute is
SQL> -- set, we would expect queries on T to return data.
SQL> --
SQL> -- To show that this might not be the case, we run dump_t with
SQL> -- some input set to a NON-NULL value. This will cause both
SQL> -- select * from T's to execute
SQL> --
SQL> -- You should notice that the first execution of "select * from T"
SQL> -- returns no data! The second "select * from T" does!
SQL> --
SQL> -- Why? See below
SQL> exec dump_t( 0 )
*** Output from SELECT * FROM T
*** Output from another SELECT * FROM T
1234
PL/SQL procedure successfully completed.

So, we run the procedure with the context attribute "x" set to null and get
the expected results (because it’s the first time in this session we are running
this procedure). We set the context attribute "x" to a non-null value and find
we get "ambiguous" results. The first select * from t in the procedure still
returns no rows – it is apparently still using the predicate "1=0". The second
query (which we did not execute the first time) returns what appears to be the
correct results – it is apparently using the predicate "x > 0" as we
expect.

Why did the first select in that procedure not use the predicate we
anticipated? It is because of an optimization called "cursor caching". PL/SQL
and many other execution environments do not really close a cursor when you
close a cursor. The above example may be easily reproduced in Pro*C for example
if the precompile option "release_cursor" is left to default to NO. If you take
the same code and precompile with release_cursor=YES, the Pro*C program would
behave more like queries in SQLPLUS would. The predicate used by DBMS_RLS is
assigned to a query during the PARSE phase. The first query "select * from T" is
getting parsed during the first execution of the stored procedure – when the
predicate was in fact "1=0". The PL/SQL engine is caching this parsed cursor for
you. The second time we execute the stored procedure, PLSQL simply reused the
parsed cursor from the first "select * from T", this parsed query has the
predicate "1=0" – the predicate function was not invoked at all this time
around. Since we also passed some inputs to the procedure, PLSQL executed the
second query. This query however did not already have an opened, parsed cursor
for it so it parsed it during this execution – when the context attribute was
NOT NULL. The second "select * from t" has the predicate "x>0" associated
with it. This is the cause of the ambiguity. Since we have no control over the
caching of these cursors in general, a security predicate function that may
return more then 1 predicate per session should be avoided at all cost.
Subtle hard to detect bugs in your application will be the result otherwise.
Below, in the next example, I will demonstrate how to implement a security
predicate function that cannot return more then one predicate per session. This
will ensure that:

Your results are consistent from query to query with respect to fine
grained access control.

You are never tempted to change the predicate in the middle of a session –
strange, unpredictable results will be the outcome if you do.

You are made to enforce your security policy in that single predicate for
a user rather then attempting to return a predicate customized for the current
environment the user is running in.

I suggest that all non-trivial predicate functions be implemented as they are
in the next example. This will ensure well behaved, predicable Find Grain Access
Control applications.

I should mention that there are cases where changing the predicate in the
middle of a session may be desirable. The client applications that access
objects that employ policies that can change predicates in the middle of a
session must be coded in a specific fashion to take advantage of this.
For example, in PLSQL we would have to code the application using dynamic sql
entirely to avoid the cursor caching. If you are employing this dynamic
predicate method – then you should bear in mind that the results will depend on
how the client application is coded, therefore you should not be enforcing a
security policy with this use of this feature. We will not be discussing this
possible use of the DBMS_RLS feature but rather will concentrate on its intended
use – to secure data.

Example 2; using application contexts

We would like to implement a Human Resources Security Policy. We will use the
sample SCOTT/TIGER EMP and DEPT tables and add one additional table that allows
us to designate people to be HR representatives for various departments. Our
requirements for this are:

A manager of a department can:

READ their record and the records of all employees that report to them
and all of the people that report under those people (hierarchy)

UPDATE all employees that directly report to them.

An employee can:

READ their record.

An HR representative can:

READ all records for the department they are working in (HR reps work on
departments at a time in our application)

UPDATE all records for the given department

INSERT into the given department

DELETE from the given department

As stated, our application will use the existing EMP and DEPT tables from the
SCOTT with the addition of a HR_REPS table to allow us to assign an HR
representative to a department. The schema we will use is as follows:

SQL> -- create the demo schema. it is based on the EMP & DEPT tables
SQL> -- owned by scott. We add declaritive RI to the schema and
SQL> -- rename the ENAME's in the EMP table to match our database
SQL> -- user enames we created (eg: there is a RLS_KING user and
SQL> -- RLS_KING ename in the emp table)

Now that we have the application tables EMP, DEPT and HR_REPS created, let’s
create a procedure that will let us set an application context. This application
context will contain two pieces of information – the currently logged in users
EMPNO and the role they are using (one of EMP, MGR, or HR_REP). Our dynamic
predicate routine will use the role stored in application context to decide what
the where clause should look like for the given user.

We use the EMP_BASE_TABLE and HR_REPs tables to make this determination. This
leads us to the question "why do we have a table EMP_BASE_TABLE and a view EMP
that is simply select * from emp_base_table?" Two reasons:

We use the data in the employee table to enforce our security policy.

We read that table while attempting to set an application context.

In order to read the employee data we need the application context to be set
– in order to set the application context we need to read the employee data.
It’s a chicken and egg problem. Our solution is to create a view that all
applications will use (the EMP view) and enforce our security on that view. The
original EMP_BASE_TABLE will be used by our security policy to enforce the
rules. From the EMP_BASE_TABLE we can discover who is a manager of a given
department and who works for a given user. The application and end users will
never use the EMP_BASE_TABLE – only our security policy will. The procedure to
set our context then is:

SQL> -- this is our 'trusted' procedure for setting the application
SQL> -- context
SQL> -- it enforces our policies by only setting the privileges you
SQL> -- have been granted in the application context.

So, what we’ve done so far is create a procedure that accepts a role name as
an in parameter. This procedure begins by ensuring the RoleName attribute has
not already been set. Since we will return different predicates based on the
value of RoleName in our security policy – we cannot permit a user to change
their role after it has been set. If we did, we would have a potential problem
with cached cursors and ‘old’ predicates. Next, we look up the current users
EMPNO. This does two things for us

It verifies the end user is an employee – if we get an error "NO DATA
FOUND", we know the person is not an employee. Since their context never gets
set – that person will see no data

It puts frequently used value into the application context. We can now
quickly access the EMP table by the current users EMPNO – which we will do in
the predicate function below.

The procedure then goes onto verify the current user is allowed to assume the
role they are requesting. Anyone who requests the EMP role may set it. Only
people who actually manage other people may set the MGR role. Only people who
have entries in the HR_REPS table may set the HR_REP role.

Next, we will create the database application context object and bind it to
the SET_HR_APP_DEPT procedure we just created:

SQL> -- This is our application context. the name of the
SQL> -- context is HR_APP_CTX. The Procedure is it bound to in this case
SQL> -- is SET_ROLE

So, now we have a context called Hr_App_Ctx and a procedure to set it. It is
important to note that since the context is bound to the procedure Set_Role –
that is the only way to set values in this context. For example, if we attempt
to set a RoleName in the context, in hopes of gaining access to data we should
not, we will discover that we cannot:

SQL> REM the following will FAIL. It is to show that
SQL> REM dbms_session.set_context will only work in the SET_ROLE
SQL> REM procedure for the Hr_App_Ctx context

This attribute of contexts is what makes them secure. Only your procedure may
set values in the context – ensuring you that if a value is there, it has been
validated and assigned by you. Now, to test the logic of our procedure, we will
attempt to use the stored procedure as various users and see what roles we can
set and what values are placed into the context.

SQL> grant select on sys.v_$context to rls_smith;
Grant succeeded.
SQL> connect rls_smith/rls_smith
Connected.
SQL> set serveroutput on
SQL> show user
USER is "RLS_SMITH"
SQL> exec rls.set_role( 'Mgr' )
BEGIN rls.set_role( 'Mgr' ); END;
*
ERROR at line 1:
ORA-20002: You are not a manager
ORA-06512: at "RLS.SET_ROLE", line 53
ORA-06512: at line 1

So far, we can see that our routine is available to RLS_SMITH but it won’t
let him set his context to ‘MGR’ since he is in fact not a manager. If we
inspect his context right now via the dynamic view v$context, we see:

Showing us that our logic that does not permit a user to alter their role
after setting it is in place, further is shows that the context just retains its
previous values. This is a non-fatal error message, it just means you cannot
change your role, your session is still OK.

Next, let’s connect as a different user and see how the procedure works and
look at a different way to inspect a session’s context values:

This time, we logged in as RLS_BLAKE who is the manager for department 30.
When RLS_BLAKE calls Set_Role procedure with a RoleName of ‘MGR’, we see the
context is set appropriately – he is a manager and his employee number is set.
This also demonstrates how to list the attribute value pairs in a session’s
context using the dbms_session.list_context package. This package is executable
by the general public (as opposed to the sys.v$context view we used above),
hence all users will be able to use this method to inspect their session’s
context values.

Now that we have our session context being populated the way we want, we can
set about to write our security policy procedure. This is the procedure that
will be called by the database engine at run time to provide a dynamic
predicate. The dynamic predicate will restrict what the user can read or
write.

SQL> -- create the predicate package (package to build the where clause)
SQL> -- for the HR application. We have individual functions to build
SQL> -- unique where clauses for each of SELECT/INSERT/UPDATE/DELETE.

We will be implementing a slightly different predicate for each of the Data
Manipulation Language (DML) operations. Each DML operation will be subject to
its rules. This allows for a DELETE statement to see a different (in this case a
smaller) set of data then a SELECT would. The implementation of the
HR_PREDICATE_PKG is as follows:

In the past, before Fine Grained Access Control, having one table with the
above three predicates could only have been achieved with the use of 3 views –
one each to SELECT, UPDATE, and INSERT/DELETE from. Fine Grained Access Control
simplifies this to just one table with a dynamic predicate.

In this routine we set the variable g_sel_pred to a non-null value exactly
once per session. If we have already set the predicate in a previous call to
this predicate function – we simply return that predicate again. This has 2
advantages

Its faster then evaluating and building the predicate procedurally for
each statement we parse. We reduce the amount of code executed

It removes the possibility of returning a different predicate within the
same session. As noted in the first example above, the results of returning a
different predicate in the same session can be ambiguous – using this
technique we remove that possibility from occurring.

The last step in the process is to associate our predicates with each of the
DML operations and the EMP table itself. This is accomplished as follows:

SQL> -- Add our policy to the EMP view. This associates each of the
SQL> -- HR_PREDICATE_PKG functions with the table for each of SELECT/INSERT
SQL> -- UPDATE/DELETE.
SQL> -- On INSERT and UPDATE, we set the 'update_check' flag to TRUE. This
SQL> -- is very much like creating a view with the 'CHECK OPTION'. It
SQL> -- ensures data we create in the database is data we can see in
SQL> -- the database.

So, for each of the DML operations, we have associated a different predicate
function. When the user queries the EMP table, the predicate generated by the
hr_predicate_pkg.select_function package will be invoked. When the user updates
the table, the update function in that package will be used, and so on.

Now, to test the application. We will create a package HR_APP. This package
represents our application. It has entry points to:

Retrieve data

Update data

Delete data

Insert new data

We will log in as various users, with different roles, and monitor the
behavior of our application. This will show us Fine Grain Access Control at
work.

So, that’s our "application". The listEmps routine shows every record we can
see in the EMP view. The updateSal routine updates every record we are allowed
to. The deleteAll routine deletes every record we are allowed to with the
exception of our record. The insertNew routine tries to create a new employee in
the department we request. This application simply tests all of the DML
operations we might attempt on the EMP view.

Now, as different users, we will log in and test the functionality of our
application.

SQL> @rls_adams
SQL> -- Log in as an employee with no management capability.
SQL> connect rls_adams/rls_adams
Connected.
SQL> set serveroutput on
SQL> -- First, lets try to become a manager
SQL> -- We are not a manager so it will not let
SQL> -- us become one
SQL> exec rls.set_role( 'mgr' )
BEGIN rls.set_role( 'mgr' ); END;
*
ERROR at line 1:
ORA-20002: You are not a manager
ORA-06512: at "RLS.SET_ROLE", line 53
ORA-06512: at line 1

So, that shows us we cannot assume a role we are not supposed to. To show
that no data can be accessed, we’ll immediately try to access some and see what
happens:

SQL> -- Now, lets see what happens when we try to do
SQL> -- stuff without setting the role
SQL> exec rls.hr_app.listEmps
------ Session Context ----------
HR_APP_CTX.EMPNO = 7876
------ Emp data you can see -----
BEGIN rls.hr_app.listEmps; END;
*
ERROR at line 1:
ORA-28112: failed to execute policy function
ORA-06512: at "RLS.HR_APP", line 18
ORA-06512: at line 1

We get an error message. This error message is occurring because we coded in
our predicate routine:

We are seeing the effects of issuing the raise_application_error in the
predicate function. That is causing the end user to see the ORA-28112 error
message. Below, in the next section, we will discuss how to track these errors
and how to debug them.

Next, we’ll set our role to something we are able to and retry these
operations:

So, the above shows we can see only our record, we cannot update any data
whatsoever, we cannot delete any records and inserting a new employee fails as
well. This is exactly what we intended – and it happens transparently. The
application, HR_APP, does nothing special to enforce these rules – the database
is doing it for us now.

That completes the testing of our three roles. Our requirements have been met
– we secured the data and did it transparently to the application.

Handling Errors and debugging

During the implementation of the above application, I ran into many errors
and had to debug my application. Since Fine Grained Access Control happens in
the server, it can be a little obtuse to diagnose errors and debug your
application. The following sections will help you successfully debug and
diagnose errors.

There are 4 major Oracle error codes you will encounter while developing Fine
Grained Access Control routines. They are:

ORA-28110: policy function or package <function name> has error

.
This indicates that the package or function the policy is bound to has an
error and cannot be recompiled. If you issue "show errors function
<function name>" or "show errors package body <package name>", you
will discover what the errors are. See here for an
example of diagnosing this error and how to recover from it.

ORA-28112: failed to execute policy function.

This will occur if the
predicate function has an error during execution. This could happen for
example if you have a SELECT INTO statement in the PL/SQL function that fails
to find a row and has no exception handler. The function will propagate the
NO_DATA_FOUND exception back to the caller (the database kernel) and the
database will raise the ORA-28112. See here for an
example of diagnosing this error and how to recover from it.

ORA-28113: policy predicate has error.

This will occur when the
predicate function successfully returns a where clause but the where clause
itself has an error when placed into the SQL query. For example, if you return
a where clause such as "x = 5" and the table to which this where clause is
associated with does not have a column named "x", you will receive an
ORA-28113. See here for an
example of diagnosing this error and how to recover from it.

ORA-28106: input value for argument #2 is not valid.

You will receive
this error from a call to dbms_session.set_context if the attribute name is
not a valid Oracle identifier. An application context’s attribute names must
be valid Oracle identifiers (i.e. you could use them for names of columns in
tables or as PL/SQL variable names). The only solution is to change the name
of your attribute. For example, in our application we used attributes ‘SEL’,
‘INS’, ‘UPD’, and ‘DEL’ – not ‘SELECT’, ‘INSERT’ and so on because ‘SELECT’ is
not a valid Oracle identifier name.

One utility I use frequently when writing predicate functions is a simple
‘debug’ package. This package, authored by Christopher Beck – also of Oracle (mailto:clbeck@oracle.com), allows us to
instrument our code with ‘print’ statements. This package also allows us to
liberally put statements like:

So, debug.f works similar to the C printf function and is implemented using
UTL_FILE. It creates programmer managed trace files on the database server.
These trace files contain your debug statements – things you can use to see what
is happening in your code. Since the database kernel is invoking your code,
debugging it can be hard. Having these trace files can save lots of time. The
scripts you can download (see below for that link) contain this debug package
and comments on setting it up and using it.

Pros and Cons

There are many Pros to this feature and very few Cons. In fact, it is hard to
think of any Cons to this feature at all. In any event here they are:

Pros

Cons

Simplifies Application development – separates access control from
the application and puts it with the data.

May be difficult to debug as Fine Grained Access Control happens in
the background. Packages such as ‘debug’ referred to in the diagnosing and
debugging section above make this much easier.

Ensures data in the database is always protected. No matter what
tool accesses the data we are ensured our security policy is invoked and
cannot be bypassed.

Allows for evolutionary changes to security policies with no impact
on client applications.

Simplifies the management of database objects. It reduces the total
number of database objects needed to support an application.

It performs well. The use of the application contexts allow us to
reap the benefits of shared SQL.

Scripts

Download
this tar file to get all scripts used in this article. Please be sure to read
the README.TXT file included within. Tar files may be opened on Windows using
WinZip6.0 or above.

Next Time…

In the next issue, we will explore the new database features:

Autonomous Transactions:

A feature such that any stored procedure or
anonymous block can initiate a ‘subtransaction’ and commit it – independent of
the parent transaction. This allows a trigger to commit or do DDL for example.

Invoker’s Rights:

allows stored procedures to execute with the
privileges of the invoker (including roles), as opposed to the definer’s
rights model that has historically been the only way to execute stored
procedures.

Bulk Binds from PL/SQL:

adds an array interface, similar to Pro*C, to
PL/SQL – increasing the performance of many database operations by many orders
of magnitude.

Enhanced Dynamic SQL in PL/SQL:

makes coding dynamic SQL from PL/SQL
trivial – as opposed to using dbms_sql.

Pass by Reference (NOCOPY) PL/SQL enhancement:

purely a performance
enhancement, speeds up parameter passing – as long as you understand the side
effects!