Hi all,

I'm curious as to what people currently use for their connection to the database. Currently we have a windows form client that connects directly to the database. In a new site IT is getting upset if the username/password are in the config file. We have anticipated this and also allow for integrated security.

They then raised the problem that anyone could connect to the database in something like query analyzer/management studio and delete data and drop tables.

How does everyone else handle discussions like this?

Tim

tmpreston wrote:

Hi all,

I'm curious as to what people currently use for their connection to the database. Currently we have a windows form client that connects directly to the database. In a new site IT is getting upset if the username/password are in the config file. We have anticipated this and also allow for integrated security.

You can also use connection string encryption and decrypt in code. You can manually set the connection string at runtime.

They then raised the problem that anyone could connect to the database in something like query analyzer/management studio and delete data and drop tables. How does everyone else handle discussions like this? Tim

If they don't know what 'role based security' means it will be a tough discussion indeed... It's not different from using stored procs however: if the proc 'pr_DeleteCustomer' is in the set of procs, and I have a client app I can hack out the connection string and connect to the db with that connection string as well, call the pr_DeleteCustomer with a customerID I saw in the program and there you go.

tmpreston wrote:

Hi all,

I'm curious as to what people currently use for their connection to the database. Currently we have a windows form client that connects directly to the database. In a new site IT is getting upset if the username/password are in the config file. We have anticipated this and also allow for integrated security.

They then raised the problem that anyone could connect to the database in something like query analyzer/management studio and delete data and drop tables.

How does everyone else handle discussions like this?

Tim

Wow, there is alot to this topic. I assume from your question you are using a 2-tier Windows forms app that connects directly to the SQL Server. Is this correct?

1. If each use has their own username/password you can prompt the user for this information, it should not be stored in the config.

2. If all users are connecting using the same username/password as Frans said, you can encrypt the connection string.

3. You can expand the app to a three-tier app so only the middle tier is connecting to the SQL Server. This way, the config file can be protected by standard network security so only the middle tier app can access it. Depending on your security system you may need to support authorization in your middle tier.

4. You can use an application role. This allows the application to elevate it's permissions in SQL using a single password (that you encrypt) but allows you to use NT Authentication. In this case you only need to give the users VERY low rights to the database. This way, when they connect from another app they can't do anything. The advantage here is you still have user information on each connection which you can use for auditing or whatever.

But, as I said, this really depends on your architecture.

BOb

Otis wrote:

set of procs, and I have a client app I can hack out the connection string and connect to the db with that connection string as well, call the pr_DeleteCustomer with a customerID I saw in the program and there you go.

Yes, but if authorization is properly enforced in the SP's you wouldn't be able to do anything by running the SP's that you couldn't do in the application. Also, with a DeleteCustomer_ByID SP they would only be able to delete 1 at a time, where with DELETE access to the table they could delete them all in one fell swoop. Of course this is one argument for not using cascase deletes in the database!

BOb

One major way to mitigate this is to "not" have your application ever delete data and only mark records as deleted.

In this way you can only give your users rights to SELECT and UPDATE data and not delete it. Also, they wouldn't be given rights to do DDL.

But, the best mitigation for this is good backup, auditing and alert procedures built into the app and the business processes.

BOb

(edited)

PilotBob wrote:

Otis wrote:

set of procs, and I have a client app I can hack out the connection string and connect to the db with that connection string as well, call the pr_DeleteCustomer with a customerID I saw in the program and there you go.

Yes, but if authorization is properly enforced in the SP's you wouldn't be able to do anything by running the SP's that you couldn't do in the application.

Yeah, but if I can do anything in my app I can do it through the API. That's the point here: some people still believe the myth that with a proc API, you can offer the functionality to an APP and be more secure than when you build the functionality in the app.

Edit: what exactly do you mean with: authorization in the proc? You send to the proc a unique ID you received from an earlier proc call? Otherwise I can hack it out of the app code to call the proc anyway.

Also, with a DeleteCustomer_ByID SP they would only be able to delete 1 at a time, where with DELETE access to the table they could delete them all in one fell swoop. Of course this is one argument for not using cascase deletes in the database!

Though, with a select proc as well, how is this of any concern, when you can write a simple program that executes the stuff for you?

the main point is: some people say 'with a proc API it's more secure', while the 'secure' bit isn't about data-access control, it's about job security, namely the DBA's job.

What's so ironic about all this is that on the other side of the fence, i.e. in the Java world, this never has been a dicussion of any real significant proportion. Apparently having app servers running the show is a reason to drop the argumentation about procs == more secure however on .NET we still have to wade through the blabla vented by scared DBAs who are afraid they'll lose their job because they're no longer needed to write all the procs.

-- Sorry if I sound too defensive, it's just that using procs doesn't make your app more secure, you have to add such a truckload of code to your procs and even then it's not said to be more secure anyway, after all: if there is a flaw in it, no matter how small, it will in the end be the way to break into the data.

So if the goal is to not let people access the data you're protecting, make sure they can't access the data you're protecting, that is: via role based security in the db, eventually via views.

PilotBob wrote:

One major way to mitigate this is to "not" have your application ever delete data and only mark records as deleted.

That's a bad way of doing data-access, as dragging along the data that was 'deleted' will slow things down over time, as the data will make selects slower and memory consumption go up.

Soft deletes are best done by using a trigger do move the deleted data to an archiving db. It keeps your working set to the size of how it really is and also doesn't let you forget any data

In this way you can only give your users rights to SELECT and UPDATE data and not delete it. Also, they wouldn't be given rights to do DDL.

DDL execution rights is mostly only possible by having some sort of admin level anyway, so in general not required.

But, the best mitigation for this is good backup, auditing and alert procedures built into the app and the business processes.

Well, the first rule is to create working sets for apps, if you have one big fat database with all your corp. data and an app that accesses 2 tables of it, perhaps you should replicate the two tables to a separate db which is accessed by the app. This is also a way to mitigate server breaches, one of the more important things to focus on when it comes to security.

PilotBob wrote:

Yes, but if authorization is properly enforced in the SP's you wouldn't be able to do anything by running the SP's that you couldn't do in the application.

Otis wrote:

Yeah, but if I can do anything in my app I can do it through the API. That's the point here: some people still believe the myth that with a proc API, you can offer the functionality to an APP and be more secure than when you build the functionality in the app.

Ok, let me start by saying that I don't think you need to access all your data through SP's but you "can" build a more secure API. But, that is only needed if you are giving your users CRUD rights to the data. Normally you don't do this!!! If you don't give them access there is no issue.

Otis wrote:

Edit: what exactly do you mean with: authorization in the proc? You send to the proc a unique ID you received from an earlier proc call? Otherwise I can hack it out of the app code to call the proc anyway.

Ok, lets say you are using NT Authentication which means you need to grant each user SELECT/UPDATE/DELETE rights to the tables needed for the app.

Also you have a role/permission in your app "CanDeleteCustomers".

So, in your app when a user tries to delete a customer your code checks IsInRole(ROLE_CanDeleteCustomers) and if that is false you throw a SecurityException. That is all well and works fine, as long as the user is using the app.

Now, the user opens up Query Analyzer... since they have DELETE rights to the customer table they can issue a DELETE FROM Customers command and it will work. (Even if using a view as you say, they would still be able to delete all records in the view.)

Ok, now lets assume you want to use a full SP API to the database. So, you don't give the user any rights to the tables and only give them rights to exec your CRUD procs.

Now assume a SP called DeleteCustomer. The SP would include the code to check in the user was in the role "CanDeleteCustomers" before doing it's work.

Your application now will still throw the security exception if the user was not in the role. However, if the user connects to the Db via query analyzer and calls the SP (they have no access to delete from the table or view) the SP is going to return an error if they are not in the role.

Otis wrote:

PilotBob wrote:

Also, with a DeleteCustomer_ByID SP they would only be able to delete 1 at a time, where with DELETE access to the table they could delete them all in one fell swoop. Of course this is one argument for not using cascase deletes in the database!

Though, with a select proc as well, how is this of any concern, when you can write a simple program that executes the stuff for you?

True, but I guess the question here is what is the intent. Many times you are protecting against stupidity rather than malice.

Otis wrote:

the main point is: some people say 'with a proc API it's more secure', while the 'secure' bit isn't about data-access control, it's about job security, namely the DBA's job.

Well, yes an no. As I said, it is more than one piece. If you are going to give you users full CRUD rights to the user outside of the application then you are asking for trouble whether you have an SP API or not.

So, my answer to them is to NOT give the user that level of access when they are outside of the app. See my above message for how I do that. I think this is even MORE secure because if a user connects to the Db they have no RIGHTS granted. Of course, the DBA will argue that this is where having SP's is more secure because now the user that hacked in can only run the SP's. But, I say once a user hacks in your in trouble no matter what!

Otis wrote:

So if the goal is to not let people access the data you're protecting, make sure they can't access the data you're protecting, that is: via role based security in the db, eventually via views.

Yes, well said. I think that's what I said above too!

BOb

PilotBob wrote:

Otis wrote:

Edit: what exactly do you mean with: authorization in the proc? You send to the proc a unique ID you received from an earlier proc call? Otherwise I can hack it out of the app code to call the proc anyway.

Ok, lets say you are using NT Authentication which means you need to grant each user SELECT/UPDATE/DELETE rights to the tables needed for the app.

Also you have a role/permission in your app "CanDeleteCustomers".

So, in your app when a user tries to delete a customer your code checks IsInRole(ROLE_CanDeleteCustomers) and if that is false you throw a SecurityException. That is all well and works fine, as long as the user is using the app.

Now, the user opens up Query Analyzer... since they have DELETE rights to the customer table they can issue a DELETE FROM Customers command and it will work. (Even if using a view as you say, they would still be able to delete all records in the view.)

---

Ok, now lets assume you want to use a full SP API to the database. So, you don't give the user any rights to the tables and only give them rights to exec your CRUD procs.

Now assume a SP called DeleteCustomer. The SP would include the code to check in the user was in the role "CanDeleteCustomers" before doing it's work.

Your application now will still throw the security exception if the user was not in the role. However, if the user connects to the Db via query analyzer and calls the SP (they have no access to delete from the table or view) the SP is going to return an error if they are not in the role.

This offers no additional security unless you assume that the hacker will never gain access via the user who is in the role "CanDeleteCustomers". It's kind of like fortifying 3 sides of a 4-sided building and pretending that you're safer.