-- illustration d'un phenomene rigolo avec les roles avec mot de passe
-- affecter a un role sans mot de passe un role avec mot de passe
-- le mot du passe du role avec mot de passe n'est jamais demande 
-- http://www.petefinnigan.com/ramblings/issues_with_roles_and_passwords.htm

create user role_test identified by role_test; 
grant create session to role_test; 
create role pwd_role identified by pwd_role;

-- droit de selectionner dba user 
grant select on dba_users to pwd_role; 
grant pwd_role to role_test; 
alter user role_test default role none;

-- connect role-test 
select username from sys.dba_users ;
select * from session_roles; 
select * from user_role_privs; 
select username from dba_users ;

-- attention les mdp de roles sont passes en clair sur le reseau
set role pwd_role identified by pwd_role;
select * from session_roles; 
select username from sys.dba_users ;

create role non_pwd_role; 
grant pwd_role to non_pwd_role; 
grant non_pwd_role to role_test; 
alter user role_test default role none; 

select * from session_roles; 
select * from user_role_privs; 
set role non_pwd_role; 
select * from session_roles;
select username from sys.dba_users;