Unanswered: MS Access To SQL Server upsizing Security Question
My company has a Quality control program that utilizes MS Access to store quality control data. The computer that runs the quality control is a component computer of a much larger system and has no user interface per say. When the whole system is started the computer with the quality program is also started. The database resides one of our company’s other servers. The data is strictly used to analyze the QC results of the current batch for and we have no interest in saving any data once we have verified the results and run any necessary reports. In fact when the system is shut down, part of the shutdown process is to delete all of the data, compact and repair the database and get ready for the next batch . There is absolutely no need for any security of the data because the data would mean absolutely nothing to anyone else.
As everyone here knows, MS Access is definetly not the best database for this process! I’ve convinced my boss of this and he gave me the go ahead to reprogram using Sql Server. I have created all of the stored procedures, views and table to accomplish my mission and have everything working with a brand new application – All I have to do is to install it.
Now my question is security. When I created all my sql Server objects I also created an SQL Logon and gave that logon permission to execute all procs and whatever other permissions needed to access the database but I’m wondering if that was the best option.
After testing my program we thought this might be something we could sell to other companies. If so, what is the best security scheme to use especially if we were to try to come up with some we could create an installation program.
We have absolutely no need to secure any data. From a security perspective MS Access was great with a single simple connection string. No user names or passwords that could change, no permissions to server objects to worry about – just connect and go. Ideally it would be great if current windows user could automatically have all the permissions it needed but I know this is not possible with SQL Server.
I would just like some professional input on how to best handle security. Application Role, Assign all permissions to Public and/or Guest… questions like that……
We have a admin account as such, but most people use their active directory accounts to log in with user authentication. Our senior developer built roles in SQL Server that allows certain people certain access to certain objects etc. I would think if a "power user" would need full controls to that access database/sql server program you could build a group that gives the users just enough access to utilize the full functionality of the database without having access to update or insert into other tables non related. This would also give you visibility into who has updated or deleted etc records, objects etc using the application. We are all assigned user id's so if I update a record it reflects that in the table.
If the database you are building is strictly for that application then you could just give them rights to that database and while you scale in the future you could create other databases on that server and grant permissions to those as well on a case by case basis. Just some thoughts....