Re: Security on Roles and packages

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Sat, 21 Jul 2001 21:37:41 GMT
Message-ID: <tkukclc94jvbae@beta-news.demon.nl>

"James Williams" <techsup_at_mindspring.com> wrote in message news:3b4f4c4f.265112875_at_nntp.mindspring.com...
> I reviewed various Oracle pubs.
>
> I created a role and assigned it all the required security to access
> schema A. I granted this role to USER B.
>
> Schema B has a package.procedure that access tables in schema A.
>
> When I compiled it would not compile saying the affected tables not
> found. I granted to the USER B explictly instead of the role and it
> worked.
>
> Of course, this is definer rights and not invoker rights. No default
> role assigned to USER B. Could that be my problem?
>
> When I access the tables using SQLPLUS it is fine even without the
> explicit grants.
>
>
>
>

> Of course, this is definer rights and not invoker rights. No default
> role assigned to USER B. Could that be my problem?

As it is definer right's roles are ignored during compilation of your procedure. If you want to use definers rights you should use direct grants.

Hth,

Sybrand Bakker, Senior Oracle DBA Received on Sat Jul 21 2001 - 16:37:41 CDT