locked
permission within a database RRS feed

  • Question

  • Hi,

    I have added a number of users to the DB. However, the DB contains different tables and views which are only for specific users. Specifically, I have a number of views and tables which have senstive data that I only want one user to access it, is there a quick way to do it without moving the tables/views to a new DB?

    Thanks in advance.

    • Moved by Tom Phillips Wednesday, September 7, 2011 2:05 PM Security question (From:SQL Server Database Engine)
    Wednesday, September 7, 2011 1:36 PM

Answers

  • Putting the pieces together from the other posts.

    First. db_datareader and db_datawriter cover all tables and views in the database.  If you want to keep some things private, you should (a) not use the roles or (b) use the roles and DENY certain users rights to those tables or (c) encrypt the data.

    I would choose not to use these two built-in roles since they are intended for sweeping rights.  Instead create your own roles that are only granted the needed rights.  Your own roles will not automatically gain rights beyond those that you grant to them.  (So be careful what you grant rights to.)

    I prefer to avoid DENY statements whenever possible because it often causes rights problems when new rights are granted.  It is easy to forget about a DENY and waste lots of time trying to figure out why your newly granted rights are not working.  (A DENY always overrides a GRANT.)

    I prefer to avoid encryption unless it is truly the answer to a problem.  In this case, if you can simply prevent people from having access to the 'secret' data then you do not have to manage encryption keys, passwords, etc.   Nothing in your note suggests to me that encryption is the right answer to your problem.

    If you want everyone to see some contents of a table, but not all rows or all columns, you can create views that will encapsulate those decisions for you. 

    RLF

     

     

    • Marked as answer by Stephanie Lv Wednesday, September 14, 2011 12:24 PM
    Wednesday, September 7, 2011 6:31 PM

All replies

  • Do not make those users  to member of db_owner database role
    GRANT EXEC ON some_sp TO nisse -- Grant permission on a single procedure.
    GRANT EXEC ON SCHEMA::dbo TO kalle -- Grant perpmission on all procedures in
                                          the dbo schema
    GRANT EXEC TO putte -- Grant EXEC permission all procedures in the database.
    To grant all "normal" permissions on a table
       GRANT SELECT, UPDATE, DELETE, INSERT on pec TO freddie
    To grant freddie access on tables in a schema:
       GRANT SELECT, UPDATE, DELETE, INSERT on SCHEMA::dbo TO freddie
    To grant him access on all tables:
       GRANT SELECT, UPDATE, DELETE, INSERT TO freddie

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, September 7, 2011 1:41 PM
  • Thanks.

    My question right now is there are other users who has dbreader/dbwriter to this DBs, does that mean they have access to those sensitive tables and views that I mentioned above?


    The thing is this DB will keep growing, and other users will have access to their own created tables and views, and I will add my sensitives tables and views as well, I just want to make sure, the other users will still only access their stuffs and they won't be able to see my stuffs.

    Thanks again.

    • Edited by wkpli Wednesday, September 7, 2011 1:46 PM
    Wednesday, September 7, 2011 1:44 PM
  • You can control permissions on these object as follow:

    DENY SELECT ON T1 TO John

    DENY INSERT ON T1 TO John

    DENY UPDATE ON T1 TO John

    DENY DELETE ON T1 TO John

     

    GRANT SELECT ON T1 TO John

    GRANT INSERT ON T1 TO John

    GRANT UPDATE ON T1 TO John

    GRANT DELETE ON T1 TO John


    -- Yaniv www.sqlserverutilities.com http://blogs.microsoft.co.il/blogs/yaniv_etrogi
    Wednesday, September 7, 2011 1:47 PM
  • Yes.

    You  can encrypt your sensitive data as only sysadmins IIRW have access to the  Master Key and its passwords 


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, September 7, 2011 1:51 PM
  • Yes.

    You  can encrypt your sensitive data as only sysadmins IIRW have access to the  Master Key and its passwords 


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, September 7, 2011 1:51 PM
  • Thanks Uri,

    I am not sure how that can be done. Can you give me further info? I just want to make sure whenever I create my sensitive table/view, I will make sure other ppl won't see it by default.

    Wednesday, September 7, 2011 2:50 PM
  • Putting the pieces together from the other posts.

    First. db_datareader and db_datawriter cover all tables and views in the database.  If you want to keep some things private, you should (a) not use the roles or (b) use the roles and DENY certain users rights to those tables or (c) encrypt the data.

    I would choose not to use these two built-in roles since they are intended for sweeping rights.  Instead create your own roles that are only granted the needed rights.  Your own roles will not automatically gain rights beyond those that you grant to them.  (So be careful what you grant rights to.)

    I prefer to avoid DENY statements whenever possible because it often causes rights problems when new rights are granted.  It is easy to forget about a DENY and waste lots of time trying to figure out why your newly granted rights are not working.  (A DENY always overrides a GRANT.)

    I prefer to avoid encryption unless it is truly the answer to a problem.  In this case, if you can simply prevent people from having access to the 'secret' data then you do not have to manage encryption keys, passwords, etc.   Nothing in your note suggests to me that encryption is the right answer to your problem.

    If you want everyone to see some contents of a table, but not all rows or all columns, you can create views that will encapsulate those decisions for you. 

    RLF

     

     

    • Marked as answer by Stephanie Lv Wednesday, September 14, 2011 12:24 PM
    Wednesday, September 7, 2011 6:31 PM
  • If You Wish to Prevent Your Tables And Views Having Sensitive Data and want to avoid deny user access on individual table then 

    Create Your Own Schema And Put Your Tables And View Inside that Schema And Deny User Access On That Schema.

     

     

     


    Mark as Answer If Reply Was Helpful Thanks Kuldeep Bisht Technical Lead @ Simplion Technologies http://mssqlguide.kuldeepbisht.com
    Thursday, September 8, 2011 5:59 AM