Re: HELP ! Security Problem

From: Kresimir Radosevic (kresimir.radosevic@zg.tel.hr)
Date: 11/30/02 

From: "Kresimir Radosevic" <kresimir.radosevic@zg.tel.hr>
Date: Sat, 30 Nov 2002 11:25:04 +0100

There is a program_name column in master..sysprocesses system table. Also
you can create trace like

---------------START OF SCRIPT

/****************************************************/
/* Created by: SQL Profiler */
/* Date: 30/11/2002 11:17:53 */
/****************************************************/

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc
extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server
has
-- write access to your network share

exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere',
@maxfilesize, NULL
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 12, @on

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
go

---------------END OF SCRIPT

As you can see there is a way to filter trace on application name
(sp_trace_setfilter line). So basically create trace to monitor only Securty
audit :Audit login, select only EventClass, ApplicationName, SID and
StartTime columns and add filters to be used just for your database and to
ignore your application name.

Periodically check the trace output and when you find intruders have a nice
chat with them. 

-- 
Kresimir Radosevic, SQL MVP
I support the Professional Association for SQL Server and its user community
of dedicated professionals.
www.sqlpass.org
"Rem" <remy.filteau@cgi.ca> wrote in message 
news:1b09e01c29652$8a2461c0$89f82ecf@TK2MSFTNGXA01...
> Hi,
>
> Recently, we bought a software runnig with SQL2K.
>
> To lanch the application, we need to entre a
> user/password, good news.  It have also to security
> mecanism inside the application, also good !
>
> But the problem, a user can acces the data by Qyery
> Analyser or others tools to acces the data.  Well quiet
> bad, because they can see all what they want even if in
> the application they're not allowed.
>
> How I can outline this problem?
>
> Thanks for your help !

Relevant Pages

  • SQL Server 2005 - Server side trace script
    ... I am trying to set up a trace that will tracks all the "RPC:Completed" events and give me some of the vital statistics like TextData, StartTime, EndTime, Duration, Reads, Writes, CPU, etc. ... I have cut and paste the script I get at the end. ... declare @TraceID int ... declare @bigintfilter bigint ...
    (microsoft.public.sqlserver.tools)
  • Re: SQL Profiler Scheduling
    ... declare @TraceID int ... declare @bigintfilter bigint ... -- display trace id for future references ... Create a trace interactively then save the script and use the script in ...
    (microsoft.public.sqlserver.tools)
  • Re: Trace question
    ... but is it possible that the 16th column is not a bigint? ... > I'm setting up a trace in Query Analyzer and having an odd problem. ... > and check the filter using: ... > declare @trace_id int ...
    (microsoft.public.sqlserver.programming)
  • Re: I want to monitor users logon by using next trace (below), but trace file is empty!
    ... you have to stop and close that trace. ... > declare @TraceID int ... > declare @bigintfilter bigint ...
    (microsoft.public.sqlserver.server)
  • Re: SQL Server 2005 - Server side trace script
    ... By default Profiler will not have the TextData column checked for the RPC events since it can get that information from the binary data column which is usually more efficient. ... To make things consistent I usually just include the textdata column and exclude the binary when I create the trace. ... The problem I have is that when I open the trace output in Profiler, I don't see the TextData column - instead, I see the BinaryData column. ... declare @TraceID int ...
    (microsoft.public.sqlserver.tools)