Choosing a password scheme for the database

In the Security Guide there is a section to assist you with the decisions about what rules you might want to have in place when users choose passwords, namely attributes like the minimum length of a password, the types of characters it must (and must not) contain, re-use of old passwords etc etc. The documentation refers to a number of pre-supplied routines that are now available in 12c to assist administrators.  This is just a quick blog post to let you know that there is no “smoke and mirrors” going on here in terms of these functions. We’re implementing them in the same way that you might choose to build them yourself. In fact, you can readily take a look at exactly what the routines do because they are just simple PL/SQL code:


SQL> select text
  2   from dba_source
  3  where name in (
  4    'ORA12C_STRONG_VERIFY_FUNCTION'
  5    'ORA12C_VERIFY_FUNCTION',
  6    'ORA_COMPLEXITY_CHECK',
  7    'ORA_STRING_DISTANCE')
  8  order by name, line;

TEXT
----------------------------------------------------------------------------------------------------------------------------------
function ora12c_strong_verify_function
(username varchar2,
 password varchar2,
 old_password varchar2)
return boolean IS
   differ integer;
begin
   if not ora_complexity_check(password, chars => 9, upper => 2, lower => 2,
                           digit => 2, special => 2) then
      return(false);
   end if;

   -- Check if the password differs from the previous password by at least
   -- 4 characters
   if old_password is not null then
      differ := ora_string_distance(old_password, password);
      if differ < 4 then
         raise_application_error(-20032, 'Password should differ from previous '
                                 || 'password by at least 4 characters');
      end if;
   end if;

   return(true);
end;

FUNCTION ora12c_verify_function
(username varchar2,
 password varchar2,
 old_password varchar2)
RETURN boolean IS
   differ integer;
   db_name varchar2(40);
   i integer;
   reverse_user dbms_id;
   canon_username dbms_id := username;
BEGIN
   -- Bug 22369990: Dbms_Utility may not be available at this point, so switch
   -- to dynamic SQL to execute canonicalize procedure.
   IF (substr(username,1,1) = '"') THEN
     execute immediate 'begin dbms_utility.canonicalize(:p1,  :p2, 128); end;'
                        using IN username, OUT canon_username;
   END IF;
   IF NOT ora_complexity_check(password, chars => 8, letter => 1, digit => 1,
                               special => 1) THEN
      RETURN(FALSE);
   END IF;

   -- Check if the password contains the username
   IF regexp_instr(password, canon_username, 1, 1, 0, 'i') > 0 THEN
     raise_application_error(-20002, 'Password contains the username');
   END IF;

   -- Check if the password contains the username reversed
   FOR i in REVERSE 1..length(canon_username) LOOP
     reverse_user := reverse_user || substr(canon_username, i, 1);
   END LOOP;
   IF regexp_instr(password, reverse_user, 1, 1, 0, 'i') > 0 THEN
     raise_application_error(-20003, 'Password contains the username ' ||
                                     'reversed');
   END IF;

   -- Check if the password contains the server name
   select name into db_name from sys.v$database;
   IF regexp_instr(password, db_name, 1, 1, 0, 'i') > 0 THEN
      raise_application_error(-20004, 'Password contains the server name');
   END IF;

   -- Check if the password contains 'oracle'
   IF regexp_instr(password, 'oracle', 1, 1, 0, 'i') > 0 THEN
        raise_application_error(-20006, 'Password too simple');
   END IF;

   -- Check if the password differs from the previous password by at least
   -- 3 characters
   IF old_password IS NOT NULL THEN
     differ := ora_string_distance(old_password, password);
     IF differ < 3 THEN
        raise_application_error(-20010, 'Password should differ from the '
                                || 'old password by at least 3 characters');
     END IF;
   END IF ;

   RETURN(TRUE);
END;

function ora_complexity_check
(password varchar2,
 chars integer := null,
 letter integer := null,
 upper integer := null,
 lower integer := null,
 digit integer := null,
 special integer := null)
return boolean is
   digit_array varchar2(10) := '0123456789';
   alpha_array varchar2(26) := 'abcdefghijklmnopqrstuvwxyz';
   cnt_letter integer := 0;
   cnt_upper integer := 0;
   cnt_lower integer := 0;
   cnt_digit integer := 0;
   cnt_special integer := 0;
   delimiter boolean := false;
   len integer := nvl (length(password), 0);
   i integer ;
   ch char(1);
begin
   -- Check that the password length does not exceed 2 * (max DB pwd len)
   -- The maximum length of any DB User password is 128 bytes.
   -- This limit improves the performance of the Edit Distance calculation
   -- between old and new passwords.
   if len > 256 then
      raise_application_error(-20020, 'Password length more than 256 characters');
   end if;

   -- Classify each character in the password.
   for i in 1..len loop
      ch := substr(password, i, 1);
      if ch = '"' then
         delimiter := true;
      elsif instr(digit_array, ch) > 0 then
         cnt_digit := cnt_digit + 1;
      elsif instr(alpha_array, nls_lower(ch)) > 0 then
         cnt_letter := cnt_letter + 1;
         if ch = nls_lower(ch) then
            cnt_lower := cnt_lower + 1;
         else
            cnt_upper := cnt_upper + 1;
         end if;
      else
         cnt_special := cnt_special + 1;
      end if;
   end loop;

   if delimiter = true then
      raise_application_error(-20012, 'password must NOT contain a '
                               || 'double-quotation mark which is '
                               || 'reserved as a password delimiter');
   end if;
   if chars is not null and len < chars then
      raise_application_error(-20001, 'Password length less than ' ||
                              chars);
   end if;

   if letter is not null and cnt_letter < letter then
      raise_application_error(-20022, 'Password must contain at least ' ||
                                      letter || ' letter(s)');
   end if;
   if upper is not null and cnt_upper < upper then
      raise_application_error(-20023, 'Password must contain at least ' ||
                                      upper || ' uppercase character(s)');
   end if;
   if lower is not null and cnt_lower < lower then
      raise_application_error(-20024, 'Password must contain at least ' ||
                                      lower || ' lowercase character(s)');
   end if;
   if digit is not null and cnt_digit < digit then
      raise_application_error(-20025, 'Password must contain at least ' ||
                                      digit || ' digit(s)');
   end if;
   if special is not null and cnt_special < special then
      raise_application_error(-20026, 'Password must contain at least ' ||
                                      special || ' special character(s)');
   end if;

   return(true);
end;

function ora_string_distance
(s varchar2,
 t varchar2)
return integer is
   s_len    integer := nvl (length(s), 0);
   t_len    integer := nvl (length(t), 0);
   type arr_type is table of number index by binary_integer;
   d_col    arr_type ;
   dist     integer := 0;
begin
   if s_len = 0 then
      dist := t_len;
   elsif t_len = 0 then
      dist := s_len;
   -- Bug 18237713 : If source or target length exceeds max DB password length
   -- that is 128 bytes, then raise exception.
   elsif t_len > 128 or s_len > 128 then
     raise_application_error(-20027,'Password length more than 128 bytes');
   elsif s = t then
     return(0);
   else
      for j in 1 .. (t_len+1) * (s_len+1) - 1 loop
          d_col(j) := 0 ;
      end loop;
      for i in 0 .. s_len loop
          d_col(i) := i;
      end loop;
      for j IN 1 .. t_len loop
          d_col(j * (s_len + 1)) := j;
      end loop;

      for i in 1.. s_len loop
        for j IN 1 .. t_len loop
          if substr(s, i, 1) = substr(t, j, 1)
          then
             d_col(j * (s_len + 1) + i) := d_col((j-1) * (s_len+1) + i-1) ;
          else
             d_col(j * (s_len + 1) + i) := LEAST (
                       d_col( j * (s_len+1) + (i-1)) + 1,      -- Deletion
                       d_col((j-1) * (s_len+1) + i) + 1,       -- Insertion
                       d_col((j-1) * (s_len+1) + i-1) + 1 ) ;  -- Substitution
          end if ;
        end loop;
      end loop;
      dist :=  d_col(t_len * (s_len+1) + s_len);
   end if;

   return (dist);
end;

The good thing about this is that:

  • the routines are transparent, which is how all good security models should be presented. Because then they are open to scrutiny and not reliant on any kind of obfuscation to be secure, and 
  • if the routines do not exactly meet your requirements, then you now have a well established starting point from which to build your own custom routines.

Of course, if you are just after a string distance function, you might be better off using the pre-supplied UTL_MATCH package.

Quick tip–database link passwords

If you are relying on database links in your application, think carefully about how you want to manage the accounts that you connect with, in particular, when it comes to password expiry.

With a standard connect request to the database, if your password is going to expire soon, you will get some feedback on this:



SQL> conn demo/demo@np12
ERROR:
ORA-28002: the password will expire within 6 days


Connected.


But when using those same credentials via a database link, you will not get any warning, so when that password expires…you might be dead in the water.



SQL> create database link demolink connect to demo identified by demo using 'np12';

Database link created.

SQL> select * from tab@demolink;

TNAME
------------------------------------------------------------------------------------------------
TABTYPE  CLUSTERID
------- ----------
EMPLOYEES
TABLE

Better to be safe than sorry…

I’ve always been worried about taking a script that is fine to run in my non-production environments (in particular a DROP script) and accidentally running it in a Production environment, shortly followed by the typing up of a fresh resume to look for a new job once the mistake is discovered Smile

The standard means of a “least privilege required” model when it comes to Production access is a good step along this road to protecting the database. But ultimately, at one time or another, an administrator with appropriate privileges, good intentions, but (say) bad cut-paste skills might inadvertently run a drop command against a database they did not want to.  (A well publicised example of this happened quite recently https://www.theregister.co.uk/2017/02/01/gitlab_data_loss/)

So it would be nice to have a method of adding a ‘double check’ facility so that we can guard against a DROP being run on your production system inadvertently. That way, even if you did  run that drop script against Production, you would be “saved from yourself”. Here’s one such possible means you might want to consider. We’ll use a context variable that has to be set in order for DROP commands to be processed.


--
-- Our context
--
SQL> create context DDL_CHECK using ddl_checker;

Context created.

--
-- A routinte to set it
--
SQL> create or replace
  2  procedure ddl_checker(p_val varchar2) is
  3  begin
  4    dbms_session.set_context('DDL_CHECK','ENABLED',p_val);
  5  end;
  6  /

Procedure created.

--
-- If we are on the Production database, DROP DDL's only proceed is the context variable is set
--
SQL> create or replace
  2  trigger ddl_checker_before
  3  before drop on scott.schema
  4  begin
  5    if sys.database_name like '%PROD%' and sys_context('DDL_CHECK','ENABLED') is null
  6    then
  7      raise_application_error(-20000,'DDL confirmation context is not set');
  8    end if;
  9  end;
 10  /

Trigger created.

--
-- And after every DROP, the context is reset for the next use so it cannot be left on inadvertently
--
SQL> create or replace
  2  trigger ddl_checker_after
  3  after drop on scott.schema
  4  begin
  5    ddl_checker(null);
  6  end;
  7  /

Trigger created.

SQL>
SQL>
SQL> grant execute on ddl_checker to scott;

Grant succeeded.

SQL>

So now we can see what happens when SCOTT attempts some DDL in Production by mistake.


SQL> conn scott/tiger
Connected.

SQL> drop table t purge;
drop table t purge
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: DDL confirmation context is not set
ORA-06512: at line 4

--
-- We must explicitly express our intention to perform a DDL
--
SQL> exec admin.ddl_checker('true')

PL/SQL procedure successfully completed.

--
-- and only then are we allowed to run a DROP command
--
SQL> drop table t purge;

Table dropped.

--
-- and in doing so, we don't have any lingering access
--
SQL> drop table t1 purge;
drop table t1 purge
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: DDL confirmation context is not set
ORA-06512: at line 4


SQL>

I stress – this is not about securing administrator access or replacing your existing processes for auditing or locking down the privileges on your production systems. This is just about that extra check to help your over-worked and/or over-tired administrators from committing a small mistake that becomes a catastrophic error.

Connection shortcuts with a wallet

I’m lazy when I connect to the database, especially on my laptop.  Anything that saves a few keystrokes I’m keen on Smile

So rather than type “sqlplus username/password@database” I like to take advantage of a wallet

In my private sqlnet.ora, or the global one if it makes sense to do so, I add the details of wallet



SQLNET.WALLET_OVERRIDE=TRUE
WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=c:\oracle\wallet)))

This tells Oracle, that when I begins a connection to the database, we will look in directory “c:\oracle\wallet” for wallet entries, which I’ll now create



C:\oracle\wallet>mkstore -create -wrl c:\oracle\wallet
Oracle Secret Store Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Enter password: ********
Enter password again: ********

This creates an empty wallet.  You’ll need a password for the wallet, which is required whenever maintenance to the wallet is required.  Then I add an entry to the wallet for my database, in this case, “MYDB”


C:\oracle\wallet>mkstore -wrl c:\oracle\wallet -createCredential MYDB scott tiger
Oracle Secret Store Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Enter wallet password: ********
Create credential oracle.security.client.connect_string1

I also have a MYDB entry in my tnsnames.ora

MYDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1525))
)
(CONNECT_DATA =
(SERVICE_NAME = np12)
)
)

At this point, we can now connect securely to the database without ever requiring a password to be hard coded on command line, or in an environment variable etc.

The wallet is consulted, which maps MYDB to scott/tiger, and also maps to MYDB in tnsnames.ora, and the two then form the database connection.

C:\oracle\wallet>sqlplus /@mydb

SQL*Plus: Release 12.1.0.2.0 Production on Mon Sep 21 16:22:43 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Thu Sep 10 2015 11:40:45 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> show user
USER is “SCOTT”

You can list the credentials stored in the wallet using

mkstore -wrl c:\oracle\wallet -listCredential

When prompted for a password, enter the password used to create the wallet.

So for me, its about saving on keystrokes, but you can see how using a wallet, also means you can now avoid hard-coding passwords in any of your applications or scripts.  There are also additional features for wallets which you can use to prohibit them from being copied across machines etc.

Upgraded and can’t see the tables you could before ?

If you take a look at the “alter user” command in the old 9i documentation, you’ll see this:

DEFAULT ROLE Clause

Specify the roles granted by default to the user at logon. This clause can contain only roles that have been granted directly to the user with a GRANT statement. You cannot use the DEFAULT ROLE clause to enable:

  • Roles not granted to the user
  • Roles granted through other roles
  • Roles managed by an external service (such as the operating system), or by the Oracle Internet Directory

Oracle enables default roles at logon without requiring the user to specify their passwords.

And if you do a similar examination of the 10g documentation, its pretty much the same, with some more elaboration.

DEFAULT ROLE Clause

Specify the roles granted by default to the user at logon. This clause can contain only roles that have been granted directly to the user with a GRANT statement. You cannot use the DEFAULT ROLE clause to enable:

  • Roles not granted to the user
  • Roles granted through other roles
  • Roles managed by an external service (such as the operating system), or by the Oracle Internet Directory

Oracle Database enables default roles at logon without requiring the user to specify their passwords or otherwise be authenticated. If you have granted an application role to the user, you should use the DEFAULT ROLE ALL EXCEPT role clause to ensure that, in subsequent logons by the user, the role will not be enabled except by applications using the authorized package.

So lets take a look at the 11.2 documentation for the same section

DEFAULT ROLE Clause

Specify the roles granted by default to the user at logon. This clause can contain only roles that have been granted directly to the user with a GRANT statement, or roles created by the user with the CREATE ROLE privilege. You cannot use the DEFAULT ROLE clause to specify:

  • Roles not granted to the user
  • Roles granted through other roles
  • Roles managed by an external service (such as the operating system), or by the Oracle Internet Directory
  • Roles that are enabled by the SET ROLE statement, such as password-authenticated roles and secure application roles

Notice the new 4th bullet point, which has replaced the handling of default roles from previous versions. If a role is password protected, then even if it is set as a default role, then it will not be enabled automatically. We can see this with a simple example:

SQL> conn / as sysdba

Connected.

SQL> create user NO_PRIVS identified by blah;

User created.

SQL> grant create session to NO_PRIVS;

Grant succeeded.

SQL> create role SUPER_DOOPER identified by SECRET_PASSWORD;

Role created.

SQL> create role DOES_NOTHING;

Role created.

SQL> grant delete any table, select any table to SUPER_DOOPER;

Grant succeeded.

SQL> grant SUPER_DOOPER  to NO_PRIVS;

Grant succeeded.

SQL> grant DOES_NOTHING to NO_PRIVS;

Grant succeeded.

SQL> alter user NO_PRIVS default role all;

User altered.

SQL> conn NO_PRIVS/blah

Connected.

SQL> select count(*) from hr.employees;

select count(*) from hr.employees

                        *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL>

SQL> set role SUPER_DOOPER identified by SECRET_PASSWORD;

Role set.

SQL> select count(*) from hr.employees;

  COUNT(*)

----------

       107

SQL>

12.1.0.2 security grrr…

One of my favourite security “tricks” used to be the following:

SQL> [create|alter] user MY_USER identified by values 'impossible';

Looks odd, but by setting the encrypted value of someone’s password to something that it is impossible to encrypt to, means you’ll never be able to connect as that account. (Think schema’s owning objects etc).

I hear you ask: “Why not just lock the account?”

Well…in my opinion, that’s a security hole. Let’s say Oracle publishes a security bug concerning (say) the MDSYS schema. As a hacker, I’d like to know if a database has the MDSYS schema. All I need do is:

SQL> connect MDSYS/nonsense

Why is that a security hole ? Because I wont get “Invalid username or password”. I’ll get “ORA-28000: the account is locked” and voila…Now I know that the MDSYS user is present in that database.

Setting a user password to a impossible encrypted value, means a hacker will never know if the user account exists at all, because the error returned is the familiar “ORA-01017: invalid username/password; logon denied”

But looked what’s happened in 12.1.0.2 😦

SQL> create user MY_USER identified by values 'impossible';
create user MY_USER identified by values 'impossible'
*
ERROR at line 1:
ORA-02153: invalid VALUES password string

A backward step in my opinion…

Upgrade to 12c … credentials

We did a “real” upgrade to 12c this weekend, where “real” means a production system, as opposed to my laptop, a play VM etc etc 🙂

It all went relatively smoothly except for one interesting thing, that I can’t 100% say was caused by the upgrade, but it would appear to be the case.

After the upgrade, our scheduler external jobs started failing. A quick look in the alert log revealed:

Sun Jun 29 09:26:11 2014
ORA-12012: error on auto execute of job "FIRE_SCHED_DAILY"
ORA-27496: credential "LOCAL_OS_ACCT" is disabled

So its possible (not proven) that upgrading to 12c might disable credentials. In this particular case, the database went from standalone to a pluggable database.

The remedy was the simply drop and recreate the credential