none
关于SQL的权限设置。 RRS feed

  • 问题

  • 出于对SQL的安全考虑。我SQL里有数据库A和B,我想分别建两个用户分别管理两个数据库。用户1只能管理A,用户2只能管理B。同时可以分别对两个数据库做还原,还有在SQL server代理里新建作业。简单的说就是用户1可以对A有任何操作。用户2可以对B有任何操作。

    方法:

    1、新建用户1和用户2,分别给A和B的owner权限及msdb中的sql agent权限。这样用户1和用户2登陆的时候可以看到代理,而且只能对自己的数据库进行操作和新建作业,对别人的数据库没有权限访问。但是在还原数据库的时候会提示没有创建权限。需要给dbcreator权限。一旦给了以后用户1就可以还原A数据库,但是同时也可以还原B数据库了。违反了单独管理的原则。

    2、可以在服务器设置中分别对用户1和用户2设置,查看任意数据库 权限 选拒绝。这样登陆的时候就看不到对方的数据库,当然也就不能还原了(没试过用语句是否可以)。但是这种方式登陆进来的时候看不见SQL server代理,不能新建作业了又。

    纠结,有没有什么方式使用用户1只能对数据库A操作,备份还原,新建作业都是针对A数据库的。用户2只能对数据库B操作,备份还原作业也只是对数据库B的。

    我现在能想到的就是为用户1建两个用户,一个用来还原数据库(只能看到A数据库,没有代理),一个用来新建作业的(能看到其他数据库,但是没有权限访问。同时没有还原权限)。

    2012年6月19日 9:13

答案

  • 1. can use sp as zicxc said, add 'execute as owner' in sp and put sp under dbo schema in master db.

    2. users can see sql job if you put them in SqlAgentUserRole in msdb.

    • 已标记为答案 LeoChn 2012年6月21日 6:51
    2012年6月19日 13:11

全部回复

  • 可以考虑写两个存储过程,分别做 RESTORE A 和 RESTORE B

    然后授予用户对这个存储过程的权限,不允许直接还原数据库

    2012年6月19日 11:14
  • 1. can use sp as zicxc said, add 'execute as owner' in sp and put sp under dbo schema in master db.

    2. users can see sql job if you put them in SqlAgentUserRole in msdb.

    • 已标记为答案 LeoChn 2012年6月21日 6:51
    2012年6月19日 13:11
  • 1,用存储过程或者语句做都会提示”在数据库 'master' 中拒绝了 CREATE DATABASE 权限。“必须给服务器的dbcreator权限,一旦给了这个就能对任何数据库进行还原,虽然没有访问权限,但是因为dbcreator权限太大了。

    2,SqlAgent的权限已经给了可以看见代理。但是为了1中的问题,避免给完dbcreator可以还原任意数据库,所以在服务器属性权限设置里”查看任意数据库“拒绝,这样进来了就只能看到有权限的数据库,虽然有dbcreator但因为查看不了其他数据库也就不能还原没权限的数据库了。但是,这时候就看不到代理了,给了SqlAgent也看不到。(只要服务器设置里”查看任意数据库“选拒绝,就算msdb中的SqlAgent权限给了也看不到代理)


    • 已编辑 LeoChn 2012年6月20日 2:56
    2012年6月20日 2:54
  • 另外,如果用sa做存储过程给用户A运行权限。因为还原的时候经常需要还原不同时间不同数量的日志,所以不可能用一个固定的存储过程,需要有修改的权限,一旦给了就又可以还原其他数据库了。
    2012年6月20日 2:59
  • 另外,如果用sa做存储过程给用户A运行权限。因为还原的时候经常需要还原不同时间不同数量的日志,所以不可能用一个固定的存储过程,需要有修改的权限,一旦给了就又可以还原其他数据库了。
    Stored procedure accepts parameters, one sp can do many as long as db name is static.
    2012年6月20日 3:25
  • 1,用存储过程或者语句做都会提示”在数据库 'master' 中拒绝了 CREATE DATABASE 权限。“必须给服务器的dbcreator权限,一旦给了这个就能对任何数据库进行还原,虽然没有访问权限,但是因为dbcreator权限太大了。

    2,SqlAgent的权限已经给了可以看见代理。但是为了1中的问题,避免给完dbcreator可以还原任意数据库,所以在服务器属性权限设置里”查看任意数据库“拒绝,这样进来了就只能看到有权限的数据库,虽然有dbcreator但因为查看不了其他数据库也就不能还原没权限的数据库了。但是,这时候就看不到代理了,给了SqlAgent也看不到。(只要服务器设置里”查看任意数据库“选拒绝,就算msdb中的SqlAgent权限给了也看不到代理)


    1. do you have 'execute as owner' in the sp? User doesn't need dbcreator rights if sp is owned by sysadmin.

    2. no problem if you do above.

    2012年6月20日 3:28
  • 发现了一个问题,如果在”查看任意数据库“选拒绝,虽然用户进去只能看到自己的数据库。但是仍然可以用RESTORE DATABASE来还原别人的数据库只要他有dbcreator的权限。这个就比较麻烦了。再做(在”查看任意数据库“选拒绝)这个就没有意义了。

    目前是,分用户1对数据库A有owner权限,用户2对数据库B有owner权限。同时给msdb中的SqlAgent权限。这样可以分别管理自己的数据库对别的数据库没有权限访问。同时可以用代理执行作业。就是没有办法做数据库和日志的还原操作。如果要做就要给dbcreator权限,给了以后就可以对任意数据库做还原了。纠结中。

    2012年6月20日 3:45
  • Did you consider use sp for restoring? We let our users do that and works fine.
    2012年6月20日 3:52
  • 我明白了,是不是说我要做

    1、分用户1对数据库A有owner权限,用户2对数据库B有owner权限。

    2、同时给msdb中的SqlAgent权限。

    3、用sa写两个存储过程,一个是针对A的还原,一个是针对B数据库的还原。然后分别给用户1和用户2的执行权限。用户1和用户2 可用参数来还原备份文件或者日志,但是两个存储过程针对的是两个不同的数据库。也就不能还原对方的数据库了。

    2012年6月20日 4:06
  • 这样就实现了用户1管理数据库A,可以还原,新建作业,修改等。但是不能对B库有任何操作。反过来用户2只对B库有权限。
    2012年6月20日 4:07
  • You got it finally.
    2012年6月20日 4:11
  • 感谢,我先去试试啊
    2012年6月20日 5:05
  • 我回来了。我在master下建了两个存储过程,一个是备份的一个是还原的。

    if exists(
    select * from sysobjects
    where name='pr_backup_db' and xtype='p'
    )
    begin
    drop proc pr_backup_db
    end

    go

    /*备份数据库*/
    create proc pr_backup_db
    @flag varchar(10) out,
    @backup_db_name varchar(128),
    @filename varchar(1000) --路径+文件名字
    as
    declare @sql nvarchar(4000),@par nvarchar(1000)
    select @par='@filename varchar(1000)'
    select @sql='BACKUP DATABASE '+@backup_db_name+' to disk=@filename with init'
    execute sp_executesql @sql,@par,@filename
    select @flag='ok'
    go


    if exists(
    select * from sysobjects
    where name='fn_GetFilePath' and xtype='fn'
    )
    begin
    drop function fn_GetFilePath
    end
    go

    /*创建函数,得到文件得路径*/
    create function fn_GetFilePath(@filename nvarchar(260))
    returns nvarchar(260) 
    as
    begin
    declare @file_path nvarchar(260)
    declare @filename_reverse nvarchar(260)
    select @filename_reverse=reverse(@filename)
    select @file_path=substring(@filename,1,len(@filename)+1-charindex('\',@filename_reverse))
    return @file_path
    end

    go


    if exists(
    select * from sysobjects
    where name='pr_restore_db' and xtype='p'
    )
    begin
    drop proc pr_restore_db
    end
    go

    create proc pr_restore_db /*恢复数据库*/
    @flag varchar(20) out, /*过程运行的状态标志,是输入参数*/ 
    @restore_db_name nvarchar(128), /*要恢复的数据名字*/
    @filename nvarchar(260) /*备份文件存放的路径+备份文件名字*/
    as
    declare @proc_result tinyint /*返回系统存储过程xp_cmdshell运行结果*/
    declare @loop_time smallint /*循环次数*/
    declare @max_ids smallint /*@tem表的ids列最大数*/
    declare @file_bak_path nvarchar(260) /*原数据库存放路径*/
    declare @flag_file bit /*文件存放标志*/
    declare @master_path nvarchar(260) /*数据库master文件路径*/
    declare @sql nvarchar(4000),@par nvarchar(1000)
    declare @sql_sub nvarchar(4000)
    declare @sql_cmd nvarchar(4000) 
    /*
    判断参数@filename文件格式合法性,以防止用户输入类似d: 或者 c:\a\ 等非法文件名
    参数@filename里面必须有'\'并且不以'\'结尾
    */
    if right(@filename,1)<>'\' and charindex('\',@filename)<>0
    begin 
    select @sql_cmd='dir '+@filename
    EXEC @proc_result = master..xp_cmdshell @sql_cmd,no_output
    IF (@proc_result<>0) /*系统存储过程xp_cmdshell返回代码值:0(成功)或1(失败)*/


    begin
    select @flag='not exist' /*备份文件不存在*/
    return /*退出过程*/
    end
    /*创建临时表,保存由备份集内包含的数据库和日志文件列表组成的结果集*/
    create table #tem(
    LogicalName nvarchar(128), /*文件的逻辑名称*/
    PhysicalName nvarchar(260) , /*文件的物理名称或操作系统名称*/
    Type char(1), /*数据文件 (D) 或日志文件 (L)*/
    FileGroupName nvarchar(128), /*包含文件的文件组名称*/
    [Size] numeric(20,0), /*当前大小(以字节为单位)*/
    [MaxSize] numeric(20,0) /*允许的最大大小(以字节为单位)*/
    )
    /*
    创建表变量,表结构与临时表基本一样
    就是多了两列,
    列ids(自增编号列),
    列file_path,存放文件的路径
    */
    declare @tem table( 
    ids smallint identity, /*自增编号列*/
    LogicalName nvarchar(128), 
    PhysicalName nvarchar(260), 
    File_path nvarchar(260), 
    Type char(1), 
    FileGroupName nvarchar(128)
    )
    insert into #tem 
    execute('restore filelistonly from disk='''+@filename+'''')
    /*将临时表导入表变量中,并且计算出相应得路径*/
    insert into @tem(LogicalName,PhysicalName,File_path,Type,FileGroupName)
    select LogicalName,PhysicalName,dbo.fn_GetFilePath(PhysicalName),Type,FileGroupName
    from #tem
    if @@rowcount>0 
    begin
    drop table #tem
    end
    select @loop_time=1
    select @max_ids=max(ids) /*@tem表的ids列最大数*/
    from @tem
    while @loop_time<=@max_ids
    begin
    select @file_bak_path=file_path 
    from @tem where ids=@loop_time
    select @sql_cmd='dir '+@file_bak_path
    EXEC @proc_result = master..xp_cmdshell @sql_cmd,no_output
    /*系统存储过程xp_cmdshell返回代码值:0(成功)或1(失败)*/
    IF (@proc_result<>0) 
    select @loop_time=@loop_time+1 
    else
    BREAK /*没有找到备份前数据文件原有存放路径,退出循环*/
    end
    select @master_path=''
    if @loop_time>@max_ids 
    select @flag_file=1 /*备份前数据文件原有存放路径存在*/
    else
    begin
    select @flag_file=0 /*备份前数据文件原有存放路径不存在*/
    select @master_path=dbo.fn_GetFilePath(filename)
    from master..sysdatabases where name='master'
    end
    select @sql_sub=''
    /*type='d'是数据文件,type='l'是日志文件 */ 
    /*@flag_file=1时新的数据库文件还是存放在原来路径,否则存放路径和master数据库路径一样*/
    select @sql_sub=@sql_sub+'move '''+LogicalName+''' to '''
    +case type 
    when 'd' then case @flag_file 
    when 1 then File_path 
    else @master_path 
    end 
    when 'l' then case @flag_file
    when 1 then File_path 
    else @master_path 
    end 
    end
    +case type
    when 'd' then @restore_db_name+'_'+LogicalName+'_data.mdf'',' 
    when 'l' then @restore_db_name+'_'+LogicalName+'_log.ldf'',' 
    end
    from @tem
    select @sql='RESTORE DATABASE @db_name FROM DISK=@filename with '
    select @sql=@sql+@sql_sub+'replace'
    select @par='@db_name nvarchar(128),@filename nvarchar(260)'
    print @sql
    execute sp_executesql @sql,@par,@db_name=@restore_db_name,@filename=@filename
    select @flag='ok' /*操作成功*/
    end
    else
    begin
    SELECT @flag='file type error' /*参数@filename输入格式错误*/
    end

    分别给予这两个存储过程用户1的执行权限,现在备份已经没有问题了。但是还原的时候会提示

    消息 15153,级别 16,状态 1,过程 xp_cmdshell,第 1 行
    xp_cmdshell 代理帐户信息无法检索或无效。请验证 '##xp_cmdshell_proxy_account##' 凭据存在并且包含有效的信息。
    消息 8114,级别 16,状态 3,过程 xp_cmdshell,第 1 行
    从数据类型 int 转换为 tinyint 时出错。
    消息 262,级别 14,状态 1,第 1 行
    在数据库 'master' 中拒绝了 CREATE DATABASE 权限。
    消息 3013,级别 16,状态 1,第 1 行
    RESTORE FILELIST 正在异常终止。

    (0 行受影响)

    (0 行受影响)

    能帮忙看看么

    2012年6月20日 9:41
  • You have to create proxy account for xp_cmdshell, check 'sp_xp_cmdshell_proxy_account (Transact-SQL)' in books online.
    2012年6月20日 14:10
  • EXEC sp_xp_cmdshell_proxy_account 'administrator','*****'

    xp_cmdshell已经搞定了。但是还是提示没有创建权限。又回到原点了,应该必须给dbcreator权限。

    消息 262,级别 14,状态 1,第 1 行
    在数据库 'master' 中拒绝了 CREATE DATABASE 权限。
    消息 3013,级别 16,状态 1,第 1 行
    RESTORE FILELIST 正在异常终止。

    (0 行受影响)

    (0 行受影响)

    2012年6月21日 0:58
  • 消息 262,级别 14,状态 1,第 1 行
    在数据库 'master' 中拒绝了 CREATE DATABASE 权限。
    消息 3013,级别 16,状态 1,第 1 行
    RESTORE FILELIST 正在异常终止。

    (0 行受影响)

    (0 行受影响)
    RESTORE DATABASE @db_name FROM DISK=@filename with replace
    已为数据库 'NoahOA',文件 'NoahOA' (位于文件 1 上)处理了 320 页。
    已为数据库 'NoahOA',文件 'NoahOA_log' (位于文件 1 上)处理了 1 页。
    RESTORE DATABASE 成功处理了 321 页,花费 0.142 秒(17.660 MB/秒)。

    (1 行受影响)

    还原的动作应该还是执行了的,但是为什么会报没有creato database权限,有没有什么影响。

    2012年6月21日 1:23
  • Should add 'execute as owner' in sp like:

    create procedure sp_name

    with execute as owner

    as

    ...

    2012年6月21日 2:55
  • 存储过程的执行权限我已经给了,如果不给会提示

    消息 229,级别 14,状态 5,过程 pr_restore_db,第 1 行
    拒绝了对对象 'pr_restore_db' (数据库 'master',架构 'dbo')的 EXECUTE 权限。

    (1 行受影响)

    给完了存储过程的执行权限,再执行的时候提示

    消息 262,级别 14,状态 1,第 1 行
    在数据库 'master' 中拒绝了 CREATE DATABASE 权限。
    消息 3013,级别 16,状态 1,第 1 行
    RESTORE FILELIST 正在异常终止。

    (0 行受影响)

    (0 行受影响)
    RESTORE DATABASE @db_name FROM DISK=@filename with replace
    已为数据库 'NoahOA',文件 'NoahOA' (位于文件 1 上)处理了 320 页。
    已为数据库 'NoahOA',文件 'NoahOA_log' (位于文件 1 上)处理了 1 页。
    RESTORE DATABASE 成功处理了 321 页,花费 0.142 秒(17.660 MB/秒)。

    (1 行受影响)

    应该是已经执行了,但是有个拒绝了create database

    2012年6月21日 3:36
  • 终于搞定了,哈哈

    执行身份给dbo,数据库拥有者也是dbo。用户1有owner权限。搞定了

    2012年6月21日 6:50