Schema level trigger

XYZDBA is the owner of all the tables in a schema called XYZ.

I have created public synonyms for all these tables.

I have created USER1 who has alter/insert/update/select/reference priviliges on the public synonyms created above.

I have a schema level trigger like

CREATE OR REPLACE TRIGGER XYZDBA_logintrig
AFTER LOGON ON XYZDBA.SCHEMA
DECLARE
      /* variable declarations-------start*/
      ssn number(8)  ;
      hst varchar(30) ;
      logtime varchar(10);
BEGIN
      select sys_context('USERENV','SESSIONID') into ssn from dual;
      select sys_context('USERENV','HOST') into hst from dual;
      select to_char(SYSDATE,'HH24:MM:SS') into logtime from dual;
      insert into  XYZDBA.Audloginlogs(user_id,session_id,host,logon_day,logon_time)
            values (user,ssn,hst,to_date(sysdate),logtime) ;
END ;

XYZDBA has administer database trigger system privilige.

Whenever USER1 logins in I want the trigger to create a row in audloginlogs table ?

Whenever I login as XYZDBA a row is getting created . But, when I login as USER1 the row is not getting created .

What am I  doing wrong ?

regards
jana
janarAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

seazodiacCommented:
This is actually the normal behavior of your trigger.
in oracle, schema is owned by individual user.
Users can log on the oracle database as a whole to use other's schema objects, but they cannot log on other's shema.

for example:

The TEST oracle database has two users A and B, and their corresponding SCHEMAs.
User A and User B can both log on the TEST database, if privileges assigned properly, they can cross-reference each other's schema object, but user A cannot log on the user B's schema, and vice versa.
But User A, by default, log on his own schema by logging on the TEST database, so is USER B.

so if your above trigger is changed like this in the header:
CREATE OR REPLACE TRIGGER XYZDBA_logintrig
AFTER LOGON ON DATABASE
---
---

You will have a record of each user logon.

but this is not what you want, I think.

in conclusion, for events like which users are logging onto the schema level , I guess you cannot do that.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
catchmeifuwantCommented:
The trigger is created to fire,only when XYZDBA logson.

CREATE OR REPLACE TRIGGER XYZDBA_logintrig
AFTER LOGON ON XYZDBA.SCHEMA  --- Only for XYZDBA

From Oracle docs:
-----------------
Creating a trigger on DATABASE implies that the triggering event is outside the scope of a user (for example, database STARTUP and SHUTDOWN), and it applies to all users (for example, a trigger created on LOGON event by the DBA).

Creating a trigger on SCHEMA implies that the trigger is created in the current user's schema and is fired only for that user.

------------------

If you need to fire it for USER1,then use a DB level trigger..


 
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.