SQL Server Performance

About DB user privilege

Discussion in 'General DBA Questions' started by Jelly0228, Dec 25, 2003.

  1. Jelly0228 New Member

    Table "USERDB" stored the information of application 's users ,it's owner is dbo.<br />It has 3 columns :USERNAME ,PASSWORD ,USER_MARK ©s<br />there are 2 rows in it:<br />USERNAME PASSWORD USER_MARK <br />---------------------------------<br />AA AA A<br />BB BB B<br /><br />The db have two user: user_A,user_B.<br /><br />My problem is :I want to make The DB user user_A just can select the rows which user_mark is "A" and user_B just can select the rows which user_mark is "B".<br /><br />I try to create a view for user_A like this:<br />create view user_A.USERDB<br />as<br />select * from dbo.USERDB<br />where USER_MARK='A'<br />with check option<br /><br />then user_A just can select the rows which user_mark is "A" with the sentence:<br /> select * from USERDB.<br />But it also select all the rows of dbo.USERDB<img src='/community/emoticons/emotion-7.gif' alt=':s' />elect * from dbo.USERDB.<br />If I revoked the privilege that to prevent user_A to access table dbo.USERDB,then the view of user_A.USERDB can't work.<br /><br />Help me .Thanks!<br /> <br /><br /><br /><br /><br /><br />Jelly.
  2. Luis Martin Moderator

    Did you check public privileges are off?


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  3. bradmcgehee New Member

    The problem is occuring because you are using DBO to perform your SELECT, and the DBO user has permisson on these views.

    Instead create the two views using dbo as the owner. The through SQL Server security, allow user a to only access this view and deny b, and vice versa.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  4. harsh_sr New Member

    Hi Jelly

    If you revoke permissions to user from TABLE but give him permissions to VIEW, it'll work. VIEW does not need user to have table permissiosn.

    HTH
    Harsh
  5. satya Moderator

  6. Twan New Member

    The user will need permission to both the table and the view IF the owner of the view is not also the owner of the table. If they are the same, then only permissions on the view are required. This is called the ownership chain.

    Pre SQL2000 SP3 this ownership chain also worked across databases, so if dbo owned both objects then you could still grant access only to the view. SP3 and later made this a configuration option as it is a security hole)

    Cheers
    Twan
  7. airport New Member

    hi how can i now the privilege for each user in the sql server for exampple the default database
  8. satya Moderator

    Refer to the books online about SP_HELPROTECT for complete information on the privileges.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided �AS IS� with no rights for the sake of knowledge sharing.

Share This Page