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

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • 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. What are Oracle Data Guard Protection Modes (redo transport rules) and how to configure them
  2. How to Change default Oracle database listener port (1521)
  3. Oracle RMAN interview questions
  4. How to recreate the spfile for RAC instances where the spfile is stored in ASM
  5. Upgrading to Oracle 12c using RMAN DUPLICATE with “NOOPEN” clause and “BACKUP LOCATION”
  6. SLES 12: Database Startup Error with ORA-27300 ORA-27301 ORA-27303 While Starting using Srvctl
  7. How to Convert STANDARD ASM to FLEX ASM in 12C
  8. Where to find ASMLib / oracleasm RPMs for CentOS/RHEL, SUSE, OEL
  9. How To Change A Dictionary Managed Tablespace To A Locally Managed Tablespace
  10. IPv4 Subnet Basics and Oracle Clusterware

You May Also Like

Primary Sidebar

Recent Posts

  • How to Disable IPv6 on Ubuntu 18.04 Bionic Beaver Linux
  • How to Capture More Logs in /var/log/dmesg for CentOS/RHEL
  • Unable to Start RDMA Services on CentOS/RHEL 7
  • How to rename a KVM VM with virsh
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary