Sql server 許可權管理，sqlserver許可權管理

Sql server 許可權管理，sqlserver許可權管理
1、設定某一登入賬戶只能看到部分資料庫

參考 TN VIEW ANY DATABASE許可權
通過sp_addlogin建立登入名稱
–basic:使用者登陸帳號
–basic123456:使用者登陸密碼

USE masterGOexecute sp_addlogin 'basic','basic123456'

–VIEW ANY DATABASE 許可權控制是否顯示 sys.databases 和 sys.sysdatabases 視圖以及 sp_helpdb 系統預存程序中的中繼資料。
可用下面語句查詢狀態：

SELECT l.name as grantee_name, p.state_desc, p.permission_name FROM sys.server_permissions AS p JOIN sys.server_principals AS l ON   p.grantee_principal_id = l.principal_idWHERE permission_name = 'VIEW ANY DATABASE' ;GO

拒絕basic登入名稱訪問任何資料庫（看不到）
deny VIEW ANY DATABASE TO [basic]；
--GRANT VIEW ANY DATABASE TO <login> 授予特定登入帳戶

若想被登入賬戶basic 看到某一資料庫如： BasicData , 執行下面的語句：

ALTER AUTHORIZATION ON DATABASE::BasicData TO [basic]

–use BasData
–這一步驟執行不了，提示已經存在basic帳號了。
–經過測試，將前面建立帳號的步驟去掉，這一語句即可正常執行。
–在資料庫BasicData裡建立資料庫basic帳號
–第一個basic為登陸名
–第二個basic為資料庫帳號
–execute sp_grantdbaccess ‘basic’,’basic’
–將TestUser帳號添加到資料庫角色為資料庫擁有者
–execute sp_addrolemember ‘db_owner’,’basic’

2、賦予、回收表的許可權

---授予使用者person對錶Person.Address的修改許可權USE AdventureWorks2008R2;GRANT UPDATE ON Person.Address TO person;GO---授予使用者person對錶Person.Address的插入許可權USE AdventureWorks2008R2;GRANT INSERT ON Person.Address TO person;GO---授予使用者person對錶Person.Address的刪除許可權USE AdventureWorks2008R2;GRANT DELETE ON Person.Address TO person; --授予使用者預存程序dbo.prc_errorlog的執行許可權GRANT EXECUTE ON dbo.prc_errorlog TO person

--回收修改USE AdventureWorks2008R2;REVOKE update ON   Person.Address FROM person;USE AdventureWorks2008R2;REVOKE alter ON   Person.Address FROM person;--回收刪除USE AdventureWorks2008R2;REVOKE delete ON   Person.Address FROM person;--回收查詢USE AdventureWorks2008R2;REVOKE select ON   Person.Address FROM person;

3、如何設定對列的許可權

一、方式一：使用視圖

將需要限制使用者只能看到特定的幾個列、設定成一個視圖，然後對這個視圖進行許可權控制

二、方式二：使用GRANT語句

1、授予相關列的查詢許可權（SELECT）

在資料庫db1中，登入名稱UserA 只能有許可權查詢 Employee表 裡面的BusinessEntityID, NationalIDNumber, LoginID三個欄位許可權，不能查詢其它欄位

GRANT SELECT(BusinessEntityID, NationalIDNumber, LoginID) ON Employee TO UserA

2、授予相關列的修改許可權（Update）

對於登入名稱UserB，只允許其修改 Address表 的AddressLine1,AddressLine2兩個欄位，其它欄位不許修改

GRANT UPDATE(AddressLine1,AddressLine2) ON Address TO UserB

4、查詢許可權

登入名表

select * from master.sys.syslogins

登入名與伺服器角色關聯表

select * from sys.server_role_members

伺服器角色表

select * from sys.server_principals

查詢登入名擁有的伺服器角色

select SrvRole = g.name, MemberName = u.name, MemberSID = u.sid
from sys.server_role_members m inner join sys.server_principals g on g.principal_id = m.role_principal_id inner join sys.server_principals u on u.principal_id = m.member_principal_id

資料庫使用者表

select * from sysusers

資料庫使用者表角色關聯表
select * from sysmembers

資料庫角色表
select * from sys.database_principals

查詢資料庫使用者擁有的角色
select ta.name as username,tc.name as databaserole from sysusers ta inner join sysmembers tb on ta.uid=tb.memberuid inner join sys.database_principals tc on tb.groupuid=tc.principal_id

查詢當前資料庫使用者關聯的登入名

 use AdventureWorks2008R2  select ta.name as loginname,tb.name as databaseusername from master.sys.syslogins ta inner join sysusers tb on ta.sid=tb.sid 

如果將當前資料庫還原到另一台伺服器執行個體上,剛好那台伺服器上也存在person登入使用者，你會發現二者的sid不一樣，
由於sid不一樣,所以登入使用者不具有當前資料庫的存取權限,我們要想辦法將二者關聯起來。

關聯登入名與資料庫使用者（將資料庫使用者的sid刷成登入名的sid）

   use AdventureWorks2008R2    EXEC sp_change_users_login 'Update_One', 'person', 'person'    Go

5、補充

USE [master]GO---建立登入名CREATE LOGIN [person] WITH PASSWORD=N'person', DEFAULT_DATABASE=[news], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFFGOUSE [news]GO---在指定的資料庫下建立和登入名相關聯的資料庫使用者CREATE USER [person] FOR LOGIN [person]GOUSE [news]GO---在指定的資料庫下授予使用者SELECT,DELETE,UPDATE,INSERT,EXECUTE許可權。GRANT SELECT,DELETE,UPDATE,INSERT,EXECUTE TO person;

參考 SQL Server 許可權管理
over.

   

頂

0

踩

0

查看評論