Monthly Archives: May 2014


Oracle password verify function

This week I had to implement a password verify function at a client.

My sources of inspiration were both:

– Stefan Oehrli blog entry on [Oracle 12c new password verify function]

– Mike Smithers blog entry on [Mama Mia ! Oracle Database Password Complexity and Seventies Euro-Pop]

 

From Mike Smithers I specially like the small PL/SQL to test the password function. I dare to reproduce here:

set lines 130
set serveroutput on size unlimited
spool verify_test.log
DECLARE
--
-- script to test check_password_fn
--
    TYPE typ_passwords IS TABLE OF VARCHAR2(30) INDEX BY PLS_INTEGER;
    tbl_passwords typ_passwords;
     
    l_old_password VARCHAR2(30) := 'Waterloo1';
     
    l_dummy BOOLEAN;
BEGIN
    --
    -- setup list of passwords to test with...
    --
    tbl_passwords(1) := 'simple'; -- too short
    tbl_passwords(2) := 'mike1234'; -- same as user
    tbl_passwords(3) := '4321ekim'; -- mike backwards
    tbl_passwords(4) := '1oolretaW'; -- old password backwards
    tbl_passwords(5) := 'Waterloo18'; -- too similar to old password
    tbl_passwords(6) := 'Brotherhood_0f_Man'; -- dictionary
    tbl_passwords(7) := 'P455w0rd'; -- simple
    tbl_passwords(8) := 'Sm0king_15_0nly_Vice'; -- should pass.
    tbl_passwords(9) := 'm1K31234'; -- should fail - too similar to user
    tbl_passwords(10) := 'W4terl001'; -- should fail - too similar to old password
    tbl_passwords(11) := 'the_day_before_you_came'; -- should fail - does not have any uppercase or numbers
    tbl_passwords(12) := 'ONLYSeventeen'; -- should fail - no numbers
    tbl_passwords(13) := '48840000'; -- should fail - no letters
    tbl_passwords(14) := 'ABBA0000'; -- should fail - no lowercase letters
    tbl_passwords(15) := 'abba0000'; -- should fail - no uppercase letters
    tbl_passwords(16) := 'Mamma_Mia1'; -- should pass
 
--  tbl_passwords(1) := 'W4terl001'; 
--    tbl_passwords(2) := 'P455w0rd'; -- simple
 
    --
    -- Nested block required to account for failures...
    --
    FOR i IN 1..tbl_passwords.COUNT LOOP
        BEGIN
            l_dummy := check_password_fn(
                username => 'MIKE',
                old_password => l_old_password,
                new_password => tbl_passwords(i));
            DBMS_OUTPUT.PUT_LINE('Test '||i||' - password allowed.');
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('Test '||i||' ERROR : '||SQLERRM);
        END;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Test run completed.');
END;
/
spool off

One the requirements I had was:

– The password should include three of the four following sets: English uppercase letters, English lowercase letters, base 10 digits, special characters like ‘$’,’#’,’!’,’?’

 

The password function provided by Oracle does not allows to say this choice of “three out of four”, so I developed myself and added to the Oracle default function the following code:

--
-- Finally, make sure that the password contains
-- characters from 3 of the four categories
-- UPPERCASE, lowercase, numbers and punctuation characters.
--
IF 
    SIGN(REGEXP_INSTR(new_password, '[[:upper:]]'))+
    SIGN(REGEXP_INSTR(new_password, '[[:lower:]]')) +
    SIGN(REGEXP_INSTR(new_password, '[[:digit:]]')) +
    SIGN(REGEXP_INSTR(new_password, '[[:punct:]]')) < 3
THEN
   RAISE_APPLICATION_ERROR( -20006,'Password must contain characters from three of the following four categories: English uppercase, lowercase, base 10 digits, non-alphabetic characters like ! $ # %');
END IF;


Add path to ASMCMD prompt

Just discovered this morning how to show the path on the ASMCMD tool and could not be easier:

anjovm2:/home/grid:+ASM2 $ asmcmd -p
ASMCMD [+] > cd dgdata
ASMCMD [+dgdata] > cd db12c
ASMCMD [+dgdata/db12c] > ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
spfiledb12c.ora
ASMCMD [+dgdata/db12c] > cd datafile
ASMCMD [+dgdata/db12c/datafile] >

ORA-17628: Oracle error 19505 returned by remote Oracle server

Today I was doing a procedure for duplicating databases at a client environment. One error that pop up time to time was the one below:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 05/13/2014 18:49:01
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on t1 channel at 05/13/2014 18:49:00
ORA-17628: Oracle error 19505 returned by remote Oracle server

At several websites you can see different causes:

– Control file location specification was wrong;

– db_file_name_convert were not specified;

– FRA diskgroup inexisting in the target;

– etc.

I had the following two cases on my experiments:

– destination database directory not existing on the DGDATA diskgroup (+DGDATA/<db_unique_name>)

– I did not connect to auxiliary using a standard network naming (I was using ezconnect).