Home » SQL & PL/SQL » SQL & PL/SQL » How can i lock a table to restrict from selection for another user (merged) (How can i lock a table to restrict from selection for another user)
How can i lock a table to restrict from selection for another user (merged) [message #431996] Fri, 20 November 2009 05:51 Go to next message
gan.chowdary
Messages: 10
Registered: February 2007
Location: India
Junior Member

I want to lock a table(one row) in such a way that the locked table record should not available to other user untill i release the table.

1)I have tried with TABLE LOCK (IN EXCLUSIVE MODE)
2)I have tried with FOR UPDATE OF <COL NAME>.


Where as the table is still available for the other user for selection. Can any one please help me on this.

Regards,
Gandhi


Re: How can i lock a table to restrict from selection for another user [message #432000 is a reply to message #431996] Fri, 20 November 2009 05:54 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Revoke and re - Grant the SELECT privilege.
Re: How can i lock a table to restrict from selection for another user [message #432001 is a reply to message #431996] Fri, 20 November 2009 05:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No way you are always able to select a table if you have the privilege to do it.

Regards
Michel
Re: How can i lock a table to restrict from selection for another user [message #432004 is a reply to message #431996] Fri, 20 November 2009 05:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
MODERATORS:

The process is to FIRST merge the topics THEN to delete the duplicates and NOT to delete them without merging.
It is unpleasant to write an answer and get an "the topic you are trying to answer does not exist" error message.

Regards
Michel
Re: How can i lock a table to restrict from selection for another user [message #432005 is a reply to message #432004] Fri, 20 November 2009 05:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Seconded!!
Re: How can i lock a table to restrict from selection for another user [message #432007 is a reply to message #431996] Fri, 20 November 2009 06:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could do it by requiring the user to acquire a share mode lock before performing the SELECT. That way if someone takes out an exclusive lock, the SELECTS will not happen.

We need to take a step back and ask what you're trying to achieve and why. I can think of reasons why someone from a non-oracle background might think they need this when they actually don't in Oracle
Re: How can i lock a table to restrict from selection for another user (merged) [message #432075 is a reply to message #431996] Fri, 20 November 2009 23:05 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
There are other options as well. In particular two come to mind in addition to what others have suggested:

1) user DBMS_RLS to create a security policy that prevents users from selecting on a table and then enable the policy for the duration in which you want to prevent it.

2) apply virtual private database to protect the data (either Oracle's offering, or roll your own).

I have never done #1 so are on your own for the research.

Good luck, Kevin
Previous Topic: SELECT statement with IF/ELSe or CASE
Next Topic: get weekday
Goto Forum:
  


Current Time: Fri Dec 02 18:20:56 CST 2016

Total time taken to generate the page: 0.23419 seconds