Change to Password Protected Default Role Not Well Documented

Recently I upgraded a 10.2.0.4 database to 11.2.0.1 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 11.1.0.7 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.

Advertisements

About April C Sims

Oracle DBA for over a decade...enough said.
This entry was posted in Uncategorized. Bookmark the permalink.

2 Responses to Change to Password Protected Default Role Not Well Documented

  1. Jenosa says:

    A mi me pasa exactamente lo mismo, saben si hay que activar algo para que no se pierda el password del rol?

  2. Just hit this problem during 9.2 to 10.2.0.5 this weekend. Thanks for your post April it was a great help.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s