การใช้ Roles บน MSSQL

RDBMS: MSSQL2008 R2

ใน SQLServer มีการใช้ Role เช่นเดียวกับ Oracle 

1. สร้าง Role

use test
create role test_exec_role

2. กำหนดสิทธิ์ให้กับ Role ข้างล่างนี้เป็นการกำหนดสิทธิ์ในการ Execute Stored Procedure ให้กับ Role ที่เราสร้างไว้ เราสามารถสร้าง Script ได้โดยใช้ SQL Statement เพื่อช่วยให้ไม่ต้องเขียนทีละ Statement

use test 

select 'grant exec on dbo.'+name+' to  test_exec_role' from sys.procedures

นำผลที่ได้ มารันเพื่อ Grant สิทธิ์ให้กับ Role

use test

grant exec on dbo.sp_procedure1 to  test_exec_role

grant exec on dbo.sp_procedure2 to  test_exec_role

grant exec on dbo.sp_procedure3 to  test_exec_role


3. สร้าง Users
สร้าง Login ใน SQLServer การจะสร้าง User จะต้องสร้างทั้ง Login ซึ่งเป็น User ที่ใช้ในการ Login เข้า Instance หลังจากนั้นจะต้องสร้าง Database User อีกทีหนึ่งเพื่อให้สามารถเข้า Database ตัวที่ต้องการได้ คำสั่งข้างล่างกำหนดให้ Login ไม่มีวันหมดอายุ และไม่กำหนดเงื่อนไขใด ๆ ในการสร้าง User

use [master]
create login [testuser] with password=N'testpwd123', default_database=[test], check_expiration=OFF, check_policy=OFF

สร้าง DB User

use [test]
create user [testuser] for login [testuser] with default_schema=[dbo]

การลบ Login และ DB User
use [master]
drop login [testuser]

use [test]
drop user [testuser]

4. Grant Role ที่เราสร้างไว้ให้กับ User

use [test]
exec sp_addrolemember 'test_exec_role', [testuser]

5. Grant Role เพิ่มเติม Role, เหล่านี้เป็น Role ที่มีอยู่ใน MSSQL อยู่แล้ว โดย db_reader เป็นการ Grant สิทธิ์อ่านอย่างเดียว และ db_reader เป็นสิทธิ์ให้เขียนอย่างเดียว

use [test]
exec sp_addrolemember 'db_datareader', [testuser]
exec sp_addrolemember 'db_datawriter', [testuser]