dbawiki

User Tools

Site Tools

You are here: start » user_profiles
user_profiles

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision		Previous revision
user_profiles [2018/12/06 21:05] – created 91.177.234.129user_profiles [2020/10/25 13:51] (current) – external edit 127.0.0.1
Line 1: Line 1:
-====== User_Profiles ======+Pete Finnigan has a set of scripts not only to hack and find exploits but also to find things like users with privileges they should not have. 
 +  * [[http://www.petefinnigan.com/tools.htm|PeteFinnigan.com Tools]] 
 + 
 +==== script to show schema / user profile and the profile setup ==== 
 +<code> 
 +set lines 200 pages 1000 
 +col username           for a23  head "Username" 
 +col dba                for a5   head "DBA|privs" 
 +col status             for a8   head "Status" 
 +col lock_date          for a21  head "Lock|date" 
 +col expiry_date        for a21  head "Expiry|date" 
 +col default_tablespace for a20  head "Default|tablespace" 
 +col profile            for a18  head "Profile" 
 +col pwd_verify         for a20  head "Pwd|function" 
 +col plt                for a4   head "Pwd|life" 
 +col fla                for a8   head "Failed|logins" 
 +col rum                for a5   head "Pwd|reuse" 
 +col pgt                for a5   head "Pwd|grace" 
 +col pwd_lok            for a7   head "Lock|days" 
 + 
 +with profile_detail as 
 +( 
 +select dbp.profile 
 +,      dbp.resource_name 
 +,      decode(dbp.limit,'DEFAULT',def.limit,'NULL','','UNLIMITED','',dbp.limit) limit 
 +from   dba_profiles dbp 
 +,     (select def.resource_name 
 +       ,      decode(def.limit,'NULL','','UNLIMITED','',def.limit) limit 
 +       from   dba_profiles def 
 +       where  profile = 'DEFAULT' 
 +      ) def 
 +where  1=1 
 +and    dbp.resource_name = def.resource_name 
 +order  by 2,1 
 +) 
 +select dbu.username 
 +,      decode(dba.grantee,null,'NO','YES') dba 
 +,      replace( replace( replace( replace(dbu.account_status,'LOCKED','LOCK'), 'EXPIRED','EXP'), '(GRACE)','(GR)'), ' & ','&') status 
 +,      dbu.lock_date 
 +,      dbu.expiry_date 
 +,      dbu.default_tablespace 
 +,      dbu.profile 
 +,      pwd.limit pwd_verify 
 +,      plt.limit plt 
 +,      fla.limit fla 
 +,      rum.limit rum 
 +,      pgt.limit pgt 
 +,      plk.limit pwd_lok 
 +from   dba_users dbu 
 +,     (select grantee 
 +       from   dba_role_privs 
 +       where  granted_role = 'DBA' 
 +      ) dba 
 +,      profile_detail pwd 
 +,      profile_detail plt 
 +,      profile_detail fla 
 +,      profile_detail rum 
 +,      profile_detail pgt 
 +,      profile_detail plk 
 +where  1=1 
 +and    pwd.profile       = dbu.profile 
 +and    pwd.resource_name = 'PASSWORD_VERIFY_FUNCTION' 
 +and    plt.profile       = dbu.profile 
 +and    plt.resource_name = 'PASSWORD_LIFE_TIME' 
 +and    fla.profile       = dbu.profile 
 +and    fla.resource_name = 'FAILED_LOGIN_ATTEMPTS' 
 +and    rum.profile       = dbu.profile 
 +and    rum.resource_name = 'PASSWORD_REUSE_MAX' 
 +and    pgt.profile       = dbu.profile 
 +and    pgt.resource_name = 'PASSWORD_GRACE_TIME' 
 +and    plk.profile       = dbu.profile 
 +and    plk.resource_name = 'PASSWORD_LOCK_TIME' 
 +and    dbu.username      = dba.grantee(+) 
 +order  by expiry_date 
 +/ 
 +</code> 
 + 
 +==== Password verification function for user profile ==== 
 +<code> 
 +FUNCTION verify_function_11g 
 +(username varchar2, 
 +  password varchar2, 
 +  old_password varchar2) 
 +  RETURN boolean IS 
 +   n boolean; 
 +   m integer; 
 +   differ integer; 
 +   isdigit boolean; 
 +   ischar  boolean; 
 +   ispunct boolean; 
 +   db_name varchar2(40); 
 +   digitarray varchar2(20); 
 +   punctarray varchar2(25); 
 +   chararray varchar2(52); 
 +   i_char varchar2(10); 
 +   simple_password varchar2(10); 
 +   reverse_user varchar2(32); 
 + 
 +BEGIN 
 +   digitarray:= '0123456789'; 
 +   chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'; 
 +   punctarray:='!"#$%&()``*+,-/:;<=>?_'; 
 + 
 +   -- 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; 
 + 
 +   -- Check if the password is same as the username reversed 
 + 
 +   FOR i in REVERSE 1..length(username) LOOP 
 +     reverse_user := reverse_user || substr(username, i, 1); 
 +   END LOOP; 
 +   IF NLS_LOWER(password) = NLS_LOWER(reverse_user) THEN 
 +     raise_application_error(-20003, 'Password same as username reversed'); 
 +   END IF; 
 + 
 +   -- Check if the password is the same as server name and or servername(1-100) 
 +   select name into db_name from sys.v$database; 
 +   if NLS_LOWER(db_name) = NLS_LOWER(password) THEN 
 +      raise_application_error(-20004, 'Password same as or similar to server name'); 
 +   END IF; 
 +   FOR i IN 1..100 LOOP 
 +      i_char := to_char(i); 
 +      if NLS_LOWER(db_name)|| i_char = NLS_LOWER(password) THEN 
 +        raise_application_error(-20005, 'Password same as or similar to server name '); 
 +      END IF; 
 +    END LOOP; 
 + 
 +   -- Check if the password is too simple. A dictionary of words may be 
 +   -- maintained and a check may be made so as not to allow the words 
 +   -- that are too simple for the password. 
 +   IF NLS_LOWER(password) IN ('welcome1', 'database1', 'account1', 'user1234', 
 +        'password1', 'oracle123', 'computer1', 'abcdefg1', 'change_on_install') THEN 
 +      raise_application_error(-20006, 'Password too simple'); 
 +   END IF; 
 + 
 +   -- Check if the password is the same as oracle (1-100) 
 +    simple_password := 'oracle'; 
 +    FOR i IN 1..100 LOOP 
 +      i_char := to_char(i); 
 +      if simple_password || i_char = NLS_LOWER(password) THEN 
 +        raise_application_error(-20007, 'Password too simple '); 
 +      END IF; 
 +    END LOOP; 
 + 
 +   -- Check if the password contains at least one letter, one digit 
 +   -- 1. Check for the digit 
 +   isdigit:=FALSE; 
 +   m := length(password); 
 +   FOR i IN 1..10 LOOP 
 +      FOR j IN 1..m LOOP 
 +         IF substr(password,j,1) = substr(digitarray,i,1) THEN 
 +            isdigit:=TRUE; 
 +             GOTO findchar; 
 +         END IF; 
 +      END LOOP; 
 +   END LOOP; 
 + 
 +   IF isdigit = FALSE THEN 
 +      raise_application_error(-20008, 'Password must contain at least one digit'); 
 +   END IF; 
 + 
 +   -- 2. Check for the character 
 +   <<findchar>> 
 +   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(-20009, 'Password must contain at least one alphabetic character'); 
 +   END IF; 
 + 
 +   -- 3. Check for the punctuation 
 +   <<findpunct>> 
 +   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(-20010, 'Password should contain at least one punctuation'); 
 +   END IF; 
 + 
 + 
 +   <<endsearch>> 
 +   -- Check if the password differs from the previous password by at least 
 +   -- 3 letters 
 +   IF old_password IS NOT NULL THEN 
 +     differ := length(old_password) - length(password); 
 + 
 +     differ := abs(differ); 
 +     IF differ < 3 THEN 
 +       IF length(password) < length(old_password) THEN 
 +         m := length(password); 
 +       ELSE 
 +         m := length(old_password); 
 +       END IF; 
 + 
 +       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 < 1 THEN 
 +         raise_application_error(-20011, 'Password should differ from the \\ 
 +            old password by at least 1 characters'); 
 +       END IF; 
 +     END IF; 
 +   END IF; 
 +   -- Everything is fine; return TRUE ; 
 +   RETURN(TRUE); 
 +END; 
 +</code>
  
-=====Password verification function for user profile===== 
-<code>0@@</code> 
user_profiles.1544130327.txt.gz · Last modified: 2018/12/06 21:05 by 91.177.234.129
Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki