locked
Read Only with filtering RRS feed

  • Question

  • I have to grant a user read only access to a database to pull some data for crystal reports, howerver there are certian records in this case accounts payable that he can not have access to. Is there a way in sql 2005 to grant read access but lock him out of the data he can't see? For instance I have a table that has a field called IN_DOC that points back to a table that has the key value for the AP data so in any table that the field IN_DOC that has  this key the user can not see. I have 5 tables with this field. There is data in these tables that the user can see and should see for his reports. This is a 3rd party database. Is there a quick way to lock these records down? Since he will be using crystal reports and he is the one creating the reports I don't have the chance to write custom sql for it.

     

    Wednesday, January 13, 2010 2:51 PM

Answers

  • Hi,

    I think you may try the method Akim said above, define 2 views:

    View_1: All records that DRAWER_ID column in {'1170436713_374611003612', ...}
    SELECT * FROM IN_DOC 
    WHERE DRAWER_IN IN
    ('1170436713_374611003612', '20000002FT_00014S3778BK', '20000003DH_0001CS38FJF5', '200000079Y_00001R2ZHEN9')
    

    View_2: All records that the developer can see, user EXCEPT (Transact-SQL)
    SELECT * FROM IN_DOC
    EXCEPT
    SELECT * FROM View_1
    In this case, you grant SELECT permission on View_2 to the user. I am not sure this is what you want, please let use know if it helps.
    Chunsong Feng
    Microsoft Online Community Support

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Proposed as answer by Alex Feng (SQL) Wednesday, January 20, 2010 12:28 PM
    • Marked as answer by SQLBOY36 Wednesday, February 3, 2010 9:47 PM
    Friday, January 15, 2010 4:09 AM

All replies

  • If I understand you correctly, you need column level permission.
    For columns which user cannot see, DENY SELECT permission.

    DENY SELECT ColumnName ON tableName TO UserName

    Wednesday, January 13, 2010 3:14 PM
  • Instead of giving your report developer I'd creat views and filter out anything he not suppose to see... Just an idea.

    Regards,
    Akim
    Wednesday, January 13, 2010 3:16 PM
  • Try something like this

    CREATE

    TABLE student (

    id

    INT,

    name

    NCHAR(20),

    ssn

    VARCHAR(11)

     

    )

     

     

    INSERT INTO student

     

    (id, name, ssn) VALUES (1, 'david', '999-12-1234')

     

     

    GRANT SELECT (id, name) ON student TO [jim]

     

    DENY SELECT (ssn) ON student TO [jim]


    Thanks, Leks
    Wednesday, January 13, 2010 4:18 PM
  • Not exactly. What I have is. The user needs to see all columns just not rows where the DRAWER_ID in

    '1170436713_374611003612'

     

    ,

    '20000002FT_00014S3778BK'

     

    ,

    '20000003DH_0001CS38FJF5'

     

    ,

    '200000079Y_00001R2ZHEN9'




    table in_doc
    (
    DOC_ID
    DRAWER_ID
    FOLDER
    TAB
    F3
    F4
    F5
    DOC_TYPE_ID
    INSTANCE_ID
    UNDER_VERSION_CONTROL
    CURRENT_VERSION
    IS_CHECKED_OUT
    CHECK_OUT_PROJ_ID
    CHECK_OUT_NOTES
    CHECK_OUT_USR_ID
    CHECK_OUT_TIME
    CREATION_USR_ID
    CREATION_TIME
    MOD_USR_ID
    MOD_TIME
    IS_IN_PROJECT
    CONTENT_STATUS
    )
     so really what I would have is

    select

     

    *

    from

     

    IN_DOC with (nolock)

    where

     

    drawer_id not in ('1170436713_374611003612',

     

    '20000002FT_00014S3778BK',

     

    '20000003DH_0001CS38FJF5',

     

    '200000079Y_00001R2ZHEN9'

     

    )--- data user should be able to see and not the data in these values.


    Wednesday, January 13, 2010 7:05 PM
  • Hi,

    I think you may try the method Akim said above, define 2 views:

    View_1: All records that DRAWER_ID column in {'1170436713_374611003612', ...}
    SELECT * FROM IN_DOC 
    WHERE DRAWER_IN IN
    ('1170436713_374611003612', '20000002FT_00014S3778BK', '20000003DH_0001CS38FJF5', '200000079Y_00001R2ZHEN9')
    

    View_2: All records that the developer can see, user EXCEPT (Transact-SQL)
    SELECT * FROM IN_DOC
    EXCEPT
    SELECT * FROM View_1
    In this case, you grant SELECT permission on View_2 to the user. I am not sure this is what you want, please let use know if it helps.
    Chunsong Feng
    Microsoft Online Community Support

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Proposed as answer by Alex Feng (SQL) Wednesday, January 20, 2010 12:28 PM
    • Marked as answer by SQLBOY36 Wednesday, February 3, 2010 9:47 PM
    Friday, January 15, 2010 4:09 AM