Day 6: Security

December 15, 2020

One of the often underappreciated, but crucially important features of any database management system is that of database security. The rationale is simple: the contents of the database are among the highest value targets in an organization to a threat actor and can contain a wealth of information including basic confidential employee information, legally protected personally identifiable information or even proprietary intellectual property of the organization. While a thorough treatise on properly securing an Oracle database is beyond the scope of this article, Oracle 18c/19c does provide some features that work towards inherently increasing database security by reducing the attack surface.

The Problem

Historically speaking, Oracle has not differentiated between the idea of a user account (accounts used to establish a connection to the database) and an application owner schema account an owner of the collection of tables, indexes, and other objects used to store the database data) except in a purely logical sense. While certain accounts were used as the ownership accounts for these database objects, they were identical to user accounts and each had their own password that could be used to login to the account. Over the evolution of the Oracle RDBMS, the number of these types of accounts has continued to grow and current releases have in excess of 30+ individual, distinct accounts (not including those created by third-party applications or individual sites for their own custom development). Breaching one of these accounts meant complete control of the database objects stored within that schema. Even worse, if the account had been setup without adhering to the principle of least privilege (DBA role, elevated system privileges, use of ANY privileges, etc.) then the impacts could be more far reaching, not contained to only the impacted schema. Unfortunately, that scenario is far more common than it should be.

Up until recently, the toolset to deal with this situation was limited to a handful of solutions; such as restricting the password to a trusted administrator and/or setting it to an incredibly complex password value, designed to thwart casual guesses. However, none of these solutions removed the core issue, which was that the schema was a login account that was still needed to make changes to the schema objects, preventing it from being permanently locked. Moreover, password protections at the database could be easily misconfigured to disable password locking, password expiration, and password lockouts leading to accounts whose passwords were set once and never changed – or even left at their well- documented default values. These became well-known vulnerability points that could be quietly attacked and used to gain an initial foothold in the database.

The Solution

Beginning in Oracle 18c, database accounts may be setup as Schema Only accounts in addition to traditional user accounts. The Schema Only account allow accounts to be created in the database which do not have a password associated with them, and, thus, cannot be logged into. This is done by the use of the NO AUTHENTICATION command syntax for the CREATE USER command and is able to be done for both administrative and non-administrative accounts (but only in database instances, not ASM instances).

Consider the following example:

SQL> create user normal_user identified by C0mpl3x#Passw0rd; 
User created.
SQL> create user hr_schema no authentication;
User created. 
SQL> set lines 1024
SQL> col username format a20
SQL> select username,
  2          account_status,
  3          authentication_type
  4  from dba_users
  5  where username in ('NORMAL_USER','HR_SCHEMA') 
  6  order by 1
USERNAME             ACCOUNT_STATUS                   AUTHENTI 
-------------------- -------------------------------- -------- 
HR_SCHEMA            OPEN                             NONE 
NORMAL_USER          OPEN                             PASSWORD

Above, two user accounts have been created: normal_user which can be authenticated to using the specified password to logon to the database, and hr_schema which is created as a user account on the database, but has no associated password (a Schema Only account). This configuration can be confirmed by viewing the AUTHENTICATION_TYPE column of DBA_USERS, which shows that normal_user has a traditional PASSWORD authentication mechanism, while hr_schema has NONE associated with it.

Without a password associated with the account, the hr_schema account serves only as a repository to contain databases objects (for example) and cannot be logged onto directly.

To receive the full 7 page article complete with code, please complete the form provided.  A pdf will be automatically emailed to you, so be sure to check your spam/junk mail if you do not see it.

    Full Name (required)

    Your Email (required)

    Company (required)

    Phone number (required)

    Job Title (required)