verify function restriction

[Claudiu]

hi Erman,

we are trying to restrict all database users with standart oracle function verify_function_11g . we already wrote the code but code work for also SYS and other admin,dba users.We want an exception code block for admin users .Could you help ?  

This is our code and as I say its also working for SYS too but we want an exception  :)

CREATE OR REPLACE FUNCTION SYS.verify_function_ORACLE
(username varchar2,
  password varchar2,
  old_password varchar2)
  RETURN boolean IS
   n boolean;
   m integer;
   db_name varchar2(30);
   i_char varchar2(10);

BEGIN
   -- Check for the minimum length of the password
   IF length(password) < 8 THEN
      raise_application_error(-20001, 'Password length less than 8');
   END IF;
   -- Check if the password is same as the username or username(1-100)
   IF NLS_LOWER(password) = NLS_LOWER(username) THEN
     raise_application_error(-20002, 'Password same as or similar to user');
   END IF;
   FOR i IN 1..100 LOOP
      i_char := to_char(i);
      if NLS_LOWER(username)|| i_char = NLS_LOWER(password) THEN
        raise_application_error(-20005, 'Password same as or similar to user name ');
      END IF;
    END LOOP;
   -- Everything is fine; return TRUE ;
   RETURN(TRUE);
END;

[ErmanArslansOracleBlog]

You don't need to do any code changes.
This is easy.
Just create a seperate database profile for the accounts other than SYS and other admin users.
Assign the verify function to this new profile.

One profile for middle-tier application schemas (“managed schemas”) and one for human beings. Assign middle-tier application schemas to the profile and all accounts used by individual database administrators to the second profile (maybe just the default profile).

[claudiu]

thank your for answer but already did that and i have new profile which is PROFILE_A,
and also i have new user USER_A, assigned USER_A to PROFILE_A and also PROFILE_A has new verify function.
But sys still cant do anyting for example sys cant set  USER_A passowrd lower thatn 8 characters because of code in verify function

[Claudiu]

i am trying below code at new verify function but couldnt setup logic;

    IF     SYS_CONTEXT ('USERENV', 'SESSION_USER') IN ('SYS','CLAUDIU')
       AND
            SYS_CONTEXT ('USERENV', 'IP_ADDRESS') NOT IN ('10.10.10.10'))
 

[ErmanArslansOracleBlog]

What do you mean " couldnt setup the logic"

Just try putting that "if" block şnto your verify function (into the correct place) and return(true) if the condition in your if statement is met.