2 Replies Latest reply: Jan 30, 2013 11:59 PM by DanielSagner RSS

    Secure App. Roles vs. role password


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