Home » RDBMS Server » Security » Removing privileges on a table after commited update
Removing privileges on a table after commited update [message #64344] Fri, 14 January 2005 10:50 Go to next message
RYAN
Messages: 22
Registered: December 2000
Junior Member
Hello,

I would like to remove the ability for someone to update a table/view once they have updated it once and committed that update. What is the best way to do this?

Thanks,
Re: Removing privileges on a table after commited update [message #64345 is a reply to message #64344] Fri, 14 January 2005 15:03 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
you could just have a trigger which raises an error on update and delete.

CREATE OR REPLACE TRIGGER abc_no_ud
--ABC no Update or Delete
BEFORE UPDATE or DELETE
ON abc
FOR EACH ROW
BEGIN
  IF UPDATING THEN raise_application_error(-20501, 'Update on ABC prohibited'); END IF;
  IF UPDATING THEN raise_application_error(-20502, 'Delete on ABC prohibited'); END IF;
END;
/
Re: Removing privileges on a table after commited update [message #64346 is a reply to message #64344] Fri, 14 January 2005 15:13 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Reading your requirement more closely, you'd need a flag field to indidate that the row's is "updated already". Also consider that you'd probably like a way to override the check for a privileged user. You could check the username making the change, the module name (from v$session), IP address or whatever in the trigger. You could also reference a global variable in a package which you set for the duration of the priviledged user's session or whenever.
Re: Removing privileges on a table after commited update [message #64365 is a reply to message #64345] Mon, 17 January 2005 16:53 Go to previous message
jack
Messages: 123
Registered: September 2000
Senior Member
Hi,andrew
I tried your trigger. It couldn't resolve the person's question because I couldn't change the table at all. Even the first time!

Do you have better idea?
Previous Topic: DBMS_OBFUSCATION_TOOLKIT.MD5
Next Topic: Length of the string to be passed to Des Encryption
Goto Forum:
  


Current Time: Wed Dec 08 22:19:55 CST 2021