August 19, 2005 at 1:35 am
Because of security restrictions, we want to disable our user's direct access to tables in DB, and to use Application roles instead (we work in C# with ADO.NET). But we have a problem - it seems that application role don't work with connection pooling. After reopen the connection it shows an network error. Microsoft advises: disable pooling. But this is not acceptable - connections reopen slowly. Any ideas?
August 19, 2005 at 1:54 am
Use stored procs and views to hide the tables.
Ensure the user is not sysadmin or db_owner.
Use revoke and grant to limit permissions via usual roles.
Quite simple really..
You should be in the position of having to open up access as needed rather than lock it down and application roles do have drawbacks, not least passwords have to be used rather than trusted NT auth
August 19, 2005 at 2:32 am
OK, these are simple rules. I apologise, but I'm quite new in SQL Server (I worked with Progress RDBMS, and it hasn't stored procedures and even don't use SQL language).
But requirement is: no other user access to any data in DB (either in tables or views) except via our application. Simple requirement, but I don't think it's simple to realize. I will appreciate any idea!
August 19, 2005 at 3:00 am
Using sps and roles will recuce your surface area, but there is always the possibilty of someone using access or MS Query to access your database.
Now, it is possible to lock the db down so that they cannot see table data and can only execute sps, but they have to know how to do it
If this is not sufficient in itself to tie access to your app only.
Application roles are one way.
Another perhaps is to use the APP_NAME ( ) function at the top of each sp to test for your app and simply exit if it's not.
I don't know of any other way but there are some clever guys out there who may have better ideas
For example, locking down MS Query, GPO etc
August 19, 2005 at 4:32 am
Yes, the idea is to lock the DB down. As I can see there are two approaches:
First - Application roles. But problem with them is that they do not allow connection pooling. The only decision (according to MSDN) is to switch connection pooling off. But this leads to poor performans and is inacceptable.
Second - using stored procedures only, possibly with APP_NAME() checking. Because I didn't use stored procedures before in my work, I have some questions about this approach:
- Do I need a set of stored procs (for create, insert, update, delete and read) for each table in DB? Or maybe one more complex stored proc per table to do all these operations? Or maybe not per table?
- Can I generate SQL text in my application and pass it to stored procedure to execute it dynamically? What about SQL injection? To avoid it in ADO.NET, we use parametrized queries. Can such a queries be used in stored procedures?
Any other suggestions would be appreciate!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply