Re: user privileges; select any table

Marek and I had an offline discussion about my AUUUUGH!

There are too many 3rd party packages out there that want DBA privs, that grant CONNECT instead of CREATE SESSION to users, etc etc.

Marek, as far as your suggestions go, all good. A stored procedure that would do as you wish might be one owned by the owner of the tables, and also have a table for executing users that could have

username object_name allowed_operation BOO STATE_TAX TRUNCATE etc
and grant execute on this proc to authorized users; what they can do is further constrained by what's in the control table.

What I don't like, and I have seen too many times, is people creating procs in SYS to get around the you-can't-grant-TRUNCATE so only the table owner or someone with TRUNCATE ANY TABLE can do it, and with inadequat security controls to boot, on top of it.

Otherwise granting desired object level priveliges to a ROLE, then granting that role to desired users, would do what you want mostly, except of course, TRUNCATE. That needs a proc owned by the object owner, no other way around it other than granting TRUNCATE_ANY_TABLE to people, a bad idea.

RSH.
"Marek Zyskowski" <mzyskowski_at_aptilon.com> wrote in message news:Fbbh8.26046$X2.271560_at_nnrp1.uunet.ca...
> 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 Wed Mar 06 2002 - 11:23:31 CST