0 
0 Many applications built in the Client/Server era made use of the fact that every end user had his or her own Database User Account and every Database Connection and Session were for the use of a single end user at any time. No proxy users or connection pooling were invented. So there was a direct relation at any time between the actual end user typing away at the keyboard and the database user that is available through the USER pseudo-function.
USER can be used – and is very frequently used – in SQL as well as PL/SQL. In this article I will discuss the merits of removing USER from all application code. And also how to go about it. It is based on very recent – yesterday – experience with the migration of a WebForms 6.0 application to the 10g toolstack, with a further migration to Java/J2EE lying around the corner.
Prior to Oracle 10g, performance could also be a reason for trying to avoid excessive use of USER in PL/SQL code. That has largely been resolved in 10g, as you can see below in the note on FAST DUAL.
Far more important is the fact that in an increasing number of applications, for starters almost all HTML and Java based web applications, the end user has not direct relationship with the database user. The Client/Server days with each end-users corresponding with a database user account are long gone. That means that references to USER have come to have a different meaning. A typical Java/J2EE application will have a connection pool with up to several tens of database connections, usually all to the same database user. That means that no matter who is the end user, the same database user account is used. USER does not tell us anything anymore! Of course this also means that the meaning of Database Roles is very limited when it comes to application security.
Note that I am not suggesting we should banish all user related logic and authorization from our applications and databases! What I am saying though is that we must make sure that we refer to the user we intend to refer to. And typically that is the end user, who may or may not relate directly to a database user.
Getting down to business
Many applications I have come across have their own table with application users. Typical columns in an application users table for a Client/Server applications are USERNAME (corresponding with a Database User) and ID or USER_ID, a primary key that is typically referenced from other tables. Such applications frequently contain queries – either in Views or Client Side filter expressions such as Form Block Where Clauses – that restrict record access based on record-ownership, something like:
select * from some_table tbl , app_users usr where usr.username = USER and tbl.usr_id = usr.id /
A more advanced approach to the similar authorization requirement is implementation using VPD (Virtual Private Database) – which requires an Oracle Enterprise Edition database. Note: for an introduction to VPD, see this article Virtual Private Database, securing your data by my colleague Marcos Claver. Using VPD, predicates returned by the Policy Function could be something like:
function TBL_FUN_AUT ( p_schema_name in varchar2 , p_table_name in varchar2 ) return varchar2 is l_usr_id number(10); begin select usr.id into l_usr_id from app_users usr where usr.username = USER ; return ' tbl.usr_id = '||l_usr_id; end TBL_FUN_AUT;
As this policy function is invoked only once in each session, the select against app_users is performed less often than in the previous case.
It is all good and well to take out references to USER, but what do we replace it with? We still need to filter and authorize data based on the current end user. So how do we know server side who the current end user is.
The best solution I can see is to make the filter logic and authorization code refer to our own Application Context in which we make sure that the current user is set up. There can be several ways to set the end user in this context: the Java middle tier of a Web Application can do it, a Database LOGON trigger can do it, a Client/Server LOGON or (RE)CONNECT event can do it etc.
First our Application Context:
create or replace context pks_context using pks_context_editor /
The using clause in the creation of the Application Context refers to a database package that is to be used to set values in this particular context. The package is defined as follows:
create or replace package pks_context_editor as c_username constant varchar2(8):= 'USERNAME'; c_userid constant varchar2(8):= 'USERID'; c_module constant varchar2(6):= 'MODULE'; procedure set_user ( p_username in varchar2 ); procedure set_module ( p_module in varchar2 ); end pks_context_editor; / create or replace package body pks_context_editor as procedure set_user ( p_username in varchar2 ) is l_user_id number; begin dbms_session.set_context ( 'PKS_CONTEXT' , c_username , p_username ); select usr_id into l_usr_id from app_users usr where usr.USERNAME = p_username ; -- this next statement ensures that for all authorization filters, we can directly use the APP_USERS.ID value -- instead of deriving it over and over again from the USER(NAME) dbms_session.set_context ( 'PKS_CONTEXT' , c_userid , l_usr_id ); end set_user; procedure set_module ( p_module in varchar2 ) is begin dbms_session.set_context ( 'PKS_CONTEXT' , c_module , p_module ); end set_module; end pks_context_editor; /
In case of Client/Server applications or WebForms applications – where too each user has his own database connection and user account – it is easiest to set the current user from a Database Logon trigger, such as this one:
CREATE OR REPLACE
TRIGGER PKS_LOGON_TRG
AFTER LOGON ON DATABASE
declare
l_plsql_call varchar2(2000):= 'begin
pks_context_editor.set_user( p_username => sys_context( ''USERENV'', ''SESSION_USER''));
end;';
BEGIN
-- we make use of dynamic sql to have changes in the pks_context_editor package not invalidate this logon trigger
execute immediate l_plsql_call;
exception
WHEN OTHERS
THEN
dbms_output.put_line( 'Error in database logon trigger '||sqlerrm);
END;
/
In Java Web applications, we have to build in logic that will set the End User (aka Web User) immediately after retrieving a connection from the connection pool.
Thus using our own application context to contain the current end user, for the moment regardless of how the application made sure that the end sure gets set in this context, we can rewrite all logic – SQL and PL/SQL – that so far made use of USER, to now make use of SYS_CONTEXT(‘PKS_CONTEXT’,’USERNAME’) or SYS_CONTEXT(‘PKS_CONTEXT’,’USERID’).
For example:
select * from some_table tbl , app_users usr where usr.username = USER and tbl.usr_id = usr.id /
gets rewritten as:
select *
from some_table tbl
where tbl.usr_id = SYS_CONTEXT('PKS_CONTEXT','USERID')
/
Proposed standard for SQL and PL/SQL development
It is a continuous struggle for us at AMIS to keep our standards for SQL and PL/SQL development up to date. I propose a very clear standard:
Thou shalt not use USER in either SQL or PL/SQL code.
Instead, use references to an Application Context variable, such as sys_context(‘APP_CONTEXT’,’USERID’) and ensure that either in a database LOGON trigger or from the application the current end user is set in this context.
Rationale: USER is the database user and often does not correspond to the actual end user. To ensure that authorization and filtering based on user preferences is done correctly, the code should refer to the representation of the end user and not just the database user. In addition, this approach yields a small performance benefit, especially in pre 10g databases where USER is translated to select USER from dual.
A note to the side: FAST DUAL
It is perhaps noteworthy to know that a reference to USER is translated to this select statement:
select USER [ into] from DUAL
Prior to 10g, this meant that frequent reference to USER in PL/SQL meant frequent queries against DUAL. Each call to the DUAL creates logical I/Os, which the database can do without. In some cases the call to DUAL is inevitable as in the line
In Oracle9i
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
1 TABLE ACCESS (FULL) OF 'DUAL'
In 10g
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
0 FAST DUAL
Notice the use of the new FAST DUAL optimization plan, as opposed to the FULL TABLE SCAN of DUAL in Oracle9i. This improvement reduces the consistent reads significantly, benefiting applications that use the DUAL table frequently.
Related posts:
Oracle 7Up to 10g – How time flies… Training for seasoned Oracle SQL and PL/SQL Developers 
Building a Route Planner with Oracle SQL – Finding the quickest route in a graph Bloom Filters, Hierarchical Profiling, Synopses & One pass distinct sampling, jumping the GAPP … – feast for DBAs (considered harmful..) Oracle Advanced Queuing and JMS – bridging from AQ to JMS and vice versa Oracle Open World / Java One Preview bij AMIS
[…] In the Amis Technology corner Lucas jellema is discussing the problem with Oracle’s USER: Standard for Database Development – Getting rid of USER from PL/SQL and SQL – no longer is USER equivalent to End User. Many applications use USER to determine who’s logged on to the database. Security is based on USER and roles. But this only works if every user actually logs in using his own account. […]
The benefit of using application contexts is twofold: changes in the values are directly picked up. Values in the package variables are only picked up if you enforce that the policy function is called again after a change is made in the values of the package variables. In Oracle 9i, the Policy Function is called only once per session – the first time a query is run on the table or view the policy function is defined against – unless the policy function depends on the SYS_CONTEXT function. In 10g, you can specify for policy functions if they are static, dynamic (invoked every time a query is run) or context sensitive.
The second benefit of using the Application Context over Package Functions is that a reference to an Application Context in a where clause is resolved like a bind-parameter and evaluated only once per query, whereas a package function is called over an over again for every record that is evaluated, even if the function returns the same value for every call.
Instead of using SYS_CONTEXT, I’m using package_variables. This gives me more flexibility.
In a logon trigger, I can fill the ‘Context-user’ and extra ‘context-data’.
I also fill an internal table with RLS-clauses.
Every VPN-definition is set like:
begin
DBMS_RLS.ADD_POLICY (
object_schema => ‘xxx_OWN’,
object_name => ‘COMPANIES’,
policy_name => ‘RLS_COMPANIES’,
function_schema => ‘SYSBEHEER’,
policy_function => ‘SBR$AUTORISATIE.RLS_autorisatie’
);
end;
The function SBR$AUTORISATIE.RLS_autorisatie returns the WHERE-clause for the current user.
This clause can be different for different users. This can be handy for batch-processing.
FUNCTION SBR$AUTORISATIE.RLS_AUTORISATIE
(p_obj_schema VARCHAR2
,p_obj_name VARCHAR2)
RETURN VARCHAR2
IS
t_predicate VARCHAR2(4000) := ‘1=1’;
BEGIN
IF t_rls_aut_tab.exists (p_obj_name)
THEN
t_predicate := t_rls_aut_tab(p_obj_name);
END IF;
RETURN (t_predicate);
END RLS_AUTORISATIE;
By using the extra context-data, I don’t need the USER_column in my tables.
Users can only access companies they’re authorised for:
SELECT *
FROM COMPANIES
WHERE sbr$autorisatie.autorisatie_company (company_code) = sbr.vtrue;
FUNCTION autorisatie_company
(p_company_code VARCHAR2)
RETURN VARCHAR2
IS
t_return sbr.tp_chr%TYPE := sbr.vfalse;
BEGIN
IF t_aut_company_tab.exists (p_company_code)
THEN
t_return := sbr.vtrue;
END IF;
RETURN (t_return);
END autorisatie_company;
For the Batch-usser, the where-clause from the function SBR$AUTORISATIE.RLS_AUTORISATIE results in:
SELECT *
FROM COMPANIES
WHERE 1=1;
Other tables can depend on this authorised comany table:
SELECT*
FROM COMPANY_LOCATIONS CLS
WHERE EXISTS (SELECT 1 FROM COMPANIES CPS WHERE CPS.COMPANY_CODE = CLS.COMPANY_CODE)
And for batch-users the where_clause can be:
SELECT*
FROM COMPANY_LOCATIONS CLS
WHERE 1=1
Good article – I’ve been thinking about the need for alternatives to USER for some time, but hadn’t come up with a solution I
liked. I’m not sure about using an application context however. Shouldn’t this be a database-wide (or even enterprise-wide)
solution? After all, a single user may have access to more than one application. I have a schema called COMMON, with the same
objects and procedures in all of my databases – things that are granted to PUBLIC with PUBLIC synonyms. Maybe the identity
context should be part of COMMON?
didnt used proxy user for a long time now, but if i remember correctly USER will give the proxy user name. am I wrong?
Review by Pete Finnigan on http://www.petefinnigan.com/weblog/archives/00000549.htm
09/12/2005: “Amis talks about the need to remove USER from PL/SQL and SQL code”
I saw a great article on the Amis blog a few days ago and made a note to have a look and talk about it here. The article is written by Lucas Jellema and is titled ” Standard for Database….[Read More]
I saw a great article on the Amis blog a few days ago and made a note to have a look and talk about it here. The article is written by Lucas Jellema and is titled “Standard for Database Development – Getting rid of USER from PL/SQL and SQL – no longer is USER equivalent to End User”. This is a great article that talks about the need to get rid of the USER pseudo function from older code written in the days when all database users had their own database account and using the function did return the correct user. This was in the days before proxy users and connection pooling. Lucas starts with the reasons USER was used and the implications of using it now and why it should be removed and the fact that he is saying it shouldn’t be removed in all cases. Lucas goes on to show some examples of a home grown application user table and also a VPD example. He goes on to give examples of how the user might be set and the issues of application servers, middle tiers and logon triggers. Lucas also talks about the fact that the use of USER translates to a select from dual and how this has improved in 10g.
Great paper and well worth a read.
Very clear exercise!
Great article. Just yesterday, I worked on this in an application of mine. My need was for archiving/journaling changes to a table. I sought a different approach that does not rely on the application to set the context as I wanted to capture changes being done with other end user tools. (sql plus, toad etc.) I chose to grab osuser and module from v$context and use that instead. It is in testing now, so I am not sure if it will work in all situations, but so far, so good.