Standard for Database Development – Getting rid of USER from PL/SQL and SQL – no longer is USER equivalent to End User

8

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 <some variable> ] from DUAL</some>

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 ause Oracle code treats DUAL as a special table, some ideas for tuning tables may not apply or be relevant. Oracle Database 10g makes all that worry simply disappear: Because DUAL is a special table, the consistent gets are considerably reduced and the optimization plan is different as seen from the event 10046 trace.

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.

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

8 Comments

  1. Pingback: IT-eye Weblog » Oracle Proxy Users

  2. 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.

  3. 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

  4. 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?

  5. didnt used proxy user for a long time now, but if i remember correctly USER will give the proxy user name. am I wrong?

  6. 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.

  7. 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.