Vulnerability with Temp Tables & AD users

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Vulnerability with Temp Tables & AD users

Author

Topic

OaktreeDave
Starting Member

2 Posts

Posted - 2010-03-02 : 12:37:45

When you log into Report Manager in 2008 SSRS, it recognizes your Active Directory name, which allows developers to set up user-specific security levels. However, if security is based on an AD *group*, there seems to be a vulernability: If two members of that group run the same report at the same time, and that report uses stored procedures to create temp tables, the two users *may* inadvertently wind up using the same instance of that temporary table. I have found this to be the case, because the report in question returned specific user-related information. This is even true if the temp table deletes itself before recreating itself. Another thing I have noticed: Although it is claimed that the #tablename is appended with a unique code to keep it unique for each user, it seems to consistently append the same identical unique information each time it runs. Has anyone else noticed this, and do you have an idea on why this happens? I tried creating my own unique #tablenames in the stored proc using EXEC sp_executesql @... but I was getting unepxected errors, so I just decided to explicitly enter everyone in the group into Report Manager's security for this report. It seems like there should be a better way to do this. Also, will it happen again, in spite of the explicitly named users?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-03-02 : 15:15:49

I don't believe that. There is some other error in the code you're using.

If you use ##temp, then yes, they can all access same instance. With #temp, each is unique and gets a seperate record in sys.objects.

Let's see the SP

OaktreeDave
Starting Member

2 Posts

Posted - 2010-03-02 : 16:39:57

Thanks for the interest. Here it is (I had to filter out sensitive info...) It reads an SQL view of resources by region and job function for a matrix report, and bolds out certain resources based on the fontweight assigned in the view it calls.

To do this, I use an SQL cursor to store an initial query of the Regions, and 16 temp tables as follows:
1 copy of the Resource view
13 Resource function tables
1 Region table
1 Report table

Here is the SP:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_resourcesbyFunction]
AS
BEGIN
SET NOCOUNT ON;

--1- Count # of names for each function in a region into a cursor
DECLARE RowCounts CURSOR FOR
select e.RowDisplayOrder, e.Region, MAX(e.Counted) from
(SELECT r.RowDisplayOrder, r.Region, r.ColumnDisplayOrder, COUNT(*) as Counted
from v_ResourcesByFunction r
GROUP BY r.RowDisplayOrder, r.Region, r.ColumnDisplayOrder) e
GROUP BY e.RowDisplayOrder, e.Region
ORDER BY e.RowDisplayOrder, e.Region

--2- Create a temp #Report table
IF OBJECT_ID('tempdb.dbo.#Report') IS NOT NULL DROP TABLE #Report

CREATE TABLE #Report(RowDisplayOrder int, Region varchar(50),
.
.
.
ID int IDENTITY)

OPEN RowCounts
DECLARE @RowDisplayOrder int, @Region varchar(50), @MaxRows int, @Processing int
FETCH NEXT FROM RowCounts INTO @RowDisplayOrder, @Region, @MaxRows

--3- Create Temp #Resource table so we don't need to run the vewi constantly:
IF OBJECT_ID('tempdb.dbo.#Resource') IS NOT NULL DROP TABLE #Resource

CREATE TABLE #Resource (RowDisplayOrder int, Region varchar(50), ColumnDisplayOrder int,
Function varchar(50), ResourceName varchar(50),
FontWeight varchar(10), ID int IDENTITY)
INSERT INTO #Resource
--This section translates FontWeight after its initial setting (1-4) is used to sort this select...
--(Heavy font appear at the top, etc.)
SELECT RowDisplayOrder, Region, ColumnDisplayOrder, Function, ResourceName,
FontWeight
FROM v_ResourcesByFunction
GROUP BY RowDisplayOrder, Region, ColumnDisplayOrder, Function, FontWeight, ResourceName
ORDER BY RowDisplayOrder, ColumnDisplayOrder, FontWeight, ResourceName

--Translate FontWeight into the actual font weights...
Update #Resource
Set FontWeight = CASE FontWeight
WHEN '1' then 'Heavy'
WHEN '2' then 'ExtraBold'
WHEN '3' then 'Bold'
WHEN '4' then 'SemiBold'
ELSE 'Default' END

--4- Populate a #Region temp table:
WHILE @@FETCH_STATUS = 0
BEGIN
--print 'Refreshing Resource temp tables for Region ' + @Region
--4.a- Create copy of Resources for this region function, with Identity
IF OBJECT_ID('tempdb.dbo.#e1') IS NOT NULL DROP table #e1
.
.
.
IF OBJECT_ID('tempdb.dbo.#e14') IS NOT NULL DROP table #e14

CREATE TABLE #e1 (ResourceName varchar(50), FontWeight varchar(10), ID int IDENTITY)
.
.
.
CREATE TABLE #e14 (ResourceName varchar(50), FontWeight varchar(10), ID int IDENTITY)

INSERT INTO #e1 SELECT ResourceName, FontWeight from #Resource where RowDisplayOrder = @RowDisplayOrder and Function = '...' and ResourceName > '' ORDER BY ID
.
.
.
INSERT INTO #e14 SELECT ResourceName, FontWeight from #Resource where RowDisplayOrder = @RowDisplayOrder and JobFunction not in (...) and ResourceName > '' ORDER BY ID

--4.b- Drop & Create a temp #Region table (or table-valued parameter) for the region, with 12 functions
IF OBJECT_ID('tempdb.dbo.#Region') IS NOT NULL DROP TABLE #Region

CREATE TABLE #Region (RowDisplayOrder int, Region varchar(50),
ID int IDENTITY)

--4.c- Insert into #Region the max number of rows required, adding identity to each row, with blanks for the names.
DECLARE @CurrentRow int
SET @CurrentRow = 1

WHILE @MaxRows >= @CurrentRow
BEGIN
INSERT INTO #Region
SELECT @RowDisplayOrder , @Region ,
...
SET @CurrentRow = @CurrentRow + 1
END

--4.d- Update #Region with each name for each function using 12 cursors
UPDATE #Region SET ... = right(f1.ResourceName, FontWeight = f1.FontWeight from #e1 f1 join #Region r1 on r1.id=f1.id and f1.ResourceName > ''
.
.
.
.
UPDATE #Region SET ... = right(f1.ResourceName, FontWeight = f1.FontWeight from #e1 f1 join #Region r1 on r1.id=f1.id and f1.ResourceName > ''

--4.e- Insert #Region rows into #Report table
INSERT INTO #Report

SELECT RowDisplayOrder, Region,
.
.
.
FROM #Region

--5- Continue step 4 for each remaining region.
FETCH NEXT FROM RowCounts INTO @RowDisplayOrder, @Region, @MaxRows
END

CLOSE RowCounts
DEALLOCATE RowCounts
IF OBJECT_ID('tempdb.dbo.#Resource') IS NOT NULL DROP TABLE #Resource
IF OBJECT_ID('tempdb.dbo.#e1') IS NOT NULL DROP table #e1

.
.
.
IF OBJECT_ID('tempdb.dbo.#e14') IS NOT NULL DROP table #e14

IF OBJECT_ID('tempdb.dbo.#Region') IS NOT NULL DROP TABLE #Region

--6- render #Report contents in in SSRS or Excel
select * from #Report order by ID

END

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-03-02 : 21:25:01

there's no paramaters. everyone should get the same result. this could be cleaned up a lot too

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources