Guest User

Untitled

a guest
Oct 23rd, 2016
50
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.41 KB | None | 0 0
  1. deny view definition to some_db_role
  2.  
  3. grant view definition on dbo.some_proc to some_db_role
  4.  
  5. grant view definition, references on symmetric key::some_key to some_db_role
  6.  
  7. USE master;
  8. GO
  9. IF EXISTS (SELECT 1 FROM master.sys.databases d WHERE d.name = 'TestPerms_20160921')
  10. BEGIN
  11. DROP DATABASE TestPerms_20160921;
  12. END;
  13. GO
  14. CREATE DATABASE TestPerms_20160921;
  15. GO
  16. USE TestPerms_20160921;
  17. GO
  18.  
  19. IF OBJECT_ID('dbo.Test') IS NOT NULL
  20. DROP TABLE dbo.Test;
  21. CREATE TABLE dbo.Test
  22. (
  23. TestID INT NOT NULL
  24. );
  25. GO
  26. IF OBJECT_ID('dbo.TestProc') IS NOT NULL
  27. DROP PROCEDURE dbo.TestProc;
  28. GO
  29. CREATE PROCEDURE dbo.TestProc
  30. AS
  31. BEGIN
  32. SELECT 1;
  33. END
  34. GO
  35.  
  36. IF EXISTS (
  37. SELECT 1
  38. FROM sys.database_principals dp
  39. WHERE dp.name = 'TestU'
  40. )
  41. BEGIN
  42. DROP USER TestU;
  43. END
  44. CREATE USER TestU WITHOUT LOGIN;
  45.  
  46. IF EXISTS (
  47. SELECT *
  48. FROM sys.database_principals dp
  49. WHERE dp.name = 'dbtestrole'
  50. )
  51. BEGIN
  52. DROP ROLE dbtestrole;
  53. END
  54. CREATE ROLE dbtestrole;
  55.  
  56. ALTER ROLE dbtestrole ADD MEMBER TestU;
  57.  
  58. GRANT SELECT ON dbo.Test TO dbtestrole;
  59. GRANT EXECUTE ON dbo.TestProc TO dbtestrole;
  60.  
  61. EXECUTE AS USER = 'TestU';
  62.  
  63. /* column names and types **are** visible */
  64. SELECT s.name
  65. , t.name
  66. , c.name
  67. , ty.name
  68. FROM sys.tables t
  69. INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
  70. INNER JOIN sys.columns c ON t.object_id = c.object_id
  71. INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
  72. WHERE s.name = 'dbo'
  73. AND t.name = 'Test'
  74. ORDER BY s.name
  75. , t.name
  76. , c.name;
  77.  
  78. /* access to view the definition is denied */
  79. EXEC sys.sp_helptext @objname = 'dbo.TestProc', @columnname = NULL;
  80.  
  81. /* we can run the proc! */
  82. EXEC dbo.TestProc;
  83.  
  84. REVERT;
  85.  
  86. DENY VIEW DEFINITION ON dbo.Test TO dbtestrole;
  87.  
  88. EXECUTE AS USER = 'TestU';
  89.  
  90. /* no rows returned as we can no longer view the table definition */
  91. SELECT s.name
  92. , t.name
  93. , c.name
  94. , ty.name
  95. FROM sys.tables t
  96. INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
  97. INNER JOIN sys.columns c ON t.object_id = c.object_id
  98. INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
  99. WHERE s.name = 'dbo'
  100. AND t.name = 'Test'
  101. ORDER BY s.name
  102. , t.name
  103. , c.name;
  104.  
  105. /* we can still run the proc */
  106. EXEC dbo.TestProc;
  107.  
  108. /* we can still see the content of the table */
  109. SELECT *
  110. FROM dbo.Test;
  111.  
  112. REVERT;
  113.  
  114. USE master;
  115. GO
  116. IF EXISTS (SELECT 1 FROM master.sys.databases d WHERE d.name = 'TestPerms_20160921')
  117. DROP DATABASE TestPerms_20160921;
Add Comment
Please, Sign In to add comment