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 DEFINER 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 it. |
SELECT dbadmin.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 use it in an attempt to log into a database. |
|
