Re: user privileges; select any table

AAAUUUGGHHH! CONNECT and RESOURCE were supposed to be murdered horribly and buried deep somewhere in the Mojave Desert!

Never use them, totally outrageous. Bad juju. Impaired Karma. Worth 2000 years in Purgatory if you are Catholic.

First, anyone who can create a given table in their own schema, can drop it. Or anything else. CONNECT and RESOURCE grant such wildly broad privileges, I never even bring them up except in DBA classes in a list of "THINGS NEVER TO DO".

Use roles to grant these users specific permissions on objects. If they must employ stored procedures etc those procedures should be owned by the owner of those objects, else direct (not by role) object grants to each user for each object will be required.

CONNECT. *shudder*

'not a good thing anymore in Kansas, Toto, not with a million and one more granular privileges to grant' "Woof woof!" 'Exactly, Toto.'

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 Tue Mar 05 2002 - 21:57:14 CST