Oracle DBA Lesson 74 Part 1 — Check a Role Before Granting It

A supplied role is a bundle of privileges. Choose access by comparing that bundle with the operations an account actually needs. Then decide how those privileges should become active in a session.

Oracle DBA Lesson 74 Part 1 — Check a Role Before Granting It

Start with the duty

A migration account that creates tables and views in its own schema needs CREATE TABLE and CREATE VIEW, login access, and a finite quota on the intended permanent tablespace. A quota permits allocation up to a limit. It does not reserve physical capacity. A view that references another owner's objects also needs the applicable underlying privileges. Review those separately.

Oracle's documented 19c RESOURCE role includes CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, and CREATE TYPE. That list goes beyond this job and omits CREATE VIEW. RESOURCE no longer provides UNLIMITED TABLESPACE. CONNECT is a compatibility role documented with CREATE SESSION. Inspect the installed grants. The role name is not a permanent contract. DBA includes many powerful system privileges, including ANY privileges. A custom role can express the required duty more precisely.

See Oracle 19c predefined roles.

Duty	Capability to investigate	Separate conditions
Connect	CREATE SESSION	Authentication and correct service/container
Create tables in own schema	CREATE TABLE	Finite quota, available capacity, intended tablespace
Create views in own schema	CREATE VIEW	Underlying object privileges and stored-code rules
Read two application tables	Relevant SELECT object grants	Exact owner/object names and permitted data
Pass a role to others	Role administration authority	Usually unnecessary for an application runtime or deployment duty

A role's breadth and its activation rules are separate decisions. Conditional activation still requires narrowly scoped contents.

Inspect direct system privileges

Read the system privileges granted directly to CONNECT, RESOURCE, and DBA:

SELECT grantee, privilege, admin_option
FROM dba_sys_privs
WHERE grantee IN ('CONNECT','RESOURCE','DBA')
ORDER BY grantee, privilege;

GRANTEE identifies the user or role receiving a system privilege. PRIVILEGE names the operation. ADMIN_OPTION is YES or NO and reports whether that privilege was granted with administrative authority. Powerful ANY privileges differ in reach and restrictions. Compare each privilege with the objects it can affect.

See DBA_SYS_PRIVS and GRANT.

Follow nested roles and delegation

Follow role grants, including roles granted to other roles:

SELECT grantee, granted_role, admin_option, default_role
FROM dba_role_privs
WHERE grantee IN ('APP_MIGRATE','DBA')
ORDER BY grantee, granted_role;

Each row is one grant: GRANTEE receives GRANTED_ROLE. When the recipient is another role, follow that granted role and read its privileges too. Oracle documents SCHEDULER_ADMIN as included in DBA. A broad role can bring another administrative bundle into the access path.

On a role grant, ADMIN_OPTION = YES permits onward administration, including granting that role to others. That authority is separate from enabling and using the role. DEFAULT_ROLE describes default activation for a user. Read it together with the role's authentication type and the roles enabled in the session. A query of direct system privileges does not expand nested roles.

See DBA_ROLE_PRIVS.

Include object and column grants

For each candidate role, and for every role included in it, read object and column privileges:

SELECT grantee, owner, table_name, privilege, grantable
FROM dba_tab_privs
WHERE grantee = 'APP_DEPLOY'
ORDER BY owner, table_name, privilege;

SELECT grantee, owner, table_name, column_name,
       privilege, grantable
FROM dba_col_privs
WHERE grantee = 'APP_DEPLOY'
ORDER BY owner, table_name, column_name, privilege;

APP_DEPLOY is a custom role in these queries, not a supplied role. Use the same statements for each role being compared. DBA_TAB_PRIVS lists grants on objects, which can include packages and other object types as well as tables. Column-specific grants are in DBA_COL_PRIVS. GRANTABLE is the grant option on an object privilege. It is a different field from ADMIN_OPTION on a role or system privilege.

SELECT_CATALOG_ROLE provides access to dictionary objects. Data Pump full-database roles can expose data across schemas and can run powerful export and import operations. Inspect their full contents before granting them for an administrative duty.

See Dictionary grant views, DBA_TAB_PRIVS, and DBA_COL_PRIVS.

Supplied roles are also marked in DBA_ROLES:

SELECT role, oracle_maintained, authentication_type
FROM dba_roles
WHERE role IN ('CONNECT','RESOURCE','DBA');

When ORACLE_MAINTAINED is Y, changes go through the script that created the role. Keep supplied roles intact. Build a focused application role instead of removing privileges from an Oracle-maintained role. The inventory can differ by release, installed products, and local changes.

See DBA_ROLES.

Default-off controls the initial session state

When RESOURCE is granted directly to a user, its authentication type is NONE, the role is nondefault, and no external role-management restriction applies, a fresh login leaves the role disabled. The user who holds the grant can enable it:

SET ROLE resource;
SELECT role FROM session_roles;

SET ROLE resource enables the named role and the roles included in it, and it disables the user's other roles for that session. SESSION_ROLES lists the roles enabled now, so read every returned row. DEFAULT_ROLE = NO controls activation at login. It leaves this SET ROLE path available. Issued in the user's own session, the statement does not pass through a definer-rights call stack.

See SET ROLE, SESSION_ROLES, and ALTER USER default-role settings.

Select the activation mechanism

Read the authentication type before choosing how the role becomes active:

SELECT role, authentication_type
FROM dba_roles
WHERE role IN ('RESOURCE','APP_DEPLOY_GUARDED');

APP_DEPLOY_GUARDED is a custom role name in this query. Useful AUTHENTICATION_TYPE values include:

AUTHENTICATION_TYPE	Meaning
NONE	No role password is required. Ordinary grant and activation rules apply.
PASSWORD	The password must be supplied when enabling the role.
APPLICATION	Secure application role, enabled through the package named by the role.
EXTERNAL / GLOBAL	External or directory-based control. Review the specific rules.

A password role and a secure application role use different mechanisms. Match the mechanism to the policy.

See DBA_ROLES and CREATE ROLE.

A secure role needs a meaningful policy

Name the enabling package in the role:

CREATE ROLE app_deploy_guarded
  IDENTIFIED USING app_security.deploy_gate;

CREATE ROLE requires its system privilege. The package can be created after the role. Enabling the role still requires that package, the grants the package uses, and a policy that checks the caller.

Oracle verifies that the package is on the call stack when the secure application role is enabled. The package must use invoker rights (AUTHID CURRENT_USER), validate the user and the relevant trusted context, and enable the role only after those checks succeed. It can use DBMS_SESSION.SET_ROLE or appropriate dynamic SQL. A secure application role cannot be a default role and cannot be enabled with SET ROLE ALL. The application calls the package before using the role's privileges. A logon trigger is unsuitable for this enablement. Review inheritance requirements, the complete call chain, and access to the package.

Request	Policy decision	Expected activation result
Matching identity and valid protected context	Allow	Package enables the role
Wrong identity or invalid context	Deny	Package refuses activation. A role that started disabled stays disabled.
Package enables every caller and does not validate	Always allow	Every caller who can use this path can enable the role

Use an authenticated identity and a context the caller cannot change. A client-supplied label by itself is not sufficient. Protect the package and the policy data. Cover a denied request, a disabled role, a role that is already enabled, session reuse, and a revocation path. The checks run when the role is enabled. They are not repeated for each later SQL statement. Also account for a later change in eligibility and for a session taken from a pool.

See CREATE ROLE, Secure application roles, and Package design requirements.

Quiz

1. A deployment tool needs tables and views in its own schema. What best supports a role choice?

The tool's requested role name alone A comparison of required operations with installed grants and related conditions Giving the tool every role used by administrators

2. Why inspect DBA_ROLE_PRIVS after DBA_SYS_PRIVS?

It reveals role-to-user and role-to-role grants that can add nested capabilities It replaces all object-grant checks It proves that a role is enabled in the current session

3. What does ADMIN_OPTION = YES on a role grant add?

A guarantee that the role fits an application's duty Automatic activation in every session Authority to administer that role onward, including granting it to others

4. Is making an ordinary granted role with authentication NONE nondefault enough to prevent the user from enabling it?

Yes, nondefault roles cannot be enabled later No, the granted user can ordinarily enable it with SET ROLE Yes, after the user reconnects

5. Which secure-role design enforces a useful condition?

A package that enables the role for every caller A declaration without implemented policy checks A protected package that validates identity and context, denies failures, and is tested with allowed and denied cases

https://www.apps-dba.com/ https://www.youtube.com/@EnhanceSkillsDaily