Profiles, Password Policy, Resource Limits, Proxy Authentication, and Account Lifecycle

Learning outcomes

ServiceHub has a shared application-schema password copied into deployment scripts and used by administrators, batch jobs and the web tier. When an audit record says SERVICEHUB_OWNER, nobody knows which human or component actually connected. Oracle profiles and account state control authentication/resource policy, while proxy authentication can preserve the real identity without distributing the target schema password.

1. A profile is policy attached to users

An Oracle profile can contain password/account controls and kernel resource limits. Password profile settings are enforced independently of the older RESOURCE_LIMIT switch; current 26ai also documents RESOURCE_LIMIT with default TRUE for resource-limit enforcement.

ALTER SESSION SET CONTAINER=FREEPDB1;SELECT name,value,ispdb_modifiableFROM v$parameterWHERE name='resource_limit';SELECT  profile,  resource_name,  resource_type,  limitFROM dba_profilesWHERE profile='DEFAULT'ORDER BY resource_type,resource_name;

2. Do not copy a password policy from a tutorial into production

Oracle supplies password verification functions such as ORA12C_VERIFY_FUNCTION, ORA12C_STRONG_VERIFY_FUNCTION, and a STIG-oriented function/profile. The right password lifetime, reuse, grace, failed-attempt and inactivity policy depends on enterprise identity/MFA standards, threat model, regulatory requirements and application rotation capability.

SELECT owner,object_name,statusFROM dba_objectsWHERE object_type='FUNCTION'  AND object_name IN (    'ORA12C_VERIFY_FUNCTION',    'ORA12C_STRONG_VERIFY_FUNCTION',    'ORA12C_STIG_VERIFY_FUNCTION'  )ORDER BY object_name;

3. Create an illustrative profile

BEGIN EXECUTE IMMEDIATE 'DROP PROFILE sh20_app_profile CASCADE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -2380 THEN RAISE; END IF; END;/CREATE PROFILE sh20_app_profile LIMIT  FAILED_LOGIN_ATTEMPTS 3  PASSWORD_LOCK_TIME 1/1440  PASSWORD_LIFE_TIME 90  PASSWORD_GRACE_TIME 7  INACTIVE_ACCOUNT_TIME 30  PASSWORD_REUSE_TIME 365  PASSWORD_REUSE_MAX 20  PASSWORD_VERIFY_FUNCTION ORA12C_VERIFY_FUNCTION  SESSIONS_PER_USER 3;SELECT resource_name,resource_type,limitFROM dba_profilesWHERE profile='SH20_APP_PROFILE'ORDER BY resource_type,resource_name;

The one-minute lock and three-session limit make the lab observable; they are intentionally not production recommendations. Changing a profile affects users assigned to it, and account-state changes generally affect future sessions rather than retroactively terminating every current session.

4. Schema-only target plus a minimal proxy preserves identity

A schema-only account owns objects but has no authentication method and cannot log in directly. Oracle explicitly supports proxying into schema-only accounts. A proxy user authenticates the connection while the target schema supplies the authorization domain; Oracle records both identities.

BEGIN EXECUTE IMMEDIATE 'DROP USER sh20_target CASCADE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -1918 THEN RAISE; END IF; END;/BEGIN EXECUTE IMMEDIATE 'DROP USER sh20_proxy CASCADE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -1918 THEN RAISE; END IF; END;/CREATE USER sh20_target NO AUTHENTICATION  DEFAULT TABLESPACE users QUOTA 5M ON users  PROFILE sh20_app_profile;CREATE USER sh20_proxy NO AUTHENTICATION  PROFILE sh20_app_profile;GRANT CREATE SESSION TO sh20_proxy;ALTER USER sh20_target  GRANT CONNECT THROUGH sh20_proxy;SELECT proxy,client,authenticationFROM proxy_usersWHERE proxy='SH20_PROXY'   OR client='SH20_TARGET';

PASSWORD sh20_proxy-- Enter and confirm the secret at the protected prompt.-- Do not put it in a .sql, shell history, Git repository, or lesson HTML.

5. Verify proxy identity at connection time

CONNECT sh20_proxy[sh20_target]@//localhost:1521/FREEPDB1-- Enter SH20_PROXY's password interactively.SHOW USERSELECT  SYS_CONTEXT('USERENV','SESSION_USER') AS session_user,  SYS_CONTEXT('USERENV','CURRENT_USER') AS current_user,  SYS_CONTEXT('USERENV','PROXY_USER') AS proxy_user,  SYS_CONTEXT('USERENV','AUTHENTICATION_METHOD') AS auth_methodFROM dual;

CURRENT_USER/SESSION_USER should identify the target schema while PROXY_USER identifies the authenticating middle-tier account. This gives auditing a better attribution chain than everyone logging in directly as a shared schema.

6. Account lock is different from password expiration

ALTER USER sh20_proxy ACCOUNT LOCK;SELECT username,account_status,lock_date,expiry_date,profileFROM dba_usersWHERE username='SH20_PROXY';-- A new connection attempt should fail, typically with:-- ORA-28000: The account is locked.

ALTER USER sh20_proxy ACCOUNT UNLOCK;-- For an ordinary password-authenticated human/service account:ALTER USER sh20_proxy PASSWORD EXPIRE;-- After testing, lock before retirement:ALTER USER sh20_proxy ACCOUNT LOCK;

Password expiration forces password-change lifecycle on direct password authentication. Oracle notes that proxy authentication has different expiration behavior for the proxied target; if access must stop, locking/revoking proxy authorization is the reliable control.

7. Resource profile semantics require RESOURCE_LIMIT

Limits such as SESSIONS_PER_USER, CPU or logical-read limits are resource governance controls. They are not a replacement for Resource Manager and can terminate/error a call/session abruptly. The current 26ai RESOURCE_LIMIT default is TRUE, is dynamically modifiable and is PDB-modifiable, but always query the deployed value before expecting profile resource limits to fire.

SELECT name,value,isdefault,issys_modifiable,ispdb_modifiableFROM v$parameterWHERE name='resource_limit';

8. Deliberately wrong: password in deployment SQL

-- BAD: the secret can land in source control, terminal history,-- SQL tracing, CI logs, screen recordings, ticket attachments, etc.-- CREATE USER servicehub_runtime IDENTIFIED BY SuperSecret123!;

Oracle specifically warns that passwords embedded in SQL statements can appear in network trace contexts; protect the network with TLS/NNE and use interactive password commands, secure external password stores, OCI/central identity, or a deployment secret manager. The database password verifier is not a reason to put secrets in scripts.

9. Account lifecycle checklist

• Create the identity only in the required PDB; default-deny privileges.
• Assign approved authentication/profile policy.
• Set the secret interactively or through an approved secret-delivery mechanism.
• Record owner/purpose/expiry/rotation contact in identity governance—not in the password.
• Monitor failed/inactive logins and proxy mappings.
• Lock/revoke proxy access before decommissioning; drop only after dependency/forensic retention checks.

10. Cleanup

ALTER USER sh20_target REVOKE CONNECT THROUGH sh20_proxy;DROP USER sh20_proxy CASCADE;DROP USER sh20_target CASCADE;DROP PROFILE sh20_app_profile;

11. Production judgment

Profiles implement a database-level control layer, but enterprise authentication should integrate with MFA/central identity where appropriate. Use schema-only owners where direct login has no business purpose, proxy authentication where a middle tier must preserve client identity, and interactive/wallet/secret-manager credential handling rather than embedded passwords.

No paid option or restart is required. RESOURCE_LIMIT is dynamic and PDB-modifiable. Lesson 3 records what these identities actually do using 26ai unified auditing rather than the now-desupported traditional audit configuration.

Authoritative references

• Managing Security for Database Users — profiles/account lifecycle
• Configuring Authentication — proxy/schema-only/password guidance
• CREATE PROFILE — profile settings
• RESOURCE_LIMIT — current default/dynamic/PDB behavior
• ALTER USER — lock/expire/proxy authorization