Morgan's Library Oracle Database Creating Secure Passwords

Perform Secure Password Assignment Compliant with SOX and HIPAA

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 Utilizing Password Protected Roles

The issue, simply stated, is that we often have to allow access to secure systems and do so for people that are not in the same building we are. How to do so when you cannot, legally, give someone a password in an email or over the phone.

CREATE USER dbadmin
IDENTIFIED BY dbadmin
DEFAULT TABLESPACE uwdata
TEMPORARY TABLESPACE temp
QUOTA 10M ON uwdata;

GRANT dba TO dbadmin;

CREATE USER appadmin
IDENTIFIED BY appadmin
TEMPORARY TABLESPACE temp
QUOTA 10M ON users;

GRANT create session TO appadmin;

CREATE TABLE appadmin.user_msgs (
msg_type     INTEGER,
message_text VARCHAR2(4000));

GRANT insert, select, delete ON appadmin.user_msgs TO dbadmin;

CREATE TABLE dbadmin.user_auth_config (
param_name VARCHAR2(30),
param_value VARCHAR2(30));

INSERT INTO dbadmin.user_auth_config VALUES ('MAX_PRIV_GRANT_TIME','480');
COMMIT;

CREATE TABLE dbadmin.user_auth_approved_users (
user_name   VARCHAR2(30),
host_name   VARCHAR2(30),
email_addr VARCHAR2(50),
auth_ticket VARCHAR2(30),
mgmt_appr   VARCHAR2(30),
secur_appr VARCHAR2(30),
dba_appr    VARCHAR2(30));

CREATE TABLE dbadmin.user_auth_schemas (
user_name   VARCHAR2(30),
db_name     VARCHAR2(30),
schema_name VARCHAR2(30),
read_write VARCHAR2(1) DEFAULT 'R');

CREATE TABLE dbadmin.user_auth_use_audit (
username     VARCHAR2(30),
db_name      VARCHAR2(30),
schema_name VARCHAR2(30),
read_write   VARCHAR2(1),
beg_datetime TIMESTAMP(6),
end_datetime TIMESTAMP(6));

INSERT INTO appadmin.user_msgs VALUES (1, 'Welcome to Computing at MLib');
INSERT INTO appadmin.user_msgs VALUES (1, 'To change your password which you must do every 90 days ... beg us for help');

INSERT INTO dbadmin.user_auth_approved_users VALUES ('APPADMIN','BV9999','appadmin@mlib.com', 'ABC123', 'Dan Morgan', 'Dan Morgan', 'Dan Morgan');

INSERT INTO dbadmin.user_auth_schemas VALUES ('APPADMIN', 'D1A1', 'APPADMIN', 'W');
COMMIT;

CREATE OR REPLACE PROCEDURE dbadmin.user_auth_admin(pUserName IN VARCHAR2) AUTHID DEFINER IS
lRolePwd VARCHAR2(20);
lSQLStr VARCHAR2(1024);
BEGIN
-- get a password
lRolePwd := SUBSTR(pUserName,1,2) || dbms_crypto.randombytes(8);

-- create a role
lSQLStr := 'CREATE ROLE selectall IDENTIFIED BY ' || lRolePwd;
EXECUTE IMMEDIATE lSQLStr;
EXECUTE IMMEDIATE 'GRANT select any table TO selectall';
EXECUTE IMMEDIATE 'GRANT selectall TO ' || pUserName;

-- write user message
lSQLStr := 'INSERT INTO ' || pUserName || '.USER_MSGS VALUES (2, ''Type SET ROLE selectall IDENTIFIED BY ' || lRolePwd || ''')';

EXECUTE IMMEDIATE lSQLStr;
END user_auth_admin;
/

exec dbadmin.user_auth_admin('APPADMIN');

SELECT *
FROM dba_roles;

SELECT *
FROM dba_role_privs
WHERE grantee = 'APPADMIN';

DROP ROLE selectall;

Related Topics
DBMS_CRYPTO
Native Dyamic SQL
Roles
Users

Morgan's Library Page Footer

This site is maintained by Dan Morgan. Last Updated: