OraFAQ Forum: Security » Removing privileges on a table after commited update

Home » RDBMS Server » Security » Removing privileges on a table after commited update

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Removing privileges on a table after commited update [message #64344]

Fri, 14 January 2005 10:50

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,

Report message to a moderator

Re: Removing privileges on a table after commited update [message #64345 is a reply to message #64344]

Fri, 14 January 2005 15:03

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;
/

Report message to a moderator

Re: Removing privileges on a table after commited update [message #64346 is a reply to message #64344]

Fri, 14 January 2005 15:13

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.

Report message to a moderator

Re: Removing privileges on a table after commited update [message #64365 is a reply to message #64345]

Mon, 17 January 2005 16:53

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?

Report message to a moderator

Previous Topic:	DBMS_OBFUSCATION_TOOLKIT.MD5
Next Topic:	Length of the string to be passed to Des Encryption

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Sep 21 03:44:34 CDT 2023