Presentation - Phân Quyền SQL server 2008

Phn Quyn Trong
Mn hc: An Ton v Bo Mt H Thng Thng Tin

Gv: Ths. Trng Hoi Phan
Product of Group 12 - K09406
1. 2. 3. 4. 5.
Gii thiu Qun l vic ng nhp Qun l ngi dng Qun l phn quyn Demo
Copyright Group 12 - K09406
1. Gii thiu
Bo mt trong SQL server gm ba lp:
Login security: Kim sot ai c th log vo SQl server.
Database access security : kim sot ai c th ng nhp vo Database ca SQL server. Permission security: Kim sot mt user c th thc hin thao tc g trn Database.
1. Gii thiu
2. Qun l vic ng nhp

Hai ch chng thc:
Mixed security Mode: Kt ni vi SQL server 2008 bng cch dng Windows Authentication hoc SQL server Authentication. Windows authentication mode: Ch c th kt ni vi SQL server 2000 bng Windows Authentication, Windows NT kim tra an ton tt c cc kt ni n SQL server
2. Qun l vic ng nhp

Thao tc: Chuyn i ch chng thc
2. Qun l vic ng nhp

To Login Account
Hai Phng php ng vi hai loi chng thc: To mt Windows account bng hai cch: Thao tc trc tip trn giao din: chn Sercurity.Login.New login Dng lnh: sp_grantlogin login To mi mt SQL logon account bng hai cch: Thao tc trc tip trn giao din: chn Sercurity.Login.New login
Dng lnh: sp_addlogin login, password, database_name
3. Qun l ngi dng

Sau khi cp Login account truy cp vo SQL Server bn cn cp cho Login account ny quyn l mt user troy cp mt/ nhiu Database. o SQL Server lu cc user ca Database trong table sysusers. o Mt Login account c th tr thnh user ca nhiu Database vi nhng quyn hn v mang nhiu user name khc nhau. Mc nh user name trng tn vi Login account.
3. Qun l ngi dng

Hai User c bit: dbo: mt user c tt c cc quyn trn Database. guest user: Mt Login account truy xut n mt instance ca SQL Server 2008, nhng khng c user account truy xut 1 Database c th, th c th c truy xut n Database nh mt ngi khch (guest user)
3. Qun l ngi dng

Khai bo mt user
Dng icon <tn Database>.User trong giao din. Dng thuc tnh ca Login account trong giao din. Dng lnh sp_grantdbaccess loginname.
Gn user l thnh vin ca Database Dng thuc tnh ca Database role trong giao din. Dng thuc tnh ca Login account trong giao din.
Dng lnh sp_addrolemember database_role, database_user_account
4. Qun l phn quyn

Kim sot user c th lm c g trn database bng s dng:
Cc Database role Cp quyn cho user thao tc trn object v statement
Cc c ch cp quyn:
Dng login account c to sn bi h thng v c gn role default nh: sa c gn sysadmin fixed server role.
Ch nh 1 login account l user ca mt Database: mc nh c quyn

thuc public database role. S dng role/c ch role bao trm: Sysadmin bao trm db_ower
S dng cc lnh cp quyn cho user thao tc trn object v statement nh

grant, deny, revoke
4. Qun l phn quyn

ROLES:
Role l mt cng c cho php cp quyn cho mt nhm
User thay v thc hin trn

tng user. C 2 loi Roles:
Fixed role
User-defined Database role Hoc c th phn bit:
Server Roles Database Roles
Role mc server
Role mc Database
4. Qun l phn quyn

o User Defined Roles: bn phi l thnh vin ca db_securiadmin, hay db_owner, hay sysadmin, c th
to role.
o Dng T-SQL to role: Bc 1: nh ngha mt role (Mt user_defined database role
c nh ngha trong mt DataBase)

sp_addrole role_name, role_owner Bc 2: Gn quyn v statement v object cho role Bc 3: Gn cc role l thnh vin ca role o Xa role: Sp_droprole rolename
4. Qun l phn quyn

o Quyn cho php ngi dng thc hin cch hnh ng trong Database. C hai loi quyn: Object permission (i tng): Kim sot mt User / role c th thc thi hnh ng g trn mt object c th trong Database. table , view DELETE SELECT table, view, v column
INSERT EXECUTE
UPDATE
table , view stored procedure

table, view, v column
DUMP TABLE
table
4. Qun l phn quyn

Statement (Pht biu): kim sot mt User/role c th thc hin c lnh no sau y: CREATE DATABASE CREATE DEFAULT CREATE PROCEDURE CREATE RULE CREATE TABLE CREATE VIEW BACKUP DATABASE BACKUP LOG
4. Qun l phn quyn

Cc thao tc v quyn:
Lnh Cp Quyn (grant): Nu bn cp quyn cho User v User l thnh vin ca mt Role. Cc quyn m User c c hp li t hai pha.
Granting Statement Permission : GRANT { ALL | statement [ ,...n ] } TO user_name [,...n ] Granting Object Permission : GRANT { { ALL | permission [ ,...n ] } [ (column_name [ ,...n ] ) ] ON { table | view | stored_procedure |extended_procedure | user_defined_function }} TO user_name [ ,...n ]
4. Qun l phn quyn

Lnh chi t (deny): Ngn User s dng quyn v khng cho php User c c hi tha hng do l thnh vin ca mt Role. Denying Statement Permission : DENY { ALL | statement [ ,...n ] } TO user_name [,...n ] Denying Object Permission : DENY { { ALL | permission [ ,...n ] } [ ( column_name[ ,...n ] ) ] ON { table | view | stored_procedure |extended_procedure | user_defined_function }} TO user_name [ ,...n ]
4. Qun l phn quyn

Lnh hy (revoke): Hy quyn cp grant hay t chi deny Revoking Statement Permission : REVOKE { ALL | statement [ ,...n ] } FROM user_name [ ,...n ] Revoking Object Permission : REVOKE { { ALL | permission [ ,...n ] } [ (column_name [ ,...n ] )] ON { table | view | stored_procedure |extended_procedure | user_defined_function }} {TO | FROM} user_name [ ,...n ]
5. Demo
Phn Quyn Trong
&
A
Product of Group 12 - K09406

Presentation - Phân Quyền SQL server 2008

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Presentation - Phân Quyền SQL server 2008

Uploaded by

Copyright:

Available Formats

Phn Quyn Trong

Mn hc: An Ton v Bo Mt H Thng Thng Tin

Product of Group 12 - K09406

Copyright Group 12 - K09406

Copyright Group 12 - K09406

Copyright Group 12 - K09406

2. Qun l vic ng nhp

Copyright Group 12 - K09406

2. Qun l vic ng nhp

Copyright Group 12 - K09406

2. Qun l vic ng nhp

Dng lnh: sp_addlogin login, password, database_name

Copyright Group 12 - K09406

3. Qun l ngi dng

Copyright Group 12 - K09406

3. Qun l ngi dng

Copyright Group 12 - K09406

3. Qun l ngi dng

Dng lnh sp_addrolemember database_role, database_user_account

Copyright Group 12 - K09406

4. Qun l phn quyn

Ch nh 1 login account l user ca mt Database: mc nh c quyn

S dng cc lnh cp quyn cho user thao tc trn object v statement nh

4. Qun l phn quyn

User thay v thc hin trn

Server Roles Database Roles

Copyright Group 12 - K09406

4. Qun l phn quyn

c nh ngha trong mt DataBase)

4. Qun l phn quyn

table , view stored procedure

4. Qun l phn quyn

Copyright Group 12 - K09406

4. Qun l phn quyn

Copyright Group 12 - K09406

4. Qun l phn quyn

4. Qun l phn quyn

Copyright Group 12 - K09406

Phn Quyn Trong

You might also like