Oracle Database 18c schema only accounts

Written by Simon Pane | Mar 16, 2018 4:00:00 AM

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

Locking is not ideal as locked accounts may be unlocked (i.e. during refreshes, application/schema upgrades or modifications, etc). So it runs the risk of being unlocked and hence accessed. Additionally, a locked schema gives a bad actor information that the schema does indeed exist (and hence may be worth trying to brute force attack). Therefore effectively locking by setting an impossible password may be a bit better. A further discussion of this can be found here: https://blog.pythian.com/locking-oracle-accounts/ And finally, we can create database users with external authentication via a certificate. This is probably the closest match in functionality with the minimum risk of accidental or inadvertent modifications permitting password based schema access.

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.

Conclusion

This isn't the most extravagant new feature of Oracle Database 18c. But it’s simple and adds some nice new functionality. It makes the ability to create truly password-less application schemas trivially simple. There were similar techniques that could be used with previous database releases but those have some limitations or risks - this approach seems simpler and better.

Oracle Database Consulting Services

Ready to optimize your Oracle Database for the future?

View full post