Prevent Table Owner from Deleting Records of a Table [message #607744] |
Tue, 11 February 2014 09:39 |
|
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 #607755 is a reply to message #607754] |
Tue, 11 February 2014 10:19 |
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 #607779 is a reply to message #607756] |
Tue, 11 February 2014 19:11 |
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 #607802 is a reply to message #607744] |
Wed, 12 February 2014 02:19 |
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 #607890 is a reply to message #607755] |
Thu, 13 February 2014 03:04 |
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:19Another 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
|
|
|