Re: grant on a specified table to a user

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 Received on Wed Jul 19 2006 - 06:55:56 CDT