Goodmorning,
I have a DG configuration with a logical database on Oracle 10.2.0.4 ( IBM AIX).
We would like to enable DDL for certain users in the IT department using the "alter session disable guard" command
Preferably this is to be done using the following procedure:
CREATE OR REPLACE PROCEDURE REP_SYS.enable_ddl authid current_user IS PRAGMA AUTONOMOUS_TRANSACTION;
l_dbname varchar2(10) := '';
l_allowed number(3) := 0;
BEGIN
select lower(instance_name) into l_dbname from v$instance;
if l_dbname = 'whdb'
then
SELECT COUNT (a.osuser)
INTO l_allowed
FROM v$session a
RIGHT OUTER JOIN
user_rw_allowed b
ON a.osuser = b.osuser AND audsid = USERENV ('sessionid');
if l_allowed > 0
then
execute immediate 'alter session disable guard';
end if;
end if;
END;
This procedure is to be called from a logon trigger, like:
CREATE OR REPLACE TRIGGER REP_SYS.check_logon_logging
after logon on database
begin
enable_ddl;
end;
Manual disabling guard in a users session works.
However, calling the procedure manually or via the logon trigger, seems to do nothing.
The "guard" stays enabled and no DDL is allowed
Edit: Forgot to mention that the user_rw_allowed table is a single column table containing "osuser" who are allowed to change their session
Any ideas?