• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

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

by admin

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);

Filed Under: oracle

Some more articles you might also be interested in …

  1. How to pass a value from shell script to GoldenGate replicat for mapping to target table column
  2. New Oracle Net features in version 12c
  3. Simple Steps to use LogMiner for finding high redo log generation
  4. How to change max_string_size value from STANDARD to EXTENDED
  5. How to Monitor Process Memory Usage on Oracle Pluggable Databases
  6. Oracle Database: How To Use PROFILES To Limit User Resources
  7. Background Processes Specific to Oracle RAC
  8. Oracle Database 12c New Feature – Move a Datafile Online
  9. How to convert NON-OMF to OMF files ( Oracle Managed File conversion – NON-ASM )
  10. How To Disable the Oracle WebLogic Server Default Welcome Page

You May Also Like

Primary Sidebar

Recent Posts

  • JavaFX ComboBox: Set a value to the combo box
  • Nginx load balancing
  • nginx 504 gateway time-out
  • Images preview with ngx_http_image_filter_module

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright