Seguridad Oracle y Funcion Complejidad Clave

Revoke Unnecessary Privileges

REVOKE EXECUTE ON DBMS_RANDOM from public;
REVOKE CREATE DATABASE LINK FROM connect;
REVOKE EXECUTE ON utl_tcp FROM public;
REVOKE EXECUTE ON utl_smtp FROM public;
REVOKE EXECUTE ON utl_http FROM public;
REVOKE EXECUTE ON utl_mail FROM public;
REVOKE EXECUTE ON utl_inaddr FROM public;
REVOKE EXECUTE ON utl_file FROM public;
REVOKE EXECUTE ON dbms_java FROm public;
REVOKE SYSDBA FROM rman;

FUNCION PARA VALIDAR COMPLEJIDAD DE CONTRASEÑAS.

CREATE OR REPLACE FUNCTION dba_verify_function
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
n boolean;
m integer;
differ integer;
isdigit boolean;
ischar boolean;
ischarm boolean;
ispunct boolean;
digitarray varchar2(20);
punctarray varchar2(25);
chararray varchar2(54);


BEGIN
digitarray:= '0123456789';
chararray := 'abcdefghijklmnñopqrstuvwxyzABCDEFGHIJKLMNÑOPQRSTUVWXYZ';
punctarray:='!"#$%&()``*+,-/:;<=>?_';

-- Valida que el Password no sea Igual al Username

IF NLS_LOWER(password) = NLS_LOWER(username) THEN
raise_application_error(-20001, 'La clave es la misma o similar al usuario');
END IF;

-- Valida los caraterese Minimos de Longitud del Password

IF length(password) < isdigit =" FALSE">>

ischar:=FALSE;

FOR i IN 1..length(chararray) LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(chararray,i,1) THEN
ischar:=TRUE;
GOTO findpunct;
END IF;
END LOOP;
END LOOP;

IF ischar = FALSE THEN
raise_application_error(-20003, 'El password debe contener un caracter');
END IF;


-- 3. Check for the punctuation

<>

ispunct:=FALSE;

FOR i IN 1..length(punctarray) LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(punctarray,i,1) THEN
ispunct:=TRUE;
GOTO endsearch;
END IF;
END LOOP;
END LOOP;

IF ispunct = FALSE THEN
raise_application_error(-20003, 'La clave debe contener al menos caracter especial');
END IF;

<>

-- Compruebe si la contraseña es diferente de la anterior contraseña por lo menos en Tres caracteres

-- 4 letters

IF old_password IS NOT NULL THEN
differ := length(old_password) - length(password);

IF abs(differ) < 3 THEN
IF length(password) < length(old_password) THEN
m := length(password);
ELSE
m := length(old_password);
END IF;

differ := abs(differ);
FOR i IN 1..m LOOP
IF substr(password,i,1) != substr(old_password,i,1) THEN
differ := differ + 1;
END IF;
END LOOP;

IF differ < 3 THEN
raise_application_error(-20004, 'El password debe ser diferente al menos en (3) Tres caracteres');
END IF;
END IF;
END IF;

-- Everything is fine; return TRUE ;

RETURN(TRUE);

END;
/

-- This means that all the users on the system have Password Management
-- enabled and set to the following values unless another profile is
-- created with parameter values set to different value or UNLIMITED
-- is created and assigned to the user.

ALTER PROFILE SEGURIDAD LIMIT
PASSWORD_LIFE_TIME 60
PASSWORD_GRACE_TIME 10
PASSWORD_REUSE_TIME 1800
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 1/1440
PASSWORD_VERIFY_FUNCTION dba_verify_function;

SQL> DECLARE
2 X BOOLEAN;
3 BEGIN
4 x:= dba_verify_function ('dbo','dios','pepe');
5 END;
6 /
DECLARE
*
ERROR at line 1:
ORA-20002: La clave debe ser de longitud mayor o igual a 8
ORA-06512: at "SYS.FN_COMPLEJIDAD_CLAVE", line 26
ORA-06512: at line 4