Re: grant on a specified table to a user

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 Thu Jul 20 2006 - 01:50:04 CDT