Oracle DBA Lesson 67 Part 1 — Create a Login in the Intended PDB

A local database account belongs to one pluggable database (PDB). Creating a useful login requires choosing the intended container, configuring authentication, permitting a database session, and enabling the account. Testing the actual connection checks that these decisions work together.

Oracle DBA Lesson 67 Part 1 — Create a Login in the Intended PDB

Local identity and connection destination

A local user exists only in its PDB. Two PDBs may contain local users with the same spelling. Those are separate identities with separately configured authentication and privileges. Creating APP_LOGIN in APP_PDB does not create it in REPORT_PDB.

On Oracle Database 19c, SQL*Plus uses an ordinary local password-authenticated login. The connection alias APP_SERVICE resolves to a service associated with APP_PDB. A service or alias need not have the same name as the PDB.

Connection target	Local identity reached, if provisioned and authenticated
Service associated with APP_PDB	APP_PDB's APP_LOGIN
Service associated with REPORT_PDB	REPORT_PDB's separately created APP_LOGIN

The service, the actual session container, and the account together determine the intended connection context. The second row compares a separately created local account with the same spelling.

A local username must follow the database naming rules and must not begin with the configured prefix reserved for common users. It must never start with C## or c##, even when COMMON_USER_PREFIX differs. Use an ordinary unquoted name such as app_login, which Oracle stores as APP_LOGIN. Check for an existing user and role with that name before proceeding.

See Creating User Accounts.

Check the intended container

Connect through the PDB service. With a password-authenticated administrator:

sqlplus -L admin_user@APP_SERVICE

Enter the administrator password at the prompt. In SQL*Plus:

SHOW USER
SHOW CON_NAME

SELECT username FROM dba_users
WHERE username = 'APP_LOGIN';
SELECT role FROM dba_roles
WHERE role = 'APP_LOGIN';

The connected session for this sequence is:

Connected user	CON_NAME
ADMIN_USER	APP_PDB

The destination matches when the connected user is ADMIN_USER, CON_NAME is APP_PDB, and both name checks return no rows. An existing user or role with that name is already present in this PDB. Creating the account in the root container does not place this local user in APP_PDB.

The session that creates and changes the account needs CREATE USER and ALTER USER, authority to grant CREATE SESSION, and the dictionary access used by the inspection queries. Granting a system privilege normally requires that privilege with ADMIN OPTION, or GRANT ANY PRIVILEGE.

CONTAINER=CURRENT applies creation to the connected PDB. It is not a destination-selection or connection command. In a PDB, that clause is also the default for local creation. The statement keeps it explicit so the scope is visible.

See Starting SQL*Plus.

Create a locked password-authenticated account

This SQL*Plus workflow receives a local secret through a hidden prompt. It turns command echo, substitution verification, and spooling off, and leaves substitution on:

SET ECHO OFF
SET VERIFY OFF
SPOOL OFF
SET DEFINE ON
ACCEPT account_secret CHAR PROMPT 'New account password: ' HIDE
CREATE USER app_login IDENTIFIED BY "&account_secret"
  ACCOUNT LOCK CONTAINER=CURRENT;
UNDEFINE account_secret

Clause	Purpose
CREATE USER app_login	Establishes the new local database identity and same-named schema
IDENTIFIED BY	Configures password authentication using the supplied secret
ACCOUNT LOCK	Holds login while configuration is completed
CONTAINER=CURRENT	Applies creation to the currently connected PDB

ACCEPT ... HIDE is a SQL*Plus input command. It hides entered characters. It does not create the database account. UNDEFINE removes the substitution variable afterward. Those SET commands change the SQL*Plus session. Restore the earlier client settings when finished, or exit that session.

SQL*Plus substitution is textual, so the secret must be compatible with this workflow. Check the password profile and the supported provisioning method, then use a unique generated password that the profile accepts. A suitable restricted character set is mixed-case letters, digits, and _, $, and #. Exclude ampersands, quotes, whitespace, newlines, and substitution or control characters. If policy requires other characters, use that supported provisioning mechanism instead of placing an incompatible secret in this script.

DDL has implicit commit behavior. Do not run this creation in a session with unrelated uncommitted work. ROLLBACK does not undo account creation.

See CREATE USER and SQL*Plus ACCEPT.

Grant session permission

A newly created user's privilege domain is empty. Grant the privilege that permits an ordinary database session:

GRANT CREATE SESSION TO app_login;

CREATE SESSION is the system privilege for establishing a normal database session. The account lock still holds access until the unlock step.

This grant does not provide application-table selection, table creation, or a storage quota. Later access is a separate decision. This login-only account is not a fully provisioned application owner.

See GRANT.

Review the configuration while locked

While the account is still locked, read the user row and the direct system grants:

SELECT username, common, authentication_type,
       account_status
FROM dba_users
WHERE username = 'APP_LOGIN';

SELECT privilege, admin_option
FROM dba_sys_privs
WHERE grantee = 'APP_LOGIN'
ORDER BY privilege;

Before unlocking, this sequence reads:

USERNAME	COMMON	AUTHENTICATION_TYPE	ACCOUNT_STATUS
APP_LOGIN	NO	PASSWORD	LOCKED

PRIVILEGE	ADMIN_OPTION
CREATE SESSION	NO

COMMON=NO identifies a local account. PASSWORD matches the chosen authentication method. LOCKED records the deliberate login hold. The privilege query lists direct system grants. It is not an inventory of all effective access through PUBLIC, roles, object grants, or other controls.

If the status shows expiration, timed locking, or another combination, interpret that condition from DBA_USERS and the password profile.

See DBA_USERS.

Unlock and test the connection

When configuration is ready, remove the login hold:

ALTER USER app_login ACCOUNT UNLOCK;

After this unlock, the account status becomes OPEN. Unlocking changes status. A valid authentication method and secret, and CREATE SESSION, are still required.

Start a separate user connection so the administrator session is not confused with the test:

sqlplus -L app_login@APP_SERVICE

Enter the account password at the prompt, then run:

SHOW USER
SHOW CON_NAME

Connected user	CON_NAME
APP_LOGIN	APP_PDB

The successful session in the intended PDB is the verification. The metadata row alone is not a tested connection. A successful login provides no general entitlement to application tables. Selection requires the relevant object privilege or another applicable grant.

If login fails, identify the error and inspect the service and container, authentication and password policy, account status, and CREATE SESSION in that container. Network, service availability, and additional security controls can also affect connection success.

See ALTER USER.

Recap

A local account belongs to one PDB. Creating APP_LOGIN in APP_PDB does not create it in REPORT_PDB. The same spelling in another PDB is a separate local user. A local name must follow the naming rules and must never start with C## or c##. Connect through the service for the intended PDB, confirm the user and container, and confirm the name is unused as both a user and a role.

Create the account locked and password-authenticated, with CONTAINER=CURRENT so creation applies to the connected PDB. CREATE SESSION is granted separately and is the privilege that permits an ordinary session. While the account is locked, DBA_USERS shows COMMON=NO, AUTHENTICATION_TYPE=PASSWORD, and ACCOUNT_STATUS=LOCKED, and the direct system grant is CREATE SESSION. ACCOUNT UNLOCK changes the status to OPEN. The password, the session privilege, and the PDB stay as they were. Verify with a separate prompted connection: SHOW USER and SHOW CON_NAME show APP_LOGIN in APP_PDB.

Quiz

1. Does creating APP_LOGIN locally in APP_PDB create the same identity in every PDB?

Yes, if the name is uppercase Yes, once the password is set No; the identity belongs to its own PDB Only after unlocking

2. Which privilege permits an ordinary database session?

CREATE SESSION CREATE TABLE An unlimited storage quota Changing CURRENT_SCHEMA

3. What does ACCOUNT UNLOCK do in this workflow?

Grants access to every application table Changes account status to enable access, while other login conditions still apply Changes the account to a common user Moves the account to root

4. What is the effect of CONTAINER=CURRENT while connected to APP_PDB for this CREATE USER?

Creates a local account in APP_PDB Connects to a PDB named CURRENT Creates the user in all PDBs Selects the PDB from the username

5. Which observation best verifies the intended login?

The username appears in a script COMMON is NO somewhere in the CDB A separate prompted connection succeeds, then SHOW USER and SHOW CON_NAME confirm APP_LOGIN and APP_PDB An administrator can query DBA_USERS

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