 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Security / May 2006User access on a company intranet
I have set user level security up on the database used by my department, which I'm the owner of all the tables, queries, etc.. I have given everyone in my department a user name, assigned them to groups and gave each a password. While the security works on my computer others in my department who access the database through the server are not prompted for their user name or password. While other are not allowed access because they do not have permission. What have I done wrong? Does the security in Access only work on stand alone computers? Does it work when the database is on the server? > I have set user level security up on the database used by my > department, which I'm the owner of all the tables, queries, etc.. I > have given everyone in my department a user name, assigned them to > groups and gave each a password. How did you give each a password? I'm asking because I suspect you think the PID you assigned to the username is a password, it isn't. > While the security works on my > computer others in my department who access the database through the > server are not prompted for their user name or password. While other > are not allowed access because they do not have permission. What > have I done wrong? Does the security in Access only work on stand > alone computers? Does it work when the database is on the server? It does work on shared databases. You've missed a step in securing it if someone is able to open it without username/password (i.e. they're likely using the default system.mdw workgroup file). They are being silently logged in as 'Admin', and they shouldn't even be able to open the mdb as this user. Security is complex, and you need to ensure you follow every step. Missing one step, or doing things out of order can result in an unsecure mdb. Security FAQ http://support.microsoft.com/?id=207793 Security Whitepaper http://support.microsoft.com/?id=148555 Although the whitepaper is old, it contains information to help you understand security. I've also outlined the detailed steps at www.jmwild.com/AccessSecurity.htm  SignatureJoan Wild Microsoft Access MVP I assigned each user a unique password "ex: sunset" for each user. I also used the security wizard to do this so I don't know how I missed a step. > > I have set user level security up on the database used by my > > department, which I'm the owner of all the tables, queries, etc.. I [quoted text clipped - 30 lines] > I've also outlined the detailed steps at > www.jmwild.com/AccessSecurity.htm I will assume you are using Access 2003? If someone is getting in without providing a username/password, it's possible that you assigned permissions to the 'Users' Group during the steps of the wizard - is that so? SignatureJoan Wild Microsoft Access MVP > I assigned each user a unique password "ex: sunset" for each user. I > also used the security wizard to do this so I don't know how I missed [quoted text clipped - 39 lines] >> Joan Wild >> Microsoft Access MVP I believe at work we are using a the verison prior to 2003. When the screen came up for permissions I did not do anything on it. I did assgn my user to groups, some to an update group and to a data entry grounp. > I will assume you are using Access 2003? If someone is getting in without > providing a username/password, it's possible that you assigned permissions [quoted text clipped - 43 lines] > >> Joan Wild > >> Microsoft Access MVP OK, that's version 2002. When you open the mdb via the shortcut and login as you, go to the Tools, Security, Permissions dialog and select the Users Group. Check that it doesn't have any permissions on any object. Click on the Ownership tab, and verify that 'Admin' does not own anything. SignatureJoan Wild Microsoft Access MVP > I believe at work we are using a the verison prior to 2003. When the > screen came up for permissions I did not do anything on it. I did [quoted text clipped - 53 lines] >>>> Joan Wild >>>> Microsoft Access MVP I checked and none of the users have permissions, but the groups do. Like the update group can read and update. Also, all tables, queries, etc. list me as the owner. However, if I go to another persons computer and open the database it showes unknown as the owner. Is there any way to start over? > OK, that's version 2002. When you open the mdb via the shortcut and login > as you, go to the Tools, Security, Permissions dialog and select the Users [quoted text clipped - 59 lines] > >>>> Joan Wild > >>>> Microsoft Access MVP It's good that groups have permissions, but does the 'Users Group' have any permissions? If you really want to start over, you should find a file in the same folder as the mdb, but with a bak extension. You can rename your secure mdw and your mdb, and then rename the bak file to have a mdb extension. This is an unsecured copy of your mdb, and you can start over with it. SignatureJoan Wild Microsoft Access MVP > I checked and none of the users have permissions, but the groups do. > Like the update group can read and update. Also, all tables, [quoted text clipped - 73 lines] >>>>>> Joan Wild >>>>>> Microsoft Access MVP When I go into the User/Group Permissions and click on the group box my groups all have different permissions. The User group has no premissions for any table or queries. However, if I change the object type to database it showes permissions for Open/Run, Open Exculisive and Admin with the current user as me for the user group. As far as starting over, there has been infomation placed in the database since I used the security wizard. If I use the .bak file will it have the new information, as well? > It's good that groups have permissions, but does the 'Users Group' have any > permissions? [quoted text clipped - 83 lines] > >>>>>> Joan Wild > >>>>>> Microsoft Access MVP > When I go into the User/Group Permissions and click on the group box > my groups all have different permissions. The User group has no > premissions for any table or queries. However, if I change the > object type to database it showes permissions for Open/Run, Open > Exculisive and Admin with the current user as me for the user group. The Users Group should not have *any* permissions on the database object; nor on forms/reports/macros either. That may solve your problem. > As far as starting over, there has been infomation placed in the > database since I used the security wizard. If I use the .bak file > will it have the new information, as well? No it won't; the wizard created this file before doing anything. SignatureJoan Wild Microsoft Access MVP It still does not work. Most can still access the database without any prompt then what I have set up within my database. Others gets a message that they do not have permission and for them to contact the admin. How do I give them permission. I have given them a user name and password that I set up in the Security Wizard, but they can not get to the prompt to use it If this is a problem my database is set up on a server and the database is in a folder only people in my department can access. Is there any way to turn off the security function in Access? When I first set this up I gave each user in my department a user name (jsmith) however this may be different then the user name they have on the network. I also gave each a unique password and added their user name and password to my security settings in the wizard. After doing this when I first went in I was asked to join the Secured1 database, but no one else was asked this. > > When I go into the User/Group Permissions and click on the group box > > my groups all have different permissions. The User group has no [quoted text clipped - 12 lines] > > No it won't; the wizard created this file before doing anything. > It still does not work. Most can still access the database without > any prompt then what I have set up within my database. Others gets a > message that they do not have permission and for them to contact the > admin. How do I give them permission. I have given them a user name > and password that I set up in the Security Wizard, but they can not > get to the prompt to use it The ones getting the 'no permission' message tells me they either don't have permission to open the db (not likely), or they aren't using the correct mdw file. Have you given them a desktop shortcut with the following in the target?: "path to msaccess.exe" "path to secure mdb" /wrkgrp "path to mdw" The path to mdw would be the path to the mdw you used to secure it with. > If this is a problem my database is set up on a server and the > database is in a folder only people in my department can access. Is > there any way to turn off the security function in Access? If you rename the bak file to have a mdb extension, that file will be unsecured. You may have to rejoin system.mdw on your computer though. When you created the new mdw, it likely made it the default one to use for all sessions. Go to Tools, security, workgroup administrator and click on Join and rejoin system.mdw (you should search for it first). > When I first set this up I gave each user in my department a user name > (jsmith) however this may be different then the user name they have > on the network. That's fine. I also gave each a unique password and added their > user name and password to my security settings in the wizard. That's fine. After > doing this when I first went in I was asked to join the Secured1 > database, but no one else was asked this. 'Asked to join'? What do you mean? What was the exact message? SignatureJoan Wild Microsoft Access MVP They are still using the shortcut I placed in our department folder when I created the database before I used the security wizard and it the same one I use to open the database. Also, it is the only icon in the folder. However when I open the database I get the prompt to enter my user name and password. I can also enter other users names and password on my computer and enter as the database as them. In fact it showes them as current user when I go to the User/Permission drop down in Security and me as the owner of the database. However, when I do this on their computer it goes stright into the database. All users are using the same icon to enter the database. How do I creat a new shortcut? Do I get rid of the orginal Icon and replace it with an icon that take them to my Secured1 database? When I said below that I was asked to join what I meant was that I went into Workgroup Admin and join the Secured1 file that was created. > > It still does not work. Most can still access the database without > > any prompt then what I have set up within my database. Others gets a [quoted text clipped - 39 lines] > > 'Asked to join'? What do you mean? What was the exact message? Let me try and explain how workgroup files work. Access always uses a workgroup file, even with unsecured databases. Out of the box, it uses a workgroup file named system.mdw. When you open a database, it silently logs you in as a user named 'Admin'. The Admin user owns everything, and the Users Group has full permission on all objects. So it appears as though there is no security, but there is. When you want to implement security, you create a new mdw file, and follow the steps to secure a mdb. If you've done it correctly, then the only way to open the secure mdb is by using the mdw you created. If someone can even open a 'secure' mdb while using system.mdw, then you missed a step in securing it. Every Access session uses a mdw file. Some mdw is set as the default one to use. This is done via the Workgroup Administrator in the Security menu. Once you set a default, it will be used for all sessions, unless you specify another one. You can change the mdw by either 1. using the workgroup administrator to change the default or 2. including the /wrkgrp switch in a desktop shortcut along with the path to a different mdw. The latter is recommended. Leave the computers joined by default to system.mdw. Create a desktop shortcut with the /wrkgrp switch - this will override the default mdw for just that session of Access. More in line... SignatureJoan Wild Microsoft Access MVP > They are still using the shortcut I placed in our department folder > when I created the database before I used the security wizard and it > the same one I use to open the database. The target of this shortcut likely has only the path to the mdb in it. > Also, it is the only icon > in the folder. However when I open the database I get the prompt to > enter my user name and password. This is because you are joined by default to the mdw you used to create the mdb with. You'll find that you'll be prompted for a username/password for *every* mdb that you open. You need to change your default mdw back to system.mdw. >I can also enter other users names > and password on my computer and enter as the database as them. In > fact it showes them as current user when I go to the User/Permission > drop down in Security and me as the owner of the database. However, > when I do this on their computer it goes stright into the database. On their computer, they are joined by default to system.mdw. Since they can open the mdb, you missed a step in securing it. > All users are using the same icon to enter the database. How do I > creat a new shortcut? Do I get rid of the orginal Icon and replace > it with an icon that take them to my Secured1 database? You can just right-click on that icon and choose properties. You'll see a 'target' line in the dialog. Just edit it. You must put the path to msaccess.exe at the front of the target. One thing that may or may not cause a problem is if someone has installed access to a different folder. You should give each user a shortcut (icon) on their PC rather than having everyone use the same icon. Modify the target to have: "path to msaccess.exe" "path to mdb" /wrkgrp "path to secured1.mdw" Modify the above to reflect the actual paths to the files. Having said all that though, you still haven't secured the mdb properly since some people are getting in with no login. You should fix that first. > When I said below that I was asked to join what I meant was that I > went into Workgroup Admin and join the Secured1 file that was created. By doing so, you made it the default mdw to use for all sessions. You'll find that no matter what mdb you open, you'll be prompted for username/pwd - try opening Northwind and you'll see. You need to go back in and set system.mdw as your default instead. Use the redefined shortcut explained above to open your secure mdb. I did as you said and created a shortcut with the path you gave me and it works. Now the problem is that when someone besides my goes in after giving their user name and passwork they get a message that the database is exclusively open by another or they don't have permission. The person I was using to test has no premission as a user, but is assigned to the Full Data Users Group, where they can Read design, read data, update data, insert data and delete data. I have an auto.exe macro that runs using a password prompt I have built into my database should I disable this? > Let me try and explain how workgroup files work. > [quoted text clipped - 15 lines] > Once you set a default, it will be used for all sessions, unless you specify > another one. > You can change the mdw by either > 1. using the workgroup administrator to change the default [quoted text clipped - 59 lines] > system.mdw as your default instead. Use the redefined shortcut explained > above to open your secure mdb. I disable the autoexec but people still can't use the forms on the startup screen. It tell them that the database is open exclusively by another person or they don't have permission. The person I was using to test has no premission as a user, but is assigned to the Full Data Users Group, where they can Read design, read data, update data, insert data and delete data for tables and queries. He also does not have any permissions when I change the object type to forms and the full data user group has only run/open permission Also, if I disable the shift key so people can not go around the startup screen how will I be able to get into the database to make changes? Will I still be able to go around the startup screen to designe forms, update queries, etc? If I do disable the shift key what it the best way to do this? I have looked at some code that had been written from some of the other threads, but I'm not sure where to put it. > I did as you said and created a shortcut with the path you gave me and it > works. Now the problem is that when someone besides my goes in after giving [quoted text clipped - 88 lines] > > system.mdw as your default instead. Use the redefined shortcut explained > > above to open your secure mdb. > I disable the autoexec but people still can't use the forms on the > startup screen. It tell them that the database is open exclusively [quoted text clipped - 4 lines] > does not have any permissions when I change the object type to forms > and the full data user group has only run/open permission ALL users need full permissions on the folder where the MDB resides. Do They? > Also, if I disable the shift key so people can not go around the > startup screen how will I be able to get into the database to make [quoted text clipped - 3 lines] > been written from some of the other threads, but I'm not sure where > to put it. A) The shift key disabling can be programmatically toggled on and off either with an "easter egg" (a place to click that only you know about) or from code in a completely separate MDB. B) The users should not be using the same file that you develop in anyway so this issue should not matter SignatureRick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com All users need full permission on the folder where the mdb is located. This is windows permissions, not Access. You won't see any permissions when you look at a 'user' in the security dialog - that will only show you the explicit permissions, not the implicit ones (inherited from being a member of a group). I usually disable the shiftkey only when I'm ready to deploy to the users. You wouldn't need to do this in your copy of the database. However you can toggle the shiftkey bypass from another mdb file. Albert Kallal has a utility you can use to do this. Look for Shift Key ByPass at http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html SignatureJoan Wild Microsoft Access MVP > I disable the autoexec but people still can't use the forms on the > startup screen. It tell them that the database is open exclusively [quoted text clipped - 116 lines] >>> to go back in and set system.mdw as your default instead. Use the >>> redefined shortcut explained above to open your secure mdb. They do have full permissions to the folder that the front end of the database is located but not on the backend. Do I need to put the backend in a folder that they have full access? If I do this do I need to relink the tables. > All users need full permission on the folder where the mdb is located. This > is windows permissions, not Access. [quoted text clipped - 130 lines] > >>> to go back in and set system.mdw as your default instead. Use the > >>> redefined shortcut explained above to open your secure mdb. Yes they need full permissions on the folder where the backend is. If you move the backend, you'll need to relink, yes. SignatureJoan Wild Microsoft Access MVP > They do have full permissions to the folder that the front end of the > database is located but not on the backend. Do I need to put the [quoted text clipped - 143 lines] >>>>> to go back in and set system.mdw as your default instead. Use the >>>>> redefined shortcut explained above to open your secure mdb. > They do have full permissions to the folder that the front end of the > database is located but not on the backend. Everyone should have their OWN local copy of the front end. Only the back end should be shared. > Do I need to put the > backend in a folder that they have full access? Yep. > If I do this do I > need to relink the tables. Yep. SignatureRick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com When you say local copy is this a copy on their desktop stored on their hard drive or can it be in a folder that they have full permission to get into on the server? > > They do have full permissions to the folder that the front end of the > > database is located but not on the backend. [quoted text clipped - 11 lines] > > Yep. > When you say local copy is this a copy on their desktop stored on > their hard drive or can it be in a folder that they have full > permission to get into on the server? Could be either, but on the local drive would be better performance wise. SignatureRick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com Thanks for your help everything is up and running. One last question is there any way to log when someone logs into the database and logs out? > I have set user level security up on the database used by my department, > which I'm the owner of all the tables, queries, etc.. I have given everyone [quoted text clipped - 5 lines] > work on stand alone computers? Does it work when the database is on the > server? You can create a table to hold the username, date/time in/out. You can have a form open on startup (hidden if you like), and use the load/unload events to write to the table using the CurrentUser() function and the Now() function. SignatureJoan Wild Microsoft Access MVP > Thanks for your help everything is up and running. One last question > is there any way to log when someone logs into the database and logs [quoted text clipped - 10 lines] >> stand alone computers? Does it work when the database is on the >> server? Do I created this table in the Secured1.mdw or in my regular database? If I placed it in my database how will it know the user? Do you know of any code I can look at for this? Thanks for everything, I'm still learning. > You can create a table to hold the username, date/time in/out. > [quoted text clipped - 16 lines] > >> stand alone computers? Does it work when the database is on the > >> server? I tried to write a macro where on On Load the macro set the value for User in my table to "Current()" and time to "Now()" but I get the following error message "the object doesn't contain the Automation object 'sysUsageLog' (my table). you tried to run a Visual Basic procedure to set a property or method for an object. However, the componetnt doesn't make the property or method available for Automation operations. Check the component's documentaion for information. I placed the table with my other database tables. Also, is there a way to log the computer they are using on the server? > Do I created this table in the Secured1.mdw or in my regular database? If I > placed it in my database how will it know the user? Do you know of any code [quoted text clipped - 20 lines] > > >> stand alone computers? Does it work when the database is on the > > >> server? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2010 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.