Re: SELECT ANY TABLE except from these schema

Comments embedded.

On Thu, 28 Apr 2005 00:15:45 +0200, Rick Denoire <100.17706_at_germanynet.de> wrote:

>Hello
>
>All users of one database usually get the privilege "SELECT ANY TABLE"
>(assigned thorugh a role).

This is a BAD idea, as this will render your database fully unsecure.

 But now a special schema exists, which
>should be excluded from general access.
>
>Now, it seems that this privilege must be revoked and replaced by
>object privileges for all tables of all allowed schemas. This is
>cumbersome because for any new table created, again an additional
>object privilege must be granted.
>
>So my questions:
>Is it possible to exclude some schemas from the SELECT ANY TABLE
>privilege?

The obvious answer is NO. Any = any.

>Can object privileges be granted schemawise as a joker?
>Something like: GRANT SELECT ON THIS_USER.*
>(I don't think this is possible but you understand the idea).

The obvious answer is no. It is not in the syntax so it is not possible.

>Can I define a role to mimic all SELECT privileges of a user on his
>own schema? (I mean without having to manually grant individual object
>privileges).
>

That role would need to be maintained: ie as soon as you add a table, you would need to issue a select to the role. So what do you gain.

>In summary: Is there a more elegant way to grant the access as
>described without having to track individual tables?
>

Write a bit of pl/sql selecting your tables from the dictionary. Less than 5 minutes work.

>Thanks a lot
>Rick Denoire

--
Sybrand Bakker, Senior Oracle DBA