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 "N00Axxess4!Ever"
DEFAULT TABLESPACE uwdata
TEMPORARY TABLESPACE temp
QUOTA 10M ON uwdata;
GRANT dba TO dbadmin;
GRANT execute ON dbms_crypto TO dbadmin;
CREATE USER appadmin
IDENTIFIED BY "N00Axxess4!Ever"
TEMPORARY TABLESPACE temp
QUOTA 10M ON uwdata;
GRANT create session TO appadmin;
CREATE TABLE appadmin.user_msgs (
msg_type INTEGER,
message_text VARCHAR2(4000))
TABLESPACE uwdata;
GRANT insert, select, delete ON appadmin.user_msgs TO dbadmin;
CREATE TABLE dbadmin.user_auth_config (
param_name VARCHAR2(30),
param_value VARCHAR2(30))
TABLESPACE uwdata;
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))
TABLESPACE uwdata;
CREATE TABLE dbadmin.user_auth_schemas (
user_name VARCHAR2(30),
db_name VARCHAR2(30),
schema_name VARCHAR2(30),
read_write VARCHAR2(1) DEFAULT 'R')
TABLESPACE uwdata;
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))
TABLESPACE uwdata;
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; |
| TBD |
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) || sys.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;
/ |
| TBD |
exec dbadmin.user_auth_admin('APPADMIN');
SELECT *
FROM dba_roles;
SELECT *
FROM dba_role_privs
WHERE grantee = 'APPADMIN';
DROP ROLE selectall; |
| TBD |
|