February 23, 2010 at 10:41 am
Greetings.
Goals we are trying to achieve:
1. Place most of our users in a single block, permissions-wise (with fine-tuning of activity access controlled within applications.) (A smaller percent of privileged users would be handled separately.)
2. Prevent access to SQL Server outside of designated applications (i.e., not via Access, etc.)
3. Be able to track/audit individual user actions against the data.
Ways we could do this:
A. Place the large block of users in a Windows domain group, which is also set up as a login and user in SQL Server, associated with a database role defining the permissions. This makes #3 simple, but it isn't so good at #2 (except that all access is by stored procedures anyway, there is not permitted direct table access.)
B. Use individual logins in SQL Server (either matching the user Windows domain accounts, or SQL Server logins), associated with a database role. This accomplishes the above goals but seems like a lot of management and maintenace.
C. Use a special Windows account, known only to the application, under which the application accesses SQL Server (impersonation), again associated with a database role. This is good at #2 but hides the real user, so #3 doesn't seem possible.
Is any of the above (or some other) approach most recommended? Which approach do others of you use?
Thanks,
Randy
February 23, 2010 at 10:58 am
Not bad ideas altogether. However:
C. This sounds like security through obscurity, keeping the username secret is NOT security, it also can't reallty be kept secret.
2. Any user who has access to the database can access any object that their user permissions to. It is not application based.
In my view, another solution (better is open to debate).
1. You application uses a single login, username is not important. This user has permissions to sprocs, but in general should not need other rights, like it should not be able to query tables directly.
2. Use either SQL Audit or update sproc logs activity. However, logging EVERY data action is generally overkill and trades performance for... Nothing.. Chose what is important and log that. Chose what is sort of important and log that too. Logging has many "flavors", change date of record up to tracking of all changed fields in a given record.
3. Either handle role based security in your application, or use AD roles and check them in SQL.
CEWII
February 23, 2010 at 3:20 pm
Thanks for your response.
As per your comments, "2. Any user who has access ..." - Agreed. However, if users don't access SQL Server directly, but only via logins/accounts known to the applications, rather than users' own domain accounts, then users have no inherent access to the database. The only way they'd obtain direct access is if they discovered the logins/accounts and passwords used internally by the applications. That's really what C is about.
Audit trail activity would only be for data tables in which there is some concern for tracking all changes made and by whom. In those cases, it would literally be the entire row, each time a change is made, along with some audit stamp columns. However, your comments encourage this to truly be limited to only those tables requiring some monitoring. (In those tables, I'm occasionally asked, "who changed this? and when?" so that the users can verify if it's a valid change, or sometimes correcting the data with prior values, etc., possibly weeks or months after the specific column value was changed.)
Thanks!
February 23, 2010 at 3:28 pm
As far as C. there have been a number of posts here previously explicity asking how to limit a login to an application. I make a bit of an assumption that that is what you were looking for.
A user learning the username of your app account should not be considered a problem, the username is not protected.. Password though, big diff..
I've built some code to generate triggers that track field level changes on a record by record basis. Those triggers can be tricky. It is part of a project I'm trying to finish for CodePlex.
CEWII
February 23, 2010 at 4:50 pm
randy.witt (2/23/2010)
2. Prevent access to SQL Server outside of designated applications (i.e., not via Access, etc.)3. Be able to track/audit individual user actions against the data.
There are only two practical and secure ways that I know of to achieve both of these things at the same time:
The first is to have the application use a trusted connection of the User's Windows Login to connect to the server and database. This require the windows user to have Connect permission to the DB that can be granted through a Windows Group, but the users should have no other rights or permissions in the DB. Then the Application invokes an AppRole with a password that only the App knows. This AppRole has the rights to access the App DBs stored procedures, but nothing more. Those Stored Procedures then are authorized to do everything else. Individual Audit/Tracking is achieved through the use of Original_Login() function, from triggers, stored procedures or event notifications.
The second way, is the same as the first, except you use Certificates instead of AppRoles (I have never done this, but I think that it can be made to work the same way.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 24, 2010 at 8:16 am
You can use logon triggers for objectives 2 and 3:
February 24, 2010 at 8:28 am
mphilippopoulos (2/24/2010)
You can use logon triggers for objectives 2 and 3:
Logon triggers won't help audit WHAT data was changed just that a user logged in and some metadata about the logon. Also keep in mind that in the scenario originally put forth, users themselves have access to the DB, how will you pick out user/applications to exclude, what would your criteria be? The application name passed can (and IS easily) manipulated on the client side, so it feels like security through obscurity, so that isn't a good solution..
CEWII
February 24, 2010 at 8:40 am
Elliott W (2/24/2010)
mphilippopoulos (2/24/2010)
You can use logon triggers for objectives 2 and 3:Logon triggers won't help audit WHAT data was changed just that a user logged in and some metadata about the logon. Also keep in mind that in the scenario originally put forth, users themselves have access to the DB, how will you pick out user/applications to exclude, what would your criteria be? The application name passed can (and IS easily) manipulated on the client side, so it feels like security through obscurity, so that isn't a good solution..
CEWII
This code should prevent logins from connecting through SSMS,
except for a small set of logins: login1, login2, login3:
CREATE TRIGGER trg_Audit_Logon_UserConnections
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
DECLARE @data XML;
DECLARE @spid SMALLINT;
DECLARE @loginName VARCHAR(32);
DECLARE @programName VARCHAR(64);
SET @data = EVENTDATA();
SET @loginName = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'VARCHAR(32)');
SET @spid = @data.value('(/EVENT_INSTANCE/SPID)[1]', 'SMALLINT');
SELECT @programName = [program_name]
FROM sys.dm_exec_sessions
WHERE session_id = @spid;
IF @programName LIKE ('Microsoft SQL Server Management Studio%') AND @loginName NOT IN ('login1','login2','login3')
ROLLBACK;
END;
GO
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 24, 2010 at 10:30 am
You still haven't addressed the fundamental problem.. So we keep SSMS out, that is nice, but "programname" is set at the client, there are no controls on that side. and finding out what programname to send isn't all that hard in addition to being security through obscurity.
Don't get me wrong, logging the login is great. But trying to use programname as a reliable/secure method to restrict access is not a good answer and can be easily defeated. I would hate to have someone implement this method thinking that they have truly locked out users they wanted to only to realize later they really didn't..
CEWII
February 24, 2010 at 10:38 am
Elliott W (2/24/2010)
You still haven't addressed the fundamental problem.. So we keep SSMS out, that is nice, but "programname" is set at the client, there are no controls on that side. and finding out what programname to send isn't all that hard in addition to being security through obscurity.Don't get me wrong, logging the login is great. But trying to use programname as a reliable/secure method to restrict access is not a good answer and can be easily defeated. I would hate to have someone implement this method thinking that they have truly locked out users they wanted to only to realize later they really didn't..
CEWII
If the objective is to keep users from connecting through SQL client tools, such as SSMS, I don't see how the logon trigger is not a secure solution.
Can you elaborate what you mean by ""programname" is set at the client" and "trying to use programname as a reliable/secure method to restrict access is not a good answer and can be easily defeated"?
Again, in the context of preventing a user from connecting through SQL client tools...
How could a user fudge that??
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 24, 2010 at 11:23 am
The MS provided tools are not the only tools and blocking them alone not really secure. Basically anything a user can do from SSMS can be done from any tool capable of sending SQL commands to the server, or any application you write.
Perfect easy example, create a file called 1.udl and put this text into it:
[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=localhost;Application Name=ThisIsTheApplicationNameISent
Use that udl in basically any way to access the SQL server, the program name is sysprocesses will read: ThisIsTheApplicationNameISent
I did a really simple test using DTS becuase I knew I could get it to work..
Adding application name to the connection string is all that is needed.
I have just successfully fudged the application and am now sending arbitrary commands to the server while having bypassed the logon trigger restriction. All you really need to know is the allowed values which are available while the system is running, or in code, or by word of mouth. In other words, not secure.
As an additional issue you probably would need to code this as a whitelist as opposed to a blacklist for apps, the whitelist would only allow certain apps through and exclude all others, this sort-of handles new programs that you haven't encountered before, but still a poor solution.
See where I'm going with this.. If I can arbitrarily send whatever I want with no restrictions it can't be depended on, like ip address, the user doesn't get to set that on the connection it is set by their machine, and the username/password combo are verified together, restrictions and checks..
CEWII
February 25, 2010 at 11:20 am
Elliott W (2/24/2010)
The MS provided tools are not the only tools and blocking them alone not really secure. Basically anything a user can do from SSMS can be done from any tool capable of sending SQL commands to the server, or any application you write.Perfect easy example, create a file called 1.udl and put this text into it:
[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=localhost;Application Name=ThisIsTheApplicationNameISent
Use that udl in basically any way to access the SQL server, the program name is sysprocesses will read: ThisIsTheApplicationNameISent
I did a really simple test using DTS becuase I knew I could get it to work..
Adding application name to the connection string is all that is needed.
I have just successfully fudged the application and am now sending arbitrary commands to the server while having bypassed the logon trigger restriction. All you really need to know is the allowed values which are available while the system is running, or in code, or by word of mouth. In other words, not secure.
As an additional issue you probably would need to code this as a whitelist as opposed to a blacklist for apps, the whitelist would only allow certain apps through and exclude all others, this sort-of handles new programs that you haven't encountered before, but still a poor solution.
See where I'm going with this.. If I can arbitrarily send whatever I want with no restrictions it can't be depended on, like ip address, the user doesn't get to set that on the connection it is set by their machine, and the username/password combo are verified together, restrictions and checks..
CEWII
Thank you for the detailed response, it's appreciated. I was considering using logon triggers in the way I have described, and it is good being aware of the pitfalls.
BTW, apologies to the OP for highjacking this thread...;-)
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 25, 2010 at 11:32 am
You are welcome. Don't get me wrong logon triggers are great. But understanding their limits is important.. They do a great job for tracking who and when a login occured and you can get other information but program/application name just isn't one.. Good luck.
CEWII
February 26, 2010 at 8:54 am
Thanks to all who responded. This suggestion, from RBarry, addresses all of our concerns and performs well upon testing for us:
The first is to have the application use a trusted connection of the User's Windows Login to connect to the server and database. This require the windows user to have Connect permission to the DB that can be granted through a Windows Group, but the users should have no other rights or permissions in the DB. Then the Application invokes an AppRole with a password that only the App knows. This AppRole has the rights to access the App DBs stored procedures, but nothing more. Those Stored Procedures then are authorized to do everything else. Individual Audit/Tracking is achieved through the use of Original_Login() function, from triggers, stored procedures or event notifications.
Best wishes,
Randy
February 26, 2010 at 9:01 am
I have to admit I like that method.
CEWII
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply