Hello :)
We as DBA all the time thinking how to avoid doing the same task twice by implementing different kind of improvement like automation scripts.
Case from today is following...
For internal purposes there are requirements to implement process for Oracle DB account creation and password creation with special mask.
This user account management process have been created by using 1 function and 1 procedure, function will generate password in randomly by using special password mask and procedure for actual user creation.
By simple search in google I found following idea for password generate function http://www.moeding.net/archives/27-Another-PLSQL-password-generator.html
Start with the implementation of:
1. Procedure for user creation..
2. Function for passowrd generate
This is my first draft of such procedure and if you find someting wrong let me know in order to fix it.
The idea of procedure is to create user with special name starting with SQL_<DEPARTMENT>_NAME, adding department name in the name of the db user, also using SOX profile which is preliminary create in order to following some policy and as final point .. in case of user existance insted of using CREATE steitement to initiate ALTER which will change password :) ... ops also one of the functonality is that user may ask to use special password which will be added as value during the procedure execution.
Also as you can see we have special table USER_ACTION which will hold information for the user like USERNAME, ACCOUNT_TYPE, PASSWORD,CREATE_DATE,EXP_DATE - the main purpuses of all this is by using EXP_DATE to control when user should be locked based on user imput information.
|
create or replace procedure create_user_with_random_pwd (uname IN VARCHAR2, exp_date DATE DEFAULT NULL, acc_type IN VARCHAR2 DEFAULT NULL, old_password IN VARCHAR2 DEFAULT NULL)
authid current_user
is
pwd varchar2(1000);
v_old_password varchar2(1000);
v_acc_type varchar2(1000);
db_name v$database.name%TYPE;
v_uname VARCHAR2(30);
v_temp VARCHAR2(30);
e_user_already_exists EXCEPTION;
PRAGMA EXCEPTION_INIT (e_user_already_exists, -1920);
begin
select NAME into db_name from v$database;
begin
select TEMPORARY_TABLESPACE into v_temp from dba_users where USERNAME like 'SQL_%' and rownum =1;
EXCEPTION
WHEN OTHERS THEN
v_temp := 'TEMP';
end;
if acc_type is NULL then
v_acc_type := 'IT';
else
v_acc_type := 'SUPPORT';
end if;
v_old_password := old_password;
if old_password is NULL then
pwd := genpassword(9,7,1);
else
pwd := v_old_password;
end if;
v_uname := 'SQL_'||v_acc_type||'_'||uname;
EXECUTE IMMEDIATE 'CREATE USER '||v_uname||' IDENTIFIED BY "'||pwd||'" DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE '||v_temp||' PROFILE "SOX_USER"';
DBMS_OUTPUT.PUT_LINE(db_name||' '||v_uname||','||v_acc_type||','||pwd||',SYSDATE,'||exp_date);
INSERT INTO USER_ACTION (USERNAME, ACCOUNT_TYPE, PASSWORD,CREATE_DATE,EXP_DATE) VALUES (v_uname, v_acc_type, pwd,SYSDATE,exp_date);
commit;
DBMS_OUTPUT.PUT_LINE('Database '||db_name||' | Username: '||v_uname||' | have been created with password: '||pwd);
EXCEPTION
WHEN e_user_already_exists THEN
DBMS_OUTPUT.PUT_LINE ('User exists, ignored');
DELETE from USER_ACTION where USERNAME = v_uname;
commit;
EXECUTE IMMEDIATE 'ALTER USER '||v_uname||' IDENTIFIED BY "'||pwd||'" DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "'||v_temp||'" PROFILE "SOX_USER"';
EXECUTE IMMEDIATE 'ALTER USER '||v_uname||' account unlock';
DBMS_OUTPUT.PUT_LINE(db_name||' '||v_uname||','||v_acc_type||','||pwd||',SYSDATE,'||exp_date);
INSERT INTO USER_ACTION (USERNAME, ACCOUNT_TYPE, PASSWORD,CREATE_DATE,EXP_DATE) VALUES (v_uname, v_acc_type, pwd,SYSDATE,exp_date);
commit;
dbms_output.put_line('Database '||db_name||' | Username: '||v_uname||' | have been altered with password: '||pwd);
end;
/
|
show error
2. Password generate function
|
-- ---------------------------------------------------------------------------
--
-- $Id$
--
-- Copyright (c) 2011 Stefan Moeding, http://www.moeding.net/
-- All rights reserved.
--
-- Redistribution and use in source and binary forms, with or without
-- modification, are permitted provided that the following conditions
-- are met:
-- 1. Redistributions of source code must retain the above copyright
-- notice, this list of conditions and the following disclaimer.
-- 2. Redistributions in binary form must reproduce the above copyright
-- notice, this list of conditions and the following disclaimer in the
-- documentation and/or other materials provided with the distribution.
--
-- THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS ``AS IS'' AND
-- ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
-- IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
-- ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS BE LIABLE
-- FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
-- DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS
-- OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION)
-- HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
-- LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY
-- OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
-- SUCH DAMAGE.
--
CREATE OR REPLACE FUNCTION GenPassword (
i_chars_chr PLS_INTEGER DEFAULT 5,
i_chars_num PLS_INTEGER DEFAULT 2,
i_chars_spc PLS_INTEGER DEFAULT 1
) RETURN VARCHAR2
IS
-- GenPassword - Generate a reasonable secure password
--
-- Parameter:
-- i_chars_chr : number of alpha characters in the password
-- i_chars_num : number of numeric characters in the password
-- i_chars_spc : number of special characters in the password
--
-- Valid ranges are between 0 and 20 for every input parameter.
-- Characters that get easily mixed up ('1' and 'l' or 'I',
-- '0' and 'O', ...) are left out.
--
-- Example usage:
-- SQL> select genpassword() from dual;
--
-- GENPASSWORD()
-- ------------------------------------
-- MXbjA6%4
-- --- here in case of special policy you can add or remove some of the special characters
l_chr VARCHAR2(60) := 'abcdefhijkmnoprstuvwxyzABCDEFGHIJKLMNPQRSTUVWXYZ';
l_num VARCHAR2(60) := '23456789';
l_spc VARCHAR2(60) := '!$%&/()=?+*#-@:<>';
--
l_pwd VARCHAR2(60) := '';
l_sel VARCHAR2(60) := '';
BEGIN
--
-- Raise an error if the input is out of sensible bounds
--
IF (i_chars_chr NOT BETWEEN 0 AND 20) OR
(i_chars_num NOT BETWEEN 0 AND 20) OR
(i_chars_spc NOT BETWEEN 0 AND 20) THEN
RAISE value_error;
END IF;
--
l_sel := l_sel||rpad('a', i_chars_chr, 'a');
l_sel := l_sel||rpad('n', i_chars_num, 'n');
l_sel := l_sel||rpad('s', i_chars_spc, 's');
--
-- Loop over selector in random order and build the password by
-- choosing a random character from the class denoted by the
-- selector.
--
FOR rec IN (SELECT level
FROM dual
CONNECT BY level <= length(l_sel)
ORDER BY DBMS_RANDOM.value())
LOOP
CASE substr(l_sel, rec.level, 1)
WHEN 'a' THEN
l_pwd := l_pwd||substr(l_chr, DBMS_RANDOM.value(1, length(l_chr)), 1);
WHEN 'n' THEN
l_pwd := l_pwd||substr(l_num, DBMS_RANDOM.value(1, length(l_num)), 1);
WHEN 's' THEN
l_pwd := l_pwd||substr(l_spc, DBMS_RANDOM.value(1, length(l_spc)), 1);
ELSE
NULL;
END CASE;
END LOOP;
--
RETURN l_pwd;
END GenPassword;
/
show error
|
I start with testing on the procedure on multily database by using shell script to create users on following way.
| execute create_user_with_random_pwd('TESTUSER',TO_DATE('31/12/2017','DD/MM/YYYY HH:MI:SS'),''); |
This will create user TESTUSER and will add information into USER_ACTION take when the account should be locked.
( just for info ,separate process checking USER ACTION takes on daily bases and trigger action or not depending on SYSDATE=EXP_DATE
in SYSDATE=EXP_DATE executing ALTER USER ACCOUNT LOCK; )
TESTING:
|
[oracle@lab]$ ./test_password_generate.sh
TESTDB1
GENPASSWORD(9,7,1)
--------------------------------------------------------------------------------
y748aB7B4%AVhJb46
TESTDB2
GENPASSWORD(9,7,1)
--------------------------------------------------------------------------------
7V6YR-4thWi7248Tv
|
Password generate working as expected.. but whats happaning if your connecting is too fast:)
Passwrod have been generated with the same value.... OMG how come this... we are using RANDOM function....
|
[oracle@lab]$ ./test_password_generate.sh
TESTDB1
GENPASSWORD(9,7,1)
--------------------------------------------------------------------------------
6TyD8T8pU5@3sc35D
TESTDB2
GENPASSWORD(9,7,1)
--------------------------------------------------------------------------------
6TyD8T8pU5@3sc35D
|
Documentation:
12.1
http://docs.oracle.com/database/121/ARPLS/d_random.htm#ARPLS040
Operational notes
-
The RANDOM function produces integers in the range [-2^^31, 2^^31).
-
The VALUE function produces numbers in the range [0,1) with 38 digits of precision.
DBMS_RANDOM can be explicitly initialized but does not require initialization before a call to the random number generator. It automatically initializes with the date, user ID, and process ID if no explicit initialization is performed.
If this package is seeded twice with the same seed, then accessed in the same way, it produces the same result in both cases.
In some cases, such as when testing, you may want the sequence of random numbers to be the same on every run. In that case, you seed the generator with a constant value by calling an overload of SEED.
To produce different output for every run, simply omit the seed call. Then the system chooses a suitable seed for you.
11.2
Operational Notes
-
DBMS_RANDOM.RANDOM produces integers in [-2^^31, 2^^31).
-
DBMS_RANDOM.VALUE produces numbers in [0,1) with 38 digits of precision.
DBMS_RANDOM can be explicitly initialized, but does not need to be initialized before calling the random number generator. It will automatically initialize with the date, user ID, and process ID if no explicit initialization is performed.
If this package is seeded twice with the same seed, then accessed in the same way, it will produce the same results in both cases.
In some cases, such as when testing, you may want the sequence of random numbers to be the same on every run. In that case, you seed the generator with a constant value by calling one of the overloads of DBMS_RANDOM.SEED.
To produce different output for every run, simply to omit the call to "Seed" and the system will choose a suitable seed for you.
by checking following blog http://oracle-base.com i find the solution to make my procedure more deep in the RANDUM process.
What i did as changes on the procedure
|
CREATE OR REPLACE FUNCTION GenPassword (
i_chars_chr PLS_INTEGER DEFAULT 5,
i_chars_num PLS_INTEGER DEFAULT 2,
i_chars_spc PLS_INTEGER DEFAULT 1
) RETURN VARCHAR2
IS
-- GenPassword - Generate a reasonable secure password
--
-- Parameter:
-- i_chars_chr : number of alpha characters in the password
-- i_chars_num : number of numeric characters in the password
-- i_chars_spc : number of special characters in the password
--
-- Valid ranges are between 0 and 20 for every input parameter.
-- Characters that get easily mixed up ('1' and 'l' or 'I',
-- '0' and 'O', ...) are left out.
--
-- Example usage:
-- SQL> select genpassword() from dual;
--
-- GENPASSWORD()
-- ------------------------------------
-- MXbjA6%4
--
l_seed VARCHAR2(100) := TO_CHAR(SYSTIMESTAMP,'YYYYDDMMHH24MISSFFFF');
l_chr VARCHAR2(60) := 'abcdefhijkmnoprstuvwxyzABCDEFGHIJKLMNPQRSTUVWXYZ';
l_num VARCHAR2(60) := '23456789';
l_spc VARCHAR2(60) := '!$%&/()=?+*#-@:<>';
--
l_pwd VARCHAR2(60) := '';
l_sel VARCHAR2(60) := '';
BEGIN
--
-- Raise an error if the input is out of sensible bounds
--
IF (i_chars_chr NOT BETWEEN 0 AND 20) OR
(i_chars_num NOT BETWEEN 0 AND 20) OR
(i_chars_spc NOT BETWEEN 0 AND 20) THEN
RAISE value_error;
END IF;
--
l_sel := l_sel||rpad('a', i_chars_chr, 'a');
l_sel := l_sel||rpad('n', i_chars_num, 'n');
l_sel := l_sel||rpad('s', i_chars_spc, 's');
--
-- Loop over selector in random order and build the password by
-- choosing a random character from the class denoted by the
-- selector.
--
DBMS_RANDOM.seed (val => l_seed);
FOR rec IN (SELECT level
FROM dual
CONNECT BY level <= length(l_sel)
ORDER BY DBMS_RANDOM.value())
LOOP
CASE substr(l_sel, rec.level, 1)
WHEN 'a' THEN
l_pwd := l_pwd||substr(l_chr, DBMS_RANDOM.value(1, length(l_chr)), 1);
WHEN 'n' THEN
l_pwd := l_pwd||substr(l_num, DBMS_RANDOM.value(1, length(l_num)), 1);
WHEN 's' THEN
l_pwd := l_pwd||substr(l_spc, DBMS_RANDOM.value(1, length(l_spc)), 1);
ELSE
NULL;
END CASE;
END LOOP;
--
RETURN l_pwd;
END GenPassword;
/
show error
|
So, random is not really random all the time :)
btw: for 12c you can check following link
Have fun.
