Post reply

Closing a security hole

  • July 9, 2015 at 11:04 am

    #309797

    I was hoping one of the resident geniuses could assist me. I have a purchased product that has a DB with several thousand tables in dbo. I can't change anything with the default dbo schema other than security.

    I have a table called dbo.ACCOUNT. It has information from multiple sites on it. I have a reporting team that need only access the information for SITE 1 on the ACCOUNT table and anything in the other 1000+ tables in the dbo schema.

    I created a role (Analytics) and granted select access to dbo. I created a schema (Analytics) and assigned it the role. I created a view in the new schema called Analytics.ACCOUNT with a WHERE SITE = 1 clause on it. If they use the view, they see only the things they should. My problem is they can still do a select * from dbo.ACCOUNT and see everything they shouldn't see. How can I close that hole? I am not seeing a solution. If there is no way to fix this with roles/schemas/views, what else can I do? Any ideas you have to share would be appreciated.

    Some of my pain points. I have this issue with about 30 tables. Creating custom table(s) to isolate data for each site will cost me another 2T of storage. Loading those custom tables every day would be cumbersome. Keeping the DDL in sync with the source table would be cumbersome. With the way I have it set up now, if I deny access to the source tables the view breaks because it needs access to the source tables to dynamically build the view. I am just not seeing a path around this issue.

    Here is the code I ran to create everything.

    USE[DB]

    --create test user login

    CREATE LOGIN [Analytics_ETL] WITH PASSWORD=N'XXXXXXXXXXX'

    GO

    --create user in test database

    CREATE USER [Analytics_ETL] FOR LOGIN [Analytics_ETL] WITH DEFAULT_SCHEMA=[Analytics]

    GO

    --create role

    CREATE ROLE [Analytics] AUTHORIZATION [dbo]

    GO

    --create schema

    CREATE SCHEMA [Analytics] AUTHORIZATION [Analytics]

    GO

    --apply permissions to schemas

    GRANT SELECT ON SCHEMA::[dbo] TO [Analytics]

    GO

    --ensure role membership is correct

    EXEC sp_addrolemember N'Analytics', N'Analytics_ETL'

    GO

    --Allow user to connect to database

    GRANT CONNECT TO [Analytics_ETL]

    create view Analytics.ACCOUNT as

    select * from [DB].dbo.ACCOUNT with (nolock) where site = 1

  • July 9, 2015 at 1:32 pm

    #1811442

    DENY SELECT ON dbo.Accounts TO Analytics

    The view won't break, because of ownership chaining. As long as the view and the table are owned by the same user (dbo preferably), the permissions are checked to the view, not to the table.

    Oh, and drop that nolock hint from the view, unless your report writers like intermittently incorrect data (duplicate rows, missed rows)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 12, 2015 at 8:21 am

    #1811841

    Thank you so very much. It works perfectly.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply