SHARE
TWEET

Untitled

SQLSoldier Jun 3rd, 2013 71 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Use master;
  2. Go
  3.  
  4. -- New database for testing
  5. Create Database TestRoles;
  6. Go
  7.  
  8. -- Logins must be created in master database
  9. CREATE LOGIN LoanClerk
  10.         WITH PASSWORD = 'P@ssw0rd',
  11.         Default_Database = TestRoles;
  12. CREATE LOGIN SalesMgr
  13.         WITH PASSWORD = 'P@ssw0rd',
  14.         Default_Database = TestRoles;
  15. Go
  16.  
  17. -- switch to new database
  18. Use TestRoles;
  19. Go
  20.  
  21. -- Create database roles
  22. Create Role LoanClerks;
  23. Create Role SalesMgrs;
  24. Go
  25.  
  26. -- Create databse users
  27. Create User LoanClerk For Login LoanClerk;
  28. Create User SalesMgr For Login SalesMgr;
  29. Go
  30.  
  31. -- Add Users to roles
  32. Exec sp_addrolemember @rolename = N'Loanclerks',
  33.         @membername = N'LoanClerk';
  34.  
  35. Exec sp_addrolemember @rolename = N'SalesMgrs',
  36.         @membername = N'SalesMgr';
  37. Go
  38.  
  39. -- create the Loans table
  40. -- Due date is 7 days from today by default
  41. -- Role that can see the record defaults to public if not specified
  42.         -- May want to use db_owner for more security
  43. -- Stores the user name of the inserter, gives you the ability to allow the
  44.         -- inserter to see data they inserted even if not in the specified row
  45. Create Table dbo.Loans (
  46.         LoanID int identity(1, 1) not null primary key,
  47.         ISBN varchar(10) not null,
  48.         DueDate datetime not null default(DATEADD(day, 7, GETDATE())),
  49.         MemberRole sysname not null default('public'),
  50.         InsertedBy nvarchar(256) not null default(SUSER_SNAME())
  51.         );
  52. Go
  53.  
  54. CREATE VIEW dbo.vLoans
  55. AS
  56. SELECT ISBN, DueDate
  57. FROM Loans AS l
  58. WHERE IS_MEMBER(MemberRole) = 1
  59. Or InsertedBy = SUSER_SNAME();
  60. GO
  61.  
  62. -- Grant select to the view
  63. GRANT SELECT On dbo.VLoans To LoanClerks;
  64. GRANT SELECT On dbo.VLoans To SalesMgrs;
  65. Go
  66.  
  67. -- Add some sample data
  68. Insert Into dbo.Loans (ISBN, MemberRole)
  69. Values ('abcdef', 'LoanClerks'),
  70.         ('bcdefg', 'SalesMgrs'),
  71.         ('123456', 'public'), -- public = everyone with access
  72.         ('098765', 'Librarian') -- only inserter should see since no role of Librarian
  73. Go
  74.  
  75. -- Should only see rows with ISBN = abcdef and 123456
  76. EXECUTE AS USER = 'LOANCLERK';
  77.  
  78. SELECT * FROM dbo.vLoans;
  79.  
  80. REVERT;
  81. Go
  82.  
  83. -- Should only see rows with ISBN = bcdefg and 123456
  84. EXECUTE AS USER = 'SalesMgr';
  85.  
  86. SELECT * FROM dbo.vLoans;
  87.  
  88. REVERT;
  89. Go
  90.  
  91. -- Should see all rows inserted themself
  92. Select *
  93. From dbo.vLoans
  94. Go
  95.  
  96. -- Drop database
  97. Use master;
  98. Go
  99.  
  100. Drop Database Testroles;
  101. Go
  102.  
  103. -- Drop logins
  104. Drop Login LoanClerk;
  105. Drop Login SalesMgr;
RAW Paste Data
Top