Oracle DBA Lesson 70 Part 1 — Grant a System Privilege and Control Delegation

A system privilege permits a database operation or a class of operations. For a user who needs to create ordinary views in their own schema, CREATE VIEW is the relevant creation capability. Select the privilege for the actual task, then verify its recipient, container scope, and delegation rights.

Oracle DBA Lesson 70 Part 1 — Grant a System Privilege and Control Delegation

A view and its underlying data

A conventional view stores a query definition over existing tables or views. It presents the underlying data through that definition.

• CREATE VIEW allows creation in the recipient's own schema.
• For a view over another schema's table, the view owner needs appropriate underlying privileges granted directly, rather than only through a role. For an ordinary query view, this can be an appropriate direct READ or SELECT object grant.
• CREATE ANY VIEW allows creation in other schemas, subject to Oracle's documented requirements. It is a broader system privilege and is not needed to create a view in the recipient's own schema.

Granting access onward to a view has further underlying-object requirements. Permission to create a view does not automatically supply every underlying-object privilege or every onward-sharing permission.

See CREATE VIEW.

Grant CREATE VIEW locally

In the intended pluggable database:

GRANT CREATE VIEW TO app_user;

This grants CREATE VIEW locally. It omits WITH ADMIN OPTION. The grantee may already hold separate granting authority. This one grant does not inventory every authority the account holds.

See managing system privileges.

Read the grant record

SELECT grantee, privilege, admin_option, common
FROM dba_sys_privs
WHERE grantee = 'APP_USER'
  AND privilege = 'CREATE VIEW';

For that new direct local grant, the row is:

GRANTEE	PRIVILEGE	ADMIN_OPTION	COMMON
APP_USER	CREATE VIEW	NO	NO

• GRANTEE: the user or role receiving this system grant.
• PRIVILEGE: the capability granted.
• ADMIN_OPTION = NO: this grant supplies use without adding the privilege's delegation authority.
• COMMON = NO: this grant was made locally. YES describes a common grant made with CONTAINER=ALL.

DBA_SYS_PRIVS describes grants to users and roles. A query filtered to one user shows that user's direct matching grants. It does not expand every role. The view also includes INHERITED, which records container inheritance. The row above is a local grant.

DBA_SYS_PRIVS has no GRANTOR column. Audit records and maintained change records establish who issued a system grant. These four columns do not identify the historical issuer.

See DBA_SYS_PRIVS and commonly and locally granted privileges.

Check the session privilege

In the recipient's session:

SELECT privilege
FROM session_privs
WHERE privilege = 'CREATE VIEW';

SESSION_PRIVS reports system privileges currently available to the user. When the grant is available, the row is CREATE VIEW. A direct system grant does not require a new connection merely to take effect.

Creating an ordinary query view in that schema, using Oracle's DUAL, looks like this:

CREATE VIEW privilege_probe AS
SELECT 1 AS marker FROM dual;

SELECT marker FROM privilege_probe;

An ordinary table in another schema still needs the appropriate direct underlying-object grant.

See SESSION_PRIVS.

WITH ADMIN OPTION and onward grants

A separate grant chain shows delegation inside the same container:

-- Administrator
GRANT CREATE VIEW TO delegator WITH ADMIN OPTION;

-- DELEGATOR, within the same intended container
GRANT CREATE VIEW TO second_user;

WITH ADMIN OPTION adds authority to grant and revoke that system privilege for other users or roles. DELEGATOR receives that authority. SECOND_USER receives CREATE VIEW without admin option.

Known grant	Recipient's admin option
Administrator to DELEGATOR: CREATE VIEW	YES
DELEGATOR to SECOND_USER: CREATE VIEW	NO

The administrator then removes the delegator's grant:

REVOKE CREATE VIEW FROM delegator;

The delegator loses that grant. The onward system grant to SECOND_USER remains. System-privilege revocation does not automatically withdraw grants that the former holder made to other recipients. Review downstream users and roles, and explicitly revoke each grant that should end.

Other effects can occur. Losing a system privilege used by a stored program can affect that program. Object-privilege chains that use WITH GRANT OPTION follow their own cascading rules. Those rules do not apply to this system grant.

An existing system ADMIN OPTION cannot be removed on its own. Revoke the privilege and grant it again without admin option, after reviewing the effect on use and on existing onward grants. Repeating a plain GRANT does not change an existing YES to NO.

See GRANT, REVOKE, and revokes and cascading effects.

Distinguish action, ownership scope, and administration

Authority	Meaning in this context
CREATE VIEW	Create a view in the user's own schema, with underlying-access prerequisites
CREATE ANY VIEW	Create views across schemas, subject to the documented requirements
ANY system privileges generally	Broader object-category scope with documented dictionary and security restrictions. Read the particular privilege.
DBA role	A broad collection of privileges evaluated through normal privilege processing
SYSDBA	Special administrative connection authority, including instance startup and shutdown

The DBA role and a connection AS SYSDBA use different mechanisms. Creating a view in the user's own schema uses CREATE VIEW. That request does not require the DBA role, SYSDBA, CREATE ANY VIEW, or a broad granting privilege.

See managing administrative privileges.

What a direct revoke leaves in place

REVOKE CREATE VIEW FROM app_user;

Repeat the filtered DBA_SYS_PRIVS query. In the recipient's session, repeat the SESSION_PRIVS query. If CREATE VIEW is still available, another path, such as an enabled role, can still supply it. Removing one direct grant does not remove a privilege supplied through another applicable path.

For roles the session can use, SESSION_ROLES identifies enabled roles, and ROLE_SYS_PRIVS describes system privileges granted to accessible roles. An empty direct-grant query is not proof that every access path is gone. Nested roles can still carry the privilege.

GRANT, REVOKE, and object DDL commit implicitly. ROLLBACK does not undo these privilege changes. Revoking CREATE VIEW does not drop views that already exist. Dropping a view is a separate operation.

See ROLE_SYS_PRIVS.

Recap

A system privilege permits an operation or a class of operations. CREATE VIEW lets the recipient create a conventional view in their own schema. A view over another schema's table still needs a direct object privilege on the underlying object, such as READ or SELECT. CREATE ANY VIEW is broader and is not required for own-schema creation. Creating a view does not by itself grant every underlying-object privilege or the right to grant access to the view. GRANT CREATE VIEW TO app_user is a local grant and omits WITH ADMIN OPTION. DBA_SYS_PRIVS shows GRANTEE, PRIVILEGE, ADMIN_OPTION, and COMMON. For this grant the values are APP_USER, CREATE VIEW, NO, and NO. ADMIN_OPTION = NO supplies use without delegation authority. COMMON = NO means the grant was made locally. A common grant uses CONTAINER=ALL. The view has no GRANTOR column, and a filter on one user does not expand role privileges. SESSION_PRIVS lists CREATE VIEW when the session can use it. A direct system grant does not require a new connection to take effect.

WITH ADMIN OPTION lets the recipient grant and revoke that system privilege. Revoking CREATE VIEW from DELEGATOR leaves the onward grant to SECOND_USER in place. Each downstream system grant that should end needs its own REVOKE. Repeating a plain GRANT does not change ADMIN_OPTION from YES to NO. The DBA role and SYSDBA are different mechanisms from CREATE VIEW. After REVOKE CREATE VIEW FROM app_user, a remaining row in SESSION_PRIVS means another path, such as an enabled role, still supplies the privilege. SESSION_ROLES and ROLE_SYS_PRIVS describe enabled roles and the system privileges granted to accessible roles. GRANT, REVOKE, and object DDL commit implicitly. ROLLBACK does not undo them, and revoking CREATE VIEW does not drop existing views.

Quiz

1. Which system privilege fits creating a conventional view in the user's own schema?

SYSDBA CREATE VIEW DROP ANY VIEW GRANT ANY PRIVILEGE

2. What does ADMIN_OPTION = NO establish for the inspected grant?

The privilege cannot be used The recipient has no privileges of any kind This grant adds use without delegation authority The grant applies commonly to all containers

3. If a system privilege is revoked from a delegator, are its onward grants automatically removed?

Yes, including all roles recursively Yes, after the next login Only when the grantee owns a table No. Downstream system grants need separate review and explicit revocation where appropriate

4. What does COMMON = NO mean in the grant record?

The grant was made locally The database is closed The recipient cannot connect The privilege was revoked

5. A direct CREATE VIEW grant is removed, but SESSION_PRIVS still lists it. What should be investigated?

Whether all data files must be recreated Whether the user still has another applicable privilege path, such as an enabled role Whether the view must store a copy of every row Whether SYSDBA must be granted

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