blog.Toby

  BlogJava :: 首页 :: 新随笔 :: 联系 :: 聚合  :: 管理 ::
  130 随笔 :: 2 文章 :: 150 评论 :: 0 Trackbacks
SQL Server的安全机制及DBA如何管理
作者/j14n 时间/2006-11-16 11:26:00 类别/黑客攻防
 
标签:
一 安全体系
登录、安全账户(用户)、角色和组是 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
posted on 2009-06-09 11:03 渠上月 阅读(597) 评论(0)  编辑  收藏 所属分类: sql (sqlServer)

只有注册用户登录后才能发表评论。


网站导航: