Securing Backend Like Webserver Database And Linking Via Odbc Or Roaming User Profile
Apr 29, 2006
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.
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?
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?
Hi - I have a front end database that is linked to 2 backend databases. I'd like to add code so that after the splashscreen pops up it checks to see if the front end is linked. If not then asks the user to enter the path for the two backend databases. I need your HELP!!1
I have a back end database that i cannot access at the moment. Is it possible to set the link of the tables for the front end application based on the location that the database will be even though i can't see that location now?
I have an Access DB with a form that allows the user to select a new backend database. I can connect to the backend and then .refreshlinks but nothing on the form is updated. I have tried requiring the form but that doesn't do anything. I've tried loads of other things, refresh, recalc etc., but nothing updates the open form.
The only way I have managed to get it to work is to close the form and reopen it, then it shows the data from the newly linked backend database.
While it works, it doesn't look good but also there seems to be some problem with it because eventually it reports an error saying "cannot open any more databases" and when clicking OK comes back with "An error occurred while sending data to the OLE server (the application used to create the object" and a whole bunch of other messages.
I think it might have something to do with the fact that the form has a number of MS graphs open on it, but I'm not sure. Also, I can't track down a particular line of VBA code which causes this error.
how to update a form after changing the backend database.
I am looking to automate the process of linking my Access DB with an ODBC connection to an SQL DB with VBA (unless there's an easier way to do it?) - some sample code - if this is possible at all?
If I want to distribute a front end to connect with a MySQL back end ... do I need to set up the system DSN on each workstation that will be using the front end?
is there a way to create a profile on each person on my database, to show their contact details and whether they've made a purchase from me before, or must I literally just make a table entering each individual person and purchase?
My boss (in another job) showed me very quickly an extremely old database one of his old employees had made for him years ago containing all of his clients, each shown individually, and he was able to click through each one, and each window showed the client's name, contact details, personal details, etc. and he just had hundreds of entries for all of his clients and was able to scan through each individual one. I am 99% sure he said it was on Microsoft Access that he'd done it.I just want to monitor how many of mailing list subscribers have also made purchases and whatnot, as I do rewards for them, and it'll be much easier to view each person's profile and the orders they've made than have to scan through a table to find each separate order?
I am trying to add a profile picture to a dog database. The way I have it is that the picture is a file and gets renamed the dog_id, on the query for the form I have added the extra coloum
[code]ImageFile: "C:Gundog Training DatabasesProfilePictures" & [Dog_ID] & ".jpg"[code]
How do I link the backend tables to the front end so it works everywhere? I mean right know it says C:ewprojectackend.mdb how do i change that to newprojectackend.mdb ?
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.
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:
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?
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.
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.
When linking a table via ODBC I usually get asked to pick a field that contains unique values. This is all very well but I have hundreds of tables to link to across a slow network and would like to leave it running for a few hours. With the current set up, this is not possible and I have to keep clicking on ok every few seconds/minutes. Is there anyway to avoid this message so I can just leave it running until it finishes.
Hi all, I am a newbie to the forum and dont know much about it yet so please point me in the right direction if i am doing anything wrong..
My task is to create a spreadsheet that will automaticaly update on opening. The data i require is in Sage Line100 which is very difficult to work with(for me with limited database knowledge).
Because i need to link tables i thought that the best way would be to create a link with Access and create the report format that i need so that it could be exported to Excel.
1: Is this possible(or am in dreamland) 2: How do i refresh the data or is it possible to make it dynamic 3: how do i create a report that saves as an excel file and refreshes when opened
Thank you for any help that can be provided(bowing down to superior knowledge)
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.
I'm nearly at the point of distributing my database. I have been writing this on my laptop and I need to get it working on a Terminal Server and Data Server at the data centre that host our system which is a remote location. The Terminal Server has Access Runtime only, not the full version of Access. This obviously presents me with a bit of a quandary as I still need to be able to maintain the software but do not want my laptop to link via the web to the database to do so. My solution is to take a copy of the database onto my laptop when I need to do anything, which is not a problem. My issue is that I'd like a simple way of maintaining the link between the Front end and the Back end so that if I upload a new copy of the back end or the front-end it will not throw the toys out the pram at the users!
I understand that I am best to put the back end onto the data server and to then distribute the front end to each user. Each user has their own account on the Terminal Server so I can put the front end into their userprofile or something similar (if it is true that I can't just give them all shortcuts to the same front-end that is?!).
I would like to try and get the back end to link to the front end via ODBC as if this is the case I can use the File DNS path on the terminal Server so each user has access and can use File DNS on my laptop when I need to do any updates on my copy so that I am not having to do anything too complicated with the linking of the front and back end due to the differing file systems. Is this possible to do or am I completely going at this the wrong way and missing an easier method?
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?
I'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?
At work i have a newtwork of computers each with a frontend of my database on it and 1 computer with the backend on it, once there's about 8-10 computers using it, it wont let anymore in it saying something like unable to open, exculsively opened etc. How to i get round this?
Hello all I have a "Read only" frontend and Backend as 2 different files on the LAN shared drive. i have around 50 users logging on to this to view information (No editing allowed by the user). I read on some of the previous forums that Multiuser frontend does not work that well. I want to know if this is true even if my users are in read only mode??? I have tried with 3 users and it works with out a hitch. i just want to be sure before i roll out this to the entire 50users. thanks soni
i have my database split (backend and frontend) - both are residing on the server in different folders...users can't have it on their desktop since we're running on a Citrix server and each user only has a thin client.
Now currently I've got 3 users to the system. 2 users can use the system simultaneously without any problems...however the other user loads the front end but the backend data (bookings, despatches, etc) simply does not load - cannot access any data..
