September 19, 2006 at 5:10 am
I have setup SQL server 2000 at the back end and using microsoft access at front end. Microsoft access is linked to SQL server via ODBC. I want to setup user accounts with password so that
1- The user should enter the database using a secure username and password.
2- The appropriate permission should be granted on Inserts,updates, deletes.
But we have to consider the following issues
1- Each user belongs to some department.
2- We have many differnt department with specific names, like marketing, sales, admin,etc.
3- We already have full department list with their staff members and contact numbers and emails in some of the tables.
3- Each department should have appropriate permission on their related tables
4- Within each department, there may exist two to three roles with different permission level, e.g. one role can be Marketing Admin, one can be Marketing Users, etc.
How I can do that? your expert opinion please
September 19, 2006 at 6:49 am
With that in mind, all your departments and rights prior to set the roles, you might think of setting Windows authentication. This will allow you and your systema admin to set the accounts in one end and then just add them to the roles you will be creating. This is the first thing to think about before you start.
Once you make a decision in this matter, then create the roles with in the database (go to security, select database roles and create one role)
After you create the role, open role properties and assign the permissions this role is going to have (i.e. in the permission box select what acctions this role will have choose the table and enter either SELECT, UPDATE, INSERT, DELETE (be careful to assign the delete permission for each table))
After you have defined what permissions each role will have in each table, just add the users to the role.
Sample: Carlos is a manager so he will have access to the tables he needs and the rioghts will be select, update, insert. Joe is a data entry level person, so the rights to the tavles will be inert only, he won't have the right to read the data, just allow him to enter new data/ Marlene is a person who needs to create new records, read new records, but can not update She will have select and insert)
There are samples in Books On-Line that can help you better. You can do it via T-SQL too. I am just giving you some ideas in how to proceed. Good luck
September 19, 2006 at 12:14 pm
Suggestion is very nice. no doubt about that. Simply you have given the complete solution.
But, the problem in windows authentication is that in our network one computer is logged in by one user and he leaves it open. This is very common and we can not keep a constant check as the departments are at various locations.
Now what happens is that the user doesn't log off and leaves the computer open. Some other users in the same dept. who also use the database and have readonly permission set via windows authentication, they can start using the system and may modify / delete the data.
This is very critical and really an issue which has to be addressed properly.
Regards,
September 19, 2006 at 12:21 pm
This becomes the user responsibility. You can implement a set of rules where the user has to lock is PC when he leaves. Have the pc go to sleep after 5 minutes of inactivity. Then make it the user's responsibility to apply the rules. That way you weed out most of the problem. I agree that security is really important, maybe even moreso in your case, but there's a limit with what you can do with sql server. If a user physically hacks into a computer, then there's not much the server can do then...
September 19, 2006 at 10:23 pm
I understand completely.
I was wondering, the SQL server has also the facility to create SQL Logins and the SQL server can authenticate the users and grant them permissions. Can it be implemented? and what are important steps we have to take.
September 20, 2006 at 5:34 am
You could use SQL Login authentication - however, the same problem of the "open computer" would still exist. If a user logged into the Application/SQL Server and left it open - it does not matter which authentication process you use - anyone could gain access with that user's rights. Your network administrator should be implementing group policies on the network that force PCs to use a screensaver and require login to restart after inactivity. Then you can use Windows Authentication as specified above.
September 20, 2006 at 7:06 am
As the other poster have said The responsibility of the "open computer" rests solely upon the user. That is of course once GPO's are in place to lock the computer after a certain period of time, 10-15 minutes is the norm. You'll still have people that have thier username and password taped to the bottom of thier keyboard at best, and directly to thier monitor at worst. The only decent way to twart that that I've found is to be somewhat of a jerk.
Granted this works best in smaller shops where you have time to walk around every so often...when you walk past a machine that no one is sitting at, sit down, type up your standard email to the person from thier own account explaining how easy it was to do this and that they are risking company data etc etc. Make sure they understnad how much they don't care about thier company's data. Next time do the same thing and copy thier manager on it. After that other steps may be taken.
If thier manager keeps getting these emails about a particular person, perhaps it will be brought up during an Performance Eval. When someone's paycheck (read raise from a PE) is all of a sudden dependant on locking thier computer when they walk away from it, they will learn very quickly to do it.
September 21, 2006 at 2:53 pm
Even though we have corporate level policies for windows authentication and variety of GPO are in place, SQL Server authentication will suit us.
We can implement some procedures to lock the FE and whenever some one wants to use, he can unlock with his username and password and restablish the connection.
Expert opinion please.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply