May 17, 2013 at 6:54 am
We have a production OLTP database and a copy of that database to be used for reporting, since this is a internal application that all users access, all users have access to both databases. I am having a problem that certain users are mining the data using Crystal Reports and Office Products and causing extensive blocking that is in effect halting the use of the application. Marketing attempts to tell the reporting users to use the reporting database have been ineffective. Is there a way I can block access to the prod database from all applications except SSMS and the expected application?
Thank you,
May 17, 2013 at 7:30 am
I would suggest to use Roles in the Database and create a Role for reporting purpose and deny that role access to OLTP DB.
Otherwise you can create a trigger on LOGON and deny access to programs in the trigger code.
May 17, 2013 at 7:45 am
I suggest changing the security model of the application so that end users do not have direct access to the database.
May 17, 2013 at 7:48 am
Michael Valentine Jones (5/17/2013)
I suggest changing the security model of the application so that end users do not have direct access to the database.
+100
or, leave access to database but deny direct access to all tables and views in your database to all non-application logins (creating specific role should help).
Allow data access only via "published" stored procedures.
May 17, 2013 at 8:23 am
unfortunately, it is a purchased piece of software, and we are at the mercy of the vendor.
May 17, 2013 at 9:01 am
MichaelDep the problem with a logon trigger is it hits them with a hammer without regard to which database, so if both the OLTP and the reporting database is on the same server, the logon trigger would not be an option.
if they are on different servers, then you could filter on application name and prevent anything like EXCEL/Access/Crystal from connecting at all.
I hate the security-after-they-have-access, but sometimes it's required.
maybe an ugly job that runs sp_who2, and anything that is connected to the OLTP database, and also has a hostname like the offending programs gets killed , and run the job every minute or so?
something like this?
--2005/08
CREATE PROCEDURE PR_No_Tools_Touch_The_SandBox
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #Results (
[ID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
[SPID] CHAR (5) NULL,
[INSERTDT] DATETIME NULL DEFAULT(GETDATE()),
[STATUS] VARCHAR(30) NULL,
[LOGIN] VARCHAR(30) NULL,
[HOSTNAME] VARCHAR(30) NULL,
[BLKBY] VARCHAR(30) NULL,
[DBNAME] VARCHAR(30) NULL,
[COMMAND] VARCHAR(30) NULL,
[CPUTIME] INT NULL,
[DISKIO] INT NULL,
[LASTBATCH] VARCHAR(30) NULL,
[PROGRAMNAME] VARCHAR(200) NULL,
[SPIDINT] INT NULL,
[REQUESTID] INT NULL
)
--table exists, insert some data
INSERT INTO #Results(SPID,Status,Login,HostName,BlkBy,DBName,Command,CPUTime,DiskIO,LastBatch,ProgramName,SPIDINT,REQUESTID)
EXEC sp_who2
--kill any spids that have dbName = SandBox and the ProgramName like Access/Excel/Crystal
declare @cmd VARCHAR(50)
declare @spid int
declare c1 cursor for
-------------------------------------------------------------------------------------------
select *
FROM #Results
WHERE (SPID > 50
AND DBNAME = 'SandBox')
AND (ProgramName LIKE '%Excel%'
OR ProgramName LIKE '%Access%'
OR ProgramName LIKE '%Crystal%'
OR ProgramName LIKE '%Report%')
-------------------------------------------------------------------------------------------
open c1
fetch next from c1 into @spid
While @@fetch_status <> -1
begin
print 'Killing spid which is connected to a protected database and also connecting with an offending application'
SET @cmd = 'kill ' + CONVERT(VARCHAR,@spid )
EXEC (@cmd)
fetch next from c1 into @spid
end
close c1
deallocate c1
END
Lowell
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply