注意事项:
1、脚本中数据库用户oracle的密码为Oracle_123,可修改
2、Rac库中,grid用户不要放在dba组中。
3、revoke execute on 'UTL_FILE','UTL_TCP','UTL_HTTP','UTL_SMTP','DBMS_LOB','DBMS_SYS_SQL','DBMS_JOB' from public;这条sql可能会导致alert日志报错,若出现则重新授予即可。
如下是jixian.sql的内容:
1、脚本中数据库用户oracle的密码为Oracle_123,可修改
2、Rac库中,grid用户不要放在dba组中。
3、revoke execute on 'UTL_FILE','UTL_TCP','UTL_HTTP','UTL_SMTP','DBMS_LOB','DBMS_SYS_SQL','DBMS_JOB' from public;这条sql可能会导致alert日志报错,若出现则重新授予即可。
如下是jixian.sql的内容:
点击(此处)折叠或打开
- /*创建密码复杂度函数VERIFY_FUNCTION2*/
- CREATE OR REPLACE FUNCTION sys."VERIFY_FUNCTION2" (
- username varchar2,
- password varchar2,
- old_password varchar2)
- RETURN boolean IS
- n boolean;
- m integer;
- differ integer;
- isdigit boolean;
- ischar boolean;
- ispunct boolean;
- digitarray varchar2(20);
- punctarray varchar2(25);
- chararray varchar2(52);
-
- BEGIN
- digitarray:= '0123456789';
- chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
- punctarray:='!"#$%&()``*+,-/:;<=>?_';
-
- -- Check if the password is same as the username
- IF NLS_LOWER(password) = NLS_LOWER(username) THEN
- raise_application_error(-20001, 'Password same as or similar to user');
- END IF;
-
- -- Check for the minimum length of the password
- IF length(password) < 8 THEN
- raise_application_error(-20002, 'Password length less than 8');
- END IF;
-
- -- 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 ('welcome', 'database', 'account', 'user', 'password', 'oracle', 'computer', 'abcd') THEN
- raise_application_error(-20002, 'Password too simple');
- END IF;
-
- -- Check if the password contains at least one letter, one digit and one
- -- punctuation mark.
- -- 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(-20003, 'Password should contain at least one digit, one character and one punctuation');
- 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(-20003, 'Password should contain at least one \
- digit, one character and one punctuation');
- 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(-20003, 'Password should contain at least one \
- digit, one character and 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);
-
- 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, 'Password should differ by at \
- least 3 characters');
- END IF;
- END IF;
- END IF;
- -- Everything is fine; return TRUE ;
- RETURN(TRUE);
- END;
- /
-
- /*创建应用用户profile APP_PROF*/
- create profile APP_PROF limit
- failed_login_attempts 6
- password_life_time 360
- password_reuse_time 60
- password_reuse_max 5
- password_lock_time .0004
- password_grace_time 5
- password_verify_function VERIFY_FUNCTION2;
-
- /*创建系统用户profile SYS_PROF*/
- create profile SYS_PROF limit
- failed_login_attempts 6
- password_life_time 180
- password_reuse_time 60
- password_reuse_max 5
- password_lock_time .001
- password_grace_time 30
- password_verify_function VERIFY_FUNCTION2;
-
- /*创建具名用户profile CMSZ_PROF*/
- create profile CMSZ_PROF limit
- failed_login_attempts 6
- password_life_time 180
- password_reuse_time 60
- password_reuse_max 5
- password_lock_time .01
- password_grace_time 60
- password_verify_function VERIFY_FUNCTION2;
-
- /*禁止具有数据库超级管理员(SYSDBA)权限的用户从远程登陆*/
- alter system set remote_login_passwordfile=NONE scope=spfile sid='*';
-
- /*限制只有SYSDBA用户才能访问数据字典基础表*/
- alter system set O7_DICTIONARY_ACCESSIBILITY=FALSE scope=spfile sid='*';
-
- /*禁止从其他OS登录认证*/
- alter system set REMOTE_OS_AUTHENT=FALSE scope=spfile sid='*';
-
- /*开启数据库审计功能*/
- alter system set audit_trail=DB scope=spfile sid='*';
-
- /*创建用户登录触发器*/
- create table LOGIN_LOG(
- SESSION_ID NUMBER(32,0) NOT NULL,
- LOGIN_ON_TIME DATE,
- USER_IN_DB VARCHAR(50),
- IP_ADDRESS VARCHAR(20)
- );
-
- create trigger LOGIN_INFO
- after logon on database
- begin
- insert into LOGIN_LOG(SESSION_ID,LOGIN_ON_TIME,USER_IN_DB,IP_ADDRESS) select AUDSID,sysdate,sys.login_user,SYS_CONTEXT('USERENV','IP_ADDRESS') from v$session where AUDSID=USERENV('SESSIONID');
- end;
- /
-
- /*回收public角色执行权限*/
- declare
- cursor c1 is
- select table_name from dba_tab_privs
- where grantee='PUBLIC' and privilege='EXECUTE'
- and table_name in ('UTL_FILE','UTL_TCP','UTL_HTTP','UTL_SMTP','DBMS_LOB','DBMS_SYS_SQL','DBMS_JOB');
-
- v_table_name varchar2(20);
- v_sql varchar2(100);
-
- begin
- open c1;
- loop
- fetch c1 into v_table_name;
- exit when c1%notfound;
- v_sql := 'revoke execute on '||v_table_name||' from public';
- execute immediate v_sql;
- end loop;
- close c1;
- end;
- /
-
- /*回收非系统DBA用户的DBA权限*/
- declare
- cursor c1 is
- select a.username from dba_users a left join dba_role_privs b on a.username = b.grantee where granted_role = 'DBA' and a.username not in ('SYS','SYSMAN','SYSTEM');
-
- v_user_name varchar2(100);
- v_sql varchar2(100);
- begin
- open c1;
- loop
- fetch c1 into v_user_name;
- exit when c1%notfound;
- v_sql := 'revoke dba from '||v_user_name||' ';
- execute immediate v_sql;
- end loop;
- close c1;
- end;
- /
-
- /*修改lock和expire系统用户默认密码*/
- declare
- cursor c1 is
- select username from dba_users where ACCOUNT_STATUS like 'EXPIRED%LOCKED' and profile='DEFAULT' and username <> 'XS$NULL';
-
- v_user_name varchar2(100);
- v_sql1 varchar2(100);
- v_sql2 varchar2(100);
-
- begin
- open c1;
- loop
- fetch c1 into v_user_name;
- exit when c1%notfound;
- v_sql1 := 'alter user '||v_user_name||' identified by Oracle_123';
- v_sql2 := 'alter user '||v_user_name||' password expire';
- execute immediate v_sql1;
- execute immediate v_sql2;
- end loop;
- close c1;
- end;
- /
-
- /*修改open系统用户默认密码和profile*/
- declare
- cursor c1 is
- select username from dba_users where ACCOUNT_STATUS = 'OPEN' and profile ='DEFAULT' and username in ('SYS','SYSTEM','OUTLN','FLOWS_FILES','MDSYS','ORDSYS','EXFSYS','DBSNMP','WMSYS','APPQOSSYS','APEX_030200','ORDDATA','CTXSYS','ANONYMOUS',
- 'XDB','ORDPLUGINS','SI_INFORMTN_SCHEMA','OLAPSYS','ORACLE_OCM','XS$NULL','MDDATA','DIP','APEX_PUBLIC_USER','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR');
-
- v_user_name varchar2(100);
- v_sql1 varchar2(100);
- v_sql2 varchar2(100);
-
- begin
- open c1;
- loop
- fetch c1 into v_user_name;
- exit when c1%notfound;
- v_sql1 := 'alter user '||v_user_name||' identified by Oracle_123';
- v_sql2 := 'alter user '||v_user_name||' profile SYS_PROF';
- execute immediate v_sql1;
- execute immediate v_sql2;
- end loop;
- close c1;
- end;
- /
-
- /*修改Locked系统用户过期*/
- declare
- cursor c1 is
- select username from dba_users where ACCOUNT_STATUS = 'LOCKED' and profile ='DEFAULT';
-
- v_user_name varchar2(100);
- v_sql varchar2(100);
-
- begin
- open c1;
- loop
- fetch c1 into v_user_name;
- exit when c1%notfound;
- v_sql := 'alter user '||v_user_name||' password expire';
- execute immediate v_sql;
- end loop;
- close c1;
- end;
- /
-
-
-
- /*修改open应用用户profile*/
- declare
- cursor c1 is
- select username from dba_users where ACCOUNT_STATUS = 'OPEN' and profile ='DEFAULT' and username not in ('SYS','SYSTEM','OUTLN','FLOWS_FILES','MDSYS','ORDSYS','EXFSYS','DBSNMP','WMSYS','APPQOSSYS','APEX_030200','ORDDATA','CTXSYS','ANONYMOUS',
- 'XDB','ORDPLUGINS','SI_INFORMTN_SCHEMA','OLAPSYS','ORACLE_OCM','XS$NULL','MDDATA','DIP','APEX_PUBLIC_USER','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR');
-
- v_user_name varchar2(100);
- v_sql varchar2(100);
-
- begin
- open c1;
- loop
- fetch c1 into v_user_name;
- exit when c1%notfound;
- v_sql := 'alter user '||v_user_name||' profile APP_PROF';
- execute immediate v_sql;
- end loop;
- close c1;
- end;
- /
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31441616/viewspace-2152524/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31441616/viewspace-2152524/