This discussion is archived
2 Replies Latest reply: Jan 30, 2013 11:59 PM by DanielSagner RSS

Secure App. Roles vs. role password

DanielSagner Newbie
Currently Being Moderated

Seems I am missing something regarding the security of Secure Application Roles. What I do is basically:
(Schema is called SPS):

create user x identified by pwd;
grant create session to x;
grant execute on sps.sps_init to x;
grant role_x to x;
alter user x default role none;

Then: create role role_x identified using sps.sps_init;

procedure "sps_init“
authid current_user
execute immediate 'alter session set current_schema = sps';
(some code to get the appropriate role...)---
dbms_session.set_role (l_role);

Is this the correct procedure? There should be something more to it because if the user just finds out the name of the „init“ procedure, he can log in sqlplus and do
sql> exec sps_init;
and he is in.

Wouldn't a role password do a much better (and simpler) job to protect data? Can somebody enlighten me how to do it correctly and what is the real benefit?


Edited by: DanielSagner on Jan 29, 2013 11:22 PM
  • 1. Re: Secure App. Roles vs. role password
    Justin Cave Oracle ACE
    Currently Being Moderated
    Normally, the procedure that you specify for enabling the role will implement some additional validation. For example, if you had a table of application users, you might do something like this (though, obviously, you would store a password hash rather than storing the password in clear text)
    SQL> create table my_users(
      2    user_name varchar2(10),
      3    password  varchar2(10)
      4  );
    Table created.
    SQL> insert into my_users values( 'jcave', 'password' );
    1 row created.
    SQL> ed
    Wrote file afiedt.buf
      1  create or replace procedure check_password( p_username in varchar2, p_pwd in varchar2 )
      2    authid current_user
      3  as
      4    l_cnt integer;
      5  begin
      6    select count(*)
      7      into l_cnt
      8      from my_users
      9     where user_name = p_username
     10       and password  = p_pwd;
     11    if( l_cnt = 1 )
     12    then
     13      dbms_session.set_role( 'ROLE_NAME' );
     14    end if;
     15* end;
    SQL> /
    Procedure created.
    SQL> create role role_name identified using check_password;
    Role created.
    If you design things like this, you can connect to the database using a shared account that has only CREATE SESSION and then whatever application username & password the user enters determines whether the session can enable a role that allows the application to do something interesting.

    A password-protected role is another way of managing security. There are cases where a password-protected role can be helpful but they tend to be relatively uncommon. If users know the password to be able to log in to the database using whatever account the application logs in to, it can be useful to have the application enable a password-protected role so that users logging in via SQL*Plus can't easily figure out the password. But that assumes that you can successfully keep the role password private when you've failed to keep the database account password private which isn't particularly common. There are probably cases where organizations are trying to add appropriate security to an environment that hasn't had it where this may be a useful transition strategy.

  • 2. Re: Secure App. Roles vs. role password
    DanielSagner Newbie
    Currently Being Moderated
    Thank you, Justin

    While I'm not a fan of shared accounts, things are a bit clearer to me.
    I'll leave the "not answered" mark in case somebody else chimes in with some interesting example.



  • Correct Answers - 10 points
  • Helpful Answers - 5 points