Home » RDBMS Server » Security » Viewing Trigger Code (Oracle 11g,)
Viewing Trigger Code [message #489712] Tue, 18 January 2011 10:35 Go to next message
feroze
Messages: 144
Registered: September 2005
Location: chicago
Senior Member
Hi,

C:\>sqlplus websystemd3/XXXXX@dev

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jan 18 10:00:59 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> show user
USER is "WEBSYSTEMD3"   --> SCHEMA OWNER

SQL> select trigger_name from user_triggers d where d.BASE_OBJECT_TYPE='TABLE' and d.TABLE_NAME='EXCHANGERATE';

TRIGGER_NAME
------------------------------
INSERT_RATEDATE
EXCHANGERATE_TRG

SQL> desc EXCHANGERATE
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 CREDITCURRENCYCODE                        NOT NULL VARCHAR2(3)
 DEBITCURRENCYCODE                         NOT NULL VARCHAR2(3)
 RATE                                               NUMBER
 DIVIDERATE                                         NUMBER
 RATEDATE                                           DATE
 MODIFYTYPE                                         VARCHAR2(5)
 ENTERED_BY                                         VARCHAR2(8)
 ENTERED_TIMESTAMP                                  DATE
 MODIFIED_BY                                        VARCHAR2(8)
 MODIFIED_TIMESTAMP                                 DATE
 HOUR                                               VARCHAR2(10)
 MINUTE                                             VARCHAR2(10)
 RATEEXPIRATION                                     DATE
 SCREENDATE                                         DATE
 MARKETCONVENTION                                   VARCHAR2(1)

SQL> Grant select, Insert,Update, delete on EXCHANGERATE  to websystemusrd3;

SQL> disconnect
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64
bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> conn  websystemusrd3/zzzzzz@dev
Connected.
SQL> show user
USER is "WEBSYSTEMUSRD3"      

SQL >  CREATE SYNONYM WEBSYSTEMUSRD3.EXCHANGERATE FOR WEBSYSTEMD3.EXCHANGERATE;

SQL> select count(*) from EXCHANGERATE;

  COUNT(*)
----------
       138

SQL>



Now connecting through User account SQL Developer, Dev Team wants to see Trigger Code, but couldn't.
Then Through Toad we connected, and selecting Synonym and then going to Scripts Tab shows all code including Trigger Code,

What more Grants are required for USER so that they can see Owner's Trigger Code,
I know of USER_SOURCE, a SYS owned , but granting a select on this to USER will give all access other schema's CODE also.

Any suggestions on what additional Grants are required here.


Thanks
Re: Viewing Trigger Code [message #489713 is a reply to message #489712] Tue, 18 January 2011 10:38 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Have you tried dbms_metadata?
Re: Viewing Trigger Code [message #489714 is a reply to message #489712] Tue, 18 January 2011 10:38 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Any suggestions on what additional Grants are required here.
Alternative write procedure, SHOW_TRIGGER_SOURCE, that is owned by WEBSYSTEMUSRD3, which accepts as input the trigger name.
SHOW_TRIGGER_SOURCE displays content of requested trigger

GRANT EXECUTE ON SHOW_TRIGGER_SOURCE TO <other_user>;
Re: Viewing Trigger Code [message #489717 is a reply to message #489714] Tue, 18 January 2011 10:45 Go to previous messageGo to next message
feroze
Messages: 144
Registered: September 2005
Location: chicago
Senior Member
thanks I tried and got this

SQL> GRANT EXECUTE ON sys.SHOW_TRIGGER_SOURCE TO websystemusrd3;
GRANT EXECUTE ON sys.SHOW_TRIGGER_SOURCE TO websystemusrd3
*
ERROR at line 1:
ORA-04042: procedure, function, package, or package body does not exist


Re: Viewing Trigger Code [message #489719 is a reply to message #489717] Tue, 18 January 2011 10:49 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Alternative write procedure, SHOW_TRIGGER_SOURCE, that is owned by WEBSYSTEMUSRD3, which accepts as input the trigger name.
Alternative write procedure, SHOW_TRIGGER_SOURCE, that is owned by WEBSYSTEMUSRD3, which accepts as input the trigger name.
Alternative write procedure, SHOW_TRIGGER_SOURCE, that is owned by WEBSYSTEMUSRD3, which accepts as input the trigger name.

if WEBSYSTEMUSRD3 owns SHOW_TRIGGER_SOURCE, no GRANT is required for WEBSYSTEMUSRD3; only for other USER
Re: Viewing Trigger Code [message #489721 is a reply to message #489714] Tue, 18 January 2011 10:49 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
BlackSwan wrote on Tue, 18 January 2011 16:38

Alternative write procedure, SHOW_TRIGGER_SOURCE, that is owned by WEBSYSTEMUSRD3, which accepts as input the trigger name.
SHOW_TRIGGER_SOURCE displays content of requested trigger

GRANT EXECUTE ON SHOW_TRIGGER_SOURCE TO <other_user>;


You have to write the procedure first.
Re: Viewing Trigger Code [message #489722 is a reply to message #489712] Tue, 18 January 2011 10:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Dev Team wants to see Trigger Code,

Dev Team has the code in the source file it provided to be installed in the database.

Regards
Michel
Re: Viewing Trigger Code [message #489723 is a reply to message #489722] Tue, 18 January 2011 10:54 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Michel Cadot wrote on Tue, 18 January 2011 16:53
Quote:
Dev Team wants to see Trigger Code,

Dev Team has the code in the source file it provided to be installed in the database.

Regards
Michel


now there's optimism.
Re: Viewing Trigger Code [message #489727 is a reply to message #489723] Tue, 18 January 2011 11:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, it is the correct way.

Regards
Michel
Re: Viewing Trigger Code [message #489728 is a reply to message #489727] Tue, 18 January 2011 11:32 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Of course it's the correct way, doesn't mean it's not optimistic to assume everyone does it.
Re: Viewing Trigger Code [message #489733 is a reply to message #489728] Tue, 18 January 2011 12:09 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't assume that everyone does it, I perfectly know this is not the case, I see it almost every day.
I just post the answer to give to any Dev Team who asks for this.

Regards
Michel
Previous Topic: sys system sysdba sysoper
Next Topic: Audit vault vs Database Vault
Goto Forum:
  


Current Time: Thu Mar 28 20:38:09 CDT 2024