8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | 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.
Related articles.
- Schema Only Accounts in Oracle Database 18c Onward
- Proxy User and Connect Through
- My Utopian Development Environment
- Schema Owners and Application Users
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:
- Schema Only Accounts
- CREATE USER
- Schema Only Accounts in Oracle Database 18c Onward
- Proxy User and Connect Through
- My Utopian Development Environment
- Schema Owners and Application Users
Hope this helps. Regards Tim...