Oracle DBA Lesson 69 Part 1 — Ownership Without Shared Passwords

Application objects need stable ownership as operators join or leave a team. An Oracle schema-only account supplies that ownership without direct login credentials. People can connect with their own named accounts through an explicitly configured proxy relationship.

Oracle DBA Lesson 69 Part 1 — Ownership Without Shared Passwords

Separate ownership from authentication

A schema is the collection of objects owned by a database user. Creating a user also establishes the corresponding schema identity. The account initially has no application tables.

Account	Purpose
APP_OWNER	Schema-only application owner. It holds application objects and their applicable privileges.
OPERATOR_USER	Existing named account that authenticates and is permitted to open a session on behalf of APP_OWNER.

On Oracle Database 19c, NO AUTHENTICATION makes the owner schema-only. It is not a password that people share, an empty-password login, or a substitute for granted access. Oracle allows a schema-only account to be the represented client in a single-session proxy connection. No owner password is needed for that path.

See schema-only accounts.

Create the owner and grant the proxy relationship

In the intended pluggable database:

CREATE USER app_owner NO AUTHENTICATION;
GRANT CREATE SESSION TO app_owner;
ALTER USER app_owner GRANT CONNECT THROUGH operator_user;

The statements have separate purposes:

1. CREATE USER establishes the schema-only owner without credentials. It does not create application tables.
2. CREATE SESSION supplies the session privilege for the owner represented in the proxy connection. It does not give the schema-only owner credentials for a direct login.
3. GRANT CONNECT THROUGH permits the named operator as a proxy for this owner. The owner is named after ALTER USER. The authenticating proxy account follows CONNECT THROUGH.

The administrator needs CREATE USER, authority to grant CREATE SESSION, and authority to alter the owner and proxy relationship.

OPERATOR_USER already exists in the intended container, has its own authentication and CREATE SESSION, and is the named account an operator uses. Oracle recommends keeping the authenticating proxy account's own privileges minimal. Privileges exercised through the proxy session belong to the represented account, subject to the proxy role configuration.

The proxy relationship lets the operator work with the owner's authority. Give it only where the task requires an owner session. A reporting account can instead receive the specific object grants it needs. A named identity improves attribution only when people use their own accounts and the appropriate auditing is configured.

These statements do not grant CREATE TABLE or a tablespace quota. Creating tables and allocating storage for them are separate requirements. Part 2 develops them. These three statements alone create no application tables.

Read the owner's authentication type:

SELECT username, authentication_type
FROM dba_users
WHERE username = 'APP_OWNER';

USERNAME	AUTHENTICATION_TYPE
APP_OWNER	NONE

See CREATE USER, creating proxy user accounts, proxy account guidelines, and granting and revoking proxy access.

Connect with the named operator

In SQL*Plus, use the service alias for the same target container:

CONNECT operator_user[app_owner]@db_service
Enter password:

Enter the operator's password at the prompt. The square brackets are literal SQL*Plus syntax: the authenticating proxy is outside them, and the represented owner is inside. db_service is the service alias for that container.

Oracle authenticates the proxy and checks its permission to represent the owner. The session then represents APP_OWNER. Oracle also retains OPERATOR_USER as the proxy identity. Tables stay owned by APP_OWNER. No table or user identity is transferred between accounts.

A password prompt does not itself configure transport encryption. SQL*Plus CONNECT commits a pending transaction, disconnects the current session, and reconnects.

See SQL*Plus CONNECT.

Inspect both identities

In the proxy session:

SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') AS session_user,
       SYS_CONTEXT('USERENV', 'PROXY_USER') AS proxy_user
FROM dual;

SESSION_USER	PROXY_USER
APP_OWNER	OPERATOR_USER

SESSION_USER identifies the represented owner in this ordinary proxy connection. PROXY_USER identifies the account that opened the session on its behalf. Read them together.

This identity query does not prove that an audit policy is enabled, that every action was recorded, or that the owner has every privilege a task needs. The proxy grant, the owner's effective privileges, and the configured audit trail answer those questions.

By default, CONNECT THROUGH allows the client's roles. WITH ROLE can restrict which roles are available. Direct privileges of the represented account still apply.

See SYS_CONTEXT.

CURRENT_SCHEMA changes name resolution

For a reader connected as its own account:

ALTER SESSION SET CURRENT_SCHEMA = app_owner;

An unqualified reference such as orders can now resolve in the APP_OWNER schema. The reader keeps its session identity and existing privileges. Access to an application table still requires the appropriate privilege. This setting does not establish a proxy connection or grant owner authority.

When the reader has SELECT on APP_OWNER.ORDERS, the setting can let it write SELECT ... FROM orders instead of qualifying the table. The owner-creation statements do not create that table or that grant.

See ALTER SESSION.

Recap

A schema-only account can own application objects without a password for direct login. On Oracle Database 19c, CREATE USER app_owner NO AUTHENTICATION creates that owner, GRANT CREATE SESSION TO app_owner supplies the session privilege used when the owner is represented, and ALTER USER app_owner GRANT CONNECT THROUGH operator_user names the owner first and the authenticating proxy second. OPERATOR_USER already has its own authentication and CREATE SESSION. DBA_USERS shows AUTHENTICATION_TYPE NONE for APP_OWNER. Privileges used in the proxy session belong to APP_OWNER, and the proxy account's own privileges should stay minimal. These statements do not grant CREATE TABLE or a tablespace quota, and they create no application tables.

In SQL*Plus, CONNECT operator_user[app_owner]@db_service asks for the operator's password. The brackets name the represented owner. SESSION_USER is APP_OWNER and PROXY_USER is OPERATOR_USER. Tables remain owned by APP_OWNER. CONNECT commits a pending transaction, disconnects the current session, and reconnects. The password prompt does not configure transport encryption. CONNECT THROUGH allows the client's roles unless WITH ROLE restricts them. ALTER SESSION SET CURRENT_SCHEMA = app_owner changes unqualified name resolution for a reader connected as itself. It leaves that reader's identity and privileges unchanged, and it does not open a proxy session.

Quiz

1. What does NO AUTHENTICATION provide?

A shared default password A schema-only account without direct login credentials Automatic table creation Unlimited tablespace quota

2. Which statement allows OPERATOR_USER to open a proxy session as APP_OWNER?

ALTER USER operator_user GRANT CONNECT THROUGH app_owner; ALTER SESSION SET CURRENT_SCHEMA = app_owner; ALTER USER app_owner GRANT CONNECT THROUGH operator_user; GRANT CREATE TABLE TO operator_user;

3. Whose password is entered for the illustrated bracketed connection?

The operator's password The schema-only owner's password A table password No password can be requested

4. What does PROXY_USER identify in the query result?

The default tablespace The table creator's operating system group The current schema's storage quota The account that opened the session on behalf of SESSION_USER

5. Does CURRENT_SCHEMA = APP_OWNER make an ordinarily connected reader the owner?

Yes, including all owner privileges Only if the owner has no password No; it changes unqualified name resolution Yes, until the next commit

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