• 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 Start/Stop/Relocate SCAN listener in Oracle 11gR2 RAC
  2. RMAN Restore/Duplicate Performs Implicit Crosschecking and Cataloging
  3. How to Modify spfile in Oracle Database
  4. Script To Find Redolog Switch History And Find Archivelog Size For Each Instances In Oracle RAC
  5. How to get DDL for User including Privileges and Roles Using dbms_metadata.get_ddl
  6. Oracle 12C Enhancements for Cascaded Standby Databases
  7. How to Disable os-prober in CentOS/RHEL 7
  8. What is SQL Server Operating System ( SQLOS)
  9. How to Shrink the datafile of Undo Tablespace in Oracle Database
  10. How to check which options or features are used in the Oracle database

You May Also Like

Primary Sidebar

Recent Posts

  • protonvpn-cli Command Examples in Linux
  • protonvpn-cli connect Command Examples
  • procs Command Examples in Linux
  • prlimit: command not found

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright