Conclusion

This module opened with a distinction that sounds simple the first time you hear it:

1. system privileges control what you can do across the database,
2. object privileges control what you can do to one specific thing someone else owns.

That distinction is true, and it is also nowhere near the whole story. What actually filled the eight lessons since then is everything that distinction doesn't tell you on its own: how privileges get delegated safely, how they behave when revoked, how a third category squeezed in between the original two, and, running underneath nearly everything covered, the genuinely important difference between a privilege being granted, a privilege being enabled, and a privilege actually being usable at the exact moment you need it.

If this module has one idea worth carrying forward above every individual command, it is this three-way distinction, and it is worth stating plainly because the whole module keeps circling back to it from different angles without ever naming it directly until now. A privilege can be granted,

1. sitting in DBA_SYS_PRIVS or
2. DBA_ROLE_PRIVS exactly as you would expect,

and still not be usable, for one of two genuinely different reasons: either the role carrying it isn't currently enabled in the session, or the code trying to use it runs in a context where role-based privileges simply don't apply at all. These are not the same failure, and confusing them wastes time. The three-way distinction is Granted, Enabled, and Usable.

Lesson 5 introduced the first half of this gap almost in passing, as a caveat attached to the security-administrator role pattern: if a system privilege reaches a user only through a role, granted via GRANT ANY PRIVILEGE to that role rather than directly, the user has it only while the role is actually enabled. Grant the same privilege directly WITH ADMIN OPTION instead, and it stays available no matter which roles happen to be switched on. Lesson 6 turned this into something you could actually diagnose rather than just remember, building an entire troubleshooting sequence around the DEFAULT_ROLE column: DEFAULT_ROLE set to YES means a role activates automatically at connection; set to NO, it sits inactive until something in the session explicitly enables it. A privilege granted only through a non-default role is completely real and will show up in every dictionary view you check, and it will do the user no good at all until that specific role gets turned on.

Lesson 7 gave this distinction its own dedicated view and, with it, the clearest demonstration in the entire module of exactly how much granted and enabled can diverge. SESSION_PRIVS doesn't answer "what have I been granted"; it answers "what can I actually exercise right now," and the difference between those two questions is not subtle. Working through Oracle's own official example, swilliams holding the security_admin role with a substantial set of administrative privileges bundled into it, the lesson showed precisely what happens when that role is disabled: SESSION_ROLES returns no rows at all, and SESSION_PRIVS collapses down to a single row, CREATE SESSION, the one privilege swilliams holds directly rather than through the role. Nothing was revoked. DBA_ROLE_PRIVS would still show the role granted to her without any change at all. The privileges tied to that role simply aren't active in this particular session at this particular moment, and SESSION_PRIVS is built specifically to reflect that reality rather than the dictionary's more permanent record of who holds what.

The second half of the gap, the one Lesson 7 spent real depth on, is a different kind of story entirely, and arguably the single most consequential technical finding in this module: roles behave completely differently depending on whether the PL/SQL code trying to use them runs with definer's rights or invoker's rights. Named blocks with definer's rights, the default for stored procedures, functions, and triggers, disable every role outright. Not partially, not conditionally: every role is off, privilege checking never consults them, and you cannot even issue SET ROLE from inside such a block. A definer's-rights procedure can only use privileges granted directly to its owner, full stop, regardless of what roles that owner has enabled in their interactive session, and regardless of what SESSION_PRIVS shows for that same session at that same moment. Named blocks with invoker's rights, and anonymous PL/SQL blocks, work the way intuition suggests they should: whatever roles are currently enabled in the calling session apply inside the block too.

This isn't an inconsistency or a rough edge Oracle never smoothed over; it's a deliberate security design decision, and understanding why makes the restriction feel inevitable rather than arbitrary. A definer's-rights procedure exists specifically to let a user with limited privileges perform a controlled, privileged operation safely, because the procedure runs with its owner's privileges rather than the caller's. If role-based privileges were honored inside such a procedure, the procedure's actual privilege set would depend on which roles happened to be enabled in whoever called it at the moment they called it, an unstable, hard-to-audit foundation for exactly the kind of controlled access these procedures are meant to provide. Disabling roles entirely removes that instability: a definer's-rights procedure's privilege set is fixed, predictable, and determined solely by direct grants to its owner. This is precisely why the practical advice from that lesson matters as much as the theory behind it: the moment a procedure fails with a privilege error despite the connected account clearly holding that privilege interactively, check whether the code runs with definer's rights before assuming the grant itself is somehow wrong.

Lesson 8 closed the loop on this same three-way distinction from the opposite direction, revocation rather than grant. Revoking a privilege is real and immediate at the data dictionary level the instant you run the REVOKE statement, but an already-connected session doesn't necessarily notice right away: it keeps whatever roles were already enabled until it re-enables roles or reconnects entirely. The dictionary changes instantly; the session's actually-usable privilege set can lag behind that change until something in the session refreshes it. Verifying a revoke properly, as that lesson emphasized, means checking the dictionary and then having the affected session reconnect or re-enable roles and rerun whatever critical path you're concerned about, not just trusting that the dictionary query alone tells the whole story.

The second major idea running through this module is narrower than the first but no less important in practice: what happens downstream when you revoke something depends entirely on what kind of thing you revoked, and the two most common delegation clauses in this module behave in genuinely opposite ways.

Lesson 2 stated the core asymmetry precisely, early enough that it could serve as a foundation for everything after it: revoking a system privilege or role from someone who passed it along using WITH ADMIN OPTION does not cascade. Whoever they granted it to keeps it, untouched, and the original revoker would need to track down and revoke from each downstream recipient separately if the goal was to remove it everywhere. Revoking an object privilege that traveled along a WITH GRANT OPTION chain does the opposite: the revoke cascades automatically, stripping the privilege from everyone further down that specific chain in one operation, even people the person doing the revoking never directly granted anything to themselves.

Lesson 5's WITH ADMIN OPTION diagram made the non-cascading half of this concrete rather than abstract. The DBA grants CREATE USER and CREATE SESSION to Jeff with admin option; Jeff, holding that authority, makes two different delegation decisions at once, granting the same two privileges to Jenny with admin option, letting her extend the chain further if she chooses, and separately to Ashley without it, making her a terminal recipient who can use the privileges herself but never pass them on. Revoke CREATE SESSION from Jeff after that point, and Jenny and Ashley both keep exactly what they already have; the DBA would need to revoke from each of them individually to remove it everywhere. That diagram's characters resurfaced again in Lesson 6's worked DBA_SYS_PRIVS query, this time checking an entirely different privilege, CREATE TABLESPACE, which happened to show the same three names in a similar admin-option pattern, a small but genuine reminder that the same account can accumulate different, independently tracked delegation authority across many different privileges over time.

Lesson 8 widened this same idea into a full reference covering considerably more ground than the admin-option-versus-grant-option contrast alone. A role revoked from a user immediately strips that user's access to everything the role carried, but any nested role that had itself been granted to the revoked role stays exactly where it was, since the nesting relationship and the user's membership in the outer role are two separate things entirely. REFERENCES specifically requires the CASCADE CONSTRAINTS clause to drop dependent foreign-key constraints when revoked; without it, the REVOKE statement itself fails outright if those constraints exist, refusing to leave a table in a state where dependent objects reference a privilege that no longer exists. EXECUTE on a user-defined type with table or type dependents behaves the same defensive way, requiring FORCE to push the revoke through, marking every dependent object invalid in the process rather than letting a database silently accumulate broken references.

The common-versus-local distinction, covered in depth in the third thread below, turns out to be a cascade rule in its own right, and Lesson 8 was explicit about it: a privilege granted with CONTAINER=ALL can only be revoked with CONTAINER=ALL; CONTAINER=CURRENT simply doesn't touch it, confirmed directly against Oracle's own current syntax reference rather than assumed. Scope mismatch here doesn't cascade incorrectly so much as it fails to do anything at all, which is its own kind of surprise the first time you expect a revoke to work and it silently doesn't.

The third pattern running through this module picks up directly from where the previous module on creating and managing users left off: since every Oracle AI Database 26ai database is a multitenant container database, almost nothing involving a grant, a privilege check, or a revoke can be answered honestly without first asking which container the answer actually applies to.

Lesson 4 introduced the mechanical side of this for grants: a CONTAINER clause lets a privilege apply across every PDB at once with CONTAINER=ALL, provided you're connected to the CDB root and genuinely intend that broad a reach, rather than the narrower, more common case of a local grant applying only to the PDB you're currently in. Lesson 6 gave this question its own pair of dedicated columns on DBA_SYS_PRIVS itself, not just on some separate multitenant-specific view: COMMON answers whether a privilege was granted with CONTAINER=ALL or granted locally, and INHERITED answers something related but distinct, whether the specific row you're looking at, from inside one particular container, reflects a grant made directly there or one flowing down from the root. The lesson's own worked example made the distinction concrete: a common user's CREATE SESSION grant showing COMMON as YES and INHERITED as YES simultaneously, confirming both that the grant originated at the root and that the row being examined reflects that inherited origin rather than a separate, local grant made again in this specific container.

Lesson 8 carried this same question into revocation, where it stops being a matter of interpretation and becomes a hard operational rule: match the scope of the revoke to the scope of the original grant, exactly, or the revoke simply won't reach what you're trying to remove. A single common user can genuinely hold a mix of both kinds of grant at once, a common role received from the root sitting alongside local object privileges granted separately within one specific PDB, which is exactly why checking COMMON and INHERITED before attempting any revoke, rather than after discovering it silently failed, is the safer habit to build.

It's worth being explicit that this thread isn't new to this module so much as it's this module's version of a question the previous module on creating and managing users already raised repeatedly: which container does this actually apply to. There, the question showed up around default tablespaces, temporary tablespaces, and the accounts themselves, whether a user belonged to one specific PDB or to the CDB root as a common user. Here, the identical underlying question resurfaces around privileges instead of accounts: not who the user is, but where a specific grant lives, and whether checking or changing it in one PDB tells you anything at all about its status anywhere else in the container. The specific commands differ, GRANT and REVOKE here instead of CREATE USER and ALTER USER there, but the discipline required is the same one: never assume a single container's view of something is the whole picture in a database that was, by design, never meant to have just one.

Beneath these three larger patterns sits a smaller, more mechanical one worth naming explicitly, since it appears in some form in nearly every single lesson of this module: schema privileges, the newer third category introduced in Lesson 2 as something sitting between system and object privileges, kept resurfacing everywhere the module went next. Lesson 3 didn't cover them directly but set up the category structure they'd eventually occupy. Lesson 4 showed how to grant one. Lesson 5 covered delegating the authority to grant them on someone else's schema, correcting along the way a name that's easy to abbreviate wrong: GRANT ANY SCHEMA PRIVILEGE, the full three-word privilege, not a shortened two-word version that doesn't actually exist. Lesson 6 built out the entire USER_/DBA_/SESSION_SCHEMA_PRIVS family alongside the equivalent system-privilege views, and confirmed DBA_SYS_PRIVS_ALL, the view combining system and schema privileges together, as genuinely new to this specific release rather than a rebranding of something older. Lesson 7 extended SESSION_PRIVS itself into SESSION_PRIVS_ALL for exactly the same reason. Lesson 8 covered revoking a schema privilege with its own distinct authorization path, mirroring but not identical to the system-privilege revocation rules covered everywhere else in the lesson. A single new category, introduced once in Lesson 2, ended up touching every other lesson in the module in some form, which says something honest about how thoroughly current Oracle releases have woven schema-level privileges into the rest of the privilege model rather than bolting them on as an afterthought.

You can now explain precisely why system, object, and schema privileges are different things granted through different syntax and delegated through different clauses, not just that they are different. You know that WITH ADMIN OPTION and WITH GRANT OPTION behave as near-opposites under revocation, and you know the fuller cascade table beyond just those two cases: roles, REFERENCES, type dependencies, and common grants each carry their own specific rule rather than one universal behavior you could safely assume applies everywhere. You know that a privilege appearing in every dictionary view you'd think to check can still be completely unusable, either because the role carrying it isn't enabled in this session, or because the code trying to use it runs with definer's rights and was never going to consult that role in the first place. And you know that in a mandatory-CDB world, nearly every one of these questions, granting, checking, and revoking alike, needs a container-scope answer before it can be trusted at all.

That is a genuinely complete picture of how Oracle's privilege model actually works in practice, not just the two-category simplification this module opened with eight lessons ago. The commands themselves, GRANT, REVOKE, CREATE ROLE, SET ROLE, are simple enough to type from memory within a day of learning them. What separates someone who types them correctly from someone who occasionally gets surprised by the results is exactly the material this module spent its time on: the gap between granted and usable, the asymmetry underneath two delegation clauses that look almost identical, and the container-scope question that now sits quietly underneath nearly everything an Oracle DBA does.