Home » SQL & PL/SQL » SQL & PL/SQL » Restrict Permission (9.0.4.0)
Restrict Permission [message #332987] Thu, 10 July 2008 04:38 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi All,

I am using UPDATE statement.At the same time another user also using the UPDATE statement on the same table.If I want to to restrict to other user DBA will will do that.But in this 2 cutomers are there . Tehy are doing Update on the same table from diffrebt place but schema and connection string evry thing is same.I want to restrict other user access while I am applying the commad on the same table.Please let me know how to restrict the other user.

Thank you.
Re: Restrict Permission [message #332991 is a reply to message #332987] Thu, 10 July 2008 04:40 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
you can select the rows via using For Update statement.

Study Locks in oracle for further details.


Regards,
Rajat

[Updated on: Thu, 10 July 2008 04:41]

Report message to a moderator

Re: Restrict Permission [message #332993 is a reply to message #332987] Thu, 10 July 2008 04:42 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
When you say 'restrict the other user', what do you want to prevent him doing?

as @rajatratewal says, you can lock the row set that you want to update by adding a FOR UPDATE clause, but this will simply cause the other user to wait until the first user has finished his update before it lets the second update execute.
Re: Restrict Permission [message #332999 is a reply to message #332987] Thu, 10 July 2008 04:48 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Maybe it is time to read Oracle Concepts manual, found eg.online http://tahiti.oracle.com/.
This section is about concurrent access: http://download.oracle.com/docs/cd/A91202_01/901_doc/server.901/a88856/c01intro.htm#3944

> I want to restrict other user access
Not clear whether you want to restrict other user from updating the same table or only the same rows.
And, as JRowbottom pointed out, how long do you want to restrict the other user?
What do you want to do if other user wants to update AFTER you end your transaction?
Re: Restrict Permission [message #333002 is a reply to message #332999] Thu, 10 July 2008 04:53 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi All

Thank you very much....
Re: Restrict Permission [message #333005 is a reply to message #333002] Thu, 10 July 2008 04:59 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Continued...

adding from my side..
How to restrict other user from updating the same table?


Regards,
oli
Re: Restrict Permission [message #333007 is a reply to message #333005] Thu, 10 July 2008 05:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Stop them from updating it at the same time: The first user needs to lock the whole set of rows that they want to update, and not issue a commit or rollback until they've handled all of them

Stop the other user entirely: As above, but the second user will need to try to lock the rows with a ...FOR UPDATE NOWAIT. This will raise an exception if the rows are already locked.
Re: Restrict Permission [message #333008 is a reply to message #333007] Thu, 10 July 2008 05:06 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
JRowbottom wrote on Thu, 10 July 2008 05:02
Stop them from updating it at the same time: The first user needs to lock the whole set of rows that they want to update, and not issue a commit or rollback until they've handled all of them

Stop the other user entirely: As above, but the second user will need to try to lock the rows with a ...FOR UPDATE NOWAIT. This will raise an exception if the rows are already locked.



That means it can be done using cursor?
Re: Restrict Permission [message #333009 is a reply to message #333008] Thu, 10 July 2008 05:07 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
What cursor has to do in this??

Regards,
Rajat
Re: Restrict Permission [message #333010 is a reply to message #332987] Thu, 10 July 2008 05:07 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> How to restrict other user from updating the same table?

Manually lock it, as described in http://download.oracle.com/docs/cd/A91202_01/901_doc/server.901/a88856/c21cnsis.htm#3330 (by the way, it is referenced in the link in my first post).
Re: Restrict Permission [message #333016 is a reply to message #333009] Thu, 10 July 2008 05:19 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
I meant using CURSOR SELECT * FROM TABLE_1 FOR UPDATE CLOSE..?
Re: Restrict Permission [message #333019 is a reply to message #333016] Thu, 10 July 2008 05:26 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
you can use (for update) without having cursor clause.This is not meant for PL/SQL only.

If i type

select * from <table_name> 
where col1=<Chk_Value> 
for update nowait


in one session then and try to fire the same query from another
session it will result in error saying resource busy error.
If you don't specify nowait the other session will go in Hang state untill session 1 issue commit.


Regards,
Rajat

[Updated on: Thu, 10 July 2008 05:27]

Report message to a moderator

Re: Restrict Permission [message #333050 is a reply to message #332987] Thu, 10 July 2008 06:50 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> I meant using CURSOR SELECT * FROM TABLE_1 FOR UPDATE CLOSE..?
When using CURSOR FOR UPDATE, why do you not check documentation to know about its behaviour?
http://download.oracle.com/docs/cd/A91202_01/901_doc/appdev.901/a89856/06_ora.htm#2180
Re: Restrict Permission [message #333070 is a reply to message #333050] Thu, 10 July 2008 07:29 Go to previous message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Sure..Thanks flyboy for providing much information on this.


Regards,
Oli

[Correction: *not CLOSE. it should be CLAUSE]
Previous Topic: Does Oracle handling temp tables differently in 10g?
Next Topic: generate numbers between 1 to 100
Goto Forum:
  


Current Time: Wed Dec 07 04:29:39 CST 2016

Total time taken to generate the page: 0.27083 seconds