Oracle generates random passwords

Requirement: You need to change the password periodically. The password must be 1 and 11 digits. 2. It must contain uppercase/lowercase letters, numbers, and special characters. 3. exclude special characters such as (), @, and (),@,&

A random password package dbms_random can be generated in the oracle database, but it is inconvenient to use and cannot meet the above requirements. Some web tools or small software are not easy to use.

Therefore, I want to write functions in oracle and create database tables to facilitate password management. Data has a natural advantage in doing these tasks.

Step 1: Create a password management table in the database.

Example: generate random passwords for oracle and grid users in Linux

Create table HOST_TAB (hostid number, HOSTNAME VARCHAR2 (100), DESCRIPTION VARCHAR2 (2000); -- CREATE a business system list

Create table PASSWORD_TAB (hostid number, ORACLE_PASSWORD VARCHAR2 (100), GRID_PASSWORD VARCHAR2 (100), SHELL_TEXT VARCHAR2 (100), CHANGE_DATE DATE); -- CREATE a password maintenance TABLE

Step 2: Create a function that can generate a random password.

Googel casually. Some foreign friends wrote their own functions and worshipped them... The original Article address is as follows: at the same time, users who want to copy the article will describe the source when reprinting the blog.

Http://mahmoudoracle.blogspot.tw/2012/08/generate-random-password-in-oracle.html

If you do not want to use the function written by this buddy, you can also learn the dbms_random.string function of the oracle database and learn and modify the code as follows: I have to say that oracle is worth learning.

Create or replace function random_password (password_num in varchar2)
RETURN VARCHAR2
PARALLEL_ENABLE is
Optx char (1 );
Rng NUMBER;
N BINARY_INTEGER;
Ccs VARCHAR2 (128); -- candidate character subset
Xstr VARCHAR2 (4000 );
BEGIN
FOR I IN 1 .. length (password_num) LOOP
/* Get random integer within specified range */
N: = TRUNC (rng * dbms_random.value) + 1;
/* Append character to random_password2 */
Xstr: = xstr | SUBSTR (ccs, n, 1 );
Optx: = SUBSTR (password_num, I, 1 );
IF optx = 'U' THEN
-- Upper case alpha characters only
Ccs: = 'abcdefghijklmnopqrstuvwxy ';
Rng: = 26;
ELSIF optx = 'l' THEN
-- Lower case alpha characters only
Ccs: = 'abcdefghijklmnopqrstuvwxy ';
Rng: = 26;
ELSIF optx = 'A' THEN
-- Alpha characters only (mixed case)
Ccs: = 'abcdefghijklmnopqrstuvwxy' | 'abcdefghijklmnopqrstuvwxy ';
Rng: = 52;
ELSIF optx = 'n' THEN
-- Any numeric characters (upper)
Ccs: = '000000 ';
Rng: = 10;
ELSIF optx = 'X' THEN
-- Any special characters (upper)
Ccs: = '! "# $ % & () * +,-./:; <=>? @';
Rng: = 23;
ELSIF optx = 'P' THEN
-- Any printable char (ASCII subset)
Ccs: = '! "# $ % &'' () * +,-./'| '000000' |':; <=>? @ '|
'Abcdefghijklmnopqrstuvwxy' | '[\] ^ _ ''|
'Abcdefghijklmnopqrstuvwxy' | '{| }~ ';
Rng: = 95;
ELSE
Ccs: = 'abcdefghijklmnopqrstuvwxy ';
Rng: = 26; -- default to upper case
End if;

End loop;
RETURN xstr;
END random_password;

Step 3: Use stored procedures. Create a database scheduling task and change the password periodically.

Create or replace procedure change_passwords (host_name varchar2)

Cursor v_cur is
Select t1.hostid from host_tab t1 where t1.hostname = lower (host_name );
Begin
For v_ I in v_cur loop
Begin
Insert into password_tab
(Hostid, oracle_password, grid_password, shell_text, change_date)
Values
(V_ I .hostid,
'',
'',
'Echo oracle: '| RANDOM_PASSWORD ('ulaxpnlaxpnl') |
'| Chpasswd & echo grid:' | RANDOM_PASSWORD ('ulaxpnlaxpnl') |
'| Chpasswd ',
Sysdate );
Commit;
End;
End loop;
Update password_tab t
Set t. oracle_password = substr (t. shell_text, 13, 11 ),
T. grid_password = substr (t. shell_text, 47, 11 );
Commit;
End;

Usage:

Execute change_passwords (hostname => 'test'); test is the hostname in the host_tab table.

Select t2.hostname,
T2.description,
T1.oracle _ password,
T1.grid _ password,
T1.shell _ text,
T1.change _ date
From password_tab t1
Left join host_tab t2
On t1.hostid = t2.hostid;

Finally, run the command in shell_text under the root user.

It can also be automatically implemented through the oracle dbms scheduler job and crontab.

Bytes -------------------------------------------------------------------------------------------------

Allow copy. For more information, see the source ....