一 安全体系
登录、安全账户（用户）、角色和组是 Microsoft? SQL Server? 2000 安全机制的基础。连接到 SQL Server 的用户必须使用特定的登录标识符 (ID) 标识自己。因此，用户只能查看经授权可以查看的表和视图，并且只能执行经授权可以执行的存储过程和管理功能。在SQL Server中，login（登录）是用于进入服务器时进行身份识别的，每个user和login相关联，保证能进入服务器，每个user属于各个role，拥有相应的数据库权限，整个体系如图：
二 系统存储过程和命令
1、存储过程：
登录： sp_grantlogin, sp_revokelogin, sp_denylogin
sp_addlogin, sp_droplogin
sp_helplogins
用户： sp_grantdbaccess, sp_revokedbaccess
sp_adduser, sp_dropuser (这两个SP是为了向后兼容，请使用前2个代替)
sp_helpuser
角色：
服务器角色 sp_addsrvrolemember, sp_dropsrvrolemember
数据库角色 sp_addrole, sp_droprole
sp_addapprole, sp_dropapprole
sp_helprole
sp_addrolemember, sp_droprolemember
sp_helprolemember
2、命令：grant, revoke, deny
三 固定角色
1、固定服务器角色：
固定服务器角色
描述
sysadmin 可以在 SQL Server 中执行任何活动
serveradmin 可以设置服务器范围的配置选项，关闭服务器
setupadmin 可以管理链接服务器和启动过程
securityadmin 可以管理登录和 CREATE DATABASE 权限，还可以读取错误日志和更改密码
processadmin 可以管理在 SQL Server 中运行的进程
dbcreator 可以创建、更改和除去数据库
diskadmin 可以管理磁盘文件
bulkadmin 可以执行 BULK INSERT 语句
2、固定数据库角色：
固定数据库角色
描述
db_owner 在数据库中有全部权限
db_accessadmin 可以添加或删除用户 ID
db_securityadmin 可以管理全部权限、对象所有权、角色和角色成员资格
db_ddladmin 可以发出 ALL DDL，但不能发出 GRANT、REVOKE 或 DENY 语句
db_backupoperator 可以发出 DBCC、CHECKPOINT 和 BACKUP 语句
db_datareader 可以选择数据库内任何用户表中的所有数据
db_datawriter 可以更改数据库内任何用户表中的所有数据
db_denydatareader 不能选择数据库内任何用户表中的任何数据
db_denydatawriter 不能更改数据库内任何用户表中的任何数据
四 权限
包括两种类型的权限，即对象权限和语句权限：
对象操作权限总结
表 SELECT, INSERT, UPDATE, DELETE, REFERENCE
视图 SELECT, UPDATE, INSERT, DELETE
存储过程 EXECUTE
函数 表值函数 SELECT, INSERT, UPDATE, DELETE, REFERENCE
标量值函数 EXECUTE 和 REFERENCES
内嵌表函数 EXECUTE 和 REFERENCES
列 SELECT, UPDATE
注意：REFERENCE充许在GRANT、DENY、REVOKE语句中向有外键参照表中插入一行数据。
语句权限总结
CREATE DATABASE 创建数据库
CREATE TABLE 创建表
CREATE VIEW 创建视图
CREATE RULE 创建规则
CREATE DEFAULT 创建缺省
CREATE PROCEDURE 创建存储过程
BACKUP DATABASE 备份数据库
BACKUP LOG 备份事务日志
五 实用脚本
在sql server中，登录到某数据库必须有两个条件：
1、为SQL Server建立一个login；
2、为该login在数据库中建立user，给该用户关联role
/* DBA 的新增登录授权脚本*/
USE my_database
GO
--1. 新建登录
IF NOT EXISTS(SELECT 1 FROM master.dbo.syslogins WHERE name = 'wh_login' AND loginname = N'wh_login')
EXEC sp_addlogin 'wh_login', '123', 'plm25'
GO
--2. 新建用户
IF NOT EXISTS(SELECT * FROM sysusers WHERE name = N'lewis_user')
EXEC sp_grantdbaccess 'wh_login', 'lewis_user'
GO
--3. 分配权限
--语句权限
GRANT CREATE TABLE TO lewis_user
--对象权限
GRANT SELECT ON fb_app_module TO lewis_user
--授予所有权限
GRANT ALL TO lewis_user
--4. 授予角色
EXEC sp_addrolemember N'db_datareader', N'lewis_user'
EXEC sp_addrolemember N'db_datawriter', N'lewis_user'
EXEC sp_addrolemember N'db_accessadmin', N'lewis_user'
EXEC sp_addrolemember N'db_ddladmin', N'lewis_user' --建立存储过程权限
--5. 更改登录的默认数据库
EXEC sp_defaultdb @loginame = N'login', @defdb = N'default_DB_name'
--6. 更改登录的默认语言
EXEC sp_defaultlanguage @loginame = N'login', @language = N' french'
/*DBA 的删除登录授权脚本*/
--1. 拒绝授权
REVOKE SELECT ON [dbo].[fb_app_module] TO [lewis_user] CASCADE
REVOKE CREATE TABLE TO lewis_user
EXEC sp_droprolemember N'db_datareader', N'lewis_user'
--不能更改 public 角色的成员资格。也不需要移除。
--2. 删除用户
IF EXISTS(SELECT * FROM sysusers WHERE name = N'lewis')
EXEC sp_revokedbaccess 'lewis_user'
GO
--3. 删除登录
IF EXISTS(SELECT 1 FROM master.dbo.syslogins WHERE name = 'wh_login' AND loginname = N'wh_login')
EXEC sp_droplogin 'wh_login'
GO
/*DBA 的查看授权脚本*/
--1、查看服务器的登录
sp_helplogins 查看login和user
SELECT * FROM master.dbo.syslogins WHERE loginname = @login_name
--2、查看数据库的用户
sp_helpuser
SELECT * FROM dbo.sysusers where issqlrole = 1 /*数据库角色*/
SELECT * FROM dbo.sysusers where islogin = 1 /*登录*/
SELECT * FROM dbo.sysusers where issqluser = 1 /*SQL Server用户*/
SELECT * FROM dbo.sysusers where isntname = 1 /*NT用户*/
SELECT * FROM dbo.sysusers where isapprole = 1 /*服务器角色*/
--3、查看用户有什么权限和角色????
sp_helprolemember 'db_datareader'
--4、用户拥有的数据库中的对象????
--5、查看用户、登录、角色等综合信息
select DISTINCT username = o.name,
loginname = (case when (o.sid = 0x00) then NULL
else l.loginname end),
rolegroup = user_name(o.gid),
userID = o.uid,
o.hasdbaccess,
o.uid
from dbo.sysusers o left join master.dbo.syslogins l on l.sid = o.sid
where ((o.issqlrole != 1 and o.isapprole != 1) or (o.sid = 0x00) and o.hasdbaccess = 1)
and o.isaliased != 1 and (o.name = N'@your_user_name')
六 示例
-- ===================================================
-- Procedure Name: fb_plm_access_control
-- Function : set PLM Suite application user
-- Failure return: 1
-- Success return: 0
--
-- Parameters :
-- @login_name : application user name, create a account if the user does not exists, otherwise remain it.
-- @password : for the exists user, if the password is null, then remain its old password,
-- : for the new user, it can not be null.
-- @db_name : PLM Suite database name
-- ===================================================
IF EXISTS (select * from sysobjects where id = object_id(N'fb_plm_access_control') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE fb_plm_access_control
GO
CREATE PROCEDURE fb_plm_access_control
(
@login_name NVARCHAR(100),
@password NVARCHAR(100) = null,
@db_name NVARCHAR(100)
)
--WITH ENCRYPTION
AS
DECLARE @result INT
DECLARE @user_exists INT
DECLARE @execute_sp_role NVARCHAR(100)
DECLARE @create_table_role NVARCHAR(100)
DECLARE @object_name NVARCHAR(120)
DECLARE @object_type NVARCHAR(10)
DECLARE @sql VARCHAR(200)
DECLARE @error_msg VARCHAR(100)
BEGIN
-- check input paramters.
IF (@login_name IS NULL) OR (LTRIM(@login_name) = '') OR (lower(@login_name) = 'sa')
BEGIN
RAISERROR ('The login_name can not be null or sa.' , 16, 1) WITH NOWAIT
RETURN 1
END
IF NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = @db_name)
BEGIN
SET @error_msg = 'The database ' + @db_name + ' does not exits.'
RAISERROR (@error_msg, 16, 1) WITH NOWAIT
RETURN 1
END
-- if user does not exits, create it
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = @login_name)
BEGIN
SET @user_exists = 0
IF (@password IS NULL) OR (@password = '')
BEGIN
RAISERROR ('For new application user, the password can not be null.' , 16, 1) WITH NOWAIT
RETURN 1
END
EXEC sp_addlogin @loginame = @login_name, @passwd = @password, @defdb = @db_name, @deflanguage = @@language
IF @@ERROR <> 0
RETURN 1
END
ELSE
BEGIN
SET @user_exists = 1
IF (@password IS NOT NULL) AND (LTRIM(@password)<>'')
BEGIN
EXEC sp_password @old = null, @new = @password, @loginame = @login_name
IF @@ERROR <> 0
RETURN 1
END
END
-- create the execute sp role if it does not exists
SET @execute_sp_role = N'db_procexecutor'
IF NOT EXISTS (SELECT * FROM dbo.sysusers WHERE name = @execute_sp_role AND issqlrole = 1)
BEGIN
EXEC sp_addrole @rolename = @execute_sp_role
IF @@ERROR <> 0
RETURN 1
END
-- create the create table role if it does not exists
SET @create_table_role = N'db_createtable'
IF NOT EXISTS (SELECT * FROM dbo.sysusers WHERE name = @create_table_role AND issqlrole = 1)
BEGIN
EXEC sp_addrole @rolename = @create_table_role
IF @@ERROR <> 0
RETURN 1
END
-- grant privilege to the role
DECLARE cur_sp_fun CURSOR
FAST_FORWARD
FOR
SELECT name, xtype
FROM sysobjects
WHERE xtype in (N'P', N'FN', N'IF', N'TF')
AND uid = (select uid from sysusers where name = USER_NAME())
OPEN cur_sp_fun
FETCH NEXT FROM cur_sp_fun INTO @object_name, @object_type
WHILE @@FETCH_STATUS = 0
BEGIN
IF @object_type = N'P'
BEGIN
SET @sql='GRANT EXECUTE ON '+@object_name+' TO '+@execute_sp_role
END
IF @object_type = N'FN'
BEGIN
SET @sql='GRANT REFENENCES ON '+@object_name+' TO '+@execute_sp_role
SET @sql=' GRANT EXECUTE ON '+@object_name+' TO '+@execute_sp_role
END
IF @object_type IN (N'IF',N'TF')
BEGIN
SET @sql='GRANT SELECT ON '+@object_name+' TO '+@execute_sp_role
END
EXEC (@sql)
IF @@ERROR <> 0
BEGIN
SET @result = 1
BREAK
END
FETCH NEXT FROM cur_sp_fun INTO @object_name, @object_type
END
CLOSE cur_sp_fun
DEALLOCATE cur_sp_fun
IF @result = 1
RETURN 1
SET @sql = 'GRANT CREATE TABLE TO ' + @create_table_role
EXEC (@sql)
IF @@ERROR <> 0
RETURN 1
-- grant access database privilege to application user
IF @user_exists = 1
EXEC sp_revokedbaccess @name_in_db = @login_name
IF @@ERROR <> 0
RETURN 1
EXEC sp_grantdbaccess @loginame = @login_name, @name_in_db = @login_name
IF @@ERROR <> 0
RETURN 1
-- grant role to application user
EXEC sp_addrolemember @rolename = N'db_datareader', @membername = @login_name
IF @@ERROR <> 0
RETURN 1
EXEC sp_addrolemember @rolename = N'db_datawriter', @membername = @login_name
IF @@ERROR <> 0
RETURN 1
EXEC sp_addrolemember @rolename = N'db_datawriter', @membername = @login_name
IF @@ERROR <> 0
RETURN 1
EXEC sp_addrolemember @rolename = @create_table_role, @membername = @login_name
IF @@ERROR <> 0
RETURN 1
EXEC sp_addrolemember @rolename = @execute_sp_role, @membername = @login_name
IF @@ERROR <> 0
RETURN 1
RETURN 0
END
GO |