Skip to content

DBAzine.com

Sections
Personal tools
You are here: Home » Oracle » Oracle Articles Archive » Row Level Security - Part 1
Seeking new owner for this high-traffic DBAzine.com site.
Tap into the potential of this DBA community to expand your business! Interested? Contact us today.
Who Are You?
I am a:
Mainframe True Believer
Distributed Fast-tracker

[ Results | Polls ]
Votes : 3549
 

Row Level Security - Part 1

by Jonathan Lewis

Part 1  |  Part 2

In this short series I will be looking at the progressively more subtle and aggressive levels of row-level security that can be imposed on your data in Oracle 8 and 9. In part 1, I will start with contexts, and logon triggers. In part 2, I will move on to the features of “official” RLS, (also known as fine-grained access control or virtual private database) introduced in Oracle. Finally, in part 3, I will examine the way in which Oracle Corp. has extended RLS to an implementation of label security. All the code examples in these articles were tested using Oracle 9.2.0.3

History

Ever since triggers appeared in Oracle, it has been easy to impose a simple form of user-based data control by combining a table, a view, and a trigger. Take, for example, the code listed below:

create table public_table (
id number(6),
v1 varchar2(30),
owner varchar2(32)
);
create or replace trigger pt_bri
before insert on public_table
for each row
begin
new.owner := user;
end;
/
create or replace view private_view
as
select
id, v1
from public_table
where owner = user;

grant insert, select, update, delete on private_view to public;

If you now create a couple of users with the create session privilege, you will find that they can select, insert, update, and delete rows from private_view (provided they remember to include the name of its owning schema), but the only rows that they will be aware of will be the rows that they have created. They will not be able to see each other’s rows. The trigger ensures that the name of the person creating a row is attached to the row; the predicate ‘owner = user’ ensures that only the original creator of a row will be able to see that row.

(Note — the pl/sql line :new.owner := user will result in a ‘select user from dual’ so you would probably use more subtle code in an efficient production system).

Only the owner of the table will be able to see all rows; but that is because the table owner will be able to query the table, and will not be restricted to querying the view. In effect, we have a single, fixed, view text which is interpreted differently at run-time because of the user ‘pseudo-parameter’ that appears in the definition.

Although this is obviously one option for row-level security, a more useful form of row-level security would probably have to be flexible enough to cope with groups rather than individuals. A mechanism which allowed users in a specific group to see data created by the other users in that group is probably the minimum useful requirement.

Of course, a group-based mechanism like this has also been possible for a long time, and usually featured a packaged function that could be used inside both a view and a trigger in much the same way that the user pseudo-column was used above. But this method produced a large overhead because of the number of calls to the packaged function that have to take place (one call per row accessed). This specific performance issue disappeared when Oracle introduced ‘environment variables’ and the sys_context() call in Oracle 8.1.

In passing – if you have code that makes calls to userenv() function, you should be planning to change it to use the sys_context() call on the ‘userenv’ context. For example:

select sys_context(‘userenv’,’sessionid’) from dual;

rather than

select userenv(‘sessionid’) from duall;

The userenv() function is a deprecated feature, and the ‘userenv’ context has far more options available to it.

Contexts

The idea behind contexts is very simple, and remarkably secure. In its initial form, it can be defined by three features: (i) a context is a list of memory variables, with session-specific values, (ii) a session can see its current values of these variables by calling the sys_context() function, (iii) the variables in a particular context can only be set by calling a procedure has been associated with that context. For example, with a supporting security table, we could rewrite the previous code as follows:

create or replace context security_ctx
using security_proc; -- the procedure protecting the context.
-- this could be a packaged procedure.

create or replace procedure security_proc as
m_group_id varchar2(32);
begin -- should include exception handling

select group_id
into m_group_id
from security_table -- need to build this table.
where user_name = user;

dbms_session.set_context(
namespace => 'SECURITY_CTX',
attribute => 'GROUP_ID',
value => m_group_id
);

end;
/
create or replace trigger pt_bri
before insert on public_table
for each row
begin
:new.owner := sys_context('security_ctx','group_id');
-- should include error trapping, e.g. null values
end;
/

create or replace view private_view as
select id, v1
from public_table
where owner = sys_context('security_ctx','group_id');

grant insert, select, update, delete on private_view to public;

With this code in place, private_view behaves pretty much as before. Users who insert data into the view automatically have their group code attached to the row at the same time. Users who query the view will see only those rows that match their group code. But there is a gap in this implementation at the moment. How will the users group code get set in their local context ?

To complete the picture, we need to take advantage of logon triggers. For example, the schema that created the table, procedure and view could execute the following code:

create or replace trigger security_trigger
after logon on database
begin
    security_proc;
end;
/

Since the trigger fires with the privileges of the schema that created it, it will execute the procedure correctly, whichever schema logs on to Oracle and causes it to fire. This means that the procedure that sets the context need not be visible to any other user, and therefore cannot be misused.

Although this is a sufficiently powerful mechanism to enforce row-level security, or the virtual private database, bear in mind that it does depend on a row-level trigger firing on every insert into the table. Row-level triggers do add a significant overhead to processing, and in particular they block some of Oracle’s high-efficiency mechanisms and convert array processing into single-row processing.

So you should not use this mechanism without considering and testing the performance impact – particularly on batch-load processes. Remember, too, that you may find some examples of SQL where the optimiser produces a less efficient execution path because your application code will now have to reference a view (private_view) where is used to reference a table (public_table).

Be very careful when writing logon triggers, always test them against a single schema (after logon on test_user.schema would create a logon trigger on schema test_user) before creating them at the database level, or you may have to connect as SYS to clear up the mess if anything goes wrong.

To be able to create, and drop, contexts, you will need to have the privileges:

   create any context
   drop any context

And to create, and drop, database triggers, you will need to have the privilege

   administer database trigger

If you want to be able to see the current settings of variables in the currently used contexts, you could give users the privilege to view the associated dynamic performance view, so that they can run a query such as:

select namespace, attribute, value
from sys.v_$context
order by namespace, attribute;

NAMESPACE            ATTRIBUTE         VALUE
-------------------- ----------------- -----------
SECURITY_CTX         GROUP_ID          T

Conclusion

The concept of environment variables, or contexts, is a powerful and flexible addition to the Oracle working environment. By creating triggers that set context variables we can ensure that various pieces of useful information can be set automatically at logon time; at the same time, we can ensure that these variables are available to, but not changeable by, the end-user. Combining contexts with statically defined views and table-based triggers, we can create a simple, yet fairly powerful, form of the virtual private database without resorting to the full functionality of the official RLS mechanism.

--

Jonathan Lewis is a freelance consultant with more than 18 years' experience of Oracle. He specialises in physical database design and the strategic use of the Oracle database engine, is author of Practical Oracle 8i - Building Efficient Databases published by Addison-Wesley, and is one of the best-known speakers on the UK Oracle circuit. Further details of his published papers, presentations and seminars and tutorials can be found at http://www.jlcomp.demon.co.uk, which also hosts The Co-operative Oracle Users' FAQ for the Oracle-related Usenet newsgroups.


Contributors : Jonathan Lewis
Last modified 2006-01-05 10:42 AM

Row-Level Security for SQL Server 2005

Posted by cmichaelgraham at 2006-03-06 12:54 PM
You can use the free eval version of Data Nomad @ http://www.technicalmedia.com to create the users, groups, views, synonyms and triggers required to implement row-level security on SQL Server 2005 (including Express edition).

Row-Level Security for Microsoft SQL Server 2005

Data Nomad® is an affordable set of developer tools that extend the Microsoft SQL Server 2005 platform to provide row-level security and remote access features allowing developers to accurately and efficiently create and manage powerful distributed applications that insure access to information is protected.

Developers of .NET 1.1 and .NET 2.0 smart client and web applications can now easily add row-level security to database applications through the Data Nomad® developer tools. Existing databases are easily configured by identifying the tables to be protected and by creating row-level permission grants.

The same (unmodified) SQL statements work against the Nomad database extensions. The extended database appears to only contain the rows to which the user has at least read permissions. Database updates and deletes only succeed against rows to which the user has owner permissions.

This type of seamless integration is achieved by leveraging two powerful new features of Microsoft SQL Server 2005: the schema (a collection of database objects that form a single namespace) and the synonym (an alternative name for another database object providing a layer of abstraction over the original object).

The Nomad extensions support both SQL Server authentication and Integrated NT authentication for database connections, and support local, LAN-connected, and Web-connected backend databases.

“Using Technical Media’s Nomad product has saved us months of development” said Darcy Vaughan, a founder and Director of PetroWEB, Inc.

PetroWEB, Inc has obtained an exclusive Data Nomad® license for the upstream oil and gas industry. For information on utilizing Data Nomad® technology in this industry, please contact Darcy Vaughan at 303.308.9100.
Transaction Management
Reduce downtime and increase repeat sales by improving end-user experience.
Free White Paper
Database Recovery
Feeling the increased demands on data protection and storage requirements?
Download Free Report!
 
 

Powered by Plone