[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