Re: user privileges; select any table

On Tue, 05 Mar 2002 20:05:59 GMT, "John Lasta" <lasta_at_chello.at> wrote:

>how can an oracle database user make all his tables visible to another user,
>without granting object privileges, that means without granting every table
>seperately, e.g.:
>"grant select on <table01> to <user>"
>"grant select on <table02> to <user>"...
>------------------------------------
>e.g.
>there are three oracle database users:
>- schema01
>- schema02
>- user03
>
>user03 shall see all tables from schema01 but not those of schema02;
>in schema01 many tables are created (and deleted) permanently and user03
>wants to see them immediately without disturbing schema01 to give him the
>select privilege.
>
>Any advice would be great !!
>lasta
>
>
>
>

This can't be done without compromising the security of the database, as it is basically all or nothing. 'select any table' privilege within a schema doesn't exist, and Oracle has never been a zero-administration product. You could consider using roles, however in that case you still would need to grant privilege for every individual table. Databases are commonly designed, which is something different than the programs you create in your own bikeshed. Unfortunately granting DBA or SELECT ANY table privileges to basically ALL users is a common practice with many developers. Your request 'user03 want to see them immediately' points to such an environment. That is exactly the environment many DBAs do not even want to *touch*, let alone administrate.

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Tue Mar 05 2002 - 14:43:46 CST