I've been fiddling around with the Tools>>Security settings but I can't seem to find a way where only I can make changes to the database and the users can't just use the switchboard. If you are regular user you don't need password but if you are admin you do.
How do I do that?
I have two questions. All of my experience has been in Access 2003. My work just upgraded to 2007. I am ready to secure a database that I have now converted to a 2007.
1. Is it possible to set the db to open as read only for everyone on staff except for a few people?
2. How do I set security levels where some can view all tables and others can only view specific tables?
For example, I only want 2 people to be able to modify any data. And I don't want anyone to view the accounts receivables, but they need to see other data on the customers.
Thanks in advance.
All,
As I promised earlier, here is my suggestion to secure an MS-Access database. The explanation below is applicable for MS-Access 2000 and XP versions, I haven't tested it on Access 2003.
This way of securing a database is a bit different from the one I posted earlier, but more secure.
Disclaimer:
This explanation of securing a database is based on MY experineces. I mean I've been doing it in this way, and it worked OK for me.
I DO NOT say that there is no other method of securing a database.
I will NOT take responsibility for any problems caused by securing your databases either in this or in any other way.
Use this guide on your own risk.
Pre-reading notice:
Menu paths and commands may be different in the English version of MS-Access. I use Hungarian MS-Access, but I am doing my best to remember (or translate to) the English menus and commands.
Where I'm in doubt about the exact English menu path or command and there are more than one possible translations, I enclose the alternative between parentheses.
Menu paths are indicated like this: File -> Get external data -> Import...
Command buttons are indicated like this: <OK>
To secure a database:
1. close the database you want to secure.
BEFORE closing it:
- If a database password is set, then clear it.
- If the VB Project is protected then unprotect it. To unprotect the VB Project go to Tools -> [project_name] options... in VB Editor and click the Protection tab. Remove the tick from the 'Lock Project from viewing' check box and clear the password.
2. CREATE a new blank database;
3. Check and make a note of the path of the default Workgroup Information File (.mdw).
- to do this, go to Tools -> Security -> Workgroup Administrator, and read the file path indicated.
4. create a new .mdw file using the Workgroup Administrator, and stay joined to it.
- to create a new Workgroup Information File go to Tools -> Security -> Workgroup Administrator, and click <Create...>. Provide the required information, click <OK>, click <Browse...>, and type a name.
- I usually use the name of my database which I want to secure.
- Note: if you wish your .mdw file to be unique, then you need to provide a workgroup code.
- If you provide a workgroup code, it's strongly recommended to make a note of and keep it in a safe place. Should the the .mdw file be lost, you will need this information to create the "same" .mdw file.
5. create a User with your logon name and put this user to the group called 'Admins';
- to create a new user go to Tools -> Security -> User and Group accounts... Click <New...> on the Users tab. Type a logon name, and a PID.
- Note: users are identified by their PID, not by their logon name. Anytime you create a user, it's strongly recommended to make a note of the PID and keep it in a safe place. Should the the .mdw file be lost, you will need the PIDs to create the same users in a new .mdw file.
- It's enough to create a user account for yourself. You can create the other users' after the whole process.
- Don't forget to put yourself into the 'Admins' group.
6. Create a password for the default Admin user (which you are at the moment).
- To enforce users to provide a logon name and a password, you need to set a password for the default Admin user. If the default Admin user has a blank password (in other words: no password set for it), then Access automatically starts with the default Admin user, and does not require user authentication.
- To create a password for the default Admin user, go to Tools -> Security -> User and Group accounts... and select the Change Password tab. Leave the Current Password blank, then type & confirm the new password.
- The default Admin user does not need a difficult password, because this user will not have any permission on the secured database at the end. We need the password only to enforce user authentication.
7. Close the database.
8. CREATE an OTHER new blank database.
- If a password has been set for the default Admin user, it will prompt for a password.
- Delete the "Admin" and type the user name you just created. NO PASSWORD REQUIRED for this user, so just click <OK>.
- From now on, you are logged on as the user that you've just created. Note: if you haven't put this user into the Admins group, you won't be able to continue.
- IMPORTANT NOTICE: the reason of creating an other blank database instead of just doing the whole process WITHIN the database to be secured, is that the only way to take the ownership of a 'Database' object is to create it as YOU, and not as the default Admin user. As I experineced, 'Database' ownership CANNOT BE TAKEN AWAY from the creator of the 'Database' object.
9. Go to Tools -> Security -> User and Group accounts... and REMOVE the user 'Admin' from the 'Admins' group.
- Clck Users tab, and select 'Admin' from the drop-down list. Select 'Admins' in the list box 'Member' ('Member of'), and click <Remove>.
- If you haven't added the new user to the Admins group, then you won't be able to remove the default Admin user from the Admins group because Access will not let you to have no users in the Admins group. At least one user must be in Admins group.
- If you can remove the default Admin user from the Admins group, then, from now on, you are the only user with administrative permissions for this database and in this .mdw file.
10. Set a password for yourself.
- How? See point 6.
- Memorize your password, because it can only be retrieved by using certain Password Recovery processes and softwares. As far as I know, there is NO WAY to retrieve a password via VBA or MS-Access for a normal user, it needs someone with deep hacking knowledge...
11. Import all objects from your database that you want to secure.
- Go to File -> Get external data -> Import..., and browse to your database.
- Select ALL objects: tables, queries, forms, reports, macros and modules, and click <Import>.
- If you haven't unprotect the VB Project, it WILL NOT import any VB code even behind the forms.
- From now on, the owner of the 'Database' object and all tables, queries etc. is YOU.
12. Run User Level Security wizard and make the permission settings.
- It's recommended to select all objects and grant only data modification rights to the 'Users' group.
- After running the wizard, it's recommended to restrict all rights of the default Admin user manually. This will ensure that if your database is opened with the default system.mdw file (which is automatically created when MS-Access is being installed), then the default Admin user will not have any permission to any data or object.
13. Join to the default Workgroup Information File, and close the database.
- to do this, go to Tools -> Security -> Workgroup Administrator, and click <Join...>, click <Browse...> and navigate to the default .mdw file you've hopefully made a note of.
After this process, if you just open your secured database then you will open it as the default Admin user. It will not require a password, because in the default .mdw file, the Admin user does not have a password. And, if you've restricted all permissions of the deafault Admin user, you won't be able to make any data or design modifications, and even open any objects.
The reason of it is that the default Admin user's PID is the same in all .mdw files. The default Admin user is automatically created when you create a new .mdw file. The default Admin user also CANNOT BE deleted. The default Admin user uses the same PID everywhere, so regardless of how many .mdw files you have on your system, Admin user logically is the same for all databases.
So then how to open your secured database?
You need to use the .mdw file you created. So open your database with the /wrkgrp switcher. Create a shortcut and use this in the Target field:
[drive]:[database_path][database_file] /wrkgrp [drive]:[mdw_file_path][mdw_file]
Example:
"C:MyDatabasesMyDatabase.mdb" /wrkgrp "C:MyDatabasesMyDatabase.mdw"
If you open your secured database in this way, then it will require a password (of course, because the default Admin user in your .mdw file has a password). Log on as yourself, and you now can add other users.
NOTE VERY CAREFULLY:
1. BEFORE starting the whole process ALWAYS make at least one backup copy of your unsecured database.
2. NEVER delete this backup copy UNLESS you're ABSOLUTELY SURE that everything is approved and tested and IS WORKING OK in your secured database.
3. DO NOT LET anyone to make any changes in data or design in your backup copy until you're absolutely sure that everything is working OK. Otherwise your backup copy will not be anymore a clone of your database.
Other notice:
I've been blocked out from my own databases many times while I was searching for a right way of securing. Unfortunately, HELP provides a likely poor aid.
If you encounter problems, I'll do my best to help you as my time allows me to.
I kindly ask the members that feel themselves more or less experienced to make a test and post any notes/suggestions/bugs/mistakes to this thread, PLEASE.
REMEMBER: IF YOU HAVE A BACKUP COPY YOU WON'T GET TROUBLE.
This is my 2nd thread on the topic...I've searched the forums....
I have a database. It is on the shared folder on the server. I ran the security wizard and created a shortcut. On my machine it works perfectly...asks for a login, has a couple user groups etc...
When I run it off the server though, the shortcut doesn't work....refers to files on my machine?? and the database is unsecured.
Do I have to run the security wizard on every machine? Do I need to run the security wizard from the server? Or do I even need to run the security wizard at all??? My 2-day access course is failing me miserably...
Have created a database that will be placed on a shared drive, giving access to others to add, delete, modify records. That aspect of it is fine -- but how to I prevent someone from deleting a field?
View 2 Replies View RelatedHello,
for our company we have an access application we use to keep track of our customer-info.
The databse consists of 3 parts:
The (replicated) frontend
The databse holding the changing data
The database holding the unchanged data (lookup db)
We have the following problem:
somewhere in our front-end db is a bug that allows users to change the contents of the lookup db. In our case this can result in a major problem because the users are able to change the city-zip code table.
I have tried to figure out where things go wrong but so far no result.
We have picked up the idea of making the lookup db read-only for normal users. This will prevent them from modifying the contents, and will also result in error messages. Hopefully will these error messages point me in the right direction of the bug.
Problem is that the ldb, created when opening the mdb, inheritates the same security settings of the mdb. If we set the mdb to read only, the user gets an error-message stating it can not find the lookup db or that the lookup mdb is locked. This is because the user can not create or modify the ldb. On the other hand, if we set the security to create and modify for the mdb, the user still is able to change the data in our lookup db.
I have never worked with the security in access itself. and I want to try to avoid that. Is there a way of securing the data in our lookup db. So I'm able to figure out what really goes wrong.
Can someone help me out on this major problem.
I don't have the database finished so I can't really comment on the structure. However, I know the database will be used by multiple people, very likely at the same time. I've heard it's a good idea to split the mdb into a frontend and a backend so I've already started that process. I have no idea how this would fit into a security scheme though. Do I have to set up user-level security on both ends? Without actually implementing it I kind of think it would be secure if the backend had a master password and the frontend had user-level access. What do you all think?
View 1 Replies View Relatedghudson:
I am experiencing a problem with the mousetrap sample after I secured my database.
When I save on my main form and I try to go to my subform I keep getting the "Please Save this Record! You can not advance to another record until you either 'Save' the changes made to this record or 'Undo' your changed."
I have saved but it is still preventing me from going to the my subform. I numbered the Save Required msgs so that I know which one I am getting and I am getting the one from:
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Form_BeforeUpdate
Me.tbHidden.SetFocus
If Me.tbProperSave.Value = "No" Then
Beep
MsgBox "Please Save This Record!" & vbCrLf & vbLf & "You can not advance to another record until you either 'Save' the changes made to this record or 'Undo' your changes.7", vbExclamation, "Save Required"
DoCmd.CancelEvent
Exit Sub
End If
Exit_Form_BeforeUpdate:
Exit Sub
Err_Form_BeforeUpdate:
If Err = 3020 Then 'Update or CancelUpdate without AddNew or Edit
Exit Sub
Else
MsgBox Err.Number, Err.Description
Resume Exit_Form_BeforeUpdate
End If
End Sub
Above it is checking if tbProperSave.Value = "No" and in bSave you have
Case vbYes: 'Save the changes
Me.tbProperSave.Value = "Yes"
DoCmd.RunCommand acCmdSaveRecord
Me.tbProperSave.Value = "No"
So it's setting the value to no again?
I know it's not a permissions thing because I am admin and have full permissions on the forms.
Any suggestions?
I have a split db with tables in the back end and my forms, reports, code etc. in the front end.
I encrypted the back end with a password. That worked fine.
I deleted and relinked my tables to the encrypted back end. That worked fine as well.
I have a function that will disable the shift key bypass. If I run that in my front end db then I can't save it as an accde because I can't get to the HOME screen. If I save it as an accde first then I can't run my 'disable shift key bypass' function because I can't get to the modules!
How can I secure a split Access 2010 database so that the user cannot execute shift bypass on the front end or make any changes to the code?
Is it possible for me to give them access to part or all of the database so that they can run queries to target venue mailshots etc if so how would i do this?Secondly my concern is that the database is valuable and I want to protect myself from potential theft of info, ie what stops them taking the database and using it for there own purposes?
View 2 Replies View RelatedI'm trying to secure my database so users can't edit tables, forms, reports, queries, etc.I'm splitting the database, making an ACCDE for users:
1. I inserted code to disable the bypass key.
2. I inserted code to hide the Quick Access Toolbar (QAT) in the On_Load sub of the form that opens with the DB.
3. Deselect Navigation Pane, Allow Full Menus and Allow Default Shortcut Menus are deselected
4. Then, I use the immediate window to show the QAT, I then create an ACCDE.
How do I link this ACCDE with the original ACCDB? Am I supposed to delete tables from the front end and link the forms/reports to the back end DB?
When attempting to open an Access DB that has been transferred from one network to another (via CD and aeroplane!) we are finding that the copy cannot be edited - the access is effectively read only... I believe this has something to do with Workgroup admin rights but this area is all a bit confusing for me. What do I need to do to open the copy up for editing - allowing the recipients to administrate their copy of the DB?
We're using Access 2002.
On a webserver certian files are stored above the root directory. Therefor the user of the website can never access those files directly. But he is utlizing that file.
For example:
We use Hotmail to see our mails. but we never download the complete database.
So is there a method that we can secure the backend using roaming profiles or something else so the user has a link to the backend but cant directly access the backend.
hi,
Everyone know this error? the database has been placed in state by user "Admin"!
We are using a shared database in Access 2003 that is still in Access 2000
format. When more than one user has the database open (by using the vb.net), the following error
message appears "The
database has been placed in a state by user 'Admin' on machine XXXXX that
prevents it from being opened or locked."
How to slove this error? please help! Urgent!:confused:
Hello. I have just started work at a new company and have been asked to 'Network' 5 different DB's to enable multiple users (around 5) simultaneous access (Dept A - DBa / Dept B - DBb etc.....) Currently if User A accesses the DB User B gets read only access.
I have googled and also searched forum + read some posts here but have failed to answer my questions (Spilt Database / FE BE??).
I have also played with a copy of an existing DB to add security by way of Security Groups (Managed this!!).
A desktop shortcut was created:
"C:Program FilesMicrosoft OfficeOFFICE11MSACCESS.EXE" "C:Documents and SettingsPhilip HoranMy DocumentsAccessFE BEFE BE.mdb" /WRKGRP "C:Documents and SettingsPhilip HoranMy DocumentsAccessFE BESecurity.mdw"
Could I move files and manually amend to a unc path?
Advice, links to answers very much appreciated.
Kind Regards,
Phil.
Well after nine months of intensive development I have completed my first application for distribution.
Now the hard part.
It needs to be nearly impossible for people to make unauthorized copies.
I'm thinking I would like to do some kind of internet authorization but I don’t have a clue.
It's definitely going be a stand alone product, not requiring the customer to have Access installed on their PC.
Any thoughts?
Hi
I have just put together a small database, and have used the user-level security wizzard to set up groups so i can restrict access to the database.
I have also been through all the user and group permissions and tried to set the security up so that the data entry staff can enter data but not change any of the forms or queries.
This is where i am hitting the wall.... although i have set up this protection, when i access the database under the staff name and password I am still able to view the forms and queries in design view and change them.
Can someone plaese point out where I am going wrong
Thanks very much :)
I have an application that is used by individual teachers to generate reports for central admin. It is not secured except I have locked out access to all objects, Navigation pane is hidden, etc to protect the integrity of the tool. I must also maintain this as changes are propagated. I am trying to find a way to upgrade forms, functions, queries, etc without manually having to unlock and relock every db for each school site when changes are required. I have tried importing and exporting from a master db, turning objects on and off by recognizing my password, splitting code and data (db's are on flash drives and path changes every time they are inserted, many teachers cannot handle refreshing table links), I have tried writing code to import the changes at next startup, etc. It is written on Access 2003.
View 3 Replies View RelatedIt's an Access 2003 database which I want authenticated web users to be able to write new records to.
I don't think a Data Access Page is the right way to do this, is it? If something else, e.g. ASP could someone possibly point me in the right direction with steps 1, 2 and 3 :D
I'm working on an MSAcess 2003 database and I have saved the System.mdw file to a folder on the Server and linked all users to it. When multiple users attempt to log in, they get the message
Could not use 'Admin'; filer already in use.
I have used shared system.mdw files for years with no problems. But obviously the force is not with me today.
Any help with this issue would be appreciated.
Within our client estate, there are a number of types of machines and a number of admin passwords that go with them
I need to put an agent on each of these machines but first i need to make all the admin passwords the same or add our domain group to the local administrators on that machine.
In order to do that I need to know what the admin password is for that specific machine
So a series of attempts with various different passwords and users names are required.
so something like this maybe
dim admin1 as string
dim pass1 as string
dim admin2 as string
dim pass2 as string
dim admin3 as string
dim pass3 as string
dim admin4 as string
dim pass4 as string
call Shell("psexec \192.168.0.1 -u + admin1 + -p + pass1")
if error = 1 then
call Shell("psexec \192.168.0.1 -u + admin2 + -p + pass2")
else
end if
end
Is this the best way to approach this? - Am confused? - Thank you
Hi,
One of my user is getting this error when trying to launch the DB - "Could not use 'admin'. File already in use. The DB is in shared mode and other users are using it without problem. What could be the issue?
Thanks,
About three weeks ago, my work computer crash hardcore. Windows (2000 ver) had many corrupt files in the Windows Directory and in the Registry. Because of that, IT gave me an up to date computer running Widows XP. I am still running the same version of Office (2003 Pro).
I think because I have a new machine and a different platform, I am now have issues with trying to administer the permission rights. MS Access is not recognizing me as Admin and/or Creator of the db, therefore I am not able to modify permission rights....... :eek:
Does anyone know how make this db recognize that I am the Creator of it?
Thank you in advance for your help.:cool:
Kilch
I do Exchange administration, but I use Access Databases all the time to manipulate data. That is the only way I can take care of over 25,000 mailboxes with just two people.
Here is my scenario. I have two spreadsheets. First spreadsheet has UserIDs and Names. Second spreadsheet has group mailboxes and UserIDs associated with those mailboxes. I need to send an email out using Access to each of these 500 mailbox owners. In that email it must contain the name of the group mailbox and each user's name (not ID).
Examples:
Spreadsheet 1:
User01 Name1
User02 Name2
User03 Name3
User04 Name4
Spreadsheet 2:
Mailbox1 User01, User03, User04
Mailbox2 User02, User03
Mailbox3 User01, User02, User03, User04
Outgoing emails:
Mailbox1 Name1, Name3, Name4
Mailbox2 Name2, Name3
Mailbox3 Name1, Name2, Name3, Name4
I am pretty good at Excel and Access when it comes to basic data manipulation. I know how to make Access send an email out to hundreds of customers at once. Now I just need to be able to create that form letter and plug in the names where there are UserIDs.
Anyone have a suggestion (of course you do, so just put it down!).
Thanks a whole lot in advance for your assistance in this matter. And, if you have an email problem, let me know! I can prolly fix that for ya!
Don
Hello all!
I have been searching online for the past couple of days for a thorough tutorial that aids the user to make his/her own detailed database based on example data (that does not necessarily have to be adhered to) supplied as the tutorial progresses.
As a student participating in an advanced computing class, I feel that this would benefit me as I can do as the steps ask and see how certain things are done. I am not a complete Access mug... I have a basic knowledge of the fundamentals, but stumbling accross an in-depth tutorial that details all of the standard features (tables, queries, forms etc) along with possibly some example modules would be excellent and beneficial.
All I have found so far is various snippets - "SQL basics" and "Macros: what they do and how to implement them" for example - these miss the bigger picture I am looking for. Surely a step-by-step tutorial on "Designing and working a fully-featured database from the gound up" is not that rare!
Thanks in advance for any help. :)
I have 5 columns in ms access. They are as follows:
date_from, date_to, season_price, normal_price, actual_price.
However, I want to check if the sysdate is greater than date_from and less
than date_to then the value in the actual_price should be taken from the
season price for that record else the value should be taken from normal_price.
So, i would like to know how can do this? do i need to use visual basic?
I have no idea about visual basic. So, would like the complete way to do it.
Thanks