Home » SQL & PL/SQL » SQL & PL/SQL » Prevent Table Owner from Deleting Records of a Table
Prevent Table Owner from Deleting Records of a Table [message #607744] Tue, 11 February 2014 09:39 Go to next message
praveenramaswamy
Messages: 34
Registered: December 2013
Member
Dear Experts

Need your assistance and inputs here.

Schema Name: TEST
Table Name: TESTTABLE (Schema Owner of this table is TEST)

Is there a way i can prevent DELETE on this table if i login to the DB as TEST.

i.e if i login to the DB as TEST, i shouldnt be able to DELETE records from this TESTTABLE.

Is there a way other an writing DDL Trigger to prevent the deletion. Please assist
Re: Prevent Table Owner from Deleting Records of a Table [message #607745 is a reply to message #607744] Tue, 11 February 2014 09:42 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
If you have Enterprise Edition licences, you could use the Virtua;l Private Database mechanism to append a predicate of (for example) 1=2 to all DELETE statements by user TEST.
Re: Prevent Table Owner from Deleting Records of a Table [message #607746 is a reply to message #607745] Tue, 11 February 2014 09:44 Go to previous messageGo to next message
praveenramaswamy
Messages: 34
Registered: December 2013
Member
Dear john
Thanks for your quick reply.If you have some time, can u please elaborate, or can you point me to some document on how to do this
Re: Prevent Table Owner from Deleting Records of a Table [message #607747 is a reply to message #607746] Tue, 11 February 2014 09:45 Go to previous messageGo to next message
praveenramaswamy
Messages: 34
Registered: December 2013
Member
and my DB is Oracle 10.2.0.5
Re: Prevent Table Owner from Deleting Records of a Table [message #607749 is a reply to message #607746] Tue, 11 February 2014 09:51 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Linky
Re: Prevent Table Owner from Deleting Records of a Table [message #607750 is a reply to message #607749] Tue, 11 February 2014 09:53 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Is there a way i can prevent DELETE on this table if i login to the DB as TEST.
REVOKE CREATE TABLE FROM TEST;
Re: Prevent Table Owner from Deleting Records of a Table [message #607751 is a reply to message #607750] Tue, 11 February 2014 09:55 Go to previous messageGo to next message
praveenramaswamy
Messages: 34
Registered: December 2013
Member
BlackSwan wrote on Tue, 11 February 2014 09:53
>Is there a way i can prevent DELETE on this table if i login to the DB as TEST.
REVOKE CREATE TABLE FROM TEST;


Dear Blackswan
Wouldnt it stop me from create table at all. I just wanted stop the owner "TEST" from deleting only the table.
Re: Prevent Table Owner from Deleting Records of a Table [message #607752 is a reply to message #607751] Tue, 11 February 2014 09:57 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
Create the table as SYSTEM in TEST's schema:

E.g.

CREATE TABLE test.testtable ... etc


HTH
-g
Re: Prevent Table Owner from Deleting Records of a Table [message #607753 is a reply to message #607752] Tue, 11 February 2014 10:00 Go to previous messageGo to next message
praveenramaswamy
Messages: 34
Registered: December 2013
Member
gazzag wrote on Tue, 11 February 2014 09:57
Create the table as SYSTEM in TEST's schema:

E.g.

CREATE TABLE test.testtable ... etc


HTH
-g


Sorry didnt understand your solution. I shouldnt be able to DELETE
Re: Prevent Table Owner from Deleting Records of a Table [message #607754 is a reply to message #607751] Tue, 11 February 2014 10:05 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
praveenramaswamy wrote on Tue, 11 February 2014 15:55

Wouldnt it stop me from create table at all?


Re: Prevent Table Owner from Deleting Records of a Table [message #607755 is a reply to message #607754] Tue, 11 February 2014 10:19 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
Another way would be to have two schemas. An "owner" (TEST_OWNER) schema and a "login" schema (TEST). Create all the objects under the TEST_OWNER schema and grant the necessary privileges to user TEST. Then lock the "owner" schema and have the user log in as TEST.

HTH
-g
Re: Prevent Table Owner from Deleting Records of a Table [message #607756 is a reply to message #607755] Tue, 11 February 2014 10:21 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
And another way: cover the table with a view, and use INSTEAD OF triggers to trap the DELETEs
Re: Prevent Table Owner from Deleting Records of a Table [message #607779 is a reply to message #607756] Tue, 11 February 2014 19:11 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Nobody has mentioned a BEFORE DELETE statement-level trigger with a RAISE_APPLICATION_ERROR statement to make deletes fail. You could wrap it in an IF to check the current user id using SYS_CONTEXT().

Am I missing something? Is there some reason this would not work? I understand that it wouldn't stop TEST from DROPping the table - and maybe there might be loopholes with the DELETE clause of MERGE statements, but it might meet the OP's requirement.

Ross Leishman
Re: Prevent Table Owner from Deleting Records of a Table [message #607793 is a reply to message #607779] Wed, 12 February 2014 00:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
t wouldn't stop TEST from DROPping the table


Or drop/disable the trigger.

Re: Prevent Table Owner from Deleting Records of a Table [message #607802 is a reply to message #607744] Wed, 12 February 2014 02:19 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
It won't stop truncate either, plus I think the OP specifically excluded triggers in their request:
praveenramaswamy wrote on Tue, 11 February 2014 15:39

Is there a way other an writing DDL Trigger to prevent the deletion. Please assist

I assume they actually meant DML there.
Re: Prevent Table Owner from Deleting Records of a Table [message #607847 is a reply to message #607793] Wed, 12 February 2014 10:59 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Michel Cadot wrote on Wed, 12 February 2014 01:14

Or drop/disable the trigger.


Depends. OP can create trigger owned by different user. Then table owner would need DROP ANY TRIGGER to bypass restriction.

SY.
Re: Prevent Table Owner from Deleting Records of a Table [message #607848 is a reply to message #607847] Wed, 12 February 2014 11:02 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Schema Name: TEST
>Table Name: TESTTABLE (Schema Owner of this table is TEST)
>Is there a way i can prevent DELETE on this table if i login to the DB as TEST.

why must TESTTABLE be owned by TEST?
Re: Prevent Table Owner from Deleting Records of a Table [message #607849 is a reply to message #607847] Wed, 12 February 2014 11:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Right, I confused constraints and triggers, for the former the owner must be the same as the owner of the table.


Re: Prevent Table Owner from Deleting Records of a Table [message #607851 is a reply to message #607848] Wed, 12 February 2014 11:21 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
Hence my suggestion of a locked "owner" schema and a "login" schema, TEST.
Re: Prevent Table Owner from Deleting Records of a Table [message #607890 is a reply to message #607755] Thu, 13 February 2014 03:04 Go to previous message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Depends how sneaky they want to be. Could CTAS a new table with less rows, drop the old and rename the new.

This, this is how you do it:

gazzag wrote on Tue, 11 February 2014 16:19
Another way would be to have two schemas. An "owner" (TEST_OWNER) schema and a "login" schema (TEST). Create all the objects under the TEST_OWNER schema and grant the necessary privileges to user TEST. Then lock the "owner" schema and have the user log in as TEST.

HTH
-g



Proper degrees of separation.

[Updated on: Thu, 13 February 2014 03:05]

Report message to a moderator

Previous Topic: PLS-00302: component must be declared
Next Topic: Query help
Goto Forum:
  


Current Time: Thu Mar 28 20:59:10 CDT 2024