Re: grant on a specified table to a user

From: Shinyday <Shinyday_at_gmail.com>
Date: 19 Jul 2006 20:08:15 -0700
Message-ID: <1153364895.178579.285010@75g2000cwc.googlegroups.com>

Vladimir M. Zakharychev wrote:
> Shinyday wrote:
> > Hello,
> > can I grant 'create table / drop' only on a speified table to a user?
> > There is user1 with tablespace user1_tablespace and there is also
> > table_a of user1.
> > So, I create user2 and let him use the same tablespace as user1, ie.
> > 'user1_tablespace'.
> > However user2 should not touch anything except for that table allowed
> > to him (user2).
> > For example user2 should can create and drop table 'table_a_b' in
> > user1_tablespace
> > doing 'create table table_a_b as select * from user1.table_a', but he
> > should neither access
> > other object in user1_tablespace nor create/drop any ohter table.
> > Meaning that user2 can only create table/drop that specified table
> > 'table_a_b' in the tablespace 'user1_tablespace' of user1. Is it
> > possible?
> > Could you please give me some helps or tips to do that?
> >
> > Thanks
> > Shinyday
>
> Hmm... Looks like a visit to Concepts and Developer's Guide books is
> due. That's the way Oracle security works by default: user2 can't
> access any object owned by user1 unless it's explicitly granted rights
> to do it. A user can't even create anything in the database unless it's
> granted appropriate privilege (including session - that is, you can't
> even connect to the database unless you're granted privilege to do so.)
> That two users share the tablespace mean nothing but that they share
> physical storage area for their data. Now, to answer your question, the
> following actions will set up privileges as you want them:
>
> CREATE USER USER1 IDENTIFIED BY WHATEVERISTHEPASSWORD1
> DEFAULT TABLESPACE USER1_TABLESPACE
> QUOTA UNLIMITED ON USER1_TABLESPACE;
>
> GRANT CREATE SESSION,CREATE TABLE TO USER1;
>
> CREATE USER USER2 IDENTIFIED BY WHATEVERISTHEPASSWORD2
> DEFAULT TABLESPACE USER1_TABLESPACE
> QUOTA UNLIMITED ON USER1_TABLESPACE;
>
> GRANT CREATE SESSION,CREATE TABLE TO USER2;
>
> connect user1/whateveristhepassword1
> create table table_a (list of columns);
> GRANT SELECT ON table_a TO USER2;
>
> connect user2/whateveristhepassword2
> create table table_a_b as select * from user1.table_a;
>
> You will find that USER2 can create the table as select, but will be
> unable to drop the source table or modify any data in it. To create
> table as select the only privileges needed are CREATE TABLE system
> privilege and SELECT object privilege on source object(s). SELECT on a
> table doesn't allow to modify or drop it, neither it allows to create
> tables with ownership transfer (that is, being USER2 you can't CREATE
> TABLE USER1.table_a, all tables you create will be owned by USER2.)
> Just don't give too wide privileges to the users you create, and things
> will work as you want them. Specifically, don't grant CONNECT and
> RESOURCE roles to the users as these are too wide for most purposes.
>
> Hth,
> Vladimir M. Zakharychev
> N-Networks, makers of Dynamic PSP(tm)
> http://www.dynamicpsp.com

Thanks Vladimir,
I tried it. It works as supposed. However the one thing missed is that this user2
should not create anything else than table_a_b. User2 could yet create other
tables since 'grant create table to user2' is given. Originally Imeant that user2
should can create 'table_a_b' only but no more.

user1_tablespace ---------------- table_a --------------------------------------------------> user2 should 'select' only this table table_b | table_c... and so on and create it in different name here

table_a_b

<------------------------------------------------------------------------------------------+
---------------------------------------------------------------

Now, following your answer user2 can do that above but the condition is that
user2 should not create any other table in user1_tablespace. It' the problem I have.

Thanks again for your detailed answer.
Shinyday Received on Wed Jul 19 2006 - 22:08:15 CDT