Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2010
    Posts
    1

    Unanswered: Restricting Login at the Table Level

    I have been having problems with people querying/ running reports while our overnight loads are running which is causing a lot of performance issues. I want to restrict other users from accessing the current table we are loading. As a short term fix we are not allowing any other users into the database during the entire loading window but do not want to do this. We would like it to be at a table by table level. (If OUR user "X" is loading table "Y" no other users can access that table until user "X" is finished. If user "X" wants to load table "Y" and another user is currently accessing that table they need to be kicked out) From what I have read you can't prevent users from reading a table if you place a lock on it. Does anyone have any ideas as to what would be the best way to implement something like this?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >From what I have read you can't prevent users from reading a table if you place a lock on it.
    Either the document contains error or your understanding is incorrect.
    If you insist your statement is correct, the post reproducible test case that demonstrates this behavior.
    If/when schema has SELECT privilege against table, then only way to prevent SELECT is to not allow session in the DB.
    No login equals no access of table.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by anacedent View Post
    >From what I have read you can't prevent users from reading a table if you place a lock on it.
    Either the document contains error or your understanding is incorrect.
    That statement is correct: There is no way to block a regular SELECT on a table if the user has select privilege for that table.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by shammat View Post
    That statement is correct: There is no way to block a regular SELECT on a table if the user has select privilege for that table.

    I mis-read/mis-understood what was posted.

    LOCK does not block SELECT
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •