Users and access to SQL Db

[faccorp]

I have a front end app that reads/writes data to an SQL server database.

The front end would be used by various types of users where some users would have access to only parts of the database, while other users would be administrators and have access to all parts of the database.

I am currently using Windows Authentication. What would be the best way to handle user level security? Would I create a table of users in the database, and have the front end handle the access to SQL? It seems to defeat the purpose of SQL security? I'm not sure what the best direction is to go.

Any thoughts or help is appreciated.

 

[ArtieChoke]

It all depends on how you want to structure the access and where you want to maintain it.

I had a project that needed various levels of security at the form or field level, so database security wouldn't work for that.

Otherwise, what we typically do is set up Windows groups and add those to the database with the appropriate security. Then add the individuals to these groups. Connect to the database with integrated (trusted) security and the user running the program will get the privileges set up in the db.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[faccorp]

Thanks to the reponse. What are Windows Groups? Not familiar with that.

It sounds like in your first scenerio that you designed the security into the front end.

Why did you choose integrated security as opposed to Windows Authentication?

Thans again.

 

[ArtieChoke]

That's correct - the security was handled in the program itself. We set up a table in the database for valid users and their security levels. The program only let those users run it.

Our ERP software required integrated security. The only difference is you can you either method - windows or sql server - for database security. Windows (or NT) groups is something your admin can set up. Users can be added to these groups, then the groups can be used to determine who gets access to folders, etc. This way, you don't have to add users directly. It works the same way for the db - add a group instead of individual users to the db with the privs you want them to have.

If you use ODBC to connect to the db, then when you choose a trusted connection, the user running the program is passed to the db for the login. If you don't choose trusted, then you need to pass a user/pw to the db in the connection string.

If you use OLE, then it's called integrated security (I think), but it's the same idea.

"I think we're all Bozos on this bus!" - Firesign Theatre