Create Oracle role with Password

A role is a set of privileges that can be granted to users or to other roles. We can add privileges to a role and then grant the role to a user. We can then enable the role and exercise the privileges granted by the role. A role contains all privileges granted to the role and all privileges of other roles granted to it. A new role is initially empty. We add privileges to a role with the GRANT statement.

Create role, grant privileges and grant role to existing user.

CREATE ROLE jr_dba
   IDENTIFIED BY "n(V6VW3x!tFOW!SB"; 

SQL> CREATE ROLE jr_dba
   IDENTIFIED BY "n(V6VW3x!tFOW!SB";   2  

Role created.

SQL> grant select any table, backup any table to jr_dba;

Grant succeeded.

SQL> 

SQL> grant connect, resource, jr_dba to dilli;

Grant succeeded.

SQL> 

Now you can login to with the user. When a user logs into Oracle, all default roles are enabled, but non-default roles must be enabled with the SET ROLE statement.

SQL> conn dilli  
Enter password: 
Connected.
SQL> 

SQL> select count(*) from scott.emp;
select count(*) from scott.emp
                           *
ERROR at line 1:
ORA-00942: table or view does not exist

This example would enable the role called jr_dba with a password. You cannot enable role without password.

SQL> set role jr_dba;
set role jr_dba
*
ERROR at line 1:
ORA-01979: missing or invalid password for role 'JR_DBA'


SQL> set role jr_dba identified by "n(V6VW3x!tFOW!SB";

Role set.

SQL> 

Now role jr_dba is enabled and you have all privileges that roles jr_dba has. 
SQL> select count(*) from scott.emp;

  COUNT(*)
----------
 14

SQL> 

We can set non-default role to default role for the user with syntax below.

alter user <username> default role <role_list>;
alter user dilli default role jr_dba;

SQL> show user
USER is "SYS"
SQL> 
SQL> alter user dilli default role jr_dba, connect, resource;

User altered.

SQL> 

SQL> conn dilli
Enter password: 
Connected.
SQL> select count(*) from scott.emp;     
select count(*) from scott.emp
                           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> set role jr_dba identified by "n(V6VW3x!tFOW!SB";

Role set.

SQL> select count(*) from scott.emp;

  COUNT(*)
----------
 14

SQL> 

Any Password protected roles cannot be set to default

Syntax for the setting default role

alter user <username> default role <role_list>;
You can even use keywords instead of a list of roles for <role_list> :

ALL : all roles granted to that user are set by default
NONE : all roles granted have to be set upon login
ALL EXCEPT <excluded_role_list> : all roles granted are set by default except the ones in <excluded_role_list>.