set serverout on;
DECLARE
CURSOR c_dept IS select distinct a.username, b.profile, b.resource_name, b.limit from dba_users a, dba_profiles b where a.profile = b.profile and a.profile is not null and b.resource_type = 'PASSWORD' and b.resource_name = 'FAILED_LOGIN_ATTEMPTS' and b.limit <> 'UNLIMITED' and a.username not in (select c.username from dba_users_with_defpwd c) and a.username not in ('SYS','SYSTEM','SYSMAN','MGMT_VIEW', 'ANONYMOUS', 'FLOWS_FILES', 'WKPROXY', 'WKSYS', 'FLOWS_030000', 'OLAPSYS', 'APEX_PUBLIC_USER');
vs_row2 c_dept%rowtype;
BEGIN
DBMS_OUTPUT.PUT_LINE('------------------------');
FOR vs_row2 IN c_dept
LOOP
-- DBMS_OUTPUT.PUT_LINE('Department:'|| vs_row2.profile||'-');
-- alter profile tet limit failed_login_attempts unlimited;
DBMS_OUTPUT.PUT_LINE('#############');
END LOOP;
END;
/
-- 带游标<br>create or replace procedure SP_Test is
DECLARE
cursor c_dept is select distinct a.username, b.profile, b.resource_name, b.limit from dba_users a, dba_profiles b where a.profile = b.profile and a.profile is not null and b.resource_type = 'PASSWORD' and b.resource_name = 'FAILED_LOGIN_ATTEMPTS' and b.limit <> 'UNLIMITED' and a.username not in (select c.username from dba_users_with_defpwd c) and a.username not in ('SYS','SYSTEM','SYSMAN','MGMT_VIEW', 'ANONYMOUS', 'FLOWS_FILES', 'WKPROXY', 'WKSYS', 'FLOWS_030000', 'OLAPSYS', 'APEX_PUBLIC_USER');
vs_row2 c_dept%rowtype;
tempresult varchar2(1024);
begin
for vs_row2 in c_dept loop
begin
tempresult := tempresult||vs_row2.profile;
DBMS_OUTPUT.PUT_LINE('#############'||tempresult);
alter profile 'test' limit failed_login_attempts unlimited;
end;
end loop;
end;
/
select distinct a.username, b.profile, b.resource_name, b.limit from dba_users a, dba_profiles b
where a.profile = b.profile and a.profile is not null and b.resource_type = 'PASSWORD' and b.resource_name = 'PASSWORD_LIFE_TIME'
and b.limit <> 'UNLIMITED' and a.username not in (select c.username from dba_users_with_defpwd c)
and a.username not in ('SYS','SYSTEM','SYSMAN','MGMT_VIEW', 'ANONYMOUS', 'FLOWS_FILES', 'WKPROXY', 'WKSYS', 'FLOWS_030000', 'OLAPSYS', 'APEX_PUBLIC_USER' , 'DBSNMP');
select distinct a.username, b.profile, b.resource_name, b.limit from dba_users a, dba_profiles b where a.profile = b.profile and a.profile is not null and b.resource_type = 'PASSWORD' and b.resource_name = 'FAILED_LOGIN_ATTEMPTS' and b.limit <> 'UNLIMITED' and a.username not in (select c.username from dba_users_with_defpwd c) and a.username not in ('SYS','SYSTEM','SYSMAN','MGMT_VIEW', 'ANONYMOUS', 'FLOWS_FILES', 'WKPROXY', 'WKSYS', 'FLOWS_030000', 'OLAPSYS', 'APEX_PUBLIC_USER');
alter profile PROFILE_DNADBM_XY_0115 limit failed_login_attempts unlimited;