SQL Server data change and Security -


in our environ user login sa can change table data.

so write trigger capture changed data change, ip etc this

create trigger [trg_users] on [dbo].[userrights] after insert, update, delete  declare @strip varchar(max)  set @strip=(select dbo.getcurrentip())  if exists (select * inserted) , exists (select * deleted)     --print 'update happened';     insert logger(ipaddress,status)     values (@strip,'update') else if exists (select * inserted)     --print 'insert happened';     insert logger(ipaddress,status)     values (@strip,'insert') else     --print 'delete happened';     insert logger(ipaddress,status)     values (@strip,'delete')   create function [dbo].[getcurrentip] ()   returns varchar(255)     begin      declare @ip_address varchar(255);       select @ip_address = client_net_address      sys.dm_exec_connections      session_id = @@spid;      return @ip_address;     end

but problem user can change data after disabling trigger on specific table. trigger not fire , user can seamlessly change data.

so guide me best way capture data change , log them. no 1 can bypass security. please not tell me disable sa account because looking different approach capture change data. there secure way exist in sql server 2005/2008 if yes please discuss here. thanks

problem sa security checks skipped login (or other login in sysadmin role matter). so, can't revoke privilege sa , there's nothing can on instance level sa cannot bypass.

like others said, don't let login sysadmin unless there's real sysadmin work done. best practice disable sa login altogether.

on cnstructive side, best bet otherwise create sql server audit session , use windows security log target. way you'll @ least know , when stopped audit.


Comments

Post a Comment

Popular posts from this blog

apache - Remove .php and add trailing slash in url using htaccess not loading css -

i have add following code htaccess file removing .php extension , add trailing slash urls. worked fine reduced page loading speed , css code not loading @ all. pages ugly. please help rewriteengine on rewritebase / ## hide .php extension snippet # externally redirect /dir/foo.php /dir/foo rewritecond %{the_request} ^[a-z]{3,}\s([^.]+)\.php [nc] rewriterule ^ %1/ [r,l] # add trailing slash rewritecond %{request_filename} !-f rewritecond %{request_uri} !/$ rewriterule . %{request_uri}/ [l,r=301] # internally forward /dir/foo /dir/foo.php rewritecond %{request_filename} !-d rewritecond %{request_filename}.php -f rewriterule ^(.*?)/?$ $1.php [l] we can rewrite urls using .htaccess files. before writing in .htaccess files need verify kind of server using. can check server adding a <?php phpinfo(); ?> in php page. ensure mod_rewrite module enabled in apache server. can identified checking phpinfo included page. common methods used url rewriting follows ...
Read more

inno setup - TLabel or TNewStaticText - change .Font.Style on Focus like Cursor changes with .Cursor -

is possible change .font.style on focus tlabel or tnewstatictext happens cursor when use .cursor ? there no built-in support track mouse hovering in inno setup @ time. however, intercepting window procedure of controls can track yourself. following example need innocallback library: [setup] appname=my program appversion=1.5 defaultdirname={pf}\my program outputdir=userdocs:inno setup examples output [files] source: "innocallback.dll"; destdir: "{tmp}"; flags: dontcopy [code] #ifdef unicode #define aw "w" #else #define aw "a" #endif const gwl_wndproc = -4; wm_mousemove = $0200; type wparam = uint_ptr; lparam = longint; lresult = longint; twindowproc = function(hwnd: hwnd; umsg: uint; wparam: wparam; lparam: lparam): lresult; function setcapture(hwnd: hwnd): hwnd; external 'setcapture@user32.dll stdcall'; function releasecapture: bool; external 'releasecapture@user32.dll stdcall'; func...
Read more