Home » SQL & PL/SQL » SQL & PL/SQL » Revoke DML on user's objects (Oracle 9i, Windows XP)
Revoke DML on user's objects [message #386592] Mon, 16 February 2009 03:40 Go to next message
oracle_me
Messages: 59
Registered: March 2008
Member
Hi ,
I log in as user1 create a second user as user2 .and create a table in user2's schema .
Here is what I do :
Create user user2 identified by user2 ;
alter user2 grant quota 100M on system;
grant create session to user2;

Create table user2.a ( col1 number);
insert into user2.a values(1);
commit;


Now My question is can I somehow ensure that user2 has only select privileges on table a , for which user2 is the owner ..

Please help..
Thanks



Re: Revoke DML on user's objects [message #386596 is a reply to message #386592] Mon, 16 February 2009 03:44 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
You don't.
You can't.
User can do what he likes to objects he owns.
You can stop a user from creating tables (by revoking create table grant) but you can't stop them inserting/updating/deleting.

If you want a user to have read-only access to a table then that table needs to belong to a different user.
Then you can just grant select on the table to the user who's getting read only access.
Re: Revoke DML on user's objects [message #386606 is a reply to message #386592] Mon, 16 February 2009 04:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
alter user2 grant quota 100M on system;

This is an invalid statement.

Regards
Michel
Re: Revoke DML on user's objects [message #386610 is a reply to message #386606] Mon, 16 February 2009 04:24 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

Noway;;

Quote:
Create table user2.a ( col1 number);
insert into user2.a values(1);


Above two statement looks like trying from Another/DBA User.

Babu
Re: Revoke DML on user's objects [message #386611 is a reply to message #386592] Mon, 16 February 2009 04:26 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
On Oracle any user is the owner of the objects in its own schema. So the user can do whatever he wants on its objects.

The case you just described is just a confusing situation with no sense!

Usually users with limited privilege should use synonyms to access objects without the need to specify the schema owner.

Bye Alessandro
Re: Revoke DML on user's objects [message #386629 is a reply to message #386611] Mon, 16 February 2009 05:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It can be done, but it's a bit messy.
You would need another user with the CREATE ANY TRIGGER privilege to create a Before Update or Insert or Delete trigger on each one of User2's tables, which simply raised an exception if the user performing the DML was User2.
Re: Revoke DML on user's objects [message #386634 is a reply to message #386629] Mon, 16 February 2009 05:39 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
JRowbottom wrote on Mon, 16 February 2009 12:05
It can be done, but it's a bit messy.
You would need another user with the CREATE ANY TRIGGER privilege to create a Before Update or Insert or Delete trigger on each one of User2's tables, which simply raised an exception if the user performing the DML was User2.



But keep in mind that the owner of the table could disable or drop that trigger whenever he wants.

As I told before, there's another way to do it. Any other workaround is just a mistake.


Bye Alessandro

[Updated on: Mon, 16 February 2009 05:41]

Report message to a moderator

Re: Revoke DML on user's objects [message #386648 is a reply to message #386634] Mon, 16 February 2009 06:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
But keep in mind that the owner of the table could disable or drop that trigger whenever he wants.


That's why you create the trigger from a user other than the table owner - you can't disable or drop a trigger on one of your tables that someone else owns without having the ALTER ANY TRIGGER or DROP ANY TRIGGER privileges
Re: Revoke DML on user's objects [message #386659 is a reply to message #386648] Mon, 16 February 2009 08:23 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
JRowbottom wrote on Mon, 16 February 2009 13:59
you can't disable or drop a trigger on one of your tables that someone else owns without having the ALTER ANY TRIGGER or DROP ANY TRIGGER privileges


Sorry! Yes, that's right, but what about a drop table then! A drop table is always granted to a user on its own objects.

I can't understand what could be wrong on using synonyms, in such a case, instead of doing all this sense less job.

Bye Alessandro
Re: Revoke DML on user's objects [message #386663 is a reply to message #386659] Mon, 16 February 2009 08:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I quite agree - synonyms to tables in another schema and SELECT only grants are definitely the correct way to implement this sort of requirement.

I was just pointing out that if you're feeling sufficiently masochistic, you can stop a user editing his own tables.
Re: Revoke DML on user's objects [message #386667 is a reply to message #386592] Mon, 16 February 2009 08:52 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
And if that user knows what he's doing he can get round your triggers.

Create table newtable as select * from oldtable
drop table oldtable --bye bye triggers!
rename newtable as oldtable


Will do the job
Re: Revoke DML on user's objects [message #386669 is a reply to message #386667] Mon, 16 February 2009 09:17 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
drop table oldtable --bye bye triggers!

Except if there is a database trigger that doesn't allow him to do so.

Regards
Michel
Previous Topic: how to use INDEX?
Next Topic: procedure to change db link (merged)
Goto Forum:
  


Current Time: Sat Dec 10 20:18:05 CST 2016

Total time taken to generate the page: 0.10625 seconds