grant on a specified table to a user

FAQ

Memberlist

Usergroups

Profile

Preferences

· nixdoc.net

· man pages

· Linux HOWTOs

· FreeBSD Tips

· Forums

Forum index » Databases » Oracle » Server

grant on a specified table to a user

Page 1 of 1 [5 Posts]

View previous topic :: View next topic

Author

Message

Shinyday
*nix forums beginner

Joined: 20 Feb 2006
Posts: 5

Posted: Wed Jul 19, 2006 6:03 am Post subject: Note

grant on a specified table to a user

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

Vladimir M. Zakharychev
*nix forums Guru Wannabe

Joined: 22 Mar 2006
Posts: 144

Posted: Wed Jul 19, 2006 11:55 am Post subject: Note

Re: grant on a specified table to a user

Shinyday wrote:

Quote:

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

Shinyday
*nix forums beginner

Joined: 20 Feb 2006
Posts: 5

Posted: Thu Jul 20, 2006 3:08 am Post subject: Note

Re: grant on a specified table to a user

Vladimir M. Zakharychev wrote:

Quote:

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

Vladimir M. Zakharychev
*nix forums Guru Wannabe

Joined: 22 Mar 2006
Posts: 144

Posted: Thu Jul 20, 2006 6:50 am Post subject: Note

Re: grant on a specified table to a user

Shinyday wrote:

Quote:

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

Shinyday
*nix forums beginner

Joined: 20 Feb 2006
Posts: 5

Posted: Fri Jul 21, 2006 7:46 am Post subject: Note

Re: grant on a specified table to a user

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:

Quote:

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

Google

Display posts from previous:

Page 1 of 1 [5 Posts]

View previous topic :: View next topic

The time now is Sun Nov 23, 2008 1:55 pm | All times are GMT

Forum index » Databases » Oracle » Server

Jump to:

Topic		Author	Forum	Replies	Last Post
Similar Topics
	postfix smtp authentication using mysql stored user/pass	rtresidd	Postfix	0	Fri Oct 03, 2008 5:58 am
	User Environment - export PATH variable	paalepu	AIX	0	Tue Sep 12, 2006 8:12 pm
	How to ALTER a table using the ALTOBJ procedure	Serge Rielau	IBM DB2	1	Fri Jul 21, 2006 1:06 pm
	Capturing user login Information of windows	sachin	PHP	3	Fri Jul 21, 2006 5:44 am
	Recommend an email program for the debian-user-digest?	DJ Hackenbruch	Debian	5	Fri Jul 21, 2006 3:20 am

Mortgage Calculator | Loans | Credit Cards | Myspace Layouts | Xbox Mod Chip

Other DeniX Solutions sites: Unix/Linux blog | electronics forum | medicine forum | science forum |

[ Time: 0.2385s ][ Queries: 16 (0.1251s) ][ GZIP on - Debug on ]