SET SERVEROUTPUT ON
declare cursor aa
is
select username,USER_ID from dba_users ;
bb aa%rowtype;
begin
open aa;
loop
fetch aa into bb;
exit when aa%notfound;
dbms_output.put_line(bb.username);
end loop;
close aa;
end;
/
SYSADM
SYSSEC
SYSAUD
SYSCAT
SYSGIS
OUTLN
KDB
KDB1
TESTAUD1
TESTAUD2
CREATE OR REPLACE PROCEDURE PRC_LJ AS
declare cursor aa IS
select username,USER_ID from dba_users ;
bb aa%rowtype;
begin
open aa;
loop
fetch aa into bb;
exit when aa%notfound;
dbms_output.put_line(bb.username);
end loop;
close aa;
end;
/
SQL> CREATE OR REPLACE PROCEDURE object_name1
2 AS
3 BEGIN
4 declare cursor aa
5 is
6 select OBJECT_NAME from user_objects ;
7 bb aa%rowtype;
8 begin
9 open aa;
10 loop
11 fetch aa into bb;
12 exit when aa%notfound;
13 dbms_output.put_line(bb.OBJECT_NAME);
14 end loop;
15 close aa;
16 end;
17 END;
18 /
Procedure 'OBJECT_NAME1' created.
create or replace procedure test is
tx varchar2(500);
BEGIN
for i in (select 'audit all on '||object_name||';' as sql_text from user_objects )
loop
tx:=i.sql_text;
EXECUTE IMMEDIATE tx;
end loop;
END;
/
create or replace procedure t is
tx varchar2(500);
BEGIN
for i in (select 'audit all on '||object_name||';' as sql_text from user_objects ORDER BY 1)
loop
tx:=i.sql_text;
EXECUTE IMMEDIATE tx;
end loop;
END;
/
create or replace procedure p11 (P in varchar2) is
tx varchar2(500);
BEGIN
for i in (select 'audit GRANT ANY PRIVILEGE by syssec' || ';' as sql_text from dual )
loop
tx:=i.sql_text;
EXECUTE IMMEDIATE tx;
end loop;
END;
/
create or replace procedure t16(T in varchar2) is
tx varchar2(500);
BEGIN
for i in (select 'audit all on '|| owner || '.' || T || ';' as sql_text from all_objects where owner='TEST')
loop
tx:=i.sql_text;
EXECUTE IMMEDIATE tx;
end loop;
END;
/
audit 动作 on object ;
create or replace procedure t17(T in varchar2 ,T1 in varchar2) is
tx varchar2(500);
BEGIN
for i in (select 'audit '|| T || ' on ' || T1 || ' by access' || ';' as sql_text from dual )
loop
tx:=i.sql_text;
EXECUTE IMMEDIATE tx;
end loop;
END;
/
exec t17 ('INSERT','TEST.TAB1')
audit grant 权限 by syssec ;
create or replace procedure n17(N in varchar2 ) is
tx varchar2(500);
BEGIN
for i in (select 'audit '|| N || ' by syssec by access' || ';' as sql_text from dual )
loop
tx:=i.sql_text;
EXECUTE IMMEDIATE tx;
end loop;
END;
/
exec n17('GRANT ANY PRIVILEGE')
audit 权限 by all user ;
create or replace procedure p17(P in varchar2 ) is
tx varchar2(500);
BEGIN
for i in (select 'audit '|| P || ' by ' || username || ' by access' || ';' as sql_text from all_users )
loop
tx:=i.sql_text;
EXECUTE IMMEDIATE tx;
end loop;
END;
/
exec p17 ('alter system')
noaudit 权限 by all user ;
create or replace procedure p17_1(P in varchar2 ) is
tx varchar2(500);
BEGIN
for i in (select 'noaudit '|| P || ' by ' || username || ';' as sql_text from all_users )
loop
tx:=i.sql_text;
EXECUTE IMMEDIATE tx;
end loop;
END;
/
exec p17_1 ('alter system')