 Top
How to avoid losing User-Level Security on the back end when splitting the database using the Database Splitter Wizard.
Q: I set up my database with User-Level Security, but when I split the database before deploying it to the users, I used the Database Splitter Wizard and now the back end is completely unsecured! Is there any way to secure both the front end and back end only once instead of having to secure the back end after splitting it?
A: Unfortunately, the Database Splitter Wizard ignores security when creating the new back end database file and importing all of the tables into this new file. It leaves your database wide open to anyone who wants to open it. The solution is to avoid using the Database Splitter Wizard altogether and split it manually. Microsoft Access MVP Joan Wild has the helpful steps necessary on her Web site.
How to be sneakier than the snoops peeking at data they shouldn't see.
Q: I have had problems with inquisitive souls looking at data they shouldn't be viewing. I thought disabling the ability to create new databases using my workgroup would stop this, but it doesn't. All they have to do now is create a new database using the default workgroup, close it and then open it again while joined to my secure workgroup, and they can import the secure tables into the new database.
Neither of the default groups, Admins and Users, have access to use anything from the frontend or backend. I have created two user accounts, one for myself with administrator privileges and a general user account with limited access permissions. I have given the general user account read permissions on all the tables they need and then controlled what they can and can't see with user-level security. None of the objects in the database can be imported, only the tables because by default, as soon as you give someone permission to read a table, they have by default, permission to read the design. Both user accounts have a password.
How do I stop these people importing from my secure front end or back end?
A: As you've already discovered, users who are given the security permissions to use an Access database have enough security permissions to dig much deeper into the database than management is willing to permit. This is a severe downside to a file-based database architecture, which Access is built with, and is why database experts don't recommend storing secure or sensitive data in an Access database.
Database experts advise using a client/server database, such as SQL Server or Oracle, for data that needs some degree of security. Of course, these solutions cost more, so management must make the decision about whether the security of the data is worth the added cost to protect the data. But the added hassles and the cost for your time to attempt to protect the data means that management really is paying extra to protect the data. This time and hassle will at some point exceed the cost of a client/server database system, so your management will need to address this in the future.
In the meantime, truly sneaky people require a "sneakier than thou" approach by the DBA to attempt to safeguard the data. You still have a few other methods available for obfuscating the data. You can use various combinations of any of the following:
1.) With user-level security applied, don't give the "general users" accounts read permissions on any tables. Give them "run with owner's permissions" (RWOP) privileges on the queries that use these tables so that users are very limited in what data they can see.
2.) Store bogus data in the "sensitive" tables in the current database file, while modifying forms, reports and modules to programmatically use the legitimate sensitive data stored in tables in a remote database file located in a hidden network shared directory, then create and distribute an MDE front end that has all code removed, which will help cover your tracks when these snoops come snooping. For example, set the form's RecordSource Property in the Form_Open( ) event to a RWOP query in the remote database, so that you can still use bound forms, but not display table names and file locations of the legitimate tables those bound forms use in the form's RecordSource Property. For more details on how to do this, see "How to use a form to update records from a table in a remote database with a database password, without linking to that table or writing VBA code for an ADO or a DAO connection, and hide that password from the user" in Q-Built Solutions' Form Tips.
Remember that the best bogus data looks plausible but is, indeed, bogus, and can convince snoops that they have their ill-gotten goods already in hand and need look no further.
3.) When moving the legitimate tables to a remote database file, rename the tables so that they are prefixed with "USys" in order to make these tables user-defined system tables. For example, rename tblSalaries to USysTblSalaries so that unless the user has "Show System Objects" checked in the database options, these tables won't even be visible in the database window nor listed in the "Import Tables" list box for the Import Wizard.
4.) Store the sensitive tables in a library database located in a hidden network shared directory, then set a reference to this library in a remote database located in a different hidden network shared directory. Use these tables programmatically from your current database for the RecordSource Property of your forms and reports, then create an MDE file of your current front end database to help cover your tracks. This method requires expert knowledge of Access and libraries to pull off successfully.
5.) Change the database file name and/or extension of the remote database to something meaningless, but untouchable. For example, change Salaries.mdb to Registry.win so that most people won't bother opening it or dare alter it if they do open it in Notepad for investigation. (Note that if someone does open an Access database file in Notepad, alters it and then saves it, this will corrupt the database, so this is yet another reason to make sure that you have regular backups.) Windows file associations don't use this extension for an Access database, so the visible Access database icon for the file will be missing in Windows Explorer to help obscure the database from snoops.
6.) In the special situation where your snoops are importing the sensitive tables after regular business hours when other employees have gone home, move the sensitive tables to a remote database (db_A.mdb), then link to those tables from another remote database (db_B.mdb). Set the system hidden attribute on the links (see http://www.mvps.org/access/bugs/bugs0036.htm for the code) in db_B.mdb. When the db_B.mdb database is compacted, the links will be completely removed. (Make sure that this hidden attribute is only used for the links, not the tables themselves, because a database compaction will remove whatever TableDef object has this attribute set.) Have your current database use the linked tables in db_B.mdb (preferrably programmatically as record sources for forms and reports in an MDE file to help cover your tracks), and at the end of each work day, compact the db_B.mdb file. At the beginning of each work day, have a trusted employee who knows where the db_A.mdb file is located relink the tables from the db_B.mdb file to the db_A.mdb file, so that users can continue to use the linked tables.
Name both of these remote database files something that won't draw suspicious eyes, like LandscapingSchedule.mdb or CatLoversAssocDues.mdb, and put plausible data in the tables of these database files, but hide your sensitive tables with the "USys" prefix mentioned previously to help hide the tables from someone doing a cursory investigation to convince them that there's nothing of interest in the database file. (Of course, that's how these files can get deleted by Windows Administrators doing housecleaning to get rid of unnecessary files, so let Administrators know that you're obscuring the name of the database, but valuable data exists within it.)
This last method has limited applicability and requires a lot of extra work so that it's transparent to the database users, so it isn't really recommended, but it may give you some other ideas about obscuring linked tables.
Top
How to connect to an Access database with ADO after it has been secured with user-level security. See Q-Built Solutions' VBA tips.
Top
How to connect to an Access database with ADO after it has been secured with shared-level security. See Q-Built Solutions' VBA tips.
Top
How to determine what security permissions have been assigned to all users and groups.
Q: I have inherited an Access database that has been secured. Is there an easy way to find out each user's and each group's permissions?
A: Yes. Use the built-in Access Documenter to create a report. Select the Tools menu -> Analyze -> Documenter to open the Documenter dialog window. Select the "All Object Types" tab. Select the "Select All" button to check all objects.
Highlight one of the table names. Select the "Options" button to open the "Print Tables Definition" dialog window. Uncheck the "Properties" and "Relationships" check boxes and select "Nothing" options for both the "Include for Fields" and "Include for Indexes" sections. This will leave the "Permissions by User and Group" as the only item to be documented. Select the "OK" button to save these changes.
Highlight a query name and follow the above steps to document only the "Permissions by User and Group" for the queries. Do this for every type of database object.
Next, uncheck the "Relationships" and "Properties" check boxes on the "Current Database" tab.
Select the "OK" button to create the report of User and Group permissions of all objects in the database.
Top
How to determine whether a user is a member of a specific group.
Q: I have secured an Access database with user-level security. I want to prevent some of the users from editing certain fields on one form, unless they are members of the Admins group. How do I determine whether the current user has permission to edit these certain fields? All users have security permissions to open this form, but I want to unlock these fields only for members of the Admins group.
A: You can use the built-in CurrentUser( ) function and a user-defined function to determine whether this user is a member of a specific group.
'=======================================
' Function: isMemberOfGrp( )
' Author: Q-Built Solutions; www.QBuilt.com
' Date: 19 Sept. '04
' Function determines whether a user is a member
' of a specific group.
'
' Returns True if the user is a membe of the group.
' Usage: isMemberOfGrp(CurrentUser(), "Admins")
'=======================================
Public Function isMemberOfGrp(sUserName As String, sGrpName As String)
On Error Resume Next
Dim junk As String
junk = DBEngine(0).Users(sUserName).Groups(sGrpName).Name
'-------------------------------------------------
' Determine whether checking this Property
' caused an error or not.
'-------------------------------------------------
If (Err.Number = 0) Then
isMemberOfGrp = True
Else
isMemberOfGrp = False
End If
Err.Clear
End Function
Top
How to import objects into a database secured with shared level security from another database.
Q: I need to update the front end of the database, but if I replace the complete file, it will overwrite the objects that the users have created themselves, such as queries. I can use the TransferDatabase method to import objects into the front end from my file, but the front end has a database password, which the TransferDatabase method doesn't provide for. Is there a way to import the new objects from my file into the existing front end?
A: Yes. You must have exclusive access to the front end to import the objects. Paste the following two procedures into a module in the database where the new objects are located, and modify the items in red font with your own. If you are using Access 97, comment out the sSrcDBPath = CurrentProject.FullName line and uncomment the 'sSrcDBPath = CurrentDb().Name line. Save and compile the code, then run it.
Public Sub importObjIntoDBwPswd()
On Error GoTo ErrHandler
Dim sDestDBPath As String
Dim sSrcDBPath As String
Dim sPswd As String
Dim sFormName As String
Dim sTableName As String
'-------------------------------------------------
' Replace items in red font with your path and
' file name, password, and object names.
'-------------------------------------------------
sDestDBPath = "C:\Data\MyDB.mdb"
sPswd = "myPswd"
sSrcDBPath = CurrentProject.FullName ' For Access 2K & later.
'sSrcDBPath = CurrentDb().Name ' For Access 97.
sFormName = "frmNewForm"
sTableName = "tblNewTable"
Call importIntoDBwPswd(sDestDBPath, sPswd, sSrcDBPath, acForm, _
sFormName, sFormName)
Call importIntoDBwPswd(sDestDBPath, sPswd, sSrcDBPath, acTable, _
sTableName, sTableName)
Exit Sub
ErrHandler:
MsgBox "Error in importObjIntoDBwPswd( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
End Sub
Public Sub importIntoDBwPswd(sDestDBPath As String, sPswd As String, _
sSrcDBPath As String, bObjType As Byte, sSrc As String, _
sDest As String)
On Error GoTo ErrHandler
Dim accApp As New Access.Application
accApp.OpenCurrentDatabase sDestDBPath, True, sPswd
accApp.DoCmd.TransferDatabase acImport, "Microsoft Access", _
sSrcDBPath, bObjType, sSrc, sDest
CleanUp:
accApp.Quit
Set accApp = Nothing
Exit Sub
ErrHandler:
MsgBox "Error in importIntoDBwPswd( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp
End Sub
Top
How to open databases and only be prompted for User ID and password for the secure databases.
Q: I have multiple databases that I use, but only one of them is secured. Each time I open a database, even if it isn't secured, I have to supply a User ID and password to open it. Is there a way to open these unsecured databases without being prompted for my User ID and password, but still be able to open my secured database?
A: Yes. To make it easy in this situation, most folks stay joined to the default workgroup (so that they don't have to enter a User ID and password every time they open an Access database) and just use a shortcut to open the secured database with the workgroup file so that the secure workgroup is only used with the secured database. The syntax for such a shortcut would be:
"<Full path to Office>\MSAccess.EXE" "<Full path to DB>\MyDB.MDB" /wrkgrp "<Full path to secure workgroup>\Secure.MDW"
To rejoin the default workgroup, search for the "System.MDW" file on the hard drive. There may be more than one of these files, but the default workgroup file should be in the Windows System directory or in a directory in the user's profile, depending upon the operating system. Use WRKGADM.Exe again to join that workgroup. The user will not be prompted the next time he opens a database, unless the Admin user for this workgroup also has a password set.
If the default workgroup has the password set for the Admin user, then the password can be removed. To do so, join the default workgroup and open any database as the Admin user and select the Tools menu -> Security -> User and Group Accounts... to open the User and Group Accounts dialog window. Either select the "Clear Password" button to clear the Admin user's password or select the "Change Logon Password" tab and type the Admin user's current password in the "Old Password:" text box, and then leave the other two new password text boxes blank. Select the "OK" button to save the change.
Top
How to set the macro security level in Access 2003 runtime. See Q-Built Solutions' VBA Tips.
Top
How to set the user-level password for another user.
Q: I have applied user-level security to an Access database. I have also created several User ID's for the workgroup, but I can't set the password for any user except the user I'm logged in as. Is there a way to set other users' passwords before I deploy the database so that these users won't have blank passwords?
A: Yes. There are two ways: join the secure workgroup, then open a database and when prompted, type the user's User ID and no password (since one hasn't yet been assigned), and use the "User and Group Accounts" dialog window to set the user's password, or write VBA code that opens a database with the appropriate workgroup information file and the user's User ID, then changes (creates) the password for the user, then closes the database.
Top
How to use a form to update records from a table in a remote database, without linking to that table or writing VBA code for an ADO or a DAO connection. See Q-Built Solutions' Form Tips.
Top
How to use a form to update records from a table in a remote database with a database password, without linking to that table or writing VBA code for an ADO or a DAO connection, and hide that password from the user. See Q-Built Solutions' Form Tips.
Top
How to use Jet SQL to insert records into a remote database that has a database password. See Q-Built Solutions' Query Tips.
Top
How to use Jet SQL to update records in (or from) a remote database that has a database password. See Q-Built Solutions' Query Tips.
Top
Why Social Security Numbers should not be stored in an Access database.
Q: I want to use employees' Social Security Numbers as the primary key in my Employees table. I've been warned not to, but all of our employees have Social Security Numbers and these are all unique. Why shouldn't I use their SSN's as the natural key for this table?
A: Storing people's SSN's in an Access database can get your company into legal hot water. Anyone determined enough can steal those SSN's and any associated Employee ID's, names, et cetera, that are stored in the Access database. (Even if there are no names stored in the database, the Employee ID's can probably be cross-referenced in other databases or on paperwork with employee names.) If this person doesn't have the skills, it doesn't matter because the tools are available very inexpensively.
Think it can't happen to your company? The latest surveys show that 27 million Americans have been victims of identity theft. 53 million of the 120 million Americans who use the Internet regularly now bank online, so those numbers are expected to increase as con artists get more sophisticated in their electronic schemes. Don't give a disgruntled employee or an outside hacker the opportunity to steal anyone's SSN. The chances are too high that someone eventually will.
Furthermore, even if security wasn't an issue, SSN's are not guaranteed to be unique, nor are they universal, so their use as a primary key has flaws. Please see the article, "SSN FAQ Addendum" by Chris Hibbert of the Computer Professionals for Social Responsibility organization for more information.
Copyright © 2004 - 2006 Q-Built Solutions. All rights reserved.
Top
|
