3

I am part of a project to convert an old AS400 system to SQL server. The server is used for our payroll processing. Part of the payroll process is to break out the percentage of the biweekly wages to each job we work on. To do so in the AS400 process a csv file was loaded from the P&B admin's to the server in a data access area that only the admin had access to. I need to replicate this in the SQL server environment. I am planning on using SQLCMD to execute an import of the local file into a temporary table. I know it is highly unlikely that anyone would query the data in the temporary table at the time its in use but is there anyway to grant only access to the user that executed the SQLCMD script?

  • 2
    Are you using an actual temp table (one stored in tempdb) or a static table that will hold the temporarily like a stage table? – Chris Lumnah Jan 19 '18 at 15:34
  • I was planning on using an actual temp table (#temptable) since the values are not going to be needed after the aggregation process and I think it would be more secure – Steve Salowitz Jan 19 '18 at 15:38
5

A #temporary table is only visible from the session that created it. So even the same user in another session would not be able to see it. If you load a temporary table in SQLCMD it would be visible to that instance of SQLCMD. So a subsequent command could load the data into a permanent table, or open a cursor over it and call a stored procedure for each row, etc.

Alternatively you could provision a private schema for the user and load the data into a permanent table there. Then no other user (other than a database owner or other privileged user) would be able to read it. The user could then move the data into another schema later.

  • Sysadmins can see it anyway, regardless of a #temp table or not. – Anti-weakpasswords Jan 24 '18 at 4:30
2

Is there anyway to grant [#temp table] only access to the user that executed the SQLCMD script?

Unfortunately, if your threat model includes database administrators, the answer is NO.

Your only answer valid choice with that threat model is to encrypt the data client side. With SQLCMD, your best and simplest option is AlwaysEncrypted using Microsoft® Command Line Utilities 13.1 for SQL Server or later only.

See Paul White's article on viewing another session's temporary table, which boils down to

Find the #temp table

SELECT  *
FROM    tempdb.sys.tables T
WHERE   T.name LIKE N'#Private[_]%';

Find the pages

SELECT  T.name,
        T.[object_id],
        AU.type_desc,
        AU.first_page,
        AU.data_pages,
        P.[rows]
FROM    tempdb.sys.tables T
JOIN    tempdb.sys.partitions P
        ON  P.[object_id] = T.[object_id]
JOIN    tempdb.sys.system_internals_allocation_units AU
        ON  (AU.type_desc = N'IN_ROW_DATA' AND AU.container_id = P.partition_id)
        OR  (AU.type_desc = N'ROW_OVERFLOW_DATA' AND AU.container_id = P.partition_id)
        OR  (AU.type_desc = N'LOB_DATA' AND AU.container_id = P.hobt_id)
WHERE   T.name LIKE N'#Private%';

Read the supposedly private data

DBCC TRACEON (3604);
DBCC PAGE (tempdb, 1, 173, 3) WITH TABLERESULTS;

Then use the m_nextPage field to read the next page (it's in file_id : page_id) decimal format.

  • 1
    This is a nice piece of information on how to find a(nothers) temporary table, but does not really answer OP question in depth.You might benefit from writing a new question "à la": Can I find somebody else's temporary tables and then self answer it with your description (and some). Or you might want to consider fleshing out the encryption part a bit more instead of referencing only links. Good luck. – hot2use Jan 24 '18 at 7:02
  • @hot2use - I added a starting statement to more directly and clearly answer the OP's question. The details is to provide information on how that threat can be done, particularly as this answer appears to directly contradict another answer, and uses undocumented commands which are not immediately obvious or well known. This information may be required to convince management or finance that no, their payroll data is not, in fact, private, since any DBA can look up the CEO's salary, their boss's salary, etc. – Anti-weakpasswords Jan 25 '18 at 3:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.