Recently I upgraded a 10.2.0.4 database to 22.214.171.124 and the default role mechanism wasn’t working.
Queries were failing for endusers that had worked before….
Come to find out Oracle changed the way Default Roles work, the following is a quote from My Oracle Support Doc- What Roles Can Be Set as Default for a User? [ID 745407.1]
“The DEFAULT clause in the:
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:
Roles that are password authenticated and roles that are implemented as secure application roles.
The restriction has been introduced in the latest Oracle releases, 10.2.0.4 and 126.96.36.199 and will apply to all future releases. It will be introduced in the future documentation.”
To fix the problem with queries not running…it is a simple command.
alter role role_name not identified;
Now the big question is…how does this impact how I secure the database? Appreciate any feedback.