I am trying to use the same example on my schema but getting the following error...

Warning: Trigger created with compilation errors.

Elapsed: 00:00:00.17
Errors for TRIGGER DDL_TRIGGER:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/5 PL/SQL: SQL Statement ignored
7/12 PLS-00201: identifier 'SYS.V$SESSION' must be declared

Please help..

Thanks TOM

Hi Tom,

Thanks for your quick response. Well the trigger works
fine as long as the user logs into sqlplus in the same schema where the trigger is, but when a diffrent user who
has create any table prev. create table in this schema from his login then the trigger does not get fired and I can not log those changes happening on the schema......

eg.

1) Trigger is on schema A.
2) User logs into schema B with create any table prev.
3) User run the statment from schema B

create table a.t(t number);

table created.

4) No changes are logged through the trigger in schema A.

Please help...

Thanks
Neeti

Mr.Tom,

Please give an example until reading that OS file again
to understand it clearly .

Tom,

Does this DDL_TRIGGER has any potential to interact with
other before insert trigger on another table in the schema.
In the log table for DDL_TRIGGER, I added DATE field also

Recently I created a DDL_TRIGGER and the log was written to
my user schema. On the application schema there is a
before insert trigger on a table.

When I tried to disable the application trigger in the
application schema got errors
ORA-00604 error occurred at recursive SQL level 1
ORA-01401 inserted value too large for that column
ORA-06512

Once I dropped the DDL_TRIGGER , the errors went away

Thanks

I am hesitant to ask, this must be pretty basic......

Could you please explain what are these columns??

"select ora_sysevent,ora_dict_obj_owner,
ora_dict_obj_name,ora_dict_obj_type.."

I dont see any ora_ columns in v$session. It returned an invalid column error on 8.1.5 but did not return any errors on 8.1.7.3. But the desc v$session does not show these columns on both versions. Nor does the ref manual for 8.1.7

Could you, Tom, kindly explain more about this...(link to doc would be enough). thanks.

Thanx Tom.

Hi Tom:

the DDL tigger works fine until the database
is rebooted. Once the database is rebooted, errors occur during the execution
of DDL, such as the following:
The following error has occurred:
ORA-00604: error occurred at recursive SQL level 1
ORA-06550: line 123, column 22:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the
following:
. ( * @ % & - + ; / at for mod rem <an exponent (**)> and or
group having intersect minus order start union where connect
||

When recompile the trigger, it then works fine.
The database is currently rebooted nightly for backups.
now we have to disable the trigger, but we need the trigger stay alive, would you tell me why the errors occur?
Thank you very much.
an

The "Another Issue ..." followup on this item is at best disingenuous. The fact is that Oracle's labelling of this feature as a "Schema Trigger" is a *gross* distortion ... *or* Oracle's needs to rewrite its definition of "schema", which would put it at odds with the standard industry definition.

If I log on as "Barry" and ALTER Scott.Emp (via an ALTER ANY TABLE privilege), a DDL trigger tagged to Barry.SCHEMA will fire, but a trigger tied to Scott.SCHEMA will NOT fire. Since Oracle define "schema" as the thing that owns an object (which would be "Scott", NOT "Barry" in this case), how can this feature possibly be honestly described as a "Schema Trigger"??

As for why you would want to do this "ANY" privilege thing ... I guess it is fundamentally because of deficiencies in the standard SQL security model, with Oracle's extensions not doing enough to help. And I plan to talk about it at the IOUG Live! 2003 Conference in Orlando on May 1, and I've spelled it out in the supporting paper which I'd be happy to share beforehand.

In the meantime, the workaround to get a properly implemented Schema trigger is to create a data base trigger that fires when a DDL occurs, and includes a

WHEN( Ora_Dict_Obj_Owner = "<schema of interest>" )

condition as part of its specification, tho' the privilege threshold to be able to create a data base trigger is a bit higher than the other trigger types.

... and to say it is simply Session_User is to trivialise the issue. "Application Developers Guide - Fundamentals" defines "schema" as "a security domain that can contain data base objects".

If I log on as "Barry" and do an "ALTER Scott.Emp ...", there is *no* reason for a TRIGGER predicated on "BEFORE DDL ON Barry.SCHEMA" to fire (although this is what currently happens). There is *every* reason for a TRIGGER predicated on "BEFORE DDL ON Scott.SCHEMA" to fire because it is the "Scott" schema that owns the object being changed. There is *no* DDL action being performed "*ON* Barry.SCHEMA".

Let me put it another way: I want to protect "Scott" schema objects against DDL changes. Why would it occur to me - especially given the syntax of "BEFORE DDL ON <schema>.SCHEMA" - that I need to create a separate "schema" trigger against *each and every username* in the data base and, within each trigger, check to see if Ora_Dict_Obj_Owner = "Scott" to decide whether I need to do anything to protect "Scott". Since, in general, I can't know who is going to have what privileges when that may empower them to affect "Scott" so I have to be comprehensive.

(The workaround is actually to create a trigger predicated on "BEFORE DDL ON DATABASE' and add a "WHEN( Ora_Dict_Obj_Owner = 'Scott' )" but, alas, that requires access to an elevated data base privilege.)

No, Oracle need to change the syntax to "BEFORE DDL *BY* <UserName>.*USER*" to make it clear that this is the way it works. No wonder people are confused in the meantime.

Or Oracle could fix it to use Ora_Dict_Obj_Owner as the triggering schema criterium and turn this trigger type into something that would be wonderfully useful. As it is, I've yet to find a redeeming value for it.

Sincerely ... Barry Johnson

I am using 9.2.0.4. I found that in most cases, sql like "Alter table blabla drop parttion...." will not show up in v$open_curson, so I rewrite the trigger a little bit.

create or replace trigger ddl_monitor_trigger
after create or alter or drop on schema
declare
l_sysevent varchar2(25);
l_status varchar2(25);
l_obj_owner varchar2(25);
l_obj_name varchar2(25);
l_obj_type varchar2(25);
l_text varchar(1000);
l_sid varchar(10);
begin
select sql_text into l_text from v$session vs,v$sql vsql
where vs.audsid = (select SYS_CONTEXT('USERENV','SESSIONID') from dual)
and vs.SQL_ADDRESS = vsql.address
and vs.SQL_HASH_VALUE = vsql.hash_value;

select ora_sysevent into l_sysevent from dual;
select ora_dict_obj_owner into l_obj_owner from dual;
select ora_dict_obj_name into l_obj_name from dual;
select ora_dict_obj_type into l_obj_type from dual;
select status into l_status from user_objects where object_name = 'MY_PKG'
and object_type='PACKAGE' ;
if (l_status = 'INVALID' )
then
insert into system.my_table (insert_time,command,obj_owner,
obj_name,obj_type,sql_text)
values (sysdate,ora_sysevent,
l_obj_owner,l_obj_name,l_obj_type,l_text);
end if;
end;

Everything works fine except the sql_text, which did not catch the "alter table drop partition", which is the quilty sql during my test, it catch the sql from this trigger. Which is,
"select sql_text into l_text from v$session vs,v$sql vsql
where vs.audsid = (select SYS_CONTEXT('USERENV','SESSIONID') from dual)
and vs.SQL_ADDRESS = vsql.address
and vs.SQL_HASH_VALUE = vsql.hash_value;"

Could you give more help?

Thanks

Hi tom
SYS@prod> desc scott.DDLOPERATIONS
Name
---------------------------------
USER_NAME
DDL_DATE
DDL_TYPE
OBJECT_TYPE
OWNER
OBJECT_NAME
IP_ADDRESS
HOST
OS_USER
TERMINAL
TEXT

CREATE OR REPLACE TRIGGER DDLTrigger
AFTER DDL ON DATABASE
declare
l_text varchar2(4000);
BEGIN
select sql_text into l_text from v$session vs,v$sql vsql
where vs.SQL_ADDRESS = vsql.address
and vs.SQL_HASH_VALUE = vsql.hash_value
and vs.audsid = (select SYS_CONTEXT('USERENV','SESSIONID') from dual);

insert into
scott.ddloperations
(
user_name,
ddl_date,
ddl_type,
object_type,
owner,
object_name,
IP_ADDRESS,
HOST,
OS_USER,
TERMINAL,
TEXT)
VALUES
(
ora_login_user,
sysdate,
ora_sysevent,
ora_dict_obj_type,
ora_dict_obj_owner,
ora_dict_obj_name,
sys_context('USERENV', 'IP_ADDRESS'),Sys_context('USERENV', 'HOST'),
sys_context('USERENV', 'OS_USER'), sys_context('USERENV', 'TERMINAL'),
l_text);
END;
/
APPS@prod> create table scott.emp2 as select * from scott.EMP_SNAPSHOT;

Table created.
APPS@prod> select text from scott.DDLOPERATIONS;

TEXT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT SQL_TEXT FROM V$SESSION VS,V$SQL VSQL WHERE VS.SQL_ADDRESS = VSQL.ADDRESS AND VS.SQL_HASH_VALUE = VSQL.HASH_VALUE AND VS.AUDSID = (SELECT SYS_CONTEXT('USERENV','SESSIONID') FROM DUAL )

my question how to get the DDL into the text?
i see on 92 just usee ora_sql_txt but how to do it in 8i?
Thanks In advance

Tom,

In Oracle 9206 if i isssue this command

"create user user1 identified by user1;
now this user will have "default" profile can he be assigned somethings else lets say "myprofile" rather than the default when the user is created.

Thanks.

Tom,

Maybe i wasnt clear enough as to what i am looking for, when the user is created i want his profile to be "MYPROFILE" not default as the user is created on the fly (strange setup, yes) can we use some trigger or something else?

Thanks.

Tom,

Thanks for the feedback I am aware of the syntax, its created on the fly thats the reason i mentioned strange setup (app server creates user after verifying credentials etc etc). I understand it should be fixed within the app server itself but can we acheive this thru some form of system trigger.

Thanks.

.. automatically. Perhaps he has an application which is issuing the create user command and he wants to automatically assing an alternate profile to the user the application create. My advice: change the default profile to YOUR profile name (i.e. the new one you just created for the application).

Tom,

>>You could attempt to automate this by using dbms_job to >>schedule a job AFTER the create user takes place - but seems >>you really just want to

Initially i was thinking a logon trigger if the user has default profile change it to "MYPROFILE" do you see any issues with that?

Another Reader thanks as well you are correct thats what i am looking for. No we dont want to touch the default profile.

Thanks.

Thank You Tom point noted.

After DDL trigger on a database was created. This trigger is acted upon when on a achema, tables are created. I have used ora_dict-obj_owner and ora_dict_obj_type. It works as indended
DB veriosn 10g and 11g

But it generates an recursive SQL if I create a USER on the database

Could you tell us if this normal side effects or if not how to resolve it

Thanks

Hi Tom,

I have gotten to the same behavior, just different SQL but same behaviour. We have a client/server application were for some reason out of our control it drops a recreate some sequence in some of the options, we can't change the application, the application is deployed in one schema and is accessed from other schemas through synonyms. So when a sequences is recreated we want to give back the grants that it had before, and we are trying (but not succeeding) to do it through an schema trigger.

Leaving the explanation behind, this is the code that we are trying to execute:

CREATE USER GD1 IDENTIFIED BY GD1;
CREATE USER GD2 IDENTIFIED BY GD2;


-- Connected as GD1
CREATE OR REPLACE TRIGGER SEQ_CREATION_TRG AFTER CREATE ON GD1.SCHEMA 
DECLARE
 sql_text ora_name_list_t;
 stmt VARCHAR2(32000);
 n NUMBER;
BEGIN
  IF ora_dict_obj_type = 'SEQUENCE' THEN 
  n := ora_sql_txt(sql_text);
  FOR i IN 1..n LOOP
   stmt := stmt || sql_text(i);
  END LOOP;
  dbms_output.put_line('Running SQL:'||stmt);
  EXECUTE IMMEDIATE 'GRANT SELECT ON ' ||ora_dict_obj_name||' TO GF2'; 
 END IF;
END;
/


Trigger created.

SQL> CREATE SEQUENCE test_SEQ;
CREATE SEQUENCE test_SEQ
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
ORA-06512: at line 4

--DBMS_OUTPUT
Running SQL:CREATE SEQUENCE test_SEQ
Running SQL:CREATE SEQUENCE test_SEQ

is there another way to do such grant, I were thinking about firing a dbms_job that gives the necessary permission 10 or 15 seconds later

This is the version of our database:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
"CORE 11.2.0.3.0 Production"
TNS for HPUX: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

hi,

we have a 3rd party application requiring the application owner to have "ALTER USER" and "EXP_FULL_DATABASE" (including "EXECUTE ANY PROCEDURE") privileges.

we are afraid the application owner could use this privs to become DBA by getting proxy login on a default DBA account or granting the DBA role to his users.

i'm wondering if it would be possible to prevent a "ALTER USER <any dba user> GRANT CONNECT THROUGH ..." or "EXECUTE ANY PROCEDURE" by any non-expdp client using a DDL trigger?

cheers, michael