Yet another question on Application security....
Ok, i have read a few articles regarding application security and it sems to me the Microsoft just doesn't get it.
As others have posted, Vendor packages are not normally controlled by the IT staff... they do what they do. When an application is used for mission critical data, it becomes important to lock down that data as best as it can be under the limits of that application. Yet what is often lacking in a vendor package is thought about what opening up the database means.
We are using a Vendor package that does not allow for application security to be set. In other words, it uses windows authentication to grant Database access. As many others here have noted, this opens the database to manipulation via any other tool the user has at their disposal.
It seems that the general reply here is that we should get the vendor to change their package to allow for Application level security. Good Luck!
I am not a DBA (must be obvious at this point) and i do not know all the what's up and coming, but generally as a system support person, i must deal within the environment i am given. Here's what I see as the environment I am working under, the problem and the preferred solution.
Application only allows windows authentication.
The users must be allowed full access to the database in order for the application to perform updates as needed.
Security to specific data by company is controlled within the application to determine what a specific user can change.
Granting security to a user ID allows them to access and change data at will through an alternate product (E.G. Enterprise manager, MS Access etc.) without regards to the security built in to the application.
SQL Server knows what ID is requesting data and knows what application is generating the request (I have seen screen prints of the list of who is logged into the database and right alongside the UserID is the Apllication Name).
If SQL server knows these two bits of info, why is it so difficult to allow SQL server to append the Application name onto the user ID and allow access to the DB only via this combination? The permissions can be set up with some combination of UserID/Application name and no special security, no messing with special application passwords/aliases, no pleading with Vendors to make changes and best of all.... satisfying SEC rules for locking down DBs.
Am i missing something? Does anyone know how i can accomplish this simple task without stressing out the DBAs?
Thanks for all your help and creative ideas!
Rob
[2617 byte] By [
robxyz] at [2007-12-25]
We are aware of requests such as this one. This is deceptively simple problem, but the true nature of it makes it extremely complex and unfortunately we don’t have a true solution for it.
I have seem similar solutions to the one you are proposing, and while it is relatively simple to implemented such solutions in SLQ Server, they provide little to no real security. The client application name you mention is under the control of the client, and the server does not attempt to validate it in any way as it is used only for information; purposes and it is trivial to modify the TDS packet that contains this information.
The main pitfall for the problem is determining how the system can verify that the connection was established by the application it expected.
Determining the user’s context is relatively simple as every different user has a different identity, and there are established mechanisms that allow the system to verify that the calling user is himself (login/password, SSAPI, the presence of some token, etc.), while each one of these mechanisms have its own set of rules and limitations, they work to differentiate one principal from another based on the principle that a user will probably not give away his identity to anyone who asks for it (phishing and social engineering attacks come in place here, but that is outside the scope of this conversation).
On the other hand, how can an application do the same? Remember that the application itself may be (I would say should be) running on a remote machine, most likely untrusted machine, therefore the attacker has all the time and resources necessary to analyze the behavior of your application.
Using a password or a token purely generated by the application itself is useless as the application itself is already in the hands of the potential attacker and you should consider that a determined attacker has the time, resources and patience to reverse engineer whatever “secret” the application provides.
One possibility would be to have some external trusted entity to validate the client application and validate the service (SQL Server) and establish that both parties are truly talking to each other besides validating the user’s credentials. As far as I am aware (and from talking with some people here), the facilities we would require for this are not available on Windows or the AD at the time I am writing this. Even in the case we had such facilities available, it would most likely require to rewrite the application in order to take advantage of it.
Another problem is the attacker’s ability to subvert the execution of the application, even without modifying the binary itself a knowledgeable attacker can modify the execution by using a debugger/kernel debugger, making it difficult to guarantee that the application will always behave as expected, even in the case I can validate the connection was originally established by the correct binary.
A third problem would be the attacker can subvert the OS in his own box to “hijack” the TCP connections from the application, injecting arbitrary TDS packets.
We have a new mechanism in SQL Server (purely SQL Server) that, while it is not a silver bullet in any way, it may help you to minimize the permissions you need to grant to individuals and start moving some of those permissions directly to the TSQL base applications: digital signatures.
The main idea is to digitally sign the modules (SPs, functions, etc.), create a user mapped to the signing certificate and then grant the permissions on the resouces needed for the application (i.e. tables, access to other DBs, etc.) to the cert-mapped principal instead to each person who has access to execute the application. For example:
CREATETABLE [dbo].[MyApp_Data]( datachar(10))
go
CREATEPROC [dbo].[MyApp_MainFunction]
AS
TRUNCATETABLE [dbo].[MyApp_Data]
go
CREATEUSER [dummyUser] WITHOUTLOGIN
go
-- EXECUTE on the app main function
-- is the only permission we want to grant
GRANTEXECUTEON [dbo].[MyApp_MainFunction]TO [dummyUser]
go
-- Will fail as dummyUser cannot use [dbo].[MyApp_Data]
EXECUTE('EXEC [dbo].[MyApp_MainFunction]')ASUSER='dummyUser'
-- As we don't want to grant permissions directly to
-- the app resources to the app user, we will use sigantures
CREATECERTIFICATE [myApp_cert] ENCRYPTIONBY PASSWORD='50m3 p@zzw0rD!'
WITH SUBJECT='myApp signing certificate'
go
-- Create the user based on the cert and grant permissions
CREATEUSER [myApp_cert]FORCERTIFICATE [myApp_cert]
go
GRANTALTERON [dbo].[MyApp_Data]TO [myApp_cert]
go
-- Sign the module
ADD SIGNATURETO [dbo].[MyApp_MainFunction]BYCERTIFICATE [myApp_cert]WITH PASSWORD='50m3 p@zzw0rD!'
go
-- Try again to run the app,
EXECUTEASUSER='dummyUser'
go
-- it will succeed
EXEC [dbo].[MyApp_MainFunction]
go
-- But accessing the table directly will not
TRUNCATETABLE [dbo].[MyApp_Data]
go
REVERT
go
While the feature was designed to allow new ISVs to develop applications this way, a DBA can use the same mechanism to sign any existing module.
Besides the BOL documentation, Laurentiu Cristofor has some really good articles regarding this feature in his blog (http://blogs.msdn.com/lcris/), and I have at least one relevant article as well in my own blog (http://blogs.msdn.com/raulga/).
I hope this information is useful. We will appreciate any further questions and feedback.
-Raul Garcia
SDE/T
SQL Server Engine
Raul, thank you for this detailed response. I am not sure i can do you justice but your reply does leave me with some thoughts to comment on.
You made the statement, "I have seem similar solutions to the one you are proposing, and while it is relatively simple to implemented such solutions in SLQ Server, they provide little to no real security. "
I am a bit surprised at that because you also go into some depth explaining that there is quite a sophisticated mechanism in place for validating the user ID. The point you missed is that the proposed solution does not in anyway remove that User ID security level from the current process, but simply appends the application name to the user in order to validate that the user is coming from an acceptable path. Whether someone can fake the application name is less of a concern seeing that the user level security remains in place.
I think the rest of your email seems to focus on application level security which i think we'd like to avoid, and would be able to, if a secondary check were possible via permissions defined as user/application Name.
I think that most businesses feel that the access to their applications and network is secure enough that we are not trying to stop an external hacker from getting at the data as much we are in trying to keep our own employees where they belong. If an internal user is sophisticated enough and determined enough to hack into the data via emulating the application, we probably have larger concerns than permissions is going to handle. We just need to keep Joe Trader in London from seeing data from Tony Trader in the USA via standard tools (MS Access/ Enterprise manager) installed on the standard corporate desktop. They are not able to install their own packages as they are not administrators on their own PCs.
So in summary, we're not trying to stop the world, just want to keep our worker's noses where they belong.
Rob
You are absolutely correct regarding my comment on the existing solutions, I apologize. I should have said “no additional security”, as the regular user authentication and authorization still takes place.
From your description, I think that what you are really looking for is row level security (RLS). This means I can only see a subset of the table rows based on my current context and a predicate (i.e. Joe Trader can see his customers, but not see Tony Trader’s customers).
This is a common request and we are well aware of it and we are actually working on this area. I cannot guarantee this feature will be available in any future release of SQL Server, but I want to point out that we are aware of this particular case.
For now, I would recommend exploring the digital signature solution whenever it is possible, it is more restrictive than RLS in the sense that outside the application the user cannot see any information at all with ad-hoc queries, but it may be useful none the less. Let us know if you have any questions on how to use this new feature.
Thanks a lot for the feedback and comments. We are always glad to hear from our customers and improve our products based on your feedback.
-Raul Garcia
SDE/T
SQL Server Engine
In addition to what Raul said, relying on the application name parameter in the connection string is just security through obscurity.
If all you hope to achieve is to prevent Joe or Tony from accidently manipulating the database directly, what you suggest does satisfy that.
But if you want to prevent Joe or Tony from intentionally manipulating the database directly, relying on this proposed feature will not suffice. Scripts can be written that allow the user to specify the connection string and some applications that may already be installed on their machine may allow connection string manipulation as well.
I've worked with an application that checked for the application name in their stored proc before proceeding, effectively doing what you are suggesting, and it was not a secure method. It may prevent unintentional mistakes, but it will not stop someone intentionally trying to get access. You might as well just ask Joe and Tony to not use Enterprise Manager or MS Access, because if they really want to, what you are suggesting will not stop them.
That said, if an application wanted to do this today, SQL Server already exposes everything they need to implement what you are suggesting. They could deny SELECT/UPDATE to all users, GRANT EXECUTE to a set of stored procedures and check the application name in the stored procedures before doing anything.
So then the question becomes: is there enough value in baking this feature directly into the product? Since it doesn't prevent anyone with malicious intent, I'm skeptical that it mets the bar of belonging in SQL Server.
Jack Richins
SDE Sql Server
All good points. I totally agree that using the application name is not securing the database. It is simply obscuring it with an easily beatable hack. That solution would embarass Microsoft in the developer community...
However I do want to back "robxyz" up on the priority... I totally agree with his assessment. If you are in a client/server environment the brunt of responsibility for keeping hackers out is on the network. Power users are a real risk to the database. Not even from a malicious intent (i.e. maybe the app goes down and they know enough to connect via MS Access and run a stored proc outside of the context of the app which may not achieve the desired result and may make matters worse). I know MANY users that would do these kinds of things. Having experience with apps in a banking environment, there is no way they would allow the use of NT Authentication for this situation. They have hundreds of users that wouldn't have the foggiest idea about how to hack into the database, but many might be proficient with MS Access.
Keep in mind that the User ID component would theoretically keep the hackers out and the Application part is only intended to prevent the use of non authorized tools by authorized users. Therefore it wouldn't have to be as robust. Maybe implement some secure key concept that the application can pass to the database along with the user ID. Or register some identifier in the Application (GUID or something) that is also registered in the database. Maybe it would only be supported for .NET 2.0 apps?
Anyhow, I cast a vote to move this issue up in priority.
Thanks so much for your feedback!
I want to reassure you that here in Microsoft we are really taking our customers’ feedback in consideration. We really appreciate all your feedback and we are actively trying to find solutions for our customers’ problems and concerns.
As you mentioned, you would accept a solution that is not “as robust” as other authorization/authentication mechanisms we already provide, but because SQL Server is a platform where our customers (including other Microsoft products) rely on, we cannot make the same decision on behalf of our customers.
As I already mentioned in one of my previous posts, finding a general and robust solution for this problem is quite challenging, but that doesn’t mean we are giving up. As a step forward we introduced the module signature feature in SQL Server 2005. This feature allows a controlled escalation of privileges by allowing access to the resources only via the application instead of granting direct access to the resources to the application users. These signatures can be applied to both T-SQL based modules and CLR based modules, but the limitation we have with this solution is that the signed modules must reside in the database.
If the available tools are not enough to solve your problem in a satisfactory way, I would like to encourage you to provide us with more details on your particular scenario. We will do our best to help you find a solution based on the current features, but your feedback may also help us to find a general and robust solution that would be appropriate for a subset of similar scenarios.
I hope the information I have posted here will be useful. As always we appreciate your feedback.
-Raul Garcia
SDE/T
SQL Server Engine
This may be answered in another forum... apologies in advance if it is.Here is an application security problem that I see at every dot-net company.
Situation:Web-based application, RDBMS backend, many (100k’s) of application users.
Need:Application-based user logins to the website, limited (a few per application) common logins to the SQL Server, yet have the ability to track application-user (AU) CRUD activities.
In our current environment, all user interaction is via stored procedures.We strictly control the AU security and functionality from the web applications.While only a handful of the users are “internal” with NT accounts, the applications may each have 10k’s of legitimate logins.
If we had a “database cookie” (or some other container of user context) that we could access within T-SQL, we could implement framework-controlled auditing of the CRUD operations.This is different from requiring all developers to explicitly pass in the AU’s context on every SQL call (a development headache), and it eliminates having to make every AU a SQL user (a security nightmare).This “database cookie” would be associated with the database connection, and allow the AU’s login (and any other context information stuffed into the “database cookie”) to be available to the stored procedures / triggers.
Even sweeter would be if .Net had an option to automatically sync the "database cookie" with the browser session variables, so the developers would only need to reference the “database cookie” upon creation / destruction. This may also solve the issue of connection pooling.
When I first saw that SQL 2005 had cookies (for sp_setapprole), this was the functionality I assumed was finally being implemented.However, it appears MS still thinks in terms of every user being an NT user....
You can use the SET CONTEXT_INFO and CONTEXT_INFO() statements to maintain 128 bytes of information at the connection level. It is persisted between batches, as long as the connection remains open. This would get you the cookie you're looking for. If you can fit all your data into 128 bytes, that's great, but you'll need to handle the encode/decode yourself. A better solution would probably be to have a table with a GUID key and columns for any info that you wanted to store. Then generate a GUID per session and store it in the context info as a key. You would need to clean up stale sessions (so many minutes/hours/days/etc. old) at some point as well. If you're opening and closing connections between accesses to the database, then this won't work. Unfortunately, since that is how most apps are written, this may be of little use to you.
