Grant Default (non-default roles)

Roles are a powerful mechanism for implementing security schemes. A user can have one or more directly assigned roles, plus others assigned as default roles. A default role is assigned to a user whenever he or she logs on to an Oracle database. A default role must have already been granted to a user before it can be a default role.

In Oracle 23c, you can grant roles in two ways:
✅ 1. Granting Default Roles
A default role is enabled automatically for the user when they log in, unless explicitly disabled.
🔹 Syntax:

GRANT role_name TO user_name;

This assigns the role and makes it default, unless the user has specific roles set with `ALTER USER ... DEFAULT ROLE`.
🔹 Example:

GRANT hr_read_only TO john_doe;

After this, `hr_read_only` will be a default role for `john_doe`, unless overridden.
✅ 2. Granting Non-Default Roles
To grant a role without making it default (i.e., it must be enabled explicitly via `SET ROLE`), you must exclude it from the list of default roles.
🔹 Step-by-step:

1. Grant the Role:
   

   GRANT hr_admin TO john_doe;

2. Restrict it from being default:
   

   ALTER USER john_doe DEFAULT ROLE ALL EXCEPT hr_admin;

This makes `hr_admin` a non-default role. The user must now run:

SET ROLE hr_admin;

to enable it after logging in.
🛠️ View Roles Assigned and Defaults
View all roles granted to user:

SELECT * FROM dba_role_privs 
WHERE grantee = 'JOHN_DOE';

• View default roles:
  

  SELECT * FROM dba_users 
  WHERE username = 'JOHN_DOE';

• Effective roles during session:

  SELECT * FROM session_roles;

🚨 Notes (Oracle 23c Specific):

• You can grant roles to application users using Real Application Security (RAS).
• Roles can be conditionally enabled using Secure Application Roles with DBMS_SESSION.SET_ROLE.

Here is a complete SQL*Plus script for Oracle 23c that:

1. Creates two roles (`default_role`, `non_default_role`)
2. Creates a user (`sample_user`)
3. Grants the roles (one as default, one as non-default)
4. Verifies the assignments and effective role status

✅ Oracle 23c Script for Default and Non-Default Role Management

-- 1. Create roles
CREATE ROLE default_role;
CREATE ROLE non_default_role;

-- 2. Create user and grant basic privileges
CREATE USER sample_user IDENTIFIED BY Welcome#123;
GRANT CREATE SESSION TO sample_user;

-- 3. Grant roles to user
GRANT default_role TO sample_user;
GRANT non_default_role TO sample_user;

-- 4. Configure default role (only 'default_role' is default)
ALTER USER sample_user DEFAULT ROLE ALL EXCEPT non_default_role;

-- 5. Verify role grants (run as DBA or user with SELECT_CATALOG_ROLE)
PROMPT === Role Grants to USER ===
SELECT * FROM dba_role_privs WHERE grantee = 'SAMPLE_USER';

PROMPT === Default Roles for USER ===
SELECT username, default_role FROM dba_users WHERE username = 'SAMPLE_USER';

-- 6. Connect as sample_user and verify session roles
-- NOTE: Substitute this with actual reconnect logic if used in a script runner
CONNECT sample_user/Welcome#123

PROMPT === Enabled Roles on Login ===
SELECT * FROM session_roles;

-- 7. Enable non-default role manually
SET ROLE non_default_role;

PROMPT === Roles After SET ROLE ===
SELECT * FROM session_roles;

-- (Optional) Revert roles to ALL
-- ALTER USER sample_user DEFAULT ROLE ALL;

Step	Purpose
`CREATE ROLE`	Defines two roles for demonstration
`CREATE USER`	Creates a test user
`GRANT role TO user`	Assigns both roles
`ALTER USER ... DEFAULT ROLE ALL EXCEPT`	Makes `non_default_role` non-default
`session_roles`	Shows what roles are active
`SET ROLE`	Demonstrates enabling the non-default role manually

Your Oracle database has a number of predefined roles and these roles are simple groupings, although they are groupings of system privileges rather than object privileges. There are nine system roles available with Oracle8. The two most commonly used roles are:

CONNECT:	Gives a user the ability to create a session and create basic database objects
DBA:	Gives the user all system privileges, each with WITH ADMIN OPTION, which is explained in a later lesson

The CONNECT role is usually granted to all new database users, while the DBA role must be granted very selectively because of its power.

You can assign roles individually as default roles. If a user has been granted several roles, you can use the keyword ALL to make all the available roles into default roles. To make all the roles granted to user BID1 above into default roles, use the following syntax:

ALTER USER BID1 DEFAULT ROLE ALL;

ALTER USER BID1 DEFAULT ROLE 
ALL EXCEPT ADMINISTRATOR;

If you want to assign most of the roles available to a user as default roles, you can use the ALL keyword along with the EXCEPT keyword to exclude specific roles. To make all the roles granted to user BID1 into default roles, except for the ADMINISTRATOR role, use the following command. Click the View Code button.

• Changing Roles at runtime
  You can also assign roles for a user at runtime. To do this, use the SQL command SET ROLE, followed by the role name or names. The SET ROLE command can use the ALL and ALL EXCEPT keywords. Using the SET ROLE command automatically disables all roles that are not mentioned in the command. All roles for the user would be disabled except the role of BIDDER if user BID1 issued the following SQL command:
  

  SET ROLE BIDDER;
  

  
  The next lesson shows how to protect a role with a password.