Re: Privilege Problem

From: <s_teng_at_hotmail.com>
Date: Fri, 13 Nov 1998 15:07:41 GMT
Message-ID: <72hhvt$6tr$1@nnrp1.dejanews.com>

If I log on as that user, and issues the following SQL, I get the following results:

SQLWKS> select * from session_roles

2>
ROLE

0 rows selected.
SQLWKS> select * from user_role_privs

     2>
USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---

0 rows selected.

As that user I can "CREATE OR REPLACE" any proc or function that already exists under that schema, or I can even "DROP" a func or proc, but I cannot "CREATE" a new one? Does this sound right?

I know I can create the procs and funcs under another user and grant privileges and make synonyms, but the user currenty has reports and code that have the schema owner hard coded and I want minimal impact while not allowing the user to modify these objects.

>
> Teng,
>
> Might it be possible you overlooked a role that
> your user may have? Although when executing a stored
> procedure rolls are ignored, I don't believe that is
> the case when creating them.
>
> Jay!!!
>
> s_teng_at_hotmail.com wrote:
>
> > Hopefully somene can help me with a privilege problem.
> >
> > I have created a User, granted it ALL privileges and then created a stored
> > proc under that user name.
> >
> > Then I removed all privileges and only added only the minimal ones. That
> > user can no longer create stored procedures (Which is what I want), but that
> > user can continue to REPLACE the original stored proc. Here is the command
> > to grant privileges: GRANT alter session, create session, analyze any,
> > comment any table, insert any table, update any table, select any table,
> > delete any table, select any sequence, execute any procedure,lock any table,
> > unlimited tablespace TO my_user;
> >
> > How can I prevent this user from replacing this proc? The user does not
have
> > the privilege to create procedures, yet it can "CREATE OR REPLACE".
> >
> > -----------== Posted via Deja News, The Discussion Network ==----------
> > http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>
> --------------2AC5AF846588EF60AAF783B5
> Content-Type: text/html; charset=us-ascii
> Content-Transfer-Encoding: 7bit
>
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <HTML>
> <TT>Teng,</TT><TT></TT>
> <TT>     Might it be possible you overlooked a role
> that</TT>
> <TT>your user may have?  Although when executing a stored</TT>
> <TT>procedure rolls are ignored, I don't believe that is</TT>
> <TT>the case when creating them.</TT><TT></TT>
> <TT>Jay!!!</TT>
> <TT></TT> 
> s_teng_at_hotmail.com wrote:
> <BLOCKQUOTE TYPE=CITE>Hopefully somene can help me with a privilege problem.
> I have created a User, granted it ALL privileges and then created a
> stored
> proc under that user name.
> Then I removed all privileges and only added only the minimal ones. 
> That
> user can no longer create stored procedures (Which is what I want),
> but that
> user can continue to REPLACE the original stored proc.  Here is
> the command
> to grant privileges: GRANT alter session,  create session, 
> analyze any,
> comment any table,  insert any table, update any table, select
> any table,
> delete any table, select any sequence, execute any procedure,lock any
> table,
> unlimited tablespace TO my_user;
> How can I prevent this user from replacing this proc?  The user
> does not have
> the privilege to create procedures, yet it can "CREATE OR REPLACE".
> -----------== Posted via Deja News, The Discussion Network ==----------
> <A

HREF="http://www.dejanews.com/">http://www.dejanews.com/</A>   &n bsp;  
> Search, Read, Discuss, or Start Your Own</BLOCKQUOTE>
> </HTML>
>
> --------------2AC5AF846588EF60AAF783B5--
>
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Nov 13 1998 - 09:07:41 CST