Link to home
Create AccountLog in
Avatar of nativ
nativ

asked on

How to allow GUI more rights to database then when the user connects directly.

I have a .NET GUI Application for a certain set of users in the company.
The GUI requires rights to the database to perform the work (Select, Insert, Update, execute)
I want to use Windows Authentication on the database for two reasons:
1. Audit all actions performed by the user
2. We should not use a shared user when connecting to the database. Each user must have their own rights.

But, at the same time I do not want the user to be able to connect to the database directly with the same rights.
In most cases I do not want the user to ever connect to the database directly.
At most, they can execute a stored procedure or two.

How can I setup my rights so the user has different rights when connecting via the GUI to when connecting directly?

Avatar of Hammo777
Hammo777

This link pertains to your topic and has a discussion about DENY VIEW ANY DATABASE so that a user logging in through SSMS would not be able to see the database.

http://stackoverflow.com/questions/898380/how-to-disable-sql-server-management-studio-for-a-user

Avatar of Anthony Perkins
For a number of reasons, this is not a recommended approach.  For one you have the security hole of allowing SELECT, INSERT, UPDATE, and thenyou have the maintenance nightmare of maintaining these users.

However if you insist the only way I can see you doing this is through Application Roles.  However, my understanding is that application roles are somewhat relegated to the past and are not used much.  But check it out and see if it fits your requirements.
My comments were directed at the original question and not the subsequent solution presented.  That has its own set of problems, but I was not addressing them.
Avatar of nativ

ASKER

acperkins

1. I'm not sure what you are referring to as "not a recommended approach".
I know the security hole of allowing SELECT, INSERT, UPDATE - that is exactly the hole the question I asked in trying to solve.
Are you referring to "Windows Authentication" as not the recommended approach? If so, why not? What would you recommend.

2. Application Roles sounds like exactly the solution I am looking for. It solves the issue of having special rights when using the application.
Why would it be relegated to the past? What is wrong with that approach?

I will now investigate deeper into Application Roles.
But if you can point be to the drawbacks of using this it would be appreciated.
>>I know the security hole of allowing SELECT, INSERT, UPDATE - that is exactly the hole the question I asked in trying to solve.<<
That is just it.  You are still allowing for these statements.  Typically in a Production environment the only permissions that should be allowed for any user are EXECUTE.  In other words no Dynamic SQL (you may want to read up on SQL Injection), all data access (including SELECT, INSERT and UPDATE) is handled through Stored Procedures.

>>But if you can point be to the drawbacks of using this it would be appreciated.<<
Here is a good article on the subject.
SQL Server Security: Pros and Cons of Application Roles
http://www.sqlservercentral.com/articles/Security/sqlserversecurityprosandconsofapplicationroles/1116/
Avatar of nativ

ASKER

I agree with you about using SPs.
But I am dealing with an existing application and changing it all to use SPs is not in our current budget (or time-frame).
I am only looking at securing the database.
That being said, even if I were to use only "Execute rights" i would not want them to execute SPs randomly.
I only want them to execute them via the GUI.
So I would still end up with the same issue, albeit safer. (Can we call it square two? :) )

So I will ask you this.
If I rewrote the application to only use SPs, how would I stop the users from executing the SPs directly on the database?
Avatar of nativ

ASKER

It seems Application Roles work only with a single database.
If I want to make cross database calls I need to enable a Guest account.
This dramatically lowers the security of the other database.
Since multiple database are used throughout the system - We cannot use Application Role as a solution.

What would be your solution when dealing with just Execute Rights?
It might end up that I will have to force them to re-write the code into SPs. (Although I doubt they will invest that much).
>>If I rewrote the application to only use SPs, how would I stop the users from executing the SPs directly on the database?<<
With your setup, you cannot do it.

>>We cannot use Application Role as a solution.<<
Exactly.  That is one of the many reasons that it has been relegated to the past.

>>What would be your solution when dealing with just Execute Rights?<<
The way you can handle it is by using SQL Server Authentication for the application and Windows Authentication for ad-hoc read-only queries and only when and where necessary.  The problem with this approach is that it assumes you have a login process with user name and password (for auditing purposes).

This may be one of those ocassions where "You cannot do that" is the right answer.
Avatar of nativ

ASKER

I find it hard to believe that this is such a rare scenario.
Having an application which connects to the database but not allowing the user to connect themselves to the database should be a normal scenario.
Based on what you are saying the only way to achieve that is by having the application use SQL Authentication.
I don't buy that. Databases have been around for a while and I am sure there are cleaner solutions.

I am currently looking into Signed Stored Procedures.
I'm not yet sure if it is a solution - and if we can change the code to use only SPs
>>I don't buy that. Databases have been around for a while and I am sure there are cleaner solutions.<<
Fair enough.  Make sure to post your solution, so that we can all learn from it.

ASKER CERTIFIED SOLUTION
Avatar of nativ
nativ

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.