8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

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
The Issue
Schema Only Accounts (The Solution)
19c Update

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. This is described in the following articles.

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 due to too many failed connection attempts, or password expiry resulting in the former, the proxy connections fail. We can demonstrate this by explicitly locking the schema owner.

-- 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>

You can still explicitly lock a schema only account to prevent proxy access, but this will be a specific decision by an administrator, rather than happening accidentally because of password management issues.

19c Update

In Oracle 19c, the "no admin privileges" restriction has been lifted, but the documentation still says the restriction is in place (here). The example below demonstrates the change in behaviour.

First Oracle 18c.

create user c##test_sysdba identified by test_sysdba;
grant create session, sysdba to c##test_sysdba container=all;

select username,
       sysdba,
       sysoper,
       sysasm,
       sysbackup,
       sysdg,
       syskm
from   v$pwfile_users
where  username = 'C##TEST_SYSDBA';

         USERNAME    SYSDBA    SYSOPER    SYSASM    SYSBACKUP    SYSDG    SYSKM
_________________ _________ __________ _________ ____________ ________ ________
C##TEST_SYSDBA    TRUE      FALSE      FALSE     FALSE        FALSE    FALSE

SQL>


alter user c##test_sysdba no authentication
Error report -
ORA-40367: An Administrative user cannot be altered to have no authentication
type.
40367. 00000 -  "An Administrative user cannot be altered to have no authentication type."
*Cause:    An attempt was made to alter an administrative user to have no
           authentication type.
*Action:   Revoke administrative privilege from the user
           before attempting to alter the user.
SQL>

Now in Oracle 19c.

create user c##test_sysdba identified by test_sysdba;
grant create session, sysdba to c##test_sysdba container=all;

select username,
       sysdba,
       sysoper,
       sysasm,
       sysbackup,
       sysdg,
       syskm
from   v$pwfile_users
where  username = 'C##TEST_SYSDBA';

         USERNAME    SYSDBA    SYSOPER    SYSASM    SYSBACKUP    SYSDG    SYSKM
_________________ _________ __________ _________ ____________ ________ ________
C##TEST_SYSDBA    TRUE      FALSE      FALSE     FALSE        FALSE    FALSE

SQL>


alter user c##test_sysdba no authentication

User C##TEST_SYSDBA altered.

SQL>

For more information see:

Hope this helps. Regards Tim...

Back to the Top.