Oracle Database 18c schema only accounts

Posted in: Oracle

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:

  1. Locking the schema account
  2. Setting an impossible password
  3. 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.

email

Interested in working with Simon? Schedule a tech call.

About the Author

Simon describes himself as a technology enthusiast who is passionate about collecting and sharing interesting database tips. If you want to see his eyes light up, let him teach you something new. Based out of Calgary, Alberta, Simon is known for his contributions to various online Oracle communities, and being very thorough in his work. A self-proclaimed stereotypical Canadian, Simon can be found watching hockey with his family in his spare time.

1 Comment. Leave new

ForgotThePlot
April 25, 2018 3:35 am

It’s interesting, will it be possible in the future to give the grants for SELECT/UPSERT/DELETE to the whole non-authenticated schema?

Something like “grant SELECT on SCOTT.* to USER1” instead of using ugly individual grants.

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *