0

Is it possible to block a sql server user not to execute DDL and DML operations from a specific hostname (client)?

I have a CRM web application. That app connects to my database with a sql server auth user. There are several different developers working on that CRM web app. They know the user name and password from the web config file they develop. Some of them can connect to my database with that user and execute some DDL and DML commands and I want to block them by their client hostname. If a developer connects to db from his/her machine with the app user credential, I want to block them.

Improve this question
6
  • 3
    What holds you back from just not giving him any permissions he doesn't need? If he has no permissions for DDL and DML, he won't be able to do it.
    – Yannick Liekens
    Commented Jan 2, 2020 at 9:17
  • When you say hostname you mean the server or client name? Because if you meant the client name, your question would make more sense to seek a different approach other than the suggestion of @YannickLiekens. Otherwise I agree with him.
    – Ronaldo
    Commented Jan 2, 2020 at 11:25
  • @Ronaldo Yep, my question is a little bit confusing and not clear. Let me more illustrative my question. I have a CRM web application. That app connects to my database with a sql server auth. user. And, there several different developers which developes that CRM web app. They know the user name and password from the web config file they develope. Some of them can connect to my database with that user and execute some DDL and DML commands. And i want to block them by their client hostname. If a developer connects to db from his/her machine with the user credential, i want to block them.
    – DBA
    Commented Jan 2, 2020 at 11:57
  • do you have an environment for dev and another for prod? If yes, isn't it possible to have two different app users: one for dev with the password known by developers and one for prod known only by admins? This way the only method to connect to the prod would be with their own login (what I understand have low privileges) and not with the app user no matter the hostname.
    – Ronaldo
    Commented Jan 2, 2020 at 19:59
  • Yep. There are different environments for database developments. All developer accounts are under domain controller. We use AD in the company. In general, they connect both development and production database server with their own domain accounts, but some of them use the app user credential informations. And, i can detect them,who used that user credential, with my weekly database reports. But the issue is that some developer managers also use that application user information to connect database server. And, i don't want to fight with them everytime. + ..
    – DBA
    Commented Jan 3, 2020 at 7:06

2 Answers 2

Reset to default
2

And, there several different developers which developes that CRM web app. They know the user name and password from the web config file they develope. Some of them can connect to my database with that user and execute some DDL and DML commands. And i want to block them by their client hostname. If a developer connects to db from his/her machine with the user credential, i want to block them.

I don't think you should try to fix it that way.

Both pc names and IPs can be changed at will, even static IPs could be changed if wanted/needed. Which means you'd have to maintain an up-to-date list all the time.
As a dev, if you really wanted to do something, remote to another VM, execute there.
I mean, there's probably plenty of security loopholes they can find if they really wanted to do something.

Finding a solution to do what you want, would probably only make it a hassle for them, but not fool proof.

I think you'd be better off trying to find a security solution for this. If one of the devs should not be able to do DML statements, don't give them a login that has access to it. Does the user in the config really need all the permissions it has now? Perhaps you can give less permissions to that user, and give the people who need more permissions an extra role, on their own login.

Improve this answer
3
  • Yep. Unfortunately, that user has to have those rights. 'Cause, customer representatives use this CRM application and they don't know sql and shouldn't. :/
    – DBA
    Commented Jan 2, 2020 at 13:52
  • 1
    Could probably look into a solution where your devs cannot see the login/password themselves. If they connect to the database, they should use their own dev logins. Setting that up (correctly) would be out of my field of expertise I'm afraid.
    – Yannick Liekens
    Commented Jan 2, 2020 at 14:01
  • Alright, Is it possbile block them according to their AD accounts instead of a client hostname or ip address? I mean, Can i define a rule like ' if a sql server username is an app name and the hostname from an AD group account then block that connection' in sql server?
    – DBA
    Commented Jan 3, 2020 at 7:20
0

Once a user opens a connection, he has the rights that were configured as server roles plus inside of the databases.

From a technical view point, this could be solved using a trigger but just because it could, doesn't mean it should. (it shouldn't!!) The way a trigger would work is to check before each DDL / DML statement, if the current statement is being executed from a "black-listed" hostname. This is one additional query being executed for every statement.

Improve this answer
2
  • So, Can't it be done without using trigger?
    – DBA
    Commented Jan 2, 2020 at 8:55
  • Not to my knowledge
    – Fabian Schenker
    Commented Jan 2, 2020 at 10:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.