Row Level Security - Part 1
Row Level Security – pt.1
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 start with contexts, and logon triggers. In part 2 I move on to the features of ‘official’ RLS, (also known as fine-grained access control or virtual private database) introduced in Oracle 8i. Finally, in part 3, I 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 (
idnumber(6),
v1varchar2(30),
ownervarchar2(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
selectid, v1
frompublic_table
whereowner = 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 who created a row is attached to that row; and 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 involving a globally packaged variable with an initialization section 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_idvarchar2(32);
begin-- should include exception handling
selectgroup_id
intom_group_id
fromsecurity_table-- need to build this table.
whereuser_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
selectid, v1
frompublic_table
whereowner = 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 mis-used.
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 - Designing 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 which also hosts The Co-operative Oracle Users' FAQ for the Oracle-related Usenet newsgroups.