Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!prodigy.com!pd2nf1so.cg.shawcable.net!residential.shaw.ca!sn-xit-03!sn-xit-06!sn-post-02!sn-post-01!supernews.com!corp.supernews.com!not-for-mail
From: Daniel Morgan <damorgan@exxesolutions.com>
Newsgroups: comp.databases.oracle.misc
Subject: Re: Getting GRANTs for actual user
Date: Tue, 09 Sep 2003 08:15:32 -0700
Organization: ATS
Message-ID: <1063120514.966375@yasure>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.4) Gecko/20030624 Netscape/7.1 (ax)
X-Accept-Language: en-us, en
MIME-Version: 1.0
References: <ccb2643c.0309090648.69f39267@posting.google.com>
In-Reply-To: <ccb2643c.0309090648.69f39267@posting.google.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
Cache-Post-Path: yasure!unknown@pond178.drizzle.com
X-Cache: nntpcache 2.4.0b5 (see http://www.nntpcache.org/)
X-Complaints-To: abuse@supernews.com
Lines: 53
Xref: newssvr20.news.prodigy.com comp.databases.oracle.misc:131468

R. Heydenreich wrote:

>Hi all,
>
>I'm looking for a possibility to get the GRANTs for a user for a
>certain SEQUENCE. The first solution is as follows:
>
>SELECT 'GRANT ' || PRIVILEGE || ' ON ' || 
>  (SELECT DISTINCT user_name
>   FROM v$open_cursor 
>   WHERE sid in 
>     (SELECT s.sid 
>      FROM v$session s 
>      WHERE s.audsid = USERENV('SESSIONID')
>      )
>   ) || '.' || table_name || ' TO ' || grantee || 
>     DECODE (grantable, 'YES', ' WITH GRANT OPTION', 'NO', '', '')
>GRANT_COMMAND
>FROM user_tab_privs 
>WHERE table_name = 'sequence_name';
>
>This generates the following lines, f.i.:
>
>GRANT_COMMAND 
>-----------------------------------------------------------------
>GRANT ALTER ON MYSCHEMANAME.SEQUENCE_NAME TO MYUSERNAME
>GRANT SELECT ON MYSCHEMANAME.SEQUENCE_NAME TO MYUSERNAME
>
>I think, this is not the optimal solution. How can I make this
>statement faster and easier?
>
>Greatings,
>Ralf.
>  
>
If I understand your question ... what does it have to do with your SQL?

To find grants on a sequence ...

SELECT * FROM all_tab_privs_made;
and
SELECT * FROM all_tab_privs_recd;

I know sequences aren't tables .... but that doesn't stop Oracle from 
recording them in the same place.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)