Re: user privileges; select any table

Maybe I could purpose a solution.

A basic user can Start with CONNECT and RESOURCE. Any tables they create they would be able to use, any other basic user would not see them. In your example schema1 belongs to user1. Look at DBA studio and go to the section labled "DB/Security/Roles/Connect" and touch the "System Priviledges" tabed panel. This will show you the "System Priviledges" for "Connect" and "Resource".

The easiest "System Priviledge" to understand is "Create Table. Notice that things like "Drop Table" and stuff like that are absent. To allow a user to Drop Tables you need to add that "System Priviledge" to the user or another "Role" that the user posseses.

• After User1 makes some objects they can use SQL to list them. For example "SELECT TABLE_NAME FROM USER_TABLES;" This will list all of user1 tables. Similar queries exist for other objects Triggers Views etc..., I'll let you look that up.

These list would be the "Objects" that would need to grant to a "Role". Make a "Role" and "Grant" the "Object Priviledges" to the "Role" you made. Assign the "Role" to (you guessed it) User3.

Give user1 "System Priviledge" "Create Role" etc... and show user1 how to do this. Then automated the task using a AWK(Unix) of GAWK(Linux/Windows). An SQL script would make the list of objects that User1 created and AWK and some other command line stuff would put in the "GRANT blah, blah, blah...TO blah, blah"

This will automate the task. Ultimately User1 should be the one that grants access to their tables. There may also be a way to do this with Triggers and stored proceedures. I just think the best way is when a user changes their schema they run a script on there PC that fixes the "Role" used to access their objects.

Marek Zyskowski
mzyskowski_at_aptilon.com

"John Lasta" <lasta_at_chello.at> wrote in message news:Hy9h8.318064$V52.3212540_at_news.chello.at...
> 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
>
>
>
>
>
Received on Tue Mar 05 2002 - 15:53:44 CST