Xref: alice comp.databases.oracle.server:61050 Path: alice!news-feed.fnsi.net!cyclone.i1.net!newsfeed.enteract.com!newsfeed.tli.de!news-fra.maz.net!nnrp-ham.news.is-europe.net!not-for-mail From: "Ulrik Hoffmann" Newsgroups: comp.databases.oracle.server Subject: Re: problem with select from v$session Date: Sun, 8 Aug 1999 13:36:11 +0100 Organization: [posted via] IS Internet Services GmbH & Co, Germany Lines: 39 Message-ID: <7ojq6l$erk$1@nnrp-ham.news.is-europe.net> References: <37AA8C71.23677B72@synergy-infotech.com> X-Trace: nnrp-ham.news.is-europe.net 934112277 15220 195.179.177.92 (8 Aug 1999 11:37:57 GMT) NNTP-Posting-Date: 8 Aug 1999 11:37:57 GMT X-Newsreader: Microsoft Outlook Express 5.00.2314.1300 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300 Hi, this behaviour is absolutely correct. If you want to use a Table or View or any other object from another user (in this case SYS) in a stored prdocedure or in a package, you need the grant of the table directly from the user or the select any table grant from SYSTEM. So grant select on v$session to instead of select any table would cause your procedure to compile as well. This behaviour exists for the following reason: if you grant execute on your procedure to another user, he'll got success on the v$session view as well, even he has not dba-rights or other rights on successing this table. Do you see the security problem? If you give a user select grants over a role (like dba), you can be sure, that he can not create a view or procedure and grant anything on it to another user. On SQL-Plus, you've got the grant to select on v$session over the dba-role. As already said, you need the grant directly if you want to use it in a package. I would never grant the 'select any table' grant because of the security problem, you've got with this grant. Hope that helped, sorry for my bad english, Bye Uli > > If from the sqlplus i query the view v$session it gives me the result > but if the same select statement inside a stored procedure it says > v$session not declared. Now if i grant select any table to this user > then the stored procedure is compiling fine. >