Bài giảng Kiến trúc cài đặt cơ sở dữ liệu - Chương 10: Bảo mật trong SQL Server

GV Phi Loan - Khoa CNTT – HUI

Nội dung ◦ Các tính chất bảo mật của SQL Server ◦ Tài khoản đăng nhập (user login) ◦ Phân biệt user login và user ID ◦ Các loại User Roles:  Fixed Server Roles  Database Roles

◦ Các loại quyền bảo mật( Security Permissions) ◦ Nhóm lệnh DCL : GRANT, DENY, REVOKE ◦ Giải quyết xung đột giữa các quyền

GV Phi Loan - Khoa CNTT – HUI

• Information is an organizational asset, and, according to its value and scope, must be organized, inventoried, and secured . . . .

• If SQL Server is the box, then this part is about padlocking the box to keep out those who should be out, while extending welcome to those who should be allowed in.

GV Phi Loan - Khoa CNTT – HUI

Bảo mật SQL server

• Bao gồm nhiều bước liên quan đến 4 vùng

chính sau: – Platform – Authentication – Objects (including data) – Applications that access the system

GV Phi Loan - Khoa CNTT – HUI

Bảo mật các file hệ thống của SQL server • Hạn chế việc truy xuất đến các file hệ thống

của SQL server. • Các cấp bảo mật:

– Bảo mật các file dữ liệu và log – Bảo mật các đối tượng (object) trong DB – Bảo mật các application

• Mã hóa và chứng thực (encryption and

certificate)

GV Phi Loan - Khoa CNTT – HUI

Principal và securables

• Principals là các thực thể có thể yêu cầu tài

nguyên của SQL Server, có thể được phân cấp (hierarchy). Phạm vi ảnh hưởng của principal phụ thuộc vào định nghĩa giới hạn của principal.

• Mỗi principal có một mã số bảo mật khác

nhau (security identifier SID).

GV Phi Loan - Khoa CNTT – HUI

Ví dụ về Principal

• Windows Login: là 1 principal nhỏ nhất

(indivisible )

• Windows Group là 1 principal dạng tập hợp.

(collection)

GV Phi Loan - Khoa CNTT – HUI

Phân cấp các principal

• Windows-level principals: Windows Domain Login Windows Local Login • SQL Server-level principal:

SQL Server Login

• Database-level principals:

Database User Database Role Application Role

GV Phi Loan - Khoa CNTT – HUI

Principal và securables

• “Securables” là server, database, và các

đối tượng thuộc về database

• Ví dụ: – User – Database role – Server role – Type – XML schema ,….

GV Phi Loan - Khoa CNTT – HUI

Cơ chế bảo mật

• Từ SQL 2005, có 1 sự thay đổi lớn về bảo mật

so với các phiên bản trước.

• Chiến lược “secure by design, secure by

default, and secure in deployment” dùng để bảo vệ server và các database khỏi bị tấn công (security attacks)

GV Phi Loan - Khoa CNTT – HUI

Cơ chế bảo mật

• Để kiểm soát việc truy xuất vào tài nguyên SQL

Server, có 2 cơ chế bảo mật: – Authentication: là 1 quy trình dùng để xác nhận một ai đó có phải thực sự chính là họ không. Thường quy trình này liên quan đến username và password

– Authorization: dùng để tìm xem một người sau khi đã được nhận dạng có quyền ưu tiên sử dụng tài nguyên ở cấp nào?

GV Phi Loan - Khoa CNTT – HUI

Các loại tài khoản đăng nhập

• SQL Server 2012 hổ trợ các loại tài khoản

• Một principal phải có quyền (permission)

ALTER ANY LOGIN mới có thể tạo được tài khoản login SQL.

GV Phi Loan - Khoa CNTT – HUI

Các mức bảo mật

GV Phi Loan - Khoa CNTT – HUI

Hai mode Authentication

• Windows Authentication: cho phép user truy xuất vào SQL server thông qua tài khoản của Windows

• Mixed Mode Authentication: cho phép người

dùng truy xuất vào SQL server bằng cả 2 loại tài khoản: Windows và tài khoản riêng của SQL server

• Để thay đổi mode đăng nhập vào SQL server:

– Mở server Properties, – Chọn trang Security

GV Phi Loan - Khoa CNTT – HUI

Các tài khoản đăng nhập mặc định (Default login) • SQL Server tạo 2 tài khoản mặc định ngay tại thời điểm cài đặt và cấp quyền ưu tiên cao nhất – quyền quản trị cho hai tài khoản này. Tài khoản sa cho phép truy xuất vào SQL server bằng mode mixed authentication. Tài khoản BUILTIN\Administrators cho phép mọi thành viên trong nhóm administrator truy xuất vào SQL server bằng mode Windows authentication

GV Phi Loan - Khoa CNTT – HUI

Windows Authentication mode

• Mode xác thực bằng Windows mạnh hơn mode hôn hợp (mixed Mode) vì người dùng không cần thêm password và nó liên quan đến bảo mật của cả mạng. • Xác thực bằng Windows không chỉ xác thực người dùng riêng lẻ của Windows mà còn xác thực cả người dùng trong nhóm người dùng (user group) của Windows. – Khi 1 user group nào đó được chấp nhận như SQL Server login, thì người dùng nào thuộc nhóm user này đều có thể được xác thực bởi SQL Server.

GV Phi Loan - Khoa CNTT – HUI

Các loại tài khoản trong SQL server

• Cần phân biệt Login ID và user ID: – Login ID để thực hiện cơ chế

́ Authentication.

Người dùng muốn truy xuất vào hệ thống SQL Server, thì phải có login ID và password. Nhưng login ID chính nó không cho phép nguời dùng quyền truy xuất đến các DB.

– User ID thực hiện cơ chế Authorization. User ID nhận dạng người dùng trong 1 DB. Tất cả các quyền và chủ quyền của các đối tượng trong DB đều được điều khiển bởi user ID

GV Phi Loan - Khoa CNTT – HUI

Login ID và user ID

• Một login ID phải kết hợp với 1 user ID trong mỗi DB để có thể truy xuất dữ liệu trong DB. Nếu login ID không được kết hợp tường minh với 1 user ID thì nó sẽ kết hợp với user ID là guest. Nếu DB không có user ID guest thì không thể truy xuất vào DB được

• Việc quản trị sẽ dễ dàng hơn nếu login ID và user ID giống nhau nhưng điều này không bắt buộc.

GV Phi Loan - Khoa CNTT – HUI

Authentication & Authorization

• Tài khoản đăng nhập (Login ID) sẽ được ánh xạ với tài khoản user ( user ID) để cho phép user được quyền truy xuất trong 1 DB. Quá trình này gọi là authorization (hay permission validation).

• User sẽ không thể truy xuất vào DB ngay cả

khi họ có tài khoản đăng nhập (login ID) hợp lệ.

GV Phi Loan - Khoa CNTT – HUI

Bảo mật trong CSDL server

the Mgr125 login corresponds to User A in the Employees database and to User D in the Products database. These privileges permit the login to connect to the database(s), but they do not give the user any privileges against objects in those databases GV Phi Loan - Khoa CNTT – HUI

Tạo tài khoản đăng nhập Login ID

• Hai dạng login ID:

– Dùng tài khoản đăng nhập của chính

Windows làm tài khoản đăng nhập vào SQL server.

– Tạo 1 tài khoản đăng nhập của riêng SQL

server

GV Phi Loan - Khoa CNTT – HUI

Thêm tài khoản đăng nhập mới bằng Windows (Windows login)

• Tạo sẵn trước account của Windows • Nhấp phải tại folder Security\Login, chọn New

• Nhấp nút Search để tìm account của Windows • Hoặc dùng lệnh: CREATE LOGIN [\]

FROM WINDOWS;

• Vd: create login [philoan\dhth5] from windows

GV Phi Loan - Khoa CNTT – HUI

Tạo tài khoản của SQL server

• Chỉ có người quản tri (administrator) mới có

quyền tạo login ID mới.

• Nhấp phải tại folder Security\Login, chọn

New Login

• Tạo login của SQL server CREATE LOGIN

WITH PASSWORD = ''

[MUST_CHANGE]

GV Phi Loan - Khoa CNTT – HUI

Thay đổi các thuộc tính của tài khoản server • Lệnh ALTER LOGIN cho phép thay đổi các thuộc tính

của tài khoản SQL Server

ALTER LOGIN login_name { | WITH [ ,... ] Với ::= ENABLE | DISABLE Và ::= PASSWORD = 'password‘

[ OLD_PASSWORD = 'oldpassword]

Ví dụ: ALTER LOGIN user2 with password='56789'

old_password='12345'

GV Phi Loan - Khoa CNTT – HUI

Mã hóa - Encryption

• Encryption không giải quyết vấn đề kiểm

soát truy xuất nhưng nó hạn chế được việc mất mát thông tin khi việc kiểm soát truy xuất bị bỏ qua.

• Ví dụ: nếu máy tính chứa CSDL bị cấu hình

sai, nếu hacker có được thông tin nhạy cảm chẳng hạn là số thẻ tín dụng thì thông tin bị đánh cắp này cũng sẽ trở nên vô dụng nếu bị mã hóa.

GV Phi Loan - Khoa CNTT – HUI

Tạo login từ Certificate

• Certificates là các “khóa phần mềm”

được chia sẻ giữa 2 server cho phép trao đổi thông tin bảo mật giữa 2 server này.

• Ví dụ: CREATE CERTIFICATE Dan_Bacon WITH SUBJECT = 'Dan Bacon certificate in master DB’, EXPIRY_DATE = '01/01/2018'; CREATE LOGIN Dan_Bacon FROM CERTIFICATE

Dan_Bacon;

GV Phi Loan - Khoa CNTT – HUI

Xem/Xóa/Cấm tài khoản login

• Xem tài khoản login

select * from sys.server_principals

• Enable/disable tài khoản login

ALTER LOGIN sql_user_a DISABLE;

• Cấm tài khoản đăng nhập

ALTER LOGIN sql_user_a DISABLE;

• Xóa tài khoản đăng nhập

DROP LOGIN

GV Phi Loan - Khoa CNTT – HUI

Credential là gì?

• Credential là 1 bản ghi chứa thông tin xác

thực (credentials) được dùng để kết nối với tài nguyên bên ngoài SQL Server. Thông tin này chỉ dùng nội bộ trong SQL Server.

• Hầu hềt các credential đều chứa tài khoản và

password của Windows.

GV Phi Loan - Khoa CNTT – HUI

Credential là gì?

• Thông tin của credential cho phép người dùng đang kết nối với SQL Server bằng tài khoản của SQL Server được quyền truy xuất đến tài nguyên bên ngoài server. Khi tài nguyên bên ngoài là Windows, thì người dùng được xác thực như thể người dùng của Windows (đã được xác định trong credential).

• Một credential có thể được gán cho nhiều tài khoản đăng nhập của SQL Server logins. Nhưng một tài khoản SQL Server login chỉ có thể được gán cho 1 credential.

GV Phi Loan - Khoa CNTT – HUI

Tạo Credential

• Nhấp phải tại folder Security\Credentials, chọn lệnh

New Credential • Hoặc dùng lệnh: CREATE CREDENTIAL credential_name

WITH IDENTITY = 'identity_name' [ , SECRET = 'secret' ]

Identity là tên của tài khoản được dùng để kết nối ra

bên ngoài, thường là tài khoản Windows

SECRET: là password dùng để xác thực khi nối ra bên

ngoài

GV Phi Loan - Khoa CNTT – HUI

Ví dụ tạo credential

• Giả sử đã có 1 tài khoản đăng nhập bằng

Windows tên Philoan\Philoan

• Tạo 1 credential dùng tài khoản này CREATE CREDENTIAL Loan

WITH IDENTITY = ‘Philoan\Philoan’ SECRET =‘12345’

GV Phi Loan - Khoa CNTT – HUI

Tạo tài khoản SQL server gán với credential

CREATE LOGIN

WITH PASSWORD = '', CREDENTIAL = ;

Ví dụ: CREATE LOGIN test1 WITH PASSWORD=‘12345’

CREDENTIAL = ‘Loan’

GV Phi Loan - Khoa CNTT – HUI

Một số lệnh kiểm tra

• xp_loginconfig : kiểm tra mode xác thực • sp_validatelogins: xác định các tài khoản (Orphaned Windows users) bao gồm mã SID và tên tài khoản. – Khi 1 tài khoản Windows được dùng làm tài khoản đăng nhập của SQL Server bị xóa, thì tài khoản này không còn hợp lệ nữa.

GV Phi Loan - Khoa CNTT – HUI

Một số lệnh kiểm tra

• Để xem danh sách các principal hiện có

của server:

SELECT Name, SID FROM sys.server_principals

GV Phi Loan - Khoa CNTT – HUI

Tạo user cho DB hiện hành

CREATE USER user_name [ FOR LOGIN login_name]

Để thêm 1 tài khoản đăng nhập của 1 người

dùng mới vào CSDL hiện hành.

• Chỉ có dbo và thành viên của sysadmin mới

được quyền tạo tài khoản DB mới

GV Phi Loan - Khoa CNTT – HUI

Tạo user cho DB hiện hành

• Nếu không dùng FOR LOGIN, user mới sẽ được ánh xạ với tài khoản SQL Server cùng tên.

• Không thể tạo user guest vì guest đã có

sẵn bên trong mỗi DB.

GV Phi Loan - Khoa CNTT – HUI

Ví dụ tạo user cho DB

CREATE LOGIN abc WITH PASSWORD = ‘12345'; USE Toyworld; CREATE USER abc FOR LOGIN abc;

GV Phi Loan - Khoa CNTT – HUI

Xóa user cho DB hiện hành

• Để xoá tài khoản ra khỏi CSDL: DROP USER username • Ví dụ : DROP USER ‘Student’

GV Phi Loan - Khoa CNTT – HUI

Orphaned user

• Orphaned user là 1 database user tương đương với 1 tài khoản SQL login nào đó mà tài khoản này đã bị xóa.

• Để phát hiện orphaned users trong 1

CSDL:

GV Phi Loan - Khoa CNTT – HUI

Role là gì?

• Role là một công cụ cực mạnh cho phép ta

tập hợp các user vào cùng 1 unit nhờ đó ta có thể gán quyền chung cho cả unit đó.

• Mọi thành viên trong nhóm sẽ có quyền như nhau. Nếu chức năng của nhóm thay đổi, chỉ cần thay đổi quyền của nhóm, những thay đổi này sẽ được tự động áp dụng cho tất cả các thành viên trong nhóm.

GV Phi Loan - Khoa CNTT – HUI

Vai trò của role

• Mỗi role những quyền hạn (permission) khác

nhau và chứa 1 hay nhiều thành viên – Nếu công việc thay đổi. chỉ cần thay đổi quyền trong mỗi role  thay đổi sẽ tự động được áp dụng cho toàn bộ các thành viên của role đó. • Các user có thể là thành viên của nhiều role

khác nhau

GV Phi Loan - Khoa CNTT – HUI

Các loại User Roles

• Các nhóm của Windows có thể được dùng

như là các role của SQL server.

• Có 2 loại roles đã được định nghĩa sẵn trong

SQL Server: • Fixed Server Roles • Database Roles

GV Phi Loan - Khoa CNTT – HUI

Fixed Server Roles

• Server roles enable you to simplify the

assignment of permissions at the database instance level.

• Có 9 loại server roles. Danh sách sau liệt kê

các loại này mà khả năng quản trị theo thứ tự giảm dần

GV Phi Loan - Khoa CNTT – HUI

Fixed Server Roles

1. sysadmin: quyền tối đa mà không bị bất kz 1 hạn chế nào. Mặc định, tất cả các thành viên của Administratorsvà sa thuộc vào server role này 2. serveradmin: có quyền cấu hình mức server như xác lập lượng bộ nhớ mà SQL server có thể dùng khi truyền thông tin qua mạng.

3. setupadmin: có quyền thực thi replication và quản

trị các thủ tục (extended stored procedures).

4. securityadmin: điều hành bảo mật như tạo login và

gán quyền.

GV Phi Loan - Khoa CNTT – HUI

Fixed Server Roles

5. processadmin: có quyền kết thúc các tiến trình

được gọi không hợp lệ

6. dbcreator: tạo và chỉnh sửa database 7. diskadmin: thực hiện các hoạt động sao lưu như

sao chép đĩa và tạo các thiết bị sao lưu 8. bulkadmin: thực hiện lệnh BULK INSERT 9. Public: All SQL Server logins are members of this role. You can alter the permissions assigned to this role, but you cannot alter the membership of the role.

GV Phi Loan - Khoa CNTT – HUI

Quyền của thành viên server role

• Một thành viên của bất kz server role

nào đều có thể thêm các user khác vào chính server role đó.

GV Phi Loan - Khoa CNTT – HUI

Thay đổi thành viên của server role

• Dùng lệnh ALTER SERVER ROLE với các

tùy chọn sau để thay đổi thành viên của server role: – ADD MEMBER : thêm 1 server principal – DROP MEMBER: xóa 1 server principal.

• Ví dụ: ALTER SERVER ROLE serveradmin ADD MEMBER

"contoso\domain_group_b";

GV Phi Loan - Khoa CNTT – HUI

Một số lệnh về server role

• sp_helpsrvrole : cung cấp 1 danh sách các fixed

server roles

• sp_helpsrvrolemember : cho xem các thành viên của

1 fixed server role nào đó

• sp_srvrolepermission: cho xem quyền (permission)

của 1 fixed server role

• IS_SRVROLEMEMBER : cho phép kiểm tra 1 tài

khoản SQL Server có phải là thành viên của fixed server role không

• sys.server_role_members : cung cấp thông tin về

thành viên của role

GV Phi Loan - Khoa CNTT – HUI

User-Defined Server Roles

• Là 1 tính năng mới của SQL Server 2012. • Có thể tạo 1 server role của người dùng khi server roles hiện có không phù hợp với yêu cầu đặc biệt của người dùng.

• Các bước tạo user-defined server role – Creating the user-defined server role – Granting server-level permissions to the role – Adding SQL Server logins to the role

GV Phi Loan - Khoa CNTT – HUI

Ví dụ

• Tạo 1 server role quản l{ tài khoản

BenMiller USE master; CREATE SERVER ROLE buyers AUTHORIZATION

BenMiller;

GV Phi Loan - Khoa CNTT – HUI

Database roles

• Mỗi Database có một bộ các fixed database roles. Phạm vi của mỗi role này là chỉ trong từng database riêng rẽ.

• Ví dụ: nếu Database1 và Database2 cả hai đều có 1 user ID là UserX. Nhưng UserX trong Database1 thuộc fixed database role tên là db_owner. Role này không ảnh hưởng gì đến UserX trong Database2 khi user này là thành viên của role có tên là db_owner.

GV Phi Loan - Khoa CNTT – HUI

Database roles

• Database roles cho phép bạn gán quyền cho 1 nhóm các user thay vì phải gán quyền cho từng user riêng lẻ.

• Có 3 loại database roles:

• Fixed • Custom • Application

GV Phi Loan - Khoa CNTT – HUI

Fixed Database Roles

DatabaseRole

Mô tả

db_owner

Có tất cả các quyền trong DB

db_accessadmin

Thêm hay xóa các user ID

db_securityadmin Quản lý tất cả các role và permission

trong DB,có thể thay đổi chủ quyềnower (ownership)

db_ddladmin

Có thể thực hiện tất cả các lệnh DDL nhưng không thể sử dụng các lệnh GRANT, REVOKE hay DENY

db_backupoperator Có thể thực hiện các lệnh DBCC,

CHECKPOINT và BACKUP.

GV Phi Loan - Khoa CNTT – HUI

Fixed Database Roles

DatabaseRole

Mô tả

db_datareader

Có thể chọn tất cả dữ liệu từ bất kỳ bảng của user nào trong DB

db_datawriter

Có thể chỉnh sửa bất kỳ dữ liệu trong các bảng của user trong DB.

db_denydatareade r

Không thể chọn tất cả dữ liệu từ bất kỳ bảng của user nào trong DB

db_denydatawriter Không thể chỉnh sửa bất kỳ dữ liệu

trong các bảng của user trong DB.

GV Phi Loan - Khoa CNTT – HUI

Fixed Server Roles

• Mỗi user trong DB đều thuộc về role public. – Nếu 1 user chưa được cấp quyền đặc biệt gì thì

sẽ thuộc role public.

• Bất kz thành viên nào của fixed database role đều có thể thêm các user khác vào role của thành viên đó

GV Phi Loan - Khoa CNTT – HUI

Thêm thành viên vào DB role

• Lệnh sp_addrolemember để thêm 1 tài

khoản vào 1 role, khi đó thành viên có thể kế thừa bất kz quyền nào của role đó.

sp_addrolemember [ @rolename = ] 'role' ,

[ @membername = ] 'security_account'

GV Phi Loan - Khoa CNTT – HUI

Custom Database Roles

• Người dùng có thể cần có thêm một số

quyền mà các quyền này chưa được xác định trong bất kz role database cố định nào. SQL server cho phép tạo các database role tuz biến (custom)

• Khi tạo một role database tùy biến, cần thêm các quyền vào role trước , sau đó sẽ gán các user vào role.

GV Phi Loan - Khoa CNTT – HUI

Tạo role tùy biến

• Lệnh CREATE ROLE với tùy chọn

AUTHORIZATION sẽ cấp quyền sở hữu (ownership) role này cho 1 principal nào đó.

• Nếu không có tùy chọn AUTHORIZATION,

thì role sẽ được sở hữu bới chính principal thực hiện lệnh tạo role.

GV Phi Loan - Khoa CNTT – HUI

Ví dụ tạo role tùy biến

• Tạo role do kim_alert quản l{

• Cấp quyền cho role

GV Phi Loan - Khoa CNTT – HUI

Ví dụ tạo role tùy biến

• Gán quyền cho role

GV Phi Loan - Khoa CNTT – HUI

Xem danh mục user và role

• Các view chứa thông tin về about users, roles, và permissions ở mức database – sys.database_principals – sys.database_role_members – sys.database_permissions.

GV Phi Loan - Khoa CNTT – HUI

Tạo DB role tuz biến

• Dùng lệnh sp_addrole [ @rolename = ] 'role'

[ , [ @ownername = ] 'owner' ]

• Chỉ có những thành viên của role sysadmin, db_securityadmin và db_owner mới có quyền chạy lệnh này

• Ví dụ: tạo 1 role mới tên Managers cho CSDL

hiện hành.

EXEC sp_addrole 'Managers'

GV Phi Loan - Khoa CNTT – HUI

Vai trò của Application Roles

• Application roles cho phép truy xuất đến dữ

liệu khi người dùng kết nối đến DB thông qua 1 ứng dụng nào đó.

GV Phi Loan - Khoa CNTT – HUI

Application Roles

• Application role không có member (thành

viên) như các role khác. Người dùng có liên quan đến application role là do người dùng đó có khả năng chạy được ứng dụng hơn là do nguời dùng đó là thành viên của application role.

• Mặc định application role không hoạt động ( inactive) và đòi hỏi password khi được kích hoạt.

GV Phi Loan - Khoa CNTT – HUI

Kết nối với 1 application role

1. Người dùng chạy ứng dụng . Ứng dụng này kết nối

đền SQL Server.

2. Ứng dụng sẽ chạy thủ tục sp_setapprole để kích

hoạt application role.

3. Khi application hoạt động thì làm mất đi các permission khác của người dùng, chỉ còn các permission của application role.

4. Các quyền có được thông qua application role sẽ

được duy trì trong suốt thời gian kết nối cho đến khi disconnect hoặc dùng lệnh sp_unsetapprole với cookie để quay lại session trước đó.

GV Phi Loan - Khoa CNTT – HUI

Tạo application role

• Nhấp phải tại security\Role\Application Role,

chọn lệnh New application của DB

• Hoặc Tạo 1 application role cho DB hiện hành CREATE APPLICATION ROLE application_role_name

WITH PASSWORD = 'password'

Ví dụ: USE Toyworld CREATE APPLICATION ROLE ‘L1’ WITH

PASSWORD =‘’l1’

GV Phi Loan - Khoa CNTT – HUI

Kích hoạt 1 application role

sp_setapprole [@rolename =] 'role' , [@password =] 'password‘ [ , [ @fCreateCookie = ] true | false ] [ , [ @cookie =

] @cookie OUTPUT ]

• Thủ tục sp_setapprole chỉ có thể được gọi

trực tiếp bằng lệnh exec. Nó không thể được thực thi bên trong 1 thủ tục khác hay từ 1 transaction của người dùng

GV Phi Loan - Khoa CNTT – HUI

Kích hoạt 1 application role

• Cookie chứa thông tin trước khi application role được kích hoạt. Cookie được dùng cho lệnh sp_unsetapprole để quay về session trước đó và ngừng hoạt động của application role

• @fCreateCookie = xác định cookie có được tạo cùng lúc kích hoạt application hay không, (true  1, false  0)

• @cookie OUTPUT dùng để xác định cookie kiểu

varbinary(8000)

GV Phi Loan - Khoa CNTT – HUI

Ví dụ về kích hoạt application role

DECLARE @cookie varbinary(8000); EXEC sp_setapprole ‘l1', ‘l1' , @fCreateCookie =

true, @cookie = @cookie OUTPUT;

-- Application role đang hoạt động SELECT USER_NAME(); -- xem tên application role --Ngưng hoạt động application role và quay về

session trước đó

EXEC sp_unsetapprole @cookie; SELECT USER_NAME(); -- This will return the name

of the original user. GO

GV Phi Loan - Khoa CNTT – HUI

Ví dụ

• Sue đang chạy ứng dụng Sales mà ứng dụng này yêu cầu các quyền SELECT, UPDATE và INSERT vào các bảng Products và Orders trong CSDL Sales, nhưng cô ta lại không có các quyền này khi truy xuất trực tiếp vào hai bảng trên khi dùng cửa sổ Query hay các công cụ khác của SQL.

• Hãy phân quyền thích hợp cho Sue

GV Phi Loan - Khoa CNTT – HUI

Ví dụ • Tạo 1 Database role cấm các quyền SELECT,

INSERT và UPDATE trên bảng Products và Orders, và thêm Sue vào như là thành viên của role đó. Sau đó tạo 1 application role trong database Sales với các quyền SELECT, INSERT và UPDATE trên các bảng Products và Orders. Khi application chạy, cần đưa password vào để kích hoạt application role. Nếu Sue cố đăng nhập vào SQL server không thông qua ứng dụng thì cô ta không thể nào truy xuất vào được hai bảng Products và Orders

GV Phi Loan - Khoa CNTT – HUI

Least Privilege

• The principle of least privilege is a

principle of information security that dictates granting a security principal only those privileges that are necessary to perform required tasks.

GV Phi Loan - Khoa CNTT – HUI

Privileges (permissions) • Mỗi user account trong 1 database có thể được cấp (granted) một số quyền(privileges hay permissions).

• Có 3 loại quyền trong Microsoft SQL

– Server privileges (Implied Permissions ): include such

permissions as starting up, shutting down, and backing up the SQL server,

– Statement privileges: include such permissions as

creating a database and creating a table.

– Object privileges allow specific actions on a specific object, such as allowing select and update on table T1

GV Phi Loan - Khoa CNTT – HUI

Các loại quyền (Permissions)

• Khi 1 đối tượng được tạo ra, chỉ có owner (

người tạo đối tượng) mới có quyền truy xuất đối tựong. Owner phải cấp quyền cho các user khác

• User phải có quyền thích hợp để thực thi bất kz hoạt động nào liên quan đến việc thay đổi định nghĩa DB hay truy xuất dữ liệu

• Tất cả các quyền trong SQL server có thể tồn tại dưới 1 trong 3 trạng thái sau: granted ( cấp quyền), revoked (thu hồi) và denied (từ chối).

GV Phi Loan - Khoa CNTT – HUI

Object Permissions (Quyền về đối tượng) • Ngay khi 1 DB được tạo, các user cần được cho

quyền để làm việc với dữ liệu được lưu trữ trong DB.

• Có 6 lọai quyền object :

– SELECT: cho phép user đọc dữ liệu từ 1 bảng

hay view

– INSERT: cho phép user thêm các hàng mới vào

bảng

– UPDATE: cho phép user sửa đổi dữ liệu hiện

có trong bảng, tuy nhiên user không thể thêm hay xóa các hàng trong bảng được

GV Phi Loan - Khoa CNTT – HUI

Object Permissions (Quyền về đối tượng)

• DELETE: cho phép user được quyền xóa các

hàng trong bảng

• REFERENCES khi 2 bảng kết với nhau thông

qua khóa ngọai (foreign key) quyền REFERENCES cho phép user chọn dữ liệu từ bảng primary mà không cần có quyền SELECT trên bảng foreign

• EXECUTE: cho phép user thực thi thủ tục mà

user được cấp quyền

GV Phi Loan - Khoa CNTT – HUI

Statement Permissions Quyền về lệnh • Cho phép các họat động liên quan đến việc

tạo DB hay 1 đối tượng nào đó trong DB như bảng, thủ tục. Các quyền về lệnh đươc áp dụng chỉ cho lệnh hơn là cho bản thân các đối tượng.

• Các Statement permissions là

BACKUP DATABASE

CREATE PROCEDURE

BACKUP LOG

CREATE RULE

CREATE DATABASE

CREATE TABLE

CREATE DEFAULT

CREATE VIEW

CREATE FUNCTION

GV Phi Loan - Khoa CNTT – HUI

Implied Permissions Quyền ngầm định • Implied permissions kiểm sóat các họat động mà chỉ có thể được thực hiện bởi các thành viên của các role hệ thống (như sysadmin,..) hoặc các owner của các đối tượng CSDL. • Các owner của các đối tượng DB cũng có thể

có các quyền ngầm định cho phép họ thực thi tất cả các hoạt động với đối tượng mà họ là chủ

GV Phi Loan - Khoa CNTT – HUI

Data Control Language

• Có thể dùng các lệnh DCL(Data Control

Language) để thay đổi các quyền có liên quan đến user hay role của DB.

• Có 3 lệnh DCL : • GRANT • REVOKE • DENY

GV Phi Loan - Khoa CNTT – HUI

Data Control Language

• Cấp quyền về lệnh (Statement permissions):

GRANT { ALL | statement [ ,...n ] } TO security_account [ ,...n ]

GV Phi Loan - Khoa CNTT – HUI

Data Control Language Cấp quyền về đối tượng (Object permissions):

GRANT

{ ALL | permission [ ,...n ] } {

[ ( column [ ,...n ] ) ] ON { table | view } | ON { table | view } [ ( column [ ,...n ] ) ] | ON { stored_procedure |

extended_procedure }

| ON { user_defined_function }

}

TO security_account [ ,...n ] [ WITH GRANT OPTION ]

GV Phi Loan - Khoa CNTT – HUI

Data Control Language

• ALL: đối với quyền về lệnh (statement permission), thì ALL chỉ có thể được dùng bởi các thành viên của sysadmin. Đối với quyền về đối tượng (object permissions), ALL chỉ được dùng bởi các thành viên của sysadmin, db_owner và owner của đối tượng CSDL.

GV Phi Loan - Khoa CNTT – HUI

Data Control Language

• WITH GRANT OPTION (chỉ dùng cho quyền về đối tượng) cho khả năng các tài khoản sau khi được gán quyền được phép cấp quyền này cho các tài khoản khác

• Ví dụ:

Use pubs GRANT select, insert, update ON titles TO faculty

GV Phi Loan - Khoa CNTT – HUI