[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Lock records in oracle table based on username

Posted on 2013-02-06
11
Medium Priority
?
560 Views
Last Modified: 2013-02-06
I need to create a trigger in Oracle 10g, in which a record in the table can be locked by any user, but unlocked by only one or two specific ones. I have a column TAG_LOCK and it is available to everyone to write to and now I want to create the trigger where if username is not the designated "un-locker" the record cannot be changed. Below is what I have so far, but it is not working for me - the record is still locked for everyone. Could some point out what I have wrong? Thanks!

create or replace
TRIGGER INSTR_LOCK
BEFORE UPDATE ON INSTR
FOR EACH ROW
WHEN (OLD.TAG_LOCK ='Y' or OLD.TAG_LOCK='y')
BEGIN
IF (sys_context('USERENV','SESSION_USER') <> 'popvicd') THEN
RAISE_APPLICATION_ERROR(-20202,'THIS RECORD IS LOCKED AND CANNOT BE UPDATED! Click OK to return to your application.');
END IF;
END;
0
Comment
Question by:PopoDi
  • 5
  • 3
  • 3
11 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38860574
You don't have the references.  I also simplified it a little.

Try this:

WHEN (upper(:OLD.TAG_LOCK)='Y' )
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 38860576
Make username upper case:

IF (sys_context('USERENV','SESSION_USER') <> 'POPVICD') THEN
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38860593
Guess I should have tested it before posting...

It works for me:

drop table tab1 purge;
create table tab1(tag_lock char(1));


create or replace
TRIGGER INSTR_LOCK
BEFORE UPDATE ON tab1
FOR EACH ROW
WHEN (upper(OLD.TAG_LOCK) ='Y')
BEGIN
IF (sys_context('USERENV','SESSION_USER') <> 'popvicd') THEN
RAISE_APPLICATION_ERROR(-20202,'THIS RECORD IS LOCKED AND CANNOT BE UPDATED! Click OK to return to your application.');
END IF;
END; 
/

show errors


insert into tab1 values('Y');
update tab1 set tag_lock='Z';



update tab1 set tag_lock='Z'
                     *
ERROR at line 1:
ORA-20202: THIS RECORD IS LOCKED AND CANNOT BE UPDATED! Click OK to return to
your application.
ORA-06512: at "BUD.INSTR_LOCK", line 3
ORA-04088: error during execution of trigger 'BUD.INSTR_LOCK'

Open in new window

0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38860601
>>Make username upper case:

Good catch!  Missed that!
0
 

Author Comment

by:PopoDi
ID: 38860698
Thanks so much for the answers. I changed the trigger to use the upercase on the username (my username), but it is still not letting me change the record - I should be able to change it back to unlocked by changing the value in TAG_LOCK field.
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 38860738
Make sure you're connected as POPVICD and while connected as POPVICD run this query to double check you're getting POPVICD:

select sys_context('USERENV','SESSION_USER') from dual;


Can you also post your update statement?
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 38860752
It works for me as well:

drop table instr purge
/
create table instr(TAG_LOCK char);
/
create or replace
TRIGGER INSTR_LOCK
BEFORE UPDATE ON INSTR
FOR EACH ROW
WHEN (OLD.TAG_LOCK ='Y' or OLD.TAG_LOCK='y')
BEGIN
IF (sys_context('USERENV','SESSION_USER') <> USER) THEN
RAISE_APPLICATION_ERROR(-20202,'THIS RECORD IS LOCKED AND CANNOT BE UPDATED! Click OK to return to your application.');
END IF;
END;
/
insert into instr values('Y')
/
update instr set tag_lock = 'N'
/
update instr set tag_lock = 'Y'
/


table INSTR dropped.
table INSTR created.
TRIGGER INSTR_LOCK compiled
1 rows inserted.
1 rows updated.
1 rows updated.
0
 

Author Comment

by:PopoDi
ID: 38860804
I had to change it to OS_USER to get it to return my username but the trigger still would not let me unlock the record. I do not have an update statement - the unlock is done manually through a devex application or ms access (the screenshot of the error from within access included).


SYS_CONTEXT('USERENV','OS_USER')                                                                                                                                                                                                                                
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
popvicd
lockerror.bmp
0
 
LVL 23

Accepted Solution

by:
paquicuba earned 2000 total points
ID: 38860861
Please change your trigger to this and show us the error:

create or replace
TRIGGER INSTR_LOCK
BEFORE UPDATE ON INSTR
FOR EACH ROW
WHEN (OLD.TAG_LOCK ='Y' or OLD.TAG_LOCK='y')
BEGIN
IF (SYS_CONTEXT('USERENV','OS_USER') <> 'popvicd') THEN
RAISE_APPLICATION_ERROR(-20202,'THIS RECORD IS LOCKED AND CANNOT BE UPDATED BY '||SYS_CONTEXT('USERENV','OS_USER')||' Click OK to return to your application.');
END IF;
END;
/
0
 

Author Closing Comment

by:PopoDi
ID: 38860902
duh! Thank you so much for bearing with me, I should have added the username in the error message right from the start!
I needed to add the domain to the username as well and now it works.
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 38860914
Good for you!!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup
Suggested Courses

865 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question