注意事项：
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博客

绿盟 rhel6的oracle 基线修复脚本。

最新文章