January 29, 2019 at 9:37 am
I apologize for the confusing title, but I think its accurate. I have the following basic scenario. I need to extract data and move it over to another server in a csv file. I want the SQL user that generates the extract to only have access to a very limited dataset. However, creating that limited dataset requires access to several tables we don't want the account generating the extract to. In the past, I've created two users:
January 29, 2019 at 10:23 am
DataAnalyst011 - Tuesday, January 29, 2019 9:37 AMI apologize for the confusing title, but I think its accurate. I have the following basic scenario. I need to extract data and move it over to another server in a csv file. I want the SQL user that generates the extract to only have access to a very limited dataset. However, creating that limited dataset requires access to several tables we don't want the account generating the extract to. In the past, I've created two users:User1 has lots of rights to the sensitive data and populates a set of tables in a new database with the limited dataset.User2 only has rights to the limited dataset and creates the extract.However, I could never figure out a good way for user two to populate the limited dataset over in the different database. I think I'm probably way off in how I've tried to solve this so I wanted to see if anyone might be willing to offer advice or direction. Thanks in advance.
If any of the processes can be done with stored procedures, you could look at using execute as or signing the stored procedures.
Sue
January 29, 2019 at 10:29 am
Here is a link from a blog post here on SSC: http://www.sqlservercentral.com/blogs/sql-quantum-leap/2017/12/30/please-please-please-stop-using-impersonation-execute-as/
This post goes over IMPERSONATION/ EXECUTE AS, it also details ideas on how to use Module Signing to solve problems like yours.
Thanks.
January 29, 2019 at 3:19 pm
Thanks for the replies! I am working through them.
January 30, 2019 at 3:24 pm
This was very helpful. I worked through the article and then worked through the an example here: https://www.sqlskills.com/blogs/jonathan/certificate-signing-stored-procedures-in-multiple-databases/ . I have the basic setup working in my test environment.
One question I have is regarding this part in Jonathan:-- Create a login from the certificate
CREATE LOGIN [SigningLogin]
FROM CERTIFICATE [SigningCertificate];
GO
-- The Login must have Authenticate Sever access
-- per http://msdn.microsoft.com/en-us/library/ms190785.aspx
GRANT AUTHENTICATE SERVER TO [SigningLogin]
GO
-- Create a user in database b for the Login
USE b
GO
CREATE USER [SigningLogin] FROM LOGIN [SigningLogin]
GO
I haven't seen a login created without a password. Is there a security risk surrounding a login like this?
February 1, 2019 at 1:32 pm
DataAnalyst011 - Wednesday, January 30, 2019 3:24 PMThis was very helpful. I worked through the article and then worked through the an example here: https://www.sqlskills.com/blogs/jonathan/certificate-signing-stored-procedures-in-multiple-databases/ . I have the basic setup working in my test environment.One question I have is regarding this part in Jonathan:
-- Create a login from the certificate
CREATE LOGIN [SigningLogin]
FROM CERTIFICATE [SigningCertificate];
GO-- The Login must have Authenticate Sever access
-- per http://msdn.microsoft.com/en-us/library/ms190785.aspx
GRANT AUTHENTICATE SERVER TO [SigningLogin]
GO-- Create a user in database b for the Login
USE b
GO
CREATE USER [SigningLogin] FROM LOGIN [SigningLogin]
GOI haven't seen a login created without a password. Is there a security risk surrounding a login like this?
While I haven't created logins from certificates, I have done so for users. They allow you to impersonate the login when executing a stored procedure (when created with the WITH EXECUTE AS qualifier). I've also used them to break the ownership chain so as to prevent permission inheritance.
The advantage is they can be granted permissions and ownership you would not normally grant a user. As long as the user is executing the stored procedure (assuming they have rights to it), their permissions are elevated only for the scope of the stored procedure.
Also, creating a login based on a certificate allows you to sign modules with them.
It may be possible that a SQL certificate could be cracked, but that's above my pay grade, and likely a topic for another forum.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply