Lesson 15

Fourteen lessons ago, this module opened with a simple promise: by the end, you would be able to create a user, decide what that user actually needs before you create it, size its storage correctly, authenticate it sensibly, grant it exactly the privileges its job requires, modify it as circumstances change, lock it when access needs to pause, drop it cleanly when it is no longer needed, and query the data dictionary to prove all of that at any point in between. You have now done all of that. This conclusion is not a lesson-by-lesson recap. It is the pattern running underneath all fourteen lessons, visible now in a way it could not have been while you were learning any single piece of it on its own.

If one idea deserves to outlast every individual command in this module, it is this: since every Oracle AI Database 26ai database is a multitenant container database, almost nothing you do to a user account happens in a vacuum anymore. It happens somewhere specific, in a particular PDB or in the CDB root, and that location is a decision, not a default you can safely ignore.

Lesson 1 raised this the moment CREATE USER itself came up: a common user, created in the root, is visible across every PDB in the container, while a local user exists only where you created it, and CREATE USER's own CONTAINER clause lets you be explicit about which one you mean. Lesson 3 turned this from a syntax detail into a planning question, adding "which container should this account belong to" directly alongside the older questions about tablespace and profile, because in a mandatory-CDB world, skipping that question is no longer an option the way it once was. Lesson 4 and Lesson 5 showed the same idea from an entirely different angle: DATABASE_PROPERTIES, the view that reports a container's own default permanent and temporary tablespace settings, is scoped to whichever container you happen to be connected to when you query it. Set a default in one PDB, and every other PDB in the container remains completely unaffected, still running on whatever default it already had. CDB_PROPERTIES exists specifically to answer the question DATABASE_PROPERTIES cannot: what does this setting look like across the whole container at once, not just the one PDB you are sitting in right now.

Lesson 9 found the same idea hiding inside a role you have probably granted without a second thought. The CONNECT role, stripped down for a long time now to a fraction of what its name implies, retains exactly two privileges: CREATE SESSION and SET CONTAINER. That second privilege is not filler. SET CONTAINER is precisely what lets a common user move between containers within a CDB, and its presence in CONNECT reflects how routine that operation has become now that every database is multitenant, not an accident of legacy role design. Lesson 14 closed the loop with DBA_SCHEMA_PRIVS, whose COMMON and INHERITED columns answer the same container question one more time, this time for schema-level privilege grants specifically: was this privilege granted everywhere at once with CONTAINER=ALL, or locally in just this one PDB, and did it arrive here on its own merits or inherited from somewhere else in the container hierarchy. Four lessons, four completely different commands and views, one question running underneath all of them.

The second pattern threading through this module is less architectural and more of a hard deadline: starting with Oracle AI Database 26ai, traditional auditing is fully desupported. Not deprecated in the soft sense, where the old way still works but the new way is preferred. Desupported in the specific sense that you cannot create a new traditional audit setting or modify an existing one at all. If your database was upgraded from an earlier release with traditional audit settings already in place, those settings keep generating records exactly as before, but that is the only thing they can still do. Unified auditing is not simply this module's recommended approach to tracking account activity; as of this release, it is the only approach available for anything new.

This shows up constantly once you know to look for it. Lesson 1 raised it the moment CREATE USER's implicit audit trail entry came up. Lesson 2 built the first full CREATE AUDIT POLICY example around exactly this framing. Lesson 9, 10, 11, and 12 each returned to it for privilege grants, ALTER USER operations, password changes, and account locks respectively, not because the point needed repeating for its own sake, but because every one of those operations genuinely does need auditing, and the mechanism for doing so is now singular rather than one option among several. Lesson 12 went a step further and named the specific casualty directly: the bare AUDIT statement form, the one without a named policy, is literally categorized in Oracle's own current documentation under the heading "Traditional Auditing." An older script built around that exact syntax was not making a stylistic choice; it was relying on a mechanism this release has frozen in place.

One small, recurring detail rode alongside this larger point in nearly every lesson that queried the audit trail directly: the column holding the connecting username on UNIFIED_AUDIT_TRAIL is DBUSERNAME, with no underscore. It is a minor thing to get wrong, and an easy one, since DB_USERNAME reads perfectly naturally and even exists as a real column on an entirely different view. But a query built on the wrong assumption fails outright rather than quietly returning slightly wrong results, which makes it exactly the kind of small, precise fact worth carrying forward correctly.

Underneath both of those throughlines sits the actual arc this module set out to teach. Lesson 3 opened it in the right place, before any SQL runs at all: deciding who this account represents, which container it belongs in, how it authenticates, what tablespace and quota it needs, and how it will be audited, are all decisions worth making deliberately rather than discovering by accident after the account already exists in some half-considered state. Lessons 4 and 5 filled in two of those decisions in real depth: a default permanent tablespace that keeps ordinary objects out of SYSTEM, and a default temporary tablespace that keeps sort and hash-join work off the same disks the data dictionary depends on. Lesson 6 added the third piece, tablespace quotas, including the sharp distinction between a safe, per-tablespace QUOTA UNLIMITED and the considerably more dangerous UNLIMITED TABLESPACE system privilege, which overrides every quota you have set anywhere in the database at once and cannot be revoked selectively once granted.

Lessons 1, 2, and 7 then built CREATE USER itself back up from these pieces into something considerably richer than a username and a password. Current authentication options now stretch well past a local password into direct integration with cloud identity providers, Microsoft Entra ID and Oracle Cloud Infrastructure IAM among them, using OAuth 2.0 token validation rather than a database-specific credential at all. The IF NOT EXISTS clause turns a script that used to fail outright on a second run into one that simply proceeds. And schema-level privilege grants, a genuinely new capability, let a DBA hand a user broad, current-and-future access across an entire schema in one GRANT statement rather than an ever-growing list of individual object grants, provided you remember, as Lesson 2 was careful to establish and several later lessons had to correct on the record, that this capability lives in GRANT, not in some special clause of ALTER USER that circulated in earlier material but never actually existed in current syntax.

Lesson 8 addressed a question this module could easily have gotten wrong in the other direction: whether OS authentication, OPS$ accounts and IDENTIFIED EXTERNALLY, still function at all in a current release. They do. Nothing about the mechanism has been withdrawn; what has genuinely shifted is Oracle's own guidance about when relying on it still makes sense, given how much less trustworthy a bare OS login boundary is on shared, virtualized, cloud infrastructure than it was on a dedicated server decades ago. The lesson's real contribution was not a list of replacement commands, though it offered several genuinely strong ones, password files, Oracle Wallet, Centrally Managed Users, Kerberos, schema-only accounts; it was the distinction itself, between a feature that no longer works and a feature that still works but rarely deserves to be your first choice anymore.

Lesson 9 picked up immediately after account creation with the one grant every account needs before it can do anything at all: CREATE SESSION. From there the lesson built outward into the full shape of Oracle's privilege model, system privileges against object privileges, the genuine usefulness of roles for managing many users against a shared entitlement set, and two mechanics worth knowing precisely rather than approximately: revoking an admin-option privilege from one user never cascades to whoever that user granted it onward to, while dropping a role entirely does strip its privileges from every holder at once, two different operations with two different blast radii that are easy to conflate if you have not seen them stated side by side.

Lessons 10, 11, and 12 covered the maintenance work every account eventually needs: ALTER USER as the correct, non-destructive tool for changing almost anything about an existing account, password resets and forced expiry as the two ways to hand a user a new credential without ever seeing it yourself, and account locking as the cleanest way to suspend access without touching a single privilege or object underneath it. Lesson 10 also corrected a claim worth remembering precisely because of how confidently it read: converting a user to a schema-only account with NO AUTHENTICATION is not some permanent, one-way door. Nothing in current documentation supports that claim, and a standard ALTER USER ... IDENTIFIED BY statement restores login capability exactly the way you would expect it to, the same as it would for any other authentication change.

Lesson 11 rounded out password management with the profile-driven side of the picture: PASSWORD_LIFE_TIME and PASSWORD_GRACE_TIME forcing rotation on a schedule rather than leaving it to memory, and PASSWORD_REUSE_TIME paired with PASSWORD_REUSE_MAX preventing a retired password from simply cycling back into use a few changes later. That lesson also settled a genuine naming question worth carrying forward correctly: Oracle's stricter built-in password verification function is ora12c_strong_verify_function, not the differently-worded variant that circulated in older material and would fail outright if you tried to actually assign it to a profile. And Lesson 12 added a precise reason ACCOUNT_STATUS deserves a closer look than a quick glance: LOCKED and LOCKED(TIMED) look almost identical but mean entirely different things, one a deliberate DBA action and the other an automatic response to failed logins, a distinction worth checking before assuming you know why an account stopped working.

Lesson 13 closed the account lifecycle with DROP USER, and this is where CASCADE's real behavior turned out to be more specific, and in one important respect more limited, than "everything related gets cleaned up." Foreign keys in other schemas that reference the dropped user's tables are genuinely dropped along with them. Views, synonyms, and PL/SQL objects elsewhere that reference the dropped user's objects are not dropped at all; they are invalidated, and stay that way until someone manually intervenes. That distinction is the single most consequential thing a DBA can fail to anticipate before running CASCADE in a shared environment, and it is exactly why Lesson 13 built out three separate verification queries, one against the target user's own objects, one against DBA_DEPENDENCIES for what will be invalidated elsewhere, and one against DBA_CONSTRAINTS for what will be silently dropped elsewhere, rather than treating a single DBA_OBJECTS scan as a complete picture of the blast radius.

Lesson 14 gave you the tools to verify every claim made across the previous thirteen lessons for yourself, at any time, against your own database: DBA_USERS for account status and authentication type, DBA_SYS_PRIVS and DBA_ROLE_PRIVS for the privilege structure, DBA_TS_QUOTAS for storage, DBA_TAB_PRIVS for object-level grants, and DBA_SCHEMA_PRIVS for the schema-level grants this module introduced as a genuinely current capability. Taken together, these views are not a separate topic bolted onto the end of the module; they are the answer key for everything that came before, the place you go the moment you need to confirm rather than assume what an account can actually do.

It is worth naming directly what made this particular module's refactoring different from a straightforward update. An earlier pass through this content, drawing on a different model's training knowledge without access to current Oracle documentation, got a great deal right: the core syntax of CREATE USER, ALTER USER, and DROP USER has been stable for a long time and needed little more than a version label changed. But in the specific places where 26ai genuinely introduced something new, mandatory container scoping, the desupport of traditional auditing, cloud-identity authentication, schema-level privileges, that same pass sometimes produced confident, specific, plausible-sounding claims that turned out not to hold up against the actual current documentation: a whole authentication mechanism described as removed when it was simply discouraged, an account conversion described as permanent when nothing supports that, a schema-privilege feature attributed to the wrong command entirely. None of these read as guesses. Each read as recalled fact, which is exactly what made verifying them against the primary source worth the effort, rather than trusting how confidently a claim was phrased.

That is not a criticism of the earlier pass so much as an honest description of what extrapolating about a release you never actually saw documentation for looks like from the outside: mostly right, occasionally wrong in ways that are specific and hard to catch by reading alone, and only reliably separable from the truth by checking against the thing itself.

It is also worth saying plainly that not everything old in this module needed replacing. The DECODE function, considerably older than CASE expressions and largely superseded by them for readability in new code, still appears in Lesson 14's comprehensive account report, and still works exactly as written; nothing about it has stopped functioning, and there was no actual error to correct there. The same is true of OPS$ itself, discussed at length in Lesson 8: an older mechanism is not automatically a broken one, and part of doing this kind of review honestly is telling the two apart rather than treating every unfamiliar or old-fashioned term as something that must have gone wrong.

You can now take a database user from an unmade decision through its entire working life and, if the day comes, its clean removal. You know to ask which container an account belongs in before you create it, not after. You know that unified auditing is not a preference but the only mechanism this release supports for new configuration, and you know the one column name most likely to trip up a query against it. You know what CASCADE actually promises and what it quietly does not. And you have, in DBA_USERS, DBA_SYS_PRIVS, DBA_ROLE_PRIVS, DBA_TS_QUOTAS, DBA_TAB_PRIVS, and DBA_SCHEMA_PRIVS, a complete, verifiable answer to any question you might later ask about any account you have touched. That is not a large number of ideas to carry forward. It is, however, exactly the right number, and every one of them will still be true the next time you sit down to create a user, right up until Oracle changes something again and this module needs the same kind of pass it just received.

Click the Quiz link below to answer a few questions about viewing information in the data dictionary.
Users Data Dictionary - Quiz