การใช้ 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]

RESOURCE ส่วนเกินสิทธิ์

โพสต์ครั้งแรก: 12 ธันวาคม 2011

ป

กติแล้วเวลาเราสร้าง User บนฐานข้อมูล Oracle เราจะ grant บทบาท (ROLE) ที่เป็น CONNECT,RESOURCE ให้ วันนี้เราจะมาดูว่าการ Grant แบบนี้มีผลอย่างไรกับ User ที่เราสร้างขึ้น เริ่มจาก Role CONNECT ก่อน โดยทั่วไปเรา grant สิทธิ์ CONNECT ให้เพื่อให้ User นั้น ๆ สามารถล็อกอินเข้าฐานข้อมูลได้

sys@PAO1> create user korn identified by korn
2 default tablespace users;
User created.

sys@PAO1> conn korn/korn@PAO1
ERROR:
ORA-01045: user KORN lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.

sys@PAO1> conn sys@PAO1 as sysdba
Enter password:
Connected.

sys@PAO1> grant connect to korn;
Grant succeeded.

sys@PAO1> conn korn/korn
Connected.
korn@PAO1> create table t (id number);
create table t (id number)
*
ERROR at line 1:
ORA-01031: insufficient privileges

จะเห็่นว่าถ้าเราไม่ได้ grant สิทธิ์ที่เหมาะสมให้ User จะไม่สามารถล็อกอินเข้าฐานข้อมูลได้ เมื่อล็อกอินเข้าได้แล้วหากไม่ได้ grant สิทธิ์ที่เหมาะสมก็ยังไม่สามารถสร้าง Object ใด ๆ ได้เช่นกัน สิทธิ์ที่นิยม grant ให้เป็น Role ที่ชื่อว่า RESOURCE ซึ่งโดยหลัก ๆ แล้วจะทำให้ User สามารถ ทำสิ่งต่อไปนี้ได้

• CREATE TRIGGER
• CREATE SEQUENCE
• CREATE TYPE
• CREATE PROCEDURE
• CREATE CLUSTER
• CREATE OPERATOR
• CREATE INDEXTYPE
• CREATE TABLE

อย่างไรก็ตาม Role นี้ยังให้สิทธิ์แฝงมาอีกอันหนึ่งคือ UNLIMITED TABLESPACE ซึ่งทำให้ User สามารถไปสร้าง Object บน Tablespace ใดก็ได้มีขนาดเท่าใดก็ได้ (ตามแต่พื้นที่บน Tablespace จะเพียงพอ)

korn@PAO1> conn sys@pao1 as sysdba
Enter password:
Connected.
sys@PAO1> grant resource to korn;

Grant succeeded.
sys@PAO1> conn korn@pao1
Enter password:
Connected.
korn@PAO1> create table t (id number) tablespace system;

Table created.

korn@PAO1> select privilege from user_sys_privs;

PRIVILEGE
---------------------------------------
UNLIMITED TABLESPACE

korn@PAO1> select granted_role from user_role_privs
2 where username = 'KORN';

GRANTED_ROLE
------------------------------
CONNECT
RESOURCE

จะเห็นว่านอกจาก Role ทั้งสองที่เรา Grant ให้แล้ว ยังมี System Privilege ที่ชื่อ "UNLIMITED TABLESPACE" อยู่อีกตัวซึ่งทำให้ KORN สามารถสร้าง Object บน Tablespace ใด ๆ ได้ไม่จำกัดขนาด หากเราไม่ต้องการให้ KORN สามารถไปสร้าง Object บน Tablespace อื่นนอกจาก Default Tablespace ที่กำหนดไว้ให้กับ User แล้ว เราจะต้อง Revoke สิทธิ์นี้ออกโดยใช้คำสั่ง REVOKE

korn@PAO1> conn sys@pao1 as sysdba
Enter password:
Connected.
sys@PAO1> revoke unlimited tablespace from korn;

Revoke succeeded.

sys@PAO1> conn korn@pao1
Enter password:
Connected.
korn@PAO1> create table t2 (id number) tablespace system;
create table t2 (id number) tablespace system
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'SYSTEM'

korn@PAO1> create table t2 (id number) tablespace users;
create table t2 (id number) tablespace users
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

จากตัวอย่างข้างบนจะเห็นว่า แม้เราต้องการสร้าง Object บน Tablespace USERS ซึ่งเป็น Default Tablespace (ระบุเมื่อตอนเราสร้าง USER) เราก็ยังไม่สามารถสร้างได้ทั้งนี้เนื่องจาก พอเรา Revoke สิทธิ์ UNLIMITED TABLESPACE จะทำให้สิทธิ์บน Tablespace USERS หายไปด้วย เราจะต้องระบุ Quota ให้กับ User ในการใช้พื้นที่บน Tablespace ด้วย

korn@PAO1> conn sys@pao1 as sysdba
Enter password:
Connected.
sys@PAO1> alter user korn quota unlimited on users;

User altered.

sys@PAO1> conn korn@pao1
Enter password:
Connected.
korn@PAO1> create table t2 (id number) tablespace users;

Table created.
korn@PAO1> -- Clearing
korn@PAO1> drop table t purge;

Table dropped.

korn@PAO1> drop table t2 purge;

Table dropped.

ถ้าจัดเป็นชุดรวมกัน คำสั่งสร้าง User แบบย่อ ๆ ของผมจะมีหน้าตาแบบนี้ครับ
create user korn identified by korn default tablespace users;
grant connect,resource to korn;
revoke unlimited tablespace from korn;
alter user korn quota unlimited on users;