Re: ORA-01933 role privileges error

In article <38BD6925.9210C7_at_us.ibm.com>,   Doug Cowles <dcowles_at_us.ibm.com> wrote:
> In creating a view for a new user with NO roles. I noticed that part
> of the view
> referenced a package owned by a different user. I created a synonym
 for
> hat
> package, and re-ran the view code. Then I got
> Creating view ra_site_uses_allcai_v
> arp_addr_pkg.format_address(addr.address_style,,
> ERROR at line 246:
> ORA-01933: cannot create a stored object using privileges from a role.
>
> So, I granted explicit execute privileges and everything was fine.
> But,
> this new user has connect and resource roles - what privileges from a
> role is it

well, then this new user does have some roles ;) You said "a new user with NO roles" -- but they have some...

> complaining about? the new user doesn't have execute procedure so what
> gives?
> Also what does it mean cannot create stored object - how is that
 related
> to executing
> a package.
>

The stored object in question is the view. You cannot create stored objects (packages, procedures, functions, triggers, views) that get their access to the other objects they reference from a ROLE. If I grant select on a TABLE to "connect", everyone with connect can SELECT from that table but they *will not* be able to create a view on it nor a stored procedure that references it.

see http://osi.oracle.com/~tkyte/Misc/RolesAndProcedures.html for how this relates to stored procedures (but its basically the same caveats for views as well).

By granting access directly to the referenced object -- you allowed this view to be created.

> Thanks,
> Dc.
>
>

--
Thomas Kyte                              tkyte_at_us.oracle.com
Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp

--
Thomas Kyte                              tkyte_at_us.oracle.com
Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.