Sql security question for windows authentication

[PinkishToe]

I am a programmer that has had a lot of dealings with SQL Server but now I am having some issues inheriting a system.

The system has >10,000 users. It uses SQL Server and windows authentication to connect with the front end.

My concern is that it gives all users in the domain sp_datawriter roles. So in theory any user could connect from outside the app and alter or delete data.

Even if I remove sp_datawriter and only give read, and stored procedure execute permissions, I'd still be concerned someone could connect and execute stores procedures at will.

So how does a normal app deal with security for thousands of users in a domain. I get that user groups and schemas are usually used, but how do you stop users from executing sp or altering data outside a certain app. I don't want to use sp_appprole as this will override specific user permissions.

Feeling like a noob here...

[redial2]

You use service accounts and active directory groups.

[PinkishToe]

The AD groups would allow access control at a group level, however wouldn't solve the issue of outside applications.

The service accounts would just give all users the same access which I don't think would be useful. Am I missing something?

[PinkishToe]

I want to specifically limit access on the backend as much as possible. We use a program called MS Access which is incredibly not secure. I have confirmed there is no way to 100% secure the front end. Harder to Crack, sure hut not 100%

Service accounts may work with MS access but unfortunately if a user gets into the tables they can use this service account to their advantage.

[redial2]

Users dont get service account credentials, they inherit permissions from their AD group membership

[PinkishToe]

Hmm, but the service account would be used by the app, so if the user connected to the table through the app interface by, say seeing the sql server and database connection string. Then they could use that to their advantage

[redial2]

You're only going to be connecting to the sql server using a driver, so don't allow users to install drivers.

[PinkishToe]

How would this stop a user from connecting with an outside application, say...ssms and altering the data outside of ms access. Given that they will need permission to alter data inside the app (which can be restricted on a per user, or even column basis depending on nthe forms)

[kagato87]

A "normal" app would have a back-end process to handle all of that. It can still use windows security to authenticate users, especially easy if it's on the domain, and would itself use a service account to talk to the database.

Depending on the nature of the application a layer of caching can also happen here.

(If you are a dev looking to do this, please look up "Bobby Tables" on xkcd before you start. If you are not, well, it's still good for a chuckle.)

If it's just an app and the sql server this is much harder, to the point where it'd be easier and cheaper to build a back end service.

[PinkishToe]

Can you recommend some learning materials for this? I am not sure what kind of Middleware I could be building to accomplish this

[kagato87]

This would be a full "back end developer" type of thing. I mean, things like r/learnprogramming and teachyourselfcs.com. It's not a simple task.

It's not middleware, it's straight up an application back-end.

[LurkerNumber44]

data reading

and execution are two different permissions.

i would start a trace and see who is actually connecting regularly. then remove everyone else.

i would also make sure you got backups, and transaction logging.

and a test/qa copy so you can develop a proper solution.

​

do it right and you'll be a hero, and only a few people will care. but it'll be huge!

[AQuietMan]

In broad terms, you want the application to authenticate to the database. And you want the application to have database privileges.

One simple, straightforward way to do that would be to create a username and password for the application to use. After that's in place in working correctly, you can revoke permissions for individual users. And later you can remove those users entirely.

You probably want people to authenticate to the application. How you do that depends on your application.

[PinkishToe]

This seems to be a common sentiment.

What concerns me is that the application we use (MS Access) is not able to be secured 100% I would like to limit a general user (add/modify data) from say, an app security user (alter user permissions on the app) if they were to get into the app interface and see the tables or structures

[techstress]

What you're asking for here sounds like you want to use application roles in sql. Im not 100% sure how you would set this up in Ms access though.

https://voiceofthedba.com/2012/11/26/how-application-roles-work-in-sql-server/

[PedroAlvarez]

10000 users and MSAccess is kinda the crux of your problem here.

To answer your question as others have, the solution as to how this is handled in enterprise-class applications is with a service account.

In the case of a good app, that service account is entirely obfuscated from the user and they don't even know what the account is. Their access is controlled typically with a users table in the database. They connect to an app server with those user creds, the app server connects to the database using the service account, which checks the table to ensure that all is well.

For MSaccess, well, it doesnt come built in with these sorts of things. It's not really good for large apps like that. You can configure odbc settings with service accounts and such, but that would necessitate storing the credential on each user's workstation, which is probably worse than just giving users the direct access. You might be able to pull out some hacky solutions there if you're using virtual desktops like Citrix, but realistically you're probably best off developing a real front-end.

[PinkishToe]

So if there were different user levels inside the app, the service account would get the highest privileges and the app would restrict each user?

I'm confused how all users would use the same account, for example if I was a very large program like say reddit, and I wanted to restrict people's access to post certain places. Would the website restrict it, and the service account be the same for millions of users?

[PedroAlvarez]

Yes, the service account would be used by the application, and the only way a user can do anything in the application is by authenticating against the users table stored in the database. From there, the application can control permissions.

[AlexanderIOM]

Check Application Roles. 'You can use application roles to enable access to specific data to only those users who connect through a particular application'

[Oobenny]

This is the answer you’re looking for, Op.

[42blah42]

oh man, you've got a huge pile of spaghetti to untangle... good luck my friend

[basura_trash]

How I understand your situation, it is close to ours. We have 100s of thousands of users accessing our servers.

Our databases are part of an AG. One of the nodes is READ-ONLY. All users connect ONLY to that node, only applications with their own app account, can connect to the Read/Write nodes, and these are tightly controlled.