Oracle 18c and 19c: Understanding Schema Only Accounts and Enhanced User Authentication

Before Oracle 18c, database users were always created with an explicit authentication method. The primary options available were:

• Password Authentication: Users would specify a password, which would then be hashed and stored within the database’s $user table. CREATE USER usef1 IDENTIFIED BY password;
• External Authentication: Authentication relied on external systems like operating system credentials. No password was stored in the database. CREATE USER usef2 IDENTIFIED EXTERNALLY;
• Global Authentication: Authentication was handled by a directory service. Again, no password resided within the database. CREATE USER usef3 IDENTIFIED GLOBALLY;

In both External and Global scenarios, the database itself did not manage the password, offloading the authentication process.

Introducing “NO AUTHENTICATION” in Oracle 18c

Oracle 18c marked a significant shift by introducing the ability to create users without specifying any authentication method. This new user type, often referred to as a “Schema Only Account,” is designed to prevent direct logins. Its primary purpose is to enhance security, especially for high-privilege application schemas, by ensuring that no one can directly connect to them using a username and password.

This feature is not limited to new user creation; existing users can also be converted to Schema Only Accounts using the ALTER USER command.

Creating a Schema Only Account:
To create such a user, you use the NO AUTHENTICATION clause:

SQL> CREATE USER usef_schema NO AUTHENTICATION;
User created.

Attempting to connect directly to usef_schema will result in errors like ORA-01017: invalid username/password or ORA-01005: null password given. This confirms that direct login is disabled, and the PASSWORD field for this user in the $user table remains null. Furthermore, querying the dba_users view for this user will show AUTHENTICATION_TYPE as NONE.

Managing Schema Only Accounts: Privileges and Proxy Connections

Schema Only Accounts can be granted roles and privileges just like any other user. This allows other authorized users to create and manage objects within these schemas.

SQL> grant connect,resource to usef_schema;
Grant succeeded.
SQL> alter user usef_schema quota unlimited on users;
User altered.

An authorized user, for instance usef, can be granted privileges to interact with usef_schema:

SQL> grant connect,unlimited tablespace,create any table,select any table to usef;
Grant succeeded.
SQL> conn usef/a
Connected.
SQL> create table usef_schema.mytbl as select * from dual;
Table created.

However, the usef user might not have sufficient privileges to drop objects owned by usef_schema directly without additional grants.

To enable access to a Schema Only Account, a proxy connection is the recommended method. This involves granting proxy authentication to another user, allowing them to connect as the Schema Only Account.

SQL> ALTER USER usef_schema GRANT CONNECT THROUGH usef;
User altered.

Now, usef can connect as usef_schema, either locally or remotely:

SQL> CONN usef[usef_schema]/a -- Local
Connected.
SQL> CONN usef[usef_schema]/a@//192.168.1.20:1521/noncdb -- Remote
Connected.

Once connected via proxy, commands like show user and sys_context('USERENV','SESSION_USER') will confirm that the session is operating under USEF_SCHEMA, while sys_context('USERENV','PROXY_USER') will reveal the actual connecting user (USEF). This allows the proxy user to perform operations with the privileges of usef_schema, such as dropping tables.

Auditing Proxy Activities

A critical aspect of proxy connections is auditability. Oracle’s unified auditing system can accurately track actions performed through proxy users. By enabling an audit policy for the Schema Only Account, the unified_audit_trail view will record the DBPROXY_USERNAME column, clearly indicating which user connected via proxy to perform the action. This ensures transparency and accountability.

SQL> create audit policy mypol1 roles dba;
SQL> audit policy mypol1 by usef_schema;
-- Connect as nima[usef_schema] and perform actions
SQL> select p.dbusername, p.dbproxy_username, p.action_name, p.event_timestamp, p.sql_text 
     from unified_audit_trail p where p.unified_audit_policies='MYPOL1';
-- The output will show 'NIMA' in the DBPROXY_USERNAME column.

The proxy_users view can be queried to list all users granted proxy access to a specific schema.

Administrative Privileges: 18c vs. 19c

An important distinction exists regarding administrative privileges:
* Oracle 18c: It was not possible to grant administrative privileges like SYSDBA or SYSOPER to Schema Only Accounts, aiming to further restrict their capabilities. ORA-40366: Administrative privilege cannot be granted to this user.
* Oracle 19c: This restriction was relaxed, and administrative privileges can be granted to Schema Only Accounts, offering more flexibility in certain advanced scenarios.

Converting Authentication Methods

The authentication method for any user, including Schema Only Accounts, can be modified using the ALTER USER command.
* From Schema Only to Password: You can assign a password to a Schema Only Account, thereby enabling direct login and changing its AUTHENTICATION_TYPE to PASSWORD.
sql
SQL> alter user USEF_SCHEMA identified by a;
User altered.
SQL> conn usef_schema/a
Connected.
* From Authenticated to Schema Only: Conversely, a user with an existing authentication method can be converted to a Schema Only Account by removing its authentication.
sql
SQL> alter user usef no AUTHENTICATION;
User altered.

Schema Only Accounts, along with the enhancements in proxy connections and auditing, provide a robust mechanism for managing application schemas securely in Oracle 18c and later versions, offering greater control over direct database access.