SQL Server 2005/2008 安全监控和管理（待续）

╰+哭是因爲堅強的太久メ 2022-07-12 10:09 312阅读 0赞

\-- 查看当前db的登陆

select \* from sys.sql\_logins

\-- 审核登陆数据库的用户

sql server managerment studio中，右键点开服务器的属性，在安全性页签中， 选中审核“成功和失败的登陆”，所有登陆都会在..MSSQL/Log/ERRORLOG中记录一条记录。

如果勾选“启用C2审核跟踪”，将会在..MSSQL/Log/目录中，不断的增加audittrace201000502112519\_15.trc文件，记录每一个对语句和对象的所有访问的审核. 可使用SQL Server Profiler打开查看。(需手动删除该类文件，否则磁盘必满。)

\-- SQL Server Profiler

sys.traces

\--------------------------------------------------------------------------------

仅编写存储过程不足以保证应用程序的安全，还应当考虑以下潜在的安全漏洞。

1.为您希望其能够访问数据的数据库角色授予对存储过程的 EXECUTE权限。

executeto你的用户名)\--取消"元数据<FONT color="\#339966" face=""">"的任何权限

select,insert, delete,  update,alter from public;

\--创建数据库\--创建表和存储过程等等对象

create,整个实例中唯一use mydb

exec,相当于给该数据库创建了一个映射用户。同login"登陆名<FONT color="green" face=""">"。如有多个数据库，要执行多次grant,只有执行存储过程的权限

sp\_addsrvrolemember'mydbuser','sysadmin';\--添加服务角色,默认就有了。添加反而出错。

sp\_addrolemember'db\_owner','mydbuser'; \--添加数据库角色,默认就有了。添加反而出错。

SQL Server 2005开始，引入了新的触发器类型。详细帮助文档"create trigger"。

本文描述通过触发器，限制某个数据库用户(比如 sa这种高级别的用户)，只能从指定的IP登陆，这可以做为防止非法连接数据库的最后防线。只有在建立数据库连接的最后一步（账号密码验证成功后）,才会激活这个触发器。

createtrigger tr\_logincheckon  all server for logon

if eventdata().value('(/EVENT\_INSTANCE/LoginName)\[1\]','sysname') = 'sa'

andeventdata().value('(/EVENT\_INSTANCE/ClientHost)\[1\]','varchar(15)') <> '192.168.0.23'

rollbacktran   \--会话退出,即:无法连接,登陆失败

下面这个示例，是可以记录sa登陆的信息，也可以根据需要，监控所有db用户的登陆信息

\-- create table master.dbo.t\_log\_loginlog(loginame varchar(30),ipaddress varchar(40), spid int, hostname varchar(30), logtime datetime)

alter  trigger tr\_log\_loginon  all server WITH EXECUTEAS  'sa'

forlogon

declare@loginame  varchar(30),

@ipaddress varchar(30),

@spid      int,

@hostname  varchar(30);

select@loginame = eventdata().value('(/EVENT\_INSTANCE/LoginName)\[1\]','sysname'),

@ipaddress = eventdata().value('(/EVENT\_INSTANCE/ClientHost)\[1\]','sysname'),

@spid = eventdata().value('(/EVENT\_INSTANCE/SPID)\[1\]','int');

if @loginame ='sa'  or @loginame = 'jav'

begin

select @hostname =  \[host\_name\] fromsys.dm\_exec\_sessionswhere session\_id =  @spid;

insert into master.dbo.t\_log\_loginlog(loginame,ipaddress,spid,hostname,logtime)

values( @loginame,@ipaddress,@spid,@hostname,getdate() );

end

\-- sql server 2005/2008 用户授权

1. 如果要在一个db下面，各个用户使用自已的对象，最好的方法是建多个Schema(中文意思为架构,或模式), 每个用户为自已对应架构的“所有者”，可以在"服务器 > 数据库 > 你的业务DB > 安全性 > 架构"中新建.

2. 如果a用户,需要访问b用户的架构(Schema)对象，可以用grant select to a;  如果要执行b用户架构下的存储过程，或函数, 触发器等，则除了要 grant execute to a之外，在b用户的创建过程ddl语句中，还要指定: WITH EXECUTE AS N'用户a'， 举一个实际应用的例子。

\-- 以ddl语句dbo\_sp\_helptext的执行权限给某特定的用户就可以了，它就可以查看别的用户的对象源码，但无法修改或删除别的用户的对象createas

executesp\_helptext@objname;

create WITHEXECUTEASN'dbo'

begin

if(charindex('&',@cmd)> 0)

print'error:不允许执行多条命令;

end

if (charindex('dir',rtrim(ltrim(@cmd))) = 1)

begin

execute @i\_result=master..xp\_cmdshell@cmd,@no\_output;

end

begin

return\-1;

end

3. 如果A用户,需要创建自已的 job作业, 同时还需要查看其它用户的SQL Server Agent job信息 (SQL Server 代理 作业)，不能修改和删除别人的作业，则可以给它赋 msdb数据库的 "SQLAgentReaderRole"权限.  具体参见“联机帮助”

-- 导入用户数据库中的 用户及角色权限
    select 'CREATE USER [' + a.name+'] FOR LOGIN [' + b.name + ']'
      from  sys.database_principals a inner join sys.server_principals b
        on a.sid=b.sid
    
    select 'exec sp_addrolemember ''' + (select top 1 name from sys.database_principals where principal_id = s.role_principal_id)
     + ''', ''' + (select top 1 name from sys.database_principals where principal_id = s.member_principal_id) + ''';'
    from sys.database_role_members s
     where member_principal_id > 4;