Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

triggers

407048Jul 27 2005 — edited Aug 1 2005
I have two users A and B on the same database. User A owns 10 tables and also has triggers associated with its tables.

User B has all all table privileges(in terms of DML, DDL, etc) to User A's schema tables and other objects.
User B can list out all the tables by a
SELECT * FROM ALL_TABLES

and thus see the list of tables it has access.

I was hoping that the trigger information can also be viewable by User B.
But when I said
SELECT * FROM ALL_TRIGGERS , I am not able to see any records.
What can user B do to view the list of all the triggers of user A with the trigger code ?

Thanks

Comments

441309
You should grant select on all/user triggers to B using user A or DBA.
It depends on how many triggers you have available in A. you can do it manually one by one like this:
1- logon as user A
Then:
GRANT select on trigger_name to B;

or if the number of triggers are high use dynamic SQL to grant access using:

SELECT 'Grant select on ||trigger_name||' to aa; '
FROM user_triggers

Then issue each return statement.
Amir
Kamal Kishore

GRANT select on trigger_name to B;

Can you show that by an example please?

SQL> create trigger trig_emp
  2  before insert
  3  on emp
  4  for each row
  5  begin
  6    null ;
  7  end ;
  8  /

Trigger created.

SQL> grant select on trig_emp to kkishore ;
grant select on trig_emp to kkishore
                *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
hjz1321
In order to do so, user B needs CREATE ANY TRIGGER system privilege. Why do you need B to see A's trigger if I may ask?

Cheers,
Jianhui
407048
The need for user B to see user A's triggers are because :

B has full access to A's objects. user B can end up updating a table owned by A thus firing triggers built on it. The problem is currently there is no documentation or anything of that sort for B to see what triggers are there and what could be the impact in doing such an update with A's tables.
Hence B needs to technically find a way to query the database to find the triggers that it could fire (unknowingly). I still haven't found a way to do so

Thanks
Kamal Kishore
Hence B needs to technically find a way to query the database to find the triggers that it could fire (unknowingly).
Even if you were able to get a list of trigger names at runtime for table(s) in schema A, what possibily could you derive from those trigger names that will tell your application if it is safe to do the insert/update?

If there are triggers on that table that were meant to be fired when a insert/update/delete happens on that table, why is it that user B does not want that logic to execute? IF that logic does not need to be executed, why are those triggers there on that table, in first place?
181444
User A should provide that information to B. By default for security reasons only the trigger owner and DBA's can see the source for a trigger.

If fact part of the purpose of triggers is to do things that the user (DML submitter) does not have to know about and perhaps is not supposed to know about.

-- Mark D Powell --
407048
Thanks everybody who replied. That answers my question. This is something new I learnt today :-)
1 - 7
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 29 2005
Added on Jul 27 2005
#general-database-discussions
7 comments
267 views