Re: Can't create view

I've always looked at the role situation as if it tables were real estate, roles were renters and direct grants were deed transfers.

If I create a table and grant you through a role you're a renter, you can't give my property to others, I could kick you out at any time. If you created a procedure that selected against my land and granted execute on that procedure to some other person then infact you have deeded my property and you were just a tenant. If you are granted specificially through object permissions then you can do with it what you want. You CAN deed to others the select. You're given special rights, specifically to you, by me. We have a contract. I can take away your rights specifically through a revoke or a quit-claim deed. If you want to do something with my property and it involves doing more than the lease intends - you need a direct grant.

On 29 May 2001 15:38:38 GMT, catherine_devlin_at_purpleturtle.com wrote:

>
>Your discovery is correct, and it matches with the official lore.
>
>I quote from Koch & Loney, _Oracle 8: The Complete Reference_:
>
>NOTE
>Priveleges that are granted to users via roles
>cannot be used as the basis for views,
>procedures, functions, packages, or foreign
>keys. When creating these types of database
>objects, you must rely on direct grants of the
>necessary priveleges.
>
>In other words, roles have never really been implemented in
>Oracle; only a sort of shadow "role" that works just well enough
>to fool us into using it, then lets us down when we try to
>do anything interesting with it.
>
>Why? I have no idea. If there is any explanation other than
>"lazy programmers", I would be interested in knowing it.
>
>Sorry this doesn't help...
>- Catherine
>
>In article <9et3v6$pta$1_at_news.uni-leipzig.de>, Jan Schaefer
><schaefer_at_kksl.uni-leipzig.de> writes:
>>I've created a table and granted SELECT to a user. After this the user was
>>able to create a view basing on my table in his schema.
>>
>>Then I granted SELECT to a role and granted the role to the user after
>>revoking the direct SELECT grant. Now the user was unable to create the same
>>view on my table. Oracle gives a error message that states: ORA-00942: Table
>>or View does not exist. But the SELECT statement that creates the view still
>>works.
>>
>>Does anybody know why it's not possible to create the view only with SELECT
>>rights over a role?
>>
>>Thanks in advance,
>>
>>Jan
>>
>>
>
>
> ----- Posted via NewsOne.Net: Free (anonymous) Usenet News via the Web -----
> http://newsone.net/ -- Free reading and anonymous posting to 60,000+ groups
> NewsOne.Net prohibits users from posting spam. If this or other posts
>made through NewsOne.Net violate posting guidelines, email abuse_at_newsone.net
Received on Tue May 29 2001 - 18:06:16 CDT