[sql server] 簡明介紹sql server中的安全性 (使用者、角色、許可權)

--簡明介紹sql server中的安全性 (使用者、角色、許可權)
/*
使用者分為 伺服器登陸與資料庫使用者，兩者進行關聯
角色分為：
 伺服器角色（固有，不可刪改，不可增加自訂）；
 資料庫角色（固有，不可刪改、可增加自訂角色）；
查看聯機，可獲得更多協助
*/

--實際應用說明

--sql server 2000 建立登陸、使用者、角色，設定許可權
-- 添加伺服器登陸
EXEC master.dbo.sp_addlogin @loginame = N'logintest', @passwd = '111',
@defdb = N'DBTEST', @deflanguage = N'簡體中文'

/*
--設定登陸的角色
EXEC master..sp_addsrvrolemember @loginame = N'logintest', @rolename = N'sysadmin'
GO
*/
--一個登陸
--可以在多個資料庫上建立使用者
--但不能在一個資料庫上建立多個使用者
--為登入 logintest 在資料庫 dbtest 中添加安全賬戶 usertest
use dbtest
go
EXEC dbo.sp_grantdbaccess @loginame = N'logintest', @name_in_db = N'usertest'

-- 為使用者佈建資料庫角色
--  sp_addrole可以添加自訂的資料庫角色
EXEC sp_addrolemember N'db_datareader', N'usertest'
GO
EXEC sp_addrolemember N'db_datawriter', N'usertest'
GO
--以上設定使用者logintest只能登陸dbtest資料庫,但是我們卻可以訪問master,pubs等資料庫
--這是因為,那些資料庫裡有個guest使用者,guest使用者可以與沒有設定使用者的登陸名關聯
--即logintest在master庫上沒有設定關聯使用者,就使用了guest使用者

-- 刪除資料庫使用者 ,刪除登陸前必須刪除登陸的所有資料庫使用者
IF  EXISTS (SELECT * FROM dbo.sysusers WHERE name = N'usertest')
EXEC dbo.sp_revokedbaccess N'usertest'
go

-- 刪除登入 ,刪除登陸前必須刪除登陸的所有資料庫使用者
IF  EXISTS (SELECT * FROM master.dbo.syslogins WHERE name = N'logintest')
EXEC master.dbo.sp_droplogin @loginame = N'logintest'
go

----------------------------------
----自訂角色的做法
--建立角色 r_test
EXEC sp_addrole 'r_test'

--授予角色 r_test 對 jobs 表的所有許可權
GRANT ALL ON jobs TO r_test
--授予角色 r_test 對 titles 表的 SELECT 許可權
GRANT SELECT ON titles TO r_test

--添加登入 l_test,設定密碼為pwd,預設資料庫為pubs
EXEC sp_addlogin 'l_test','pwd','pubs'

--為登入 l_test 在資料庫 pubs 中添加安全賬戶 u_test
EXEC sp_grantdbaccess 'l_test','u_test'

--添加 u_test 為角色 r_test 的成員
EXEC sp_addrolemember 'r_test','u_test'

--拒絕安全賬戶 u_test 對 titles 表的 SELECT 許可權
DENY SELECT ON titles TO u_test

/*--完成上述步驟後,用 l_test 登入,可以對jobs表進行所有操作,但無法對titles表查詢,雖然角色 r_test 有titles表的select許可權,但已經在安全賬戶中明確拒絕了對titles的select許可權,所以l_test無titles表的select許可權--*/

--從資料庫 pubs 中刪除安全賬戶
EXEC sp_revokedbaccess 'u_test'

--刪除登入 l_test
EXEC sp_droplogin 'l_test'

--刪除角色 r_test
EXEC sp_droprole 'r_test'

---------------------------------
--sql server 2005裡可以有create用法
USE [master]
GO
CREATE LOGIN [logintest] WITH PASSWORD=N'111111', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
USE [db]
GO
CREATE USER [logintest] FOR LOGIN [logintest]
GO
USE [db]
GO
EXEC sp_addrolemember N'db_denydatareader', N'logintest'
GO
USE [db]
GO
EXEC sp_addrolemember N'db_denydatawriter', N'logintest'
GO
--伺服器角色

--詳細的使用權限設定，建議查看聯機的
grant 、 deny 、 REVOKE