 |
|
Oracle Tips by Burleson |
Oracle Row-Level Security
To fully understand and appreciate Oracle’s
virtual private databases it’s important to take a look at the
evolution and history of Oracle security. Back in Oracle7, Oracle
modeled their security package after the ANSI standards for relational
databases. Relational security involves the granting of
individual table privileges and system privileges to individual users.
At runtime, the Oracle SQL semantic analysis routine parses each SQL
statement to see the tables and columns involved in the query and then
ensures that the user has appropriate authority to execute the SQL
statement.
Here are some samples of basic relational security grants.
grant select
on customer to fred;
grant select any table to mary;
While the direct assignment to of object
privileges and system privileges to a user is very simple, it soon
became difficult to manage the direct grants of table privileges.
In systems that consist of thousands of tables and hundreds of users,
it is prohibitive to try to explicitly grant access to each and every
user within the community.
Traditional relational role-based Security
To get around this problem of direct grants,
Oracle implemented role-based security. Using roles, the Oracle
administrator can “bundle” together object privileges and system
privileges into a named package called a role. Once defined, the
Oracle database administrator (DBA) can assign these roles directly to
individual end-users. This approach greatly simplified the
administration of traditional relational security. Here is a
simple example of role-based security.
SQL> create
role customer_role;
Role
created.
SQL> grant
select on customer to customer_role;
Grant
succeeded.
SQL> grant
select, update on product to customer_role;
Grant
succeeded.
SQL> grant customer_role to fred;
Grant
succeeded.
Grant Execute Security
Oracle also introduced a new type of security
whereby execution privileges are granted to procedures. This
type of grant execute security completely bypass traditional Oracle
security, and relies on the fact that the user must invoke in a
predefined PL/SQL package. For example, when the DBA grant
execute on see customer maintenance package and to user for adding,
the user name Fred is that entitled to execute the page. Even
though Fred may have no assigned privileges or roles, the user name
Fred can execute a procedures within the customer maintenance package.
During the time that Fred is executing the package, he inherits the
object privileges associated with the creator of the customer
maintenance package.
Because Fred may only execute the customer
maintenance function and is not free to do anything other than the
procedure specified in the PLC cool, Oracle security is maintained
without the burden of having to granting specific privileges of tube
any hundreds of users.
The problem of row-level security
The problem with standard relational security
is that the grant statements specify that you are allowed to select
from a table, but they cannot specify which rows you are allowed to
see. An early solution to this problem was to create views on the
data, and grant specific views to specific users. By creating a
view for each class of users, row-level security was possible:
create or
replace view
star_view
as
select * from book where pub_key='P005';
grant select on star_view to fred;
connect fred/flintstone;
Now the Fred user can use the star_view as an
alias for the book table, and only sees data rows that belong to Star
Publishing.
select
pub_key, book_title from star_view;
PUB_KEY
BOOK_TITLE
-------
-------------------------------------------------------
P005 operations research theory
P005 pay no taxes and go to jail
While this approach works, there are some
serious maintenance problems:
1. For sophisticated queries, many complex
views must be created and maintained
2. The SQL execution plan for views are
often sub-optimal and difficult to tune
To get around this problem, Oracle 10g
introduced “fine-grained access control” which is also known as
Virtual Private Databases.
Fine-grained access control (Virtual
Private Databases)
Starting in Oracle8i, Oracle introduced a new
PL/SQL package for row-level security called dbms_rls. This
row-level security package became the foundation for a new type of
Oracle database security called fine-grained access control. The
Oracle marketing people got excited and re-named this feature as
Virtual Private Databases, or VPDs.
Compared to traditional role-based security,
fine-grained access control is very complex and different than the
other methods of Oracle security.
When we strip-away all of the complexity,
fine-grained access control does nothing more than change SQL
statements to add a WHERE clause.
Fine-grained access control relies on table
triggers called “policies”. A policy is a procedure that
returns the predicate for a WHERE clause. This resulting WHERE
clause is appended to the SQL statement and the SQL is executed.
This policy “predicate” is appended to all of the incoming SQL against
that table, such that specific end-users can only see specific rows
within the table. For every SQL statement, a “predicate” is
appended to the select, restricting their query. For example:
select *
from book;
Is appended with the predicate that was returned from invoking
the policy function:
select *
from book where pub_key = ‘p005’;
This predicate is returned from a “policy”
function, which is usually a PL/SQL stored procedure. The policy
is tightly-coupled to the table by invoking the DBMS_RLS.ADD_POLICY
procedure. Once coupled, the policy is fired for every SQL
statement that references the table. At login time, each
session_user is verified using the sys_context(‘USERENV’,’session_user’)
call. So Oracle knows the identity of each user. The user ID is
passed as an argument to the policy, and the policy generates the
predicate according to the logic in the PL/SQL.
Note that the VPD approach will not work for
all systems. In SAP for example, all connections to Oracle are
made with the same user ID, and it is impossible for Oracle to verify
the identity of a specific user.
Oracle achieves this dynamic SQL re-write
capability in a very creative way. Let’s take a look at how VPDs
work.
1 – At login time, the USER ID is verified
using the sys_context PL/SQL package. The sys_context package
verifies the session_user column from v$session. At this point,
Oracle knows the identity of the user.
1 – User sign-on - The database is
defined with a user-logon system-level trigger. At login time,
the sys_context or dbms_session.Set_Context procedures can be called
to verify the user ID.
2 - The User issues an SQL Query
– All incoming SQL enters the library cache where the SQL semantic
analyzer identifies all tables involved in the query.
3 – Invoke policy routine – A policy
routine is automatically called, passing the session_user as an
argument. Inside the policy, we can create custom WHERE clauses
based on the user ID. For privileges users like SYS and SYSTEM,
we do not return a predicate. Here is a simple example.
Note that the predicate looks just like a WHERE clause argument.
IN this example below, the STAR user will have “where pub_code =
‘p005’” appended to the end of svery SQL statement that they issue
against the book table.
if (sys_context('userenv','session_user')
like ('SYS',’SYSTEM’))
then
predicate := null;
else
if (sys_context('userenv','session_user') = 'STAR')
then
predicate := 'pub_code = ‘p005’')';
if (sys_context('userenv','session_user') = 'MAMMOTH')
then
predicate := 'pub_code = ‘p006’')';
end if;
4 – Return predicate and change the SQL
– Oracle appends the SQL and changes the SQL inside the library cache.
The SQL optimizer is then invoked to generate the execution plan for
the modified query.
5 – Execute and return rows – The rows
are then returned to the user who is unaware that their SQL had been
changed.
Now that we see how VPDs work, let’s look at
how you would create them.
1 – Create a set_context stored procedure
– This procedure will call dbms_session.set_context to verify the ID
of the connected user. Here is a sample of this procedure.
CREATE OR
REPLACE PACKAGE user_pack AS
PROCEDURE Set_Context;
END;
/
CREATE OR
REPLACE PACKAGE BODY user_pack IS
PROCEDURE Set_Context IS
v_ouser VARCHAR2(30);
v_id NUMBER;
BEGIN
DBMS_Session.Set_Context('SCHEMAOWNER','SETUP','TRUE');
v_ouser := SYS_CONTEXT('USERENV','SESSION_USER');
BEGIN
SELECT id
INTO v_id
FROM users
WHERE ouser = v_ouser;
DBMS_Session.Set_Context('SCHEMAOWNER','USER_ID', v_id);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_Session.Set_Context('SCHEMAOWNER','USER_ID', 0);
END;
DBMS_Session.Set_Context('SCHEMAOWNER','SETUP','FALSE');
END Set_Context;
END user_pack;
/
2 – Create a user-login system-level
trigger to invoke set_context – A system-level user login trigger
must be created to invoke the set_context procedure every time someone
connects to oracle. Here is an example.
connect
system/manager as sysdba;
create or
replace trigger
set_user_row_context
after logon on database
begin
user_pack.set_context;
end;
/
3 – Create a package and stored procedure
for the policy check – Now that al users are validated at sign-on
time, we need to write the PL/SQL policy to assign the SQL predicate.
The policy can be very simple or extremely complex, setting predicates
according to the current user ID. Here is an example.
create or
replace procedure
filter_book_rows
AS
. . . .
if (sys_context('userenv','session_user') like ('SYS',’SYSTEM’))
then
predicate := null;
else
if (sys_context('userenv','session_user') = 'STAR')
then
predicate := 'pub_code
= ‘p005’')';
if (sys_context('userenv','session_user') = 'MAMMOTH')
then
predicate := 'pub_code = ‘p006’')';
end if;
END
/
4 – Call dbms_rls.add_policy to associate
this policy with a table – With the policy written, we then call
the row-level security (RLS) package to associate out policy with a
table. Here is an example:
exec
dbms_rls.add_policy(
'pubs',
'book',
'book_data_select_policy',
'pubs',
'security_package.filter_book_rows',
'select',
true);
As we can see row-level security is quite
complex to set-up, and it can be cumbersome for complex queries.
Conclusion
Oracle 10g now has three totally separate and
distinct ways for managing data access.
1 – Role-based security – The traditional
approach of granting privileges to users or roles.
2 – Grant Execute security – This manages
security, but all users must be granted execute privileges on all the
procedures within the application.
3 – Row-level security (a.k.a. fine-grained
access control, Virtual private databases) – This scheme enhances
traditional security by appending a WHERE clause to all SQL.
For more details, see the "Easy
Oracle Series" a set of books especially designed by Oracle
experts to get you started fast with Oracle database technology.
|
