Oracle Database 18c schema only accounts
One nice new feature of Oracle Database 18c is that schemas can be created without a password. These are called "
Schema Only Accounts". This new functionality is pretty simple and is a nice step forward that can allow administrators to further secure their databases and protect their applications.
Preliminary Questions
Question: Why do we want to have these " schema only accounts" or schemas without passwords? Answer: This is applicable for schemas which hold application objects (i.e. tables, PL/SQL units, etc). I call those "application schemas". We shouldn't want to ever be able to connect to application schemas for any reason. Question: Why don't we want to connect to application schemas and hence have them have passwords? Answer: To ensure with certainty that nobody can connect to them. Because if we can connect to the application schema then we bypass all security and have unrestricted access to the schema objects. All data within the schema objects can be viewed, manipulated, and/or compromised. Plus maybe a password rotation is then required with turnover of staff with knowledge of this password. So having application schemas never connected to is a good design principle. Question: But maybe we do need to connect to them sometimes - for example when creating the schema or schema objects during application setup or upgrades, things like that? Answer: Actually I don't agree with that regardless of the version of the Oracle Database being used. We can use an alternative approach which I'll discuss below.Basic Setup and Usage
The basic setup and usage of a schema only account is really simple. We just use the new Oracle 18c DDL syntax " NO AUTHENTICATION" when creating or altering a user. For example:SQL> CREATE USER scott NO AUTHENTICATION;
User created.
SQL>
With some basic checks we see:
SQL> select username, password, password_versions, account_status, authentication_type
2 from dba_users where username = 'SCOTT';
USERNAME PASSWORD PASSWORD_VERSIONS ACCOUNT_STATUS AUTHENTICATION_TYPE
-------------------- -------------------- ----------------- -------------------- --------------------
SCOTT OPEN NONE
SQL>
Interestingly, the ACCOUNT_STATUS column still says "OPEN". The PASSWORD column is NULL in DBA_USERS in previous versions as well but now the PASSWORD_VERSIONS column is also NULL. But the proper way to identify that this is a schema only account is via
AUTHENTICATION_TYPE=NONE. Out of interest, checking in some other familiar locations, we can see that we do still seem to have some hashed values in the SYS.USER$ table :
SQL> select name, spare4 from sys.user$ where name = 'SCOTT';
NAME SPARE4
-------------------- --------------------------------------------------------------------------------
SCOTT S:0000000000000000000000000000000000000000C39C2BC7429D5EB08A12;T:000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000001068D392C48D32356722B319F97CA345
SQL>
If we try to connect to our newly created "NO AUTHENTICATION" user we get the standard ORA-01017 error. For example:
$ echo "show user" | sqlplus -s scott/tiger
ERROR:
ORA-01017: invalid username/password; logon denied
And finally, we can still of course extract the account's DDL using DBMS_METADATA and it shows the NO AUTHENTICATION clause as we'd expect:
SQL> select dbms_metadata.get_ddl('USER','SCOTT') from dual;
DBMS_METADATA.GET_DDL('USER','SCOTT')
--------------------------------------------------------------------------------
CREATE USER "SCOTT" NO AUTHENTICATION
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
SQL>
Switching Between AUTHENTICATION and NO AUTHENTICATION
We can easily switch back and fourth between a "schema only account" and a noramal account using the familiar syntax:ALTER USER ... IDENTIFIED BY ...; ALTER USER ... NO AUTHENTICATION;One caveat to this is regarding "administrative privileges" (i.e. the SYSDBA, SYSOPER, SYSRAC, etc privileges stored in password files and viewable via p$pwfile_users). Schema only accounts can be assigned all normal database roles and privileges but not the administrative privileges. If the user is assigned an administrative privilege it cannot be converted into a schema only account. And similarly schema only accounts cannot be granted the administrative privileges:
SQL> grant sysdba to scott;
grant sysdba to scott
*
ERROR at line 1:
ORA-40366: Administrative privilege cannot be granted to this user.
SQL> alter user scott identified by tiger;
User altered.
SQL> grant sysdba to scott;
Grant succeeded.
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM ACCOUNT_STATUS PASSWORD_PROFILE
-------------------- ----- ----- ----- ----- ----- ----- ------------------------------ --------------------
LAST_LOGIN LOCK_DATE EXPIRY_DA EXTERNAL_NAME AUTHENTI COM CON_ID
------------------------ --------- --------- -------------------- -------- --- ----------
SYS TRUE TRUE FALSE FALSE FALSE FALSE OPEN
PASSWORD YES 0
SYSDG FALSE FALSE FALSE FALSE TRUE FALSE OPEN
PASSWORD YES 0
SYSBACKUP FALSE FALSE FALSE TRUE FALSE FALSE OPEN
PASSWORD YES 0
SYSKM FALSE FALSE FALSE FALSE FALSE TRUE OPEN
PASSWORD YES 0
SCOTT TRUE FALSE FALSE FALSE FALSE FALSE OPEN
PASSWORD NO 3
SQL> alter user scott no authentication;
alter user scott no authentication
*
ERROR at line 1:
ORA-40367: An Administrative user cannot be altered to have no authentication type.
SQL> revoke sysdba from scott;
Revoke succeeded.
SQL> alter user scott no authentication;
User altered.
SQL>
SESSION_USER for Schema Only Accounts
If we can’t connect to the database using the application schema "how can we fully develop/populate it?" is the obvious question. We can do some things like create tables in other schemas quite easily. But other more advanced objects such as database links are more tricky to create in other schemas. So it may seem from the previous sections that the solution is to temporarily add a password, connect, do what's required, and then change it back to being schema only. However, that actually is not the intended workflow. Rather the proper way is to make a "Proxy Authenticated Connection". This feature was first introduced with Oracle Database 10g, has many other advantages, and is the proper way to change your SESSION_USER to the schema only account you want to create objects in. In this simple example I create a DBA privileged (normal) account called "SIMON_DBA" and then use it to proxy into the schema only SCOTT account created previously.SQL> create user simon_dba identified by testing;
User created.
SQL> grant dba to simon_dba;
Grant succeeded.
SQL> alter user scott grant connect through simon_dba;
User altered.
SQL> connect simon_dba[scott]/testing
Connected.
SQL> select sys_context('USERENV','SESSION_USER') as session_user,
2 sys_context('USERENV','SESSION_SCHEMA') as session_schema,
3 sys_context('USERENV','PROXY_USER') as proxy_id,
4 user
5 from dual;
SESSION_USER SESSION_SCHEMA PROXY_ID USER
-------------- -------------- -------------- --------------
SCOTT SCOTT SIMON_DBA SCOTT
SQL>
As you can see I
effectively became the schema only account SCOTT meaning I can do whatever schema work is required but I actually logged in using the SIMON_DBA credentials. Incidentally this is how we should do maintenance on
all application accounts regardless of the version of Oracle being used (as long as it’s at least 10g). For more details on the proxy authenticated connection, refer to my earlier blog:
https://blog.pythian.com/the-power-of-the-oracle-database-proxy-authenticated-connections/
Alternatives from Earlier Releases
In releases prior to Oracle Database 18c we can effectively add somewhat similar functionality by either:- Locking the schema account
- Setting an impossible password
- Creating the user with external authorization using a certificate
Additional Thoughts
Another logical question might be whether container database "common accounts" can be created as "schema only"? The answer is "yes":SQL> create user c##tester no authentication;
User created.
SQL> select username, authentication_type, password_versions from dba_users where username = 'C##TESTER';
USERNAME AUTHENTICATION_TYPE PASSWORD_VERSIONS
-------------------- -------------------- -----------------
C##TESTER NONE
SQL> select con_id, username, authentication_type, password_versions from cdb_users where username = 'C##TESTER';
CON_ID USERNAME AUTHENTICATION_TYPE PASSWORD_VERSIONS
---------- -------------------- -------------------- -----------------
1 C##TESTER NONE
3 C##TESTER NONE
SQL> alter session set container = TEST1;
Session altered.
SQL> select username, authentication_type, password_versions from dba_users where username = 'C##TESTER';
USERNAME AUTHENTICATION_TYPE PASSWORD_VERSIONS
-------------------- -------------------- -----------------
C##TESTER NONE
SQL>
So really no difference if the user is common or local in the multitennant architecture. And one final point: schema only accounts are only applicable to RDBMS databases, not ASM databases.
