amar on web

Secure Application Roles

Most applications, I have come across, make use of password enabled roles to set the access when user logs in. The password is usually placed in a table and may be encrypted. Having a weak design based on password to grant user access can become a security loophole. Oracle also provides the option of setting a role using a routine (instead of a password) that further secures the database and allows us to apply business rules to authenticate the access request. This way we can replace password with set of application checks that are difficult to hack in. Such roles are known as application roles and can be enabled using an authorized routine. The authorization routine replaces the password and is a key component here, it should be well designed to verify and validate all the required checks and conditions before enabling the role for a session.

Creating Application Role

Let us take simple example, I have the following requirement. Role AM_FULACC provides full access to users for transactions. This role should be set only if user is coming through the web server. This way, if someone directly tries using SQL*Plus or a third-party tool, the role should not get enabled. Moreover, it also ensures that since the call is coming from the web server that hosts my application, it is a genuine one.

1. create the authorization package.

Please note, within a PL/SQL unit, DBMS_SESSION.SET_ROLE call is used to set a role. I have done some hard coding in this example, you can make use of generic option to avoid this.
create or replace procedure am_full_power 
authid current_user is 
begin 
  if sys_context('USERENV', 'IP_ADDRESS') = '57.12.41.251' then 
    dbms_session.set_role('am_fullacc'); 
  else 
    raise_application_error(-20001, 'User is not authorized to connect.'); 
  end if; 
end am_full_power; 
/

In my case, I am testing on Oracle iAS. As the OC4J daemon is running on the web server, my application returns the web server ip address when I query the user environment variables. This way, I can verify that the requested session ip address is and the call is valid.

2. Create the role

SQL> Create role am_fullacc identified using am_full_power; 
Role created.

3. Grant the role to required users and no-default it.

SQL> grant am_fullacc to amar; 
Grant succeeded.

SQL> alter user amar default role none; 
User altered.

4. Additionally, the execution privilege on the routine is also required.

SQL> grant execute on am_full_power to amar; 
Grant succeeded.

Working with Application role

For this example, I am not checking privileges granted to a role, just whether the role is activated as per the rule. Now when the role setting is tried from a client (some other node than the web server) it fails, as is the rule placed in the code.
SQL> conn amar/[email protected] 
Connected.

SQL> select * from session_roles; 
no rows selected

SQL> begin 
  2    sys.am_full_power; 
  3  end; 
  4  / 
begin 
* 
ERROR at line 1: 
ORA-20001: User is not authorized to connect. 
ORA-06512: at "SYS.AM_FULL_POWER", line 7 
ORA-06512: at line 2


SQL> select * from session_roles; 
no rows selected

The role setting works fine on calling from SQL*Plus directly on the web server.
SQL> conn amar/[email protected] 
Connected.

SQL> select * from session_roles; 
no rows selected

SQL>begin 
  2    sys.am_full_power; 
  3  end; 
  4  /

PL/SQL procedure successfully completed.

SQL> select * from session_roles; 
ROLE 
------------------------------ 
AM_FULLACC

1 row selected.

Application roles cannot be directly set in SQL*Plus using the SET ROLE command. Only the authorized routine can be used to set the role.
SQL> set role am_fullacc; 
set role am_fullacc 
* 
ERROR at line 1: 
ORA-28201: Not enough privileges to enable application role 'AM_FULLACC'

SQL> exec sys.am_full_power; 
PL/SQL procedure successfully completed.

More on the Authorization routine

The routine that is used for enabling a role should be well designed and thoroughly tested for any loopholes. I would also recommend it be wrapped. The routine can be a procedure, function or even a package. It should run with invokers rights, i.e., AUTHID CURRENT_USER is mandatory. The below error is encountered if a routine with definer's right is used.
ORA-06565: cannot execute SET ROLE from within stored procedure
We can also put in code for performing some activity whenever a role is set, for instance, auditing activity

Once a routine is assigned to a role, no other program unit can be used to enable the role. For instance, I created two routines with the same code and tried enabling the role. Only the authorized routine can enable the role.
SQL> select * from session_roles; 
no rows selected

SQL> begin 
  2    sys.am_full_power2; 
  3  end; 
  4  / 
begin 
* 
ERROR at line 1: 
ORA-28201: Not enough privileges to enable application role 'AM_FULLACC' 
ORA-06512: at "SYS.DBMS_SESSION", line 120 
ORA-06512: at "SYS.AM_FULL_POWER2", line 5 
ORA-06512: at line 2

SQL> begin 
  2  sys.am_full_power; 
  3  end; 
  4  /

PL/SQL procedure successfully completed.

SQL> select * from session_roles;

ROLE 
------------------------------ 
AM_FULLACC

Below is an example of using a package as an authorization role.
create or replace package am_full_power  
authid current_user is 
  procedure enable_access; 
end; 
/

create or replace package body am_full_power is 
Procedure enable_access is 
begin 
  if sys_context('USERENV', 'IP_ADDRESS') = '57.12.41.251' then 
    dbms_session.set_role('am_fullacc'); 
  else 
    raise_application_error(-20001, 'User is not authorized to connect.'); 
  end if; 
end enable_access; 
end am_full_power;   
/

Please note that the role should be identified by only the package name and the inside routines should not be specified. When setting the role, call the complete 'package.procedure' name. Like in the below example.
SQL> alter role am_fullacc identified using sys.am_full_power; 
Role altered.

SQL> conn amar/[email protected] 
Connected.

SQL> select * from session_roles; 
no rows selected

SQL> begin 
  2  sys.am_full_power.enable_access; 
  3  end; 
  4  /

PL/SQL procedure successfully completed.

SQL> select * from session_roles;

ROLE 
------------------------------ 
AM_FULLACC

1 row selected.

A function can also be used as an authorization routine, but it cannot be part of a query or DML statement. In the below example, I am replacing the above procedure as a function.
create or replace function am_full_power 
return number 
authid current_user is 
begin 
  if sys_context('USERENV', 'IP_ADDRESS') = '57.12.41.251' then 
    dbms_session.set_role('am_fullacc'); 
    return(0); 
  else 
    raise_application_error(-20001, 'User is not authorized to connect.'); 
  end if; 
end am_full_power;

SQL> conn amar/[email protected] 
Connected.

SQL> select sys.am_full_power from dual; 
select sys.am_full_power from dual 
       * 
ERROR at line 1: 
ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML 
ORA-06512: at "SYS.DBMS_SESSION", line 120 
ORA-06512: at "SYS.AM_FULL_POWER", line 6

SQL> declare 
  2     l_chk pls_integer; 
  3  begin 
  4    l_chk :=  sys.am_full_power; 
  5  end; 
  6  /

PL/SQL procedure successfully completed.

SQL> select * from session_roles;

ROLE 
------------------------------ 
AM_FULLACC

Application role dictionary views

The application roles are shown in the DBA_ROLES dictionary view. To identify the routine that is associated with an application role, query the DBA_APPLICATION_ROLES view.
SQL> select * from dba_roles where role = 'AM_FULLACC';

ROLE                           PASSWORD 
------------------------------ -------- 
AM_FULLACC                     YES

1 row selected.

SQL> select * from dba_application_roles where role = 'AM_FULLACC';

ROLE                           SCHEMA                         PACKAGE 
------------------------------ ------------------------------ ------------------ 
AM_FULLACC                     SYS                            AM_FULL_POWER

1 row selected.

Best viewed in medium text size. Please refresh this page (F5) to view the latest information.
This page was created on 31-jan-2007 and last updated on 31-jan-2007.
please forward all queries to [email protected]