Re: grant on a specified table to a user

From: Shinyday <Shinyday_at_gmail.com>
Date: 21 Jul 2006 00:46:23 -0700
Message-ID: <1153467983.532286.91250@i3g2000cwc.googlegroups.com>

Thanks Vladimir,
I'll try this out. In the beginning I didn't think that I need trigger. It's something
more than I thought. I thought it could be possible with some 'grant privileges'.

Shinyday

Vladimir M. Zakharychev wrote:
> Shinyday wrote:
> > 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

>

> You can solve this with a DDL trigger. Something like this:

>

> create trigger deny_table_create before create on USER2.SCHEMA
> begin
> if ora_dict_obj_type = 'TABLE' and ora_dict_obj_name <> 'TABLE_A_B'
> then
> raise_application_error(-20000, 'You are not allowed to create this
> table.');
> end if;
> end deny_table_create;

>

> The trigger can be created by any user with CREATE ANY TRIGGER
> privilege.

>


> Hth,

>     Vladimir M. Zakharychev

>     N-Networks, makers of Dynamic PSP(tm)

>     http://www.dynamicpsp.com

Received on Fri Jul 21 2006 - 02:46:23 CDT