create and use a schema password no one knows?
 
Home

Resources
Library
How Can I?
Presentations
Links
Book Reviews
Downloads
User Groups


General
Contact Us
About Us
Services
Legal Notices & Terms of Use
Privacy Statement
A solution using DBMS_CRYPTO and DBMS_SCHEDULER
The biggest single problem with passwords: Database passwords, operating system passwords, PIN Number, etc. is that some human chooses it, knows it, writes it down, and may pass it along to others. Take the human out of the equation and you get a lot closer to security.

On my current assignment DOT NET developers are hard-coding passwords into Web Application servers: Security by appearance only.

So here's a simple implementation of a solution to the problem. My actual production implementation is far more complex and if not unbreakable, nothing is, a lot closer to the goal than where 99% of database implementations are today.

The heart of the solution is that any application, or user, wishing to connect to the database must first obtain the current password for a different schema by calling an "open" schema that provides access to nothing other than this one function.

The password received must then be used to log into another schema, very quickly, because the password is again changed in one second to a password unknown to any person.
CREATE OR REPLACE FUNCTION dbadmin.randombytes(byteType IN VARCHAR2)
RETURN VARCHAR2 AUTHID CURRENT_USER IS
 rStr VARCHAR2(50);
 btLen PLS_INTEGER;
 PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  rStr := dbms_crypto.randombytes(25);
  btLen := length(byteType);

  IF btLen = 3 THEN
    execute immediate 'ALTER USER pat IDENTIFIED BY "' || SUBSTRB(rStr,btLen,30) || '"';
    dbms_scheduler.create_job(
      job_name=>'A' || TO_CHAR(btLen),
      start_date=>SYSDATE+10/86400,
      enabled=>TRUE,
      auto_drop=>TRUE,
      job_type=>'PLSQL_BLOCK',
      job_action=>'DECLARE x VARCHAR2(30); BEGIN x := dbadmin.randombytes(''XXX''); END; ');
  ELSIF btLen = 4 THEN
    execute immediate 'ALTER USER addr IDENTIFIED BY "' || SUBSTRB(rStr,btLen,30) || '"';
    dbms_scheduler.create_job(
      job_name=>'A' || TO_CHAR(btLen),
      start_date=>SYSDATE+10/86400,
      enabled=>TRUE,
      auto_drop=>TRUE,
      job_type=>'PLSQL_BLOCK',
      job_action=>'DECLARE x VARCHAR2(30); BEGIN x := dbadmin.randombytes(''XXX''); END; ');
  ELSIF btLen = 5 THEN
    execute immediate 'ALTER USER opera IDENTIFIED BY "' || SUBSTRB(rStr,btLen,30) || '"';
    dbms_scheduler.create_job(
      job_name=>'A' || TO_CHAR(btLen),
      start_date=>SYSDATE+10/86400,
      enabled=>TRUE,
      auto_drop=>TRUE,
      job_type=>'PLSQL_BLOCK',
      job_action=>'DECLARE x VARCHAR2(30); BEGIN x := dbadmin.randombytes(''XXX''); END; ');
  END IF;
  RETURN rStr;
END randombytes;
/
Let's test is.
SELECT randombytes('ABC')
FROM dual;
A test on my laptop, not the fastest server on the planet, showed I can change the password 100 times in 2.84 seconds: A lot faster than any human is going to take a string of random bytes, decipher it, and then apply it to log in.
 
 
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2013 Daniel A. Morgan All Rights Reserved