Tuesday, 22 May 2018

Schema Only Accounts in Oracle Database 18c

Schema only accounts don't allow direct connections, so administration of their objects is done via a DBA user or a proxy user.

◈ Warning


Applications should never have access to the schema owner, either directly or via a proxy connection. You should only be logged in as the schema owner because you are making DDL changes to the schema objects. Applications should log in using a separate user with privileges on the schema objects.

Oracle Database 18c, Oracle Database Tutorials and Materials, Oracle Database Learning, Oracle Database Certifications

1. Schema Owners and Application Users
2. My Utopian Development Environment

The rest of this article is focused on how you control access to the schema owner, when people need to connect to perform maintenance. It's not about how to set up users for application connections.

◈ The Issue


It's a common practice to restrict direct access to a schema owner, preventing people accessing it using shared credentials. Instead they access it to do schema changes via a proxy connection, allowing you to audit which users performed which tasks. For example, we create a schema owner called SCHEMA_OWNER using the following command.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER = pdb1;

--DROP USER schema_owner CASCADE;

CREATE USER schema_owner IDENTIFIED BY secret_password
  QUOTA UNLIMITED ON users;

GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE VIEW TO schema_owner;

Create a user that will be used as a proxy user.

CREATE USER tim IDENTIFIED BY my_password;
GRANT CREATE SESSION TO tim;

We then allow a proxy connection to the SCHEMA_OWNER user via a user called TIM.

ALTER USER schema_owner GRANT CONNECT THROUGH tim;

We can now connect to the schema owner using the following syntax.

--CONN proxy_user[schema_owner]/password@pdb1

CONN tim[schema_owner]/my_password@pdb1
Connected.
SQL>

That works fine, but the ability to create direct connections to the schema owner is still possible if you know the password. In addition, if the schema owner gets locked the proxy connections fail.

-- Lock the user.
CONN / AS SYSDBA
ALTER SESSION SET CONTAINER = pdb1;

ALTER USER schema_owner ACCOUNT LOCK;

-- Test the proxy connection
CONN tim[schema_owner]/my_password@pdb1
ERROR:
ORA-28000: The account is locked.


Warning: You are no longer connected to ORACLE.
SQL>

This is not an ideal situation.

◈ Schema Only Accounts (The Solution)


To solve this problem Oracle 18c introduced a schema only account. Since no authentication method is specified direct connections to the user are not allowed, and the user is not subject to password management like password expiry. A schema only account can be created with the NO AUTHENTICATION clause.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER = pdb1;

DROP USER schema_owner CASCADE;

CREATE USER schema_owner NO AUTHENTICATION
  QUOTA UNLIMITED ON users;

GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE VIEW TO schema_owner;

We could achieve the same thing without specifying an authentication clause. This is functionally equivalent of using the NO AUTHENTICATION clause.

DROP USER schema_owner CASCADE;

CREATE USER schema_owner
  QUOTA UNLIMITED ON users;

GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE VIEW TO schema_owner;

We then allow a proxy connection to the SCHEMA_OWNER user via a user called TIM, as we did before.

ALTER USER schema_owner GRANT CONNECT THROUGH tim;

We can now connect to the schema owner using the following syntax.

--CONN proxy_user[schema_owner]/password@pdb1

CONN tim[schema_owner]/my_password@pdb1
Connected.
SQL>

It's not possible to make a direct connection to the schema owner, unless an authentication method is added subsequently using the ALTER USER command.

The authentication type is listed as NONE in the DBA_USERS view.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER = pdb1;

COLUMN username FORMAT A30
COLUMN account_status FORMAT A20

SELECT username,
       account_status,
       authentication_type
FROM   dba_users
WHERE  username = 'SCHEMA_OWNER';

USERNAME                     ACCOUNT_STATUS     AUTHENTICATION_TYPE
--------------------------  --------------------    ------------------------
SCHEMA_OWNER                   OPEN                 NONE

SQL>

You can turn an existing user into a schema only user using the ALTER USER command. This will only work if the account doesn't have any admin privileges. You can check this with the following query. If any exist they will need to be revoked before you issue the alter user command.

SELECT username,
       sysdba,
       sysoper,
       sysasm,
       sysbackup,
       sysdg,
       syskm
FROM   v$pwfile_users
WHERE  username = 'SCHEMA_OWNER';

no rows selected

SQL>

Assuming there are no admin privileges, the account can be made schema only with the following command.

ALTER USER schema_owner NO AUTHENTICATION;

User altered.

SQL>