oracle回收用户ddl,将Oracle中的DDL授予特定用户

CREATE ANY [OBJECT_TYPE]

用户不想在系统中触发它们的工作事件。

警告:使用了DBMS_标准的未记录/未记录的功能。

CREATE OR REPLACE TRIGGER schema_1_on_schema_2

before DDL on DATABASE

as

has_dba_priv number;

n number;

stmt ora_name_list_t;

BEGIN

-- exit if user is object owner

if ora_dict_obj_owner = ora_login_user then

return

end if;

-- exit if user has dba directly

select count(*)

into has_dba_priv

from dba_role_privs

where granted_role = 'DBA'

and grantee = ora_login_user;

if has_dba_priv <> 0 then

return;

end if;

-- exit if action is an automatic recompile

stmt := null;

n := ora_sql_txt(sql_text);

FOR i IN 1..n LOOP

stmt := stmt || sql_text(i);

END LOOP;

if stmt like 'ALTER % COMPILE REUSE SETTINGS%' then

return;

end if;

-- you should probably organize this into a database table of permitted

-- schema_x can affect schema_y, but this is a "basic" example

if (ora_dict_obj_owner = 'SCHEMA_2')

and (ora_login_user = 'SCHEMA_1') then

null;

else

raise_application_error (-20000, 'User ' || ora_login_user ||

' is not permitted to execute DDL against ' || ora_dict_obj_owner);

end if;

end;