Clever Geek Handbook

Block SQL Server for a specific application on a web server

I am curious if there is a way to tell SQL Server that a specific group only has access to the database from one location / application. I have a SQL Server and a web server. Our applications use stored procedures, and access for each stored procedure depends on the role that is allowed to access it. User groups are then assigned roles based on the functionality they will transform. As an added layer of security, I would like to specify a web application from which these users can access the database.

I guess this is too much. Stored procedure names are always hidden from users (all errors are hidden, and a generic user-defined "sorry that doesn't work"). Users only have access to stored procedures that they are authorized to execute. It's just a nice extra piece of security, so if a table accidentally gives everyone full access, the database will only allow full access from one location.

+1


source to share


3 answers


In the connection string you can set Application Name=MyAppName

- this is not real security, but you can check it in your SP ( sysprocesses

- in column program_name

) and sp_who

.

There is nothing to do with the tables, so I recommend that no one was in any role, which generally allowed access to the table ( SELECT

, INSERT

, UPDATE

or DELETE

).

You can check this regularly with some automated T-SQL so no one does anything stupid.



I'm not advocating this in any way , but you can do something like this for the views (by comparing the SPID of the current process and the program_name):

CREATE VIEW YourViewNameHere
AS
SELECT *
FROM YourTableNameHere
WHERE EXISTS (
    SELECT spid, program_name
    FROM sys.sysprocesses
    WHERE program_name = 'YourProgramNameHere'
        AND spid = @@SPID
)
0


source


I suggest running the application / application pool as a service account that has procedure permission but does not grant any rights to the users themselves. This entails not enforcing user security at the database level, but at the application level.



0


source


The easiest way is to just lock it at the user level. You can run your win / web application in a specific security context where you have the required rights configured for.

This provides the ability to force users to launch your application to interact with SQL and cannot just open Enterprise Manager or whatever.

0


source






More articles:


All Articles