What roles can be set as default for a user in Oracle Database

Question: What are the roles that can be set as DEFAULT for a user starting with 11.1.0.7 and 10.2.0.4?

The DEFAULT clause in the query:

sql> alter user [username] default roles [role list];

specifies the roles granted by default to the user at logon. This clause can contain only roles that have been granted directly to the user with a GRANT statement, or roles created by the user with the CREATE ROLE privilege. You cannot use the DEFAULT ROLE clause to enable:

1. Roles not granted to the user
2. Roles granted through other roles
3. Roles managed by an external service (such as the operating system), or by the Oracle Internet Directory
4. Roles that are password authenticated.
5. Roles that are implemented as secure application roles.
6. External authenticated roles

For password authenticated roles, the change has been introduced in version 10.2.0.5 and 11.1.0.7. For secure application roles, the change has been introduced in the Oracle releases 10.2.0.4 and 11.1.0.7. These changes will apply to all future releases. The above mentioned restrictions will be introduced in future documentation.

If upgrading from a previous version, use the following query to identify password protected roles that some users may have been assigned by default:

break on "Password protected Role"
select r.ROLE "Password protected Role",
p.grantee "Assigned by default to user"
from dba_roles r, dba_role_privs p
where r.PASSWORD_REQUIRED = 'YES' and p.GRANTED_ROLE = r.role
and p.default_role = 'YES'
and p.grantee <> 'SYS' and r.role not in
(select role from dba_application_roles);

One can easily turn these password enabled roles into standard roles by running the script resulting from:

select distinct 'alter role '||role||' not identified;' "-- Statements"
from dba_roles r, dba_role_privs p
where r.PASSWORD_REQUIRED = 'YES' and p.GRANTED_ROLE = r.role
and p.default_role = 'YES'
and p.grantee <> 'SYS' and r.role not in
(select role from dba_application_roles);