Re: Oracle Security? - What Gives?

From: Scott Urman <surman_at_dlsun338.us.oracle.com>
Date: 1998/08/27
Message-ID: <6s482j$4oi$1@inet16.us.oracle.com>#1/1

In article <35E34BD9.3ED67DF7_at_headroom.com>, Troy Perchotte <max_at_headroom.com> writes:
|> I created a stored procedure that first drops and then creates a
|> sequence. It runs successfully; however, when I tested it from another
|> user's account, oracle returns an "insufficicient priveledges" error.
|>
|> Since I need a large number of users to be able to execute this
|> procedure, I granted a "create any sequence" to the group's role. I
|> still received the same error.
|>
|> After speaking with oracle support, I was told that I had to grant the
|> "create any sequence" to each individual account, and that the role's
|> permission "doesn't work in this case".

No, you have to grant CREATE ANY SEQUENCE only to the owner of the procedure. You can then grant EXECUTE on the procedure to all your users via a role, and they can then call it (but not from a stored procedure of their own).

Roles are disabled inside stored procedures. This is done because the privileges are checked only when the procedure is created, and not every time it is run. Since roles can be enabled and disabled on a per session basis, checking them for every procedure invocation would be prohibitive.

|>
|> What gives???
|>
|> Troy Perchotte
|>
|>

Scott Urman Oracle Corporation surman_at_us.oracle.com

Author of _Oracle PL/SQL Programming_ ISBN 0-07-882176-2 and

_Oracle8 PL/SQL Programming_ ISBN 0-07-882305-6 Published by Oracle Press - http://www.osborne.com/oracle/index.htm

"The opinions expressed here are my own, and are not necessarily that of Oracle Corporation"

Received on Thu Aug 27 1998 - 00:00:00 CDT