none
View restrictions -permissions RRS feed

  • Question

  • Hi Team,

    I have to restrict the view permissions for two groups- consider the scenario.

    a)Can you share if you have the script to deny view permissions note in case if new view get created on that also it should get deny without modifying the script.

    the script should work in all the environments 2008+ onward.

    2)If X user has the login in sql server and also part of the Y group- do we need to deny for group & X user two times?.

    Appreciated your time!


    Regards, S_NO "_"

    Friday, August 2, 2019 9:32 AM

Answers

  • I had some other reasons to play with this, so here it goes. You will be need to be prepared to fit your needs - which does not seem be entirely clear at this point. It consists of two parts: one to deny permission all existing views and a DDL trigger to deny permissions on all new views. ...except that the script actually grants than deny permissions, but I trust that you are able to fix that. You will also have to replace the role name.

    I only tested this on SQL 2016, bu save for the CREATE OR ALTER thing, it should run on anything from SQL 2005 and up.

    DECLARE @query    nvarchar(MAX),
            @grantsql nvarchar(MAX)
    SELECT @query =
       'SELECT @sql =
          (SELECT  ''GRANT SELECT ON '' + quotename(s.name) + ''.'' + quotename(v.name) + 
                   '' TO thisrole, thatrole'' +  + char(13) + char(10)
           FROM    sys.views v
           JOIN    sys.schemas s ON s.schema_id = v.schema_id
           WHERE   (SELECT COUNT(*)
                    FROM   sys.database_permissions dp
                    JOIN   sys.database_principals pr ON dp.grantee_principal_id = pr.principal_id
                    WHERE  dp.major_id = v.object_id
                      AND  dp.type = ''SL''
                      AND  pr.name IN (''thisrole'', ''thatrole'')) < 2
           FOR XML PATH(''''), TYPE).value(''.'', ''nvarchar(MAX)'')'
    PRINT @query
    EXEC sp_executesql @query, N'@sql nvarchar(MAX) OUTPUT', @grantsql OUTPUT
    PRINT @grantsql
    EXEC(@grantsql)
    
    go
       CREATE OR ALTER TRIGGER grant_view_perm_tri ON DATABASE FOR CREATE_VIEW AS
         DECLARE @eventdata xml = eventdata(),
                 @sql       nvarchar(MAX)
         IF @eventdata.value('/EVENT_INSTANCE[1]/ObjectType[1]', 'varchar(10)') = 'VIEW'
         BEGIN 
            SELECT @sql = 'GRANT SELECT ON ' + 
                          quotename(E.e.value('(SchemaName/text())[1]', 'nvarchar(128)')) + '.' +
                          quotename(E.e.value('(ObjectName/text())[1]', 'nvarchar(128)')) + 
                          ' TO thisrole, thatrole'
            FROM   @eventdata.nodes('/EVENT_INSTANCE') AS E(e)
            PRINT @sql
            EXEC(@sql)
         END
                         
    
    


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, August 8, 2019 7:45 PM
    Moderator

All replies

  • a)Can you share if you have the script to deny view permissions note in case if new view get created on that also it should get deny without modifying the script.

    But if a new table is created there should be no DENY? Eh?

    Can you explain the real problem? Why do you need a DENY in the first place? Recall that by default, users have no permissions at all. If you feel that you need a DENY, maybe there is something you did wrong earlier in the security setup?

    2)If X user has the login in sql server and also part of the Y group- do we need to deny for group & X user two times?.

    Nope. Once there is a DENY, there is a DENY. And it does not matter how many GRANT there might be elsewhere. DENY always takes precedence.

    DENY is something you should use only exceptionally. Else you may find yourself in a cul-de-sac one day.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, August 2, 2019 10:06 PM
    Moderator
  • Hi Erland,

    Actually the data which is consists an customer data, where some of the two group- they can able to read the data from the tables but now it has been masked some of the columns so they wont be able to read those & how ever the view's still they can able to fetch it.

    hence required that option (or)Is this something to look out further?.


    Regards, S_NO "_"


    • Edited by S_NO Tuesday, August 6, 2019 1:54 PM update
    Tuesday, August 6, 2019 1:43 PM
  • I'm afraid that I don't follow.

    You talked about masked data in tables. How is that masking done? Dynamic Data Masking? But you want this to work on SQL 2008 and up, and dynamic data masking was introduced in SQL 2016.

    And why would data appear as unmasked in the views? Can you give an example?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, August 6, 2019 9:44 PM
    Moderator
  • Yes it's dynamic data masking of 2016.

    And why would data appear as unmasked in the views? Can you give an example?

    >>As per this, the apps team they want to restrict on views.

    Excuse Me on this part-the script should work in all the environments 2008+ onward.


    Regards, S_NO "_"

    Wednesday, August 7, 2019 12:26 AM
  • As this example demonstrates, data is masked both in the view and in the tables:

    CREATE TABLE maskttbl (id int NOT NULL PRIMARY KEY,
                          name sysname MASKED WITH (FUNCTION = 'default()') NOT NULL)
    
    INSERT maskttbl SELECT object_id, name FROM sys.objects
    go
    CREATE VIEW maskview AS
       SELECT id, name FROM masktbl
    go
    CREATE USER testuser WITHOUT LOGIN
    ALTER ROLE db_datareader ADD MEMBER testuser
    go
    EXECUTE AS USER = 'testuser'
    go
    SELECT * FROM maskttbl
    SELECT * FROM maskview
    go
    REVERT
    go
    DROP USER testuser
    DROP VIEW maskview
    DROP TABLE maskttbl
    

    So I still don't get why you want to restrict permission on views. OK, so the answer may just be "because...". But I also don't know why you want DENY. If you don't want users to access views, why did you grant them access in the first place?

    Have you considered using different schemas? By granting access on schema level, you can more easily control permissions.

    The reason that I don't really answer your original question is that it leads to an akward solution as it has to include a DDL trigger, because that is the only way to handle views not created yet. If you absolutely want a DENY on them. But if you never grant access, there is nothing to deny...


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, August 7, 2019 6:53 PM
    Moderator
  • Hi Erland,

    Thanks for the update & effort,appreciated..initially the accesses has exists for the views but post data masking & this is an application requirement that they have to restrict in that way-not sure on their logic part but deny on views -helps them & will check with them more and get back to you on this.

    default is the schema.


    Regards, S_NO "_"

    Thursday, August 8, 2019 2:04 PM
  • I had some other reasons to play with this, so here it goes. You will be need to be prepared to fit your needs - which does not seem be entirely clear at this point. It consists of two parts: one to deny permission all existing views and a DDL trigger to deny permissions on all new views. ...except that the script actually grants than deny permissions, but I trust that you are able to fix that. You will also have to replace the role name.

    I only tested this on SQL 2016, bu save for the CREATE OR ALTER thing, it should run on anything from SQL 2005 and up.

    DECLARE @query    nvarchar(MAX),
            @grantsql nvarchar(MAX)
    SELECT @query =
       'SELECT @sql =
          (SELECT  ''GRANT SELECT ON '' + quotename(s.name) + ''.'' + quotename(v.name) + 
                   '' TO thisrole, thatrole'' +  + char(13) + char(10)
           FROM    sys.views v
           JOIN    sys.schemas s ON s.schema_id = v.schema_id
           WHERE   (SELECT COUNT(*)
                    FROM   sys.database_permissions dp
                    JOIN   sys.database_principals pr ON dp.grantee_principal_id = pr.principal_id
                    WHERE  dp.major_id = v.object_id
                      AND  dp.type = ''SL''
                      AND  pr.name IN (''thisrole'', ''thatrole'')) < 2
           FOR XML PATH(''''), TYPE).value(''.'', ''nvarchar(MAX)'')'
    PRINT @query
    EXEC sp_executesql @query, N'@sql nvarchar(MAX) OUTPUT', @grantsql OUTPUT
    PRINT @grantsql
    EXEC(@grantsql)
    
    go
       CREATE OR ALTER TRIGGER grant_view_perm_tri ON DATABASE FOR CREATE_VIEW AS
         DECLARE @eventdata xml = eventdata(),
                 @sql       nvarchar(MAX)
         IF @eventdata.value('/EVENT_INSTANCE[1]/ObjectType[1]', 'varchar(10)') = 'VIEW'
         BEGIN 
            SELECT @sql = 'GRANT SELECT ON ' + 
                          quotename(E.e.value('(SchemaName/text())[1]', 'nvarchar(128)')) + '.' +
                          quotename(E.e.value('(ObjectName/text())[1]', 'nvarchar(128)')) + 
                          ' TO thisrole, thatrole'
            FROM   @eventdata.nodes('/EVENT_INSTANCE') AS E(e)
            PRINT @sql
            EXEC(@sql)
         END
                         
    
    


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, August 8, 2019 7:45 PM
    Moderator
  • @Thanks Erland- I will give a try,appreciated!

    Regards, S_NO "_"

    Monday, August 12, 2019 6:39 PM
  • Hi S_NO,

     

    Thank you for you reply. Have you solved this problem ?  In order to close this thread, please kindly mark useful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, August 15, 2019 5:23 AM