So, I have an interesting situation that I need a double-check on. One of my coworkers (who is no longer at the company so I can't ask him) advised some users on a PII security situation that doesn't quite make sense to me.
App team creates global temp table for a job that does "stuff" and has PII in it. They want to prevent all users of the database except for one group from reading this global temp table while the data is being massaged. The table gets dropped in about 5 minutes after it's created.
Unfortunately, everyone with read access to the DB can see this temp table. So my coworker recommended creating a role and sticking this code inside a stored procedure to grant permissions on the temp table to prevent this behavior.
CREATE PROCEDURE CreateTempTable
AS
BEGIN
-- Check if the current user is the system ID
IF SUSER_NAME() = 'SystemID'
BEGIN
-- Create a global temporary table
CREATE TABLE ##TempTable (PIIData VARCHAR(50));
-- Grant permissions on the temporary table to the system role
GRANT SELECT, INSERT, UPDATE, DELETE ON ##TempTable TO [SystemRole];
.... more code here
END
The GRANT statement is failing, so the users came to me to ask for assistance.
My thought is even if the GRANT statement worked, it still wouldn't prevent other users from seeing the data in the table and there is no way to create a temp table, local or global, that can only be accessed by a specific role or windows security group.
Tell me, am I wrong or right on this? Is there a way to secure a temp table to only a specific group of users?
January 18, 2025 at 4:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
The SQL Server documentation says that "global temporary tables are visible to all sessions" and it doesn't seem to identify a work around. That also means that you cannot assign "privs" to such a table.
https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql#temporary-tables
What you could do is make a different schema and limit privs to that within stored procedures, etc, with the understanding that members of the "dbo" and "sa" groups are still going to be able to see it. You might be able to mitigate that a bit by putting the "scratch" table in its own small database.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 20, 2025 at 1:15 pm
Jeff, thank you for verifying my own knowledge. That helps and aligns with what I was trying to tell the app team. They just didn't want to waste all the work they'd already done on this issue.
I find myself wondering if my fellow DBA used AI tools to search for this solution given that last week another DBA on our team was helping me research and also found a misleading AI response regarding this issue.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply