This content has been marked as final. Show 2 replies
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)
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.
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.
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.