 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / Oracle / Oracle Server / September 2007want to see code of stored procedure but without permission to execute it
Hi people, is it possible in Oracle9i/10g to grant a user permission to see the code of someone else's procedure but without the permission to execute this procedure ? And I do not want to give this user the system privilege 'select any dictionary' because then he can see everything in the entire database. example : I want Oracle user "FLX0_RO" able to see the code of a procedure from owner "FLX0". But FLX0_RO may not be able to execute the procedure. Is this possible with standard Oracle grants ? Thanx. Benny. > Hi people, > is it possible in Oracle9i/10g to grant a user permission to see the > code of someone else's procedure but without the permission to execute > this procedure ? > And I do not want to give this user the system privilege 'select any > dictionary' because then he can see everything in the entire database. > example : I want Oracle user "FLX0_RO" able to see the code of a > procedure from owner "FLX0". But FLX0_RO may not be able to execute > the procedure. > Is this possible with standard Oracle grants ? One quick and dirty solution is to GRANT DEBUG on the procedure. I say dirty because it would allow the user to execute the procedure during a debugging session. However, according to the documentation, he also needs the GRANT CONNECT SESSION to do so in 10g, with both DBMS_DEBUG and JDWP-based debuggers. Alas in 9i, this grant does not seem to be needed for DBMS_DEBUG. Even in 10g I suspect the "GRANT DEBUG" might be sufficient in some debugging cases. So this looks like a dirty trick security-wise... ----- échangez opinions et commentaires dans les forums de discussion. http://www.usenetgratuit.com/ > example : I want Oracle user "FLX0_RO" able to see the code of a > procedure from owner "FLX0". But FLX0_RO may not be able to execute > the procedure. BTW what do you mean exactly by "see the code"? By means of the standard dictionary views and tools such as SQL Developer? Otherwise you can still create a materialized view on FLX0's user_source and grant select on it to FLX0_RO. ----- échangez opinions et commentaires dans les forums de discussion. http://www.usenetgratuit.com/ I was wondering whether owner FLX0 should simply GRANT SELECT on <proceudure> TO FLX0_R0 and to be safe do a REVOKE EXECUTE grant from the stored procedure. I am thinking that in this case the procedure will be readable, meaning you can do a SELECT TEXT from ALL_SOURCES WHERE NAME=<> and OWNER='FLX0' ORDER BY line. Regards, z1hou1 > I was wondering whether owner FLX0 should simply > GRANT SELECT on <proceudure> TO FLX0_R0 The answer lies within the SQL reference guide. ----- échangez opinions et commentaires dans les forums de discussion. http://www.usenetgratuit.com/ No, I'm afraid with 'grant debug on ... to ...' I still can not see the code of a procedure. Not even with 'debug any procedure'. Only with 'grant execute...' a user is able to see (Toad) the code of someone elses procedure. Toad does a query on ALL_OBJECTS to show a list of available objects for a user and this does not show a procedure on which you do not have a 'grant execute'. Thanx for your efforts guys. Kind regards. Benny Found something : in Oracle9i you still need an execute grant (debug is not enough) but in 10g a debug grant is enough (you see the code in all_source) So it depends on the Oracle version. Thanx guys, Benny. > Found something : in Oracle9i you still need an execute grant (debug > is not enough) but in 10g a debug grant is enough (you see the code in > all_source) > So it depends on the Oracle version. Thanks for this piece of info. ----- échangez opinions et commentaires dans les forums de discussion. http://www.usenetgratuit.com/ > No, I'm afraid with 'grant debug on ... to ...' I still can not see > the code of a procedure. > Not even with 'debug any procedure'. > Only with 'grant execute...' a user is able to see (Toad) the code of > someone elses procedure. So, this is with TOAD, the "detail" I asked about. With sqlplus, 'grant debug' does the trick. Cheers ----- échangez opinions et commentaires dans les forums de discussion. http://www.usenetgratuit.com/ > Hi people, > is it possible in Oracle9i/10g to grant a user permission to see the [quoted text clipped - 8 lines] > > Is this possible with standard Oracle grants ? Check out the USER_SOURCE table. Perhaps you could create a view based upon this table, and grant appropriate permission on that view? Cheers, Chris >> Hi people, >> is it possible in Oracle9i/10g to grant a user permission to see the [quoted text clipped - 16 lines] >Cheers, >Chris I think you mean ALL_SOURCE. USER_SOURCE would be for the user's own objects. B. > On Tue, 25 Sep 2007 06:39:37 -0700, "Chris ( Val )" > [quoted text clipped - 26 lines] > > - Show quoted text - I provided out developers a view to do this. Without looking up the view I believe it was based on USER_SOURCE where I just removed the line that limited the results to the user's code. You need to issue a couple of direct grants with the grant option on the underlying SYS objects to the view owner and then grant the view to whoever you want to allow to see package body source (also procedure, function, and trigger source). HTH -- Mark D Powell -- >> On Tue, 25 Sep 2007 06:39:37 -0700, "Chris ( Val )" >> [quoted text clipped - 37 lines] > >HTH -- Mark D Powell -- OK. And i am seriously asking here. Wouldn't it be better to use ALL_SOURCE? Nothing fancy to be required. B. > On Tue, 25 Sep 2007 09:43:58 -0700, Mark D Powell > [quoted text clipped - 47 lines] > > - Show quoted text - Brian, the ALL_SOURCE view will not show the package body code to a normal non-special privileged non-owner. The specification yes, but not the body. It is the body of the pacakge that the developers normally want to get at and verify against the source that they are working with. HTH -- Mark D Powell -- |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2010 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.