Frequently Asked Questions About Microsoft Access Security for Microsoft
Access versions 2.0 through 2000
Version 2.42 October 2000
By Mary Chipman, Andy Baron, Chris Bell, Michael Kaplan, Paul Litwin, and
Rudy Torrico
Introduction
This FAQ was written originally to cover Microsoft Access versions 2.0
through 97. However, the basic concepts remain unchanged for Access 2000. Almost
all of the FAQ items that apply to Access 97 also apply to Access 2000. If you
have any questions or comments after reading the FAQ, please post them in the
access.security section of the Microsoft public newsgroups (msnews.microsoft.com)
or contact Microsoft Access Technical Support.
Table of Contents
- What are the steps to help protect a database?
- In a nutshell, how does Microsoft Access security
work?
- What has changed in Microsoft Access security
between Microsoft Access 2.0, 95, 97, and 2000?
3.1 Table 1: DAO Security Constants
- How can I set a single password on my database?
4.1 Database Password Bugs
- How can I clear a user's forgotten password?
- What's the best way to convert my
security-enhanced Microsoft Access application to the latest version of
Microsoft Access?
- What's all this about a security hole in
Microsoft Access 2.0?
- How can I help protect just my code without users
having to log on
- How do I delete the Admin user?
- How do I implement field-level or row-level
security on my tables (RWOP or queries with Run Permissions set to Owner's)?
- Why can't I get my RWOP Append Query to Run?
- What is the difference between an "attached"
table and a "linked" table?
- How do I manage linked tables using Microsoft
Access security?
- What permissions are necessary to update table
links?
14.1 Using RefreshLink to relink tables
14.2 Using TransferDatabase to relink tables
14.3 No Permissions necessary - Using the .Connect
property to relink tables
14.4 Error messages
- Why do users require permission to create new
tables in the destination database in order to update a table attachment?
- What happens when the front-end database
permissions on an attached table differ from those in the back-end database?
16.1 Creating New Links
16.2 What permissions should you set for <New
Table/Queries>?
16.3 Updating or Refreshing Existing
Links/Attachments
16.4 Synchronizing Permissions
- How can I retrieve the "most restrictive"
permissions for attached tables?
- Can I prevent users from linking tables?
- How do I work with a security-enhanced
application and an unsecured application at the same time?
- How do I keep users from viewing Code Behind
Forms?
- How can I tell who is logged on to my shared,
networked application?
- How can I obtain group and user membership
information programmatically?
- How can I obtain the groups that the current user
belongs to without hard-coding an Admins ID and password in the code?
- How can I help prevent users from creating new
objects in my database?
- How can I help prevent users from updating any
tables by any means other than through forms?
- How can I help protect some parts of my
application (an add-in), yet make others totally open to any Microsoft Access
user?
- How do I prevent users from holding down the
SHIFT key to bypass the AutoExec macro?
- How do I prevent a run-time application from
being opened in full retail Microsoft Access?
- Does Microsoft Access security still work if I
use OLE automation or Microsoft Query to manipulate Microsoft Access tables?
- How can I use the Security Wizard without
creating an encrypted database?
- When I use the Security Wizard in Microsoft
Access 2.0, it runs to 99%, and then freezes
- I thought I helped protect the security of my
database, but someone opened it with his or her own workgroup file. Is
Microsoft Access security broken?
- I want to create a remote site administrator able
to administer the database and add user accounts but not alter permissions on
database object
- How can I "de-secure" a database?
- I lost/forgot my password and can't get into my
database
- Do I need a separate workgroup file for every
database I develop for my department?
- How do I use DAO to manipulate permissions?
- I created a user in code but the user isn't in
the Users group and can't start Microsoft Access.
- I created a user and I can't log on as that user
- I ran the Security Wizard but users from another
workgroup can still open the database
- How do I help implement security when I am using
Visual Basic as a front-end?
- Do I need to use a System.mda when I'm using
Visual Basic to control security-enhanced objects?
- How do I open a password-protected database from
Visual Basic?
- How do I open a report in a security-enhanced
Access database from Visual Basic?
- What about using ADOX or ADO to programmatically
manage security?
- How can I open a database in code that was
encrypted using another workgroup file?
- Additional Sources of Information
The process to help protect a database is the same, no matter which version
you use. The only differences are: beginning with Microsoft Access 95, the
Security Wizard is built into the product and in Access 2000 the Security Wizard
can perform all of the steps for you, including creating a new workgroup
information file. You may elect not to use the Security Wizard and to help
protect the database manually by following these steps.
- Use the Workgroup Administrator program (Wrkgadm.exe) to create a new
workgroup information file. Write down the Name, Organization,
and WorkGroup ID strings that you will be prompted for when you create
your new workgroup information file and store them in a safe place. If your
workgroup information file ever becomes lost or corrupted, you can reconstruct
it by using these identical strings, which are then encrypted to create a
unique token. Without a valid workgroup information file, you could
conceivably be locked out of your database forever. Another reason to save
this information is for upgrading an encrypted Access database to a newer
version of Access. The recommended path for upgrading databases is to
re-create the workgroup file in the new version of Access before upgrading the
database itself.
- The Workgroup Administrator automatically switches you to the new
workgroup information file. Start Access, and open any database.
- You will be logged on as a user named Admin. Use the Security menu
options to add a password for the Admin user. The Admin user is the default
account, and setting its password is what causes Access to prompt for a logon
Name and Password the next time that you start Access.
- Create a new user, which is the account you will use to help protect the
database. Add this new user to the Admins group. Write down the strings that
you use for the name and PID in case you ever need to re-create your workgroup
information file. The PID is not the password-the string used for PID is
encrypted, along with the string used for the Name, to create a unique token
(SID, or system identifier) identifying the user.
- Quit Microsoft Access and log back on as the new user account that you
created in step 4. You will not have a password for this account yet, (the PID
you typed with the name in step 4 is not the password), so now is a
good time to set one.
- Remove the Admin user from the Admins group so that Admin is a member only
of the Users group. The Admin user account has no administrative powers built
into it; they are derived from membership in the Admins group, which does.
Although you cannot delete any of the built-in users or groups (Admin, Admins,
and Users), you can move users to and from the Admins group and restrict
permissions to the Users group.
- Open the database that you want to help protect and run the Security
Wizard. Select the objects that you want to help protect (it makes sense to
select them all). The wizard will then create a new database owned by your new
user, and will import all of the objects and relationships into it. It will
also remove all permissions from the Admin user and the Users group and
encrypt the new database. The original database will not be altered. Note that
the Access 2000 security wizard does not create a new database-it simply
creates a backup copy of the original. One flaw with this arrangement is that
not all permissions to open the database are removed from the Admin user and
Users group to open the database, even though they appear to have been
removed.
- Open the new database. Because the Security Wizard removed all permissions
from the Users group for the security-enhanced objects, you need to create
your own custom groups and assign the level of permissions needed to these
groups. Every user is required to be a member of the Users group (otherwise, a
user would not be able to start Microsoft Access), so only grant permissions
to Users that you want everyone to have. Members of the Admins group have
irrevocable power to administer database objects, so make sure to limit
membership in the Admins group to only those users who are administrators.
- Create your own users and assign them to the groups that reflect the level
of permissions that you want them to have. Do not assign permissions directly
to users because that is extremely hard to administer. Users inherit
permissions from the groups they are members of, and keeping track of the
permissions assigned to a group is much easier than keeping track of the
separate permissions of individuals. If a user is a member of multiple groups,
then that user will have all of the permissions granted to any of those groups
plus any permissions assigned specifically to the user (this is known as the
"least restrictive" rule). There is no way to deny permissions to a user if
that user is a member of a group that has been granted those permissions. If
you need to create specific permissions for only a single user, create a group
for that user and assign the permissions to the group; then, add the user to
the group. The reason for this becomes clear when you consider that the user
may quit, and you may have to set up permissions for the replacement on short
notice.
- Additionally, you may need manually to remove the Open/Run
permission from the database container for the Users group through the
security menus or through code. This will prevent someone from opening the
database by using another workgroup information file or the default System.mda/mdw.
In Microsoft Access 97, the User Level Security Wizard is supposed to remove
the Open/Run database permissions for the Users group, but fails to do
so. The Access 2000 Security Wizard removes permissions to the point where
they are not visible on the security menus, but testing has revealed that in
Access 2000 it is possible to open a database by using the default workgroup
information file regardless of the menu settings. The cure for both versions
of Access is to create a new, empty database while logged on as a member of
the Admins group and import all of the objects from the security-enhanced
database. You should take this step before spending too much time helping
protect objects because Access considers imported objects to be "new" and
loses the permission information that was stored in the source database.
The following table lists the default names and locations of the workgroup
file and the Workgroup Administrator program.
| Version |
Workgroup File Name (default) |
Workgroup File Location |
Wrkgadm.Exe Location |
| 2.0 |
System.mda |
C:\Access |
C:\Access |
| 95 |
System.mdw |
C:\MSOffice\Access |
C:\MSOffice\Access |
| 97 |
System.mdw |
C:\Windows\System |
C:\Windows\System |
| 2000 |
System.mdw |
\Program Files\Common Files\System |
\Program Files\Microsoft Office\Office\1033
Note: 1033 is the default folder for the English version of Access |
Return to Top of Page
The Microsoft Jet database engine, which Microsoft Access uses to store and
retrieve its objects and data, employs a workgroup-based security model. Every
time the Jet database engine runs, it looks for a workgroup file, which holds
information about the users and groups of users who can open databases during
that session. Any valid file name can be used, such as Wrkgrp_Sec.mdw.
The workgroup file contains the names and security IDs of all the groups and
users in that workgroup, including passwords. There are built-in groups (Admins
and Users) and a generic user account (Admin) that every workgroup contains by
default. The built-in group Guests and user account Guest, which are included in
Microsoft Access 2.0 only, can be ignored. You can add new groups and new user
accounts using Microsoft Access menus or through code.
The Admins group is always present and its users have Administer rights that
cannot be revoked. You can remove rights from the Admins group through the menus
or through code, but any member of Admins can assign them right back. There must
always be at least one member in the Admins group to administer the database.
The default user account, Admin, always starts out as a member of the Admins
group and is the account that everyone logs on as by default in an unsecured
database. The other built-in group, Users, is a generic group to which all
users must belong, no matter which other groups they belong to. It is
possible to create a user through code, but that user is not automatically added
to the Users group. If you do not take the extra step to add the person to the
Users group, the person will not be able to start Microsoft Access because many
of the tables that Microsoft Access uses internally are mapped to the
permissions of the Users group. Neither the Admin user account nor the Users
group has any built-in permissions (as the Admins group does).
Securing a database involves adding a new member to the Admins group and
removing the Admin user from that group, removing permissions from the Admin
user and from the Users group, and assigning permissions to the custom groups
that you define.
Permissions to various objects in Microsoft Access can be assigned directly
to users (explicit permissions) or to groups. Users inherit permissions from the
groups they belong to (implicit permissions). Microsoft Access employs the
"least restrictive" rule: users have the sum total of their explicit and
implicit permissions. In other words, if a user belongs to a group that has full
permissions and you make that user a member of a group that has restricted
permissions, the user will still have full permissions because he is still a
member of the unrestricted group. Although Microsoft Access allows you to assign
permissions directly to users, this is not recommended. Administering your
database can become very difficult if you do.
User and group information, including passwords, is saved in the workgroup
file, or System.mda/mdw, which validates user logons at startup. Permissions to
individual objects are saved in the database itself. You can give the groups and
users within a workgroup various levels of permission to view, modify, create,
and delete the objects and data in a database. For example, the users of a
particular group might be permitted to read only certain tables in a database
and not others, or you could permit a group to use certain forms but not to
modify the design of those forms.
Setting a password for the default Admin user account activates the logon
dialog box so that users will be prompted for a valid user ID and password each
time that they start Microsoft Access. If you never set a password, all users
will be logged on as the Admin user (with no password) and you will never see
the logon dialog box when starting Microsoft Access. So even though it may
appear that there is no security present, it is just transparent until you set a
password on the Admin user account.
The database password was introduced in Microsoft Access 95. This is a simple
password on the database itself that allows only users who know the password to
open the file. You cannot assign permissions to users or groups with this
feature. In addition, the database password feature is not considered to be very
secure. See Section 4, "How can I set a single password
on my database?" for more information about the database password feature.
Return to Top of Page
- Since Microsoft Access 95, the Security Wizard is included as part of the
Microsoft Access product. The Security Wizard assists you in creating a
security-enhanced application out of an unsecured one.
- Users can retrieve group membership information even if they are not
members of the Admins group. This was very difficult to do in Microsoft Access
2.0 and required some workarounds. See Section 23,
"How can I obtain group and user membership information programmatically?"
- In Access 95, the dbSecReadSec constant was added to permit users
to retrieve security information for an object. Without this setting, attempts
to retrieve permissions are rejected. The Access 2.0, equivalent of this
constant is DB_SEC_READSEC.
- Since Microsoft Access 95, permissions are no longer reset to <New
Tables/Queries> when a RefreshLink is performed against linked
(attached) tables.
- The minimum permissions for a linked table require Modify Design
permission on the front-end table (not the back-end base table). In Microsoft
Access 2.0, Read Design Permissions were sufficient.
- Since Microsoft Access 95, there is a new database password feature, which
allows you to set a single password on a database file. Anyone in the Admins
group or the database owner, who has opened the database exclusively, can set
or reset this password.
- Beginning with Microsoft Access 95, the default workgroup information file
is given the name, "System.mdw", not "System.mda". This helps distinguish it
from wizard and library databases, which still have the .mda extension.
- A bug with RWOP append queries in 2.0, which required implicit Insert Data
permissions for the current user, has been corrected.
- The intrinsic constants that you use in security have changed, although
they are similar enough once you get used to the format. Table 1 shows
Microsoft Access 2.0 security constants and Microsoft Access 95 through 2000
security constants.
- In Access 2000, modules can be secured by using the VBA (Visual Basic&#reg;
for Applications) password, which is set from within the Visual Basic Editor.
On the Tools menu, click <project name> Properties, and then
click the Protection tab. User-level security in Access 2000 cannot
prevent someone from viewing and editing your modules and code behind forms in
the VBE. With a VBA password you can help prevent users from viewing the
project's properties and the code in any standard/class module as well as code
behind forms. However, your best protection against someone viewing or editing
your code is to convert your MDB to the MDE file format.
Access 2000 has added a new permission to Tables: the ability to edit
AutoNumber columns. Although no constant is defined for this permission, you
can define your own in code:
Private Const dbSecModifyAutonumber = &HFF00
When a table is created in Access 2000 or an earlier-version database is
converted, this permission will not be set, by default. The Access 2000 security
user interface does not expose it, and neither does ADOX; the only way to set it
is through DAO security code using the above constant definition. The Access
2000 Security Wizard does set this permission for several of the custom groups
it creates.
Note This permission does not allow you to ignore referential
integrity; if you do not have cascading updates enabled in an enforced
relationship, then the update will fail. You should also be careful about when
you use it because it can affect what value Access will use for the next record
that is inserted into the table. To change this yourself, you can use ADOX to
modify the Seed property of the AutoNumber column. (For more information
see the white paper, "Migrating from DAO to ADO," listed in Section 47.)
|
DAO Security Constants |
|
All Container/Document objects: |
Access 2.0 |
Access 95/97/2000 |
|
No permissions on object |
DB_SEC_NOACCESS |
dbSecNoAccess |
|
Full permissions on object |
DB_SEC_FULLACCESS |
dbSecFullAccess |
|
Can delete object |
DB_SEC_DELETE |
dbSecDelete |
|
Can read an object's security-related information |
DB_SEC_READSEC |
dbSecReadSec |
|
Can edit an object's permissions |
DB_SEC_WRITESEC |
dbSecWriteSec |
|
Can change the Owner property of an object |
DB_SEC_WRITEOWNER |
dbSecWriteOwner |
|
Table Container/Document objects: |
|
|
|
Can create new tables / queries |
DB_SEC_CREATE |
dbSecCreate |
|
Can read table definitions |
DB_SEC_READDEF |
dbSecReadDef |
|
Can modify or delete table definitions |
DB_SEC_WRITEDEF |
dbSecWriteDef |
|
Can read records |
DB_SEC_RETRIEVEDATA |
dbSecRetrieveData |
|
Can add records |
DB_SEC_INSERTDATA |
dbSecInsertData |
|
Can edit records |
DB_SEC_REPLACEDATA |
dbSecReplaceData |
|
Can delete records |
DB_SEC_DELETEDATA |
dbSecDeleteData |
|
Database Container/Document |
|
|
|
Can create new databases (valid only on the Database Container object in the
workgroup file, System.mda/mdw) |
DB_SEC_DBCREATE |
dbSecDBCreate |
|
Can replicate database and/or change database password |
N/A |
dbSecDBAdmin |
|
Can open the database |
DB_SEC_DBOPEN |
dbSecDBOpen |
|
Can open the database exclusively |
DB_SEC_DBEXCLUSIVE |
dbSecDBExclusive |
|
Access Security Constants |
|
All Container/Document objects: |
Access 2.0 |
Access 95/97/2000 |
|
Can execute form/report |
DB_SEC_FRMRPT_EXECUTE |
acSecFrmRptExecute |
|
Can read the design of form/report |
DB_SEC_FRMRPT_READDEF |
acSecFrmRptReadDef |
|
Can edit the definition of form/report |
DB_SEC_FRMRPT_WRITEDEF |
acSecFrmRptWriteDef |
|
Can execute macro |
DB_SEC_MAC_EXECUTE |
acSecMacExecute |
|
Can read macro definition |
DB_SEC_MAC_READDEF |
acSecMacReadDef |
|
Can edit macro |
DB_SEC_MAC_WRITEDEF |
acSecMacWriteDef |
|
Can read module definition |
DB_SEC_MOD_READDEF |
acSecModReadDef |
|
Can edit module |
DB_SEC_MOD_WRITEDEF |
acSecModWriteDef* |
* In Access 2000, this permission has some effect in the Access user
interface (you cannot use the OutputTo command, for example) but is not
supported by VBE. Therefore modules cannot be considered to be secured by Access
in any real sense. To help bolster Access 2000 code security, you will need to
look into the VBA password, discussed in Section 3.10 earlier in this paper.
Return to Top of Page
Microsoft Access 2.0: There is no database password feature in
Microsoft Access 2.0, so you need to help protect your application by following
the steps discussed in Section 1 earlier in this paper. After you are finished,
assign the Users group permissions for the objects that you want your users to
be able to access. Distribute the password for the Admin user that you have
defined. Make sure that the Admin user is not in the Admins group. In Microsoft
Access, users can reset their own passwords, and if someone resets the password
for the Admin account and then forgets it, you won't have any way of clearing
the password unless you can log on to the database as a member of Admins and run
code to clear it. See Section 5, "How can I clear a
user's forgotten password?", for the necessary code.
Since Microsoft Access 95, Microsoft Access has supported share-level
security with a database password. You can find this feature under Tools|Security|Set
Database Password. In order to set the database password, you must be a
member of the Admins group or the database owner, and have the database open
exclusively. The database password is not supported in a replicated database.
One danger is that if you set a password and then forget it, you (and everyone
else) can be locked out of the database.
Microsoft Access 95: There is a bug in the database password feature,
in Microsoft Access 95 only. If you set a password, such as "My Password", that
contains a space in it, and then you compact the database, the next time that
you try to open the database, it will fail. The process of compacting the
database will truncate the database password at the space. Your new password
will be "My". More information about this problem can be found in the Microsoft
Knowledge Base article, ACC95: DB
Password with Space Becomes Invalid After Compact (Q152760). This bug has
been fixed in Microsoft Access 97.
Microsoft Access 95 and Microsoft Access 97: A second bug has recently
emerged that affects both Microsoft Access 95 and Microsoft Access 97 databases.
If you use a backslash (\) character in the password, you will be unable to open
the database using the password. The workaround is to type two backslashes (\\)
instead of one (\). In other words, if your password is "My\Password", typing
"My\\Password" will open your database.
Return to Top of Page
TheDAO method, NewPassword, can be used in a VBA procedure to set and
clear passwords. It takes two arguments: the old password, and the new password.
If you are a member of the Admins group or the owner of the database, you can
run NewPassword on any user and pass in empty quotation marks ("") for
either the old password or the new password arguments. If you are not one of
these accounts, you can only run NewPassword to change or clear your old
password, and you must supply the old password argument for it to take effect.
The following code will clear or change a user's password. There is no error
trapping; you will need to make sure that you pass in a valid user name and that
the string for the password does not exceed 14 characters.
Function faqChangePassword (ByVal strUser As String, _
ByVal strPwd As String) As Integer
Dim ws As Workspace
Dim usr As User
Set ws = DBEngine.Workspaces(0)
Set usr = ws.Users(strUser)
usr.NewPassword "", strPwd
End Function
You can change the database password by using the
NewPassword method on the Database object instead of a User
object, but you have to know the old password.
Return to Top of Page
The best way to convert your security-enhanced Microsoft Access application
is possible only if you remembered to write down your Workgroup ID (WID) and
Personal ID's (PID) when you created your original security-enhanced workgroup
information file. You can create a new workgroup file in the latest version of
Microsoft Access by inputting those exact strings. Then, when you convert your
database, you are ready to go. If you can't re-create your workgroup information
file, you can run a Microsoft Access database against an earlier version's
workgroup file. However, you will be unable to take advantage of any new
functionality that may have been added to user-level security in the latest
version of Microsoft Access. Your best bet is to "de-secure" the application in
the older version of Microsoft Access before converting it (grant full
permissions to the Users group and put the Admin user back in Admins, clearing
its password), as mentioned in Section 36, "How can I
'de-secure' a database?" Convert the de-secured application, and then
re-secure it in the current version of Microsoft Access, using the steps
discussed in Section 1, "What are the steps to help
protect a database?"
Return to Top of Page
The security hole in Microsoft Access 2.0 has been widely discussed on the
Internet. It exists in the user interface and does not involve complicated tools
or esoteric knowledge - many people have stumbled upon it accidentally. Someone
who knows about it can very easily get into your forms, reports, macros, and
modules. They can't use the security hole to get at your tables and queries if
you have removed all permissions from them and use queries that have
their RunPermissions property set to Owner's (the default is
User's) in order to access your data (see Section 10,
"How do I implement field-level or row-level security
on my tables (RWOP or queries with Run Permissions set to Owner's)?").
Unfortunately, your code is completely vulnerable, so if you have used the
technique of embedding an Admins account name and password in your modules in
order to perform certain operations, you may want to consider another approach.
It is important to note that this particular security hole is only available
to those users who have a valid logon ID and password and permission to open the
database. It has been plugged since Microsoft Access 95.
Note It is not humanly possible to adequately secure a desktop
database against everyone who has the determination, knowledge, and tools. There
are security holes in Access 95 - 2000 as well, so simply upgrading to a newer
version is no guarantee against hackers. If your security needs are such that
you require stronger security-enhancements, you are better off looking at a
server database such as SQL Server on a locked-up installation of Windows NT&#reg;
or Windows 2000&#reg;.
This works by using two separate workgroup information files: one for
development and securing your database, and one for distribution. You can even
use the default System.mda/mdw for distribution.
- Help protect your database completely by following the steps discussed in
Section 1, "What are the steps to help protect a
database?"
- Make sure that all permissions to modules are revoked for the Users group
and the Admin user. (If you have used the Security Wizard, this is already
taken care of.)
- Grant full permissions to the Admin user and the Users group for all the
objects that you want everyone to be able to use.
- Distribute your application using the default workgroup information file.
Because there is no password assigned to the Admin user in the default
System.mda/mdw, everyone logs on as Admin and everyone has only those
permissions you have assigned the Admin user and the Users group.
When you need to make modifications to your application, you need to switch
to your development workgroup database and log on as the owner of the database.
Beginning with Microsoft Access 97, a database can be converted to an .mde
file. This removes all editable code from the database and makes the design of
forms, reports, and modules inaccessible to users. It has no effect whatsoever
on data or on linked tables. The account creating the .mde file must own or have
Administer permission to the source .mdb file.
The answer is, you can't. This is a common bit of misinformation that has
been perpetuated in several books, starting with the release of Microsoft Access
2.0. In Microsoft Access 1.x, you could delete the Admin user, but not since.
Because the Admin account is a default account, which is identical across all
Microsoft Access installations, you do need to remove it from the Admins group
as part of the process of helping protect the database. You want Admin to be
merely another member of the Users group. You then remove all permissions from
the Users group, so Admin inherits nothing (you assign permissions only to your
custom groups). If you neglect this step, your database will never be secure.
To understand how the Run permissions work you must understand the concept of
ownership in Microsoft Access. The user who creates or imports an object in
Microsoft Access becomes the owner of that object and has full permissions to
administer that object. For example, if I create a query that draws on certain
tables, then I must have rights to the data in those tables or I would not be
able to create, run, and save the query. The RunPermissions property in
the query is by default set to User's, which means that the query will
run with whatever permissions the logged-on user has on the base tables that
make up the query. If I change the RunPermission property to Owner's,
I allow users to run the query as though they were logged on as me, the owner of
the query. The query will run with the permissions of the query owner, rather
than with the permissions of the logged-on user. Such queries are often referred
to as RWOP queries (Run With Owner's Permissions).
The RunPermissions property applies only to saved queries. You cannot
use it with SQL strings defined in your module code by the simple expedient of
typing WITH OWNERACCESS OPTION in the string. The reason for this is that
security is defined on saved objects only by setting certain bits on them. Your
module code has no way of knowing who you want the owner to be in this situation
because there is no saved object to read those bits from. You can use RWOP
queries to grant partial access to tables by restricting either the columns or
the rows returned by the query. Therefore, if you have a Salary column in your
payroll table, you can design a query for your users to run that returns all the
columns except Salary. If you wanted your users to see all salaries except
managers salaries, you could design a query that restricted the rows returned by
using a WHERE clause that excluded those with a managerial job
classification. The best way to implement this is to remove ALL permissions from
the underlying table(s) and use only queries to get at the data you want your
users to have. You then grant the appropriate level of permissions to the groups
or users only on the query itself, allowing them only to view data or to be able
to modify it through your query.
Note There are other issues to be considered when using RWOP queries
with attached tables. See Section 11, "Why can't I get
my RWOP Append Query to Run?", Section 13, "How do
I manage linked tables using Microsoft Access security?", and Section 14.1,
"Using RefreshLink to relink tables" for more
information.
Action (append, update, or delete) queries usually only require Read Data
permissions for the query object itself. The type of action query conveys the
permission intention. For example, it would have just been a nuisance to demand
that the Update Data permissions be set separately for an update query.
For RWOP queries, table permissions for the actual append, update, or delete
operation are governed by those assigned to the query owner (Insert Data, Update
Data and Delete Data). The permissions of the RWOP query's user on the target
tables are not considered at all. In fact, the query's user will normally have
no permissions on the RWOP query's source or target table(s). However, Microsoft
Access 2.0 append queries are an exception. They also require Read Data and
Insert Data permissions on the table, in addition to Read Data permission on the
append query. In the case of a linked table insert, the user needs these
permissions on both source database- and destination-database table objects.
Without those permissions, you are likely to get the error message:
Operation must use an updateable query
Terminology Change Alert: In Microsoft Access 95 and later the term
"attached" table was replaced with the synonymous term "linked" table. This new
term can be misleading because essentially the behavior has not changed, and a
linked table is not a linked object in OLE terminology. It is still just a
string reference of the disk address to the database that contains the base
table (if you look in the table properties you will see the hard-wired
connection string). Linked tables in Microsoft Access bear no relation to OLE
links found in Microsoft Word or Microsoft Excel; any similarity is in
terminology only-they do not update automatically. If you move the location of
the table or database, you will break the link.
For the purposes of this document the terms are interchangeable, and the
term, "base table," is used to refer to the table that is being linked to or
attached.
Microsoft Access 95/97/2000 linked tables: The following changes in
behavior have occurred.
- Permissions are no longer reset to <New Tables/Queries> when a
RefreshLink is performed against an existing link. This was a huge pain in
Microsoft Access 2.0, and has fortunately been fixed.
- The minimum permissions for a linked table require Modify Design
permission on the linked table (not the base table). In Microsoft Access 2.0,
Read Design permission was sufficient.
The main issues involved here are:
- Providing users with sufficient permissions for them to connect or
reconnect their application to the back-end database.
- Resolving the differences in permission settings between tables in the
front- and back-end databases.
Four techniques are available to change a back-end connection:
- Use the RefreshLink method after altering the connection string for
a TableDef.
- Use the TransferDatabase command to link the back-end tables.
- First delete the existing link and then re-add it with the new connection
string by using the Connect property of a TableDef object.
- Use the Linked Table Manager (in Access 2.0 this is called the Attachment
Manager)
Although the use of RefreshLink probably seems the more "elegant"
solution, it has the drawback of requiring Read Data permissions on the back-end
base table in order for the user to be able to execute the method. For instances
where the only access is to be provided by RWOP queries, the re-creation of the
connection (preceded by a delete) only requires Read Design permissions on the
back-end base table. See Section 10, "How do I
implement field-level or row-level security on my tables (RWOP or queries with
Run Permissions set to Owner's)?". The creation of a new attachment can be
implemented either via DAO by setting the Connect property of a
TableDef object or by use of the TransferDatabase macro action. If
you connect to a table by using the menus, you need Read Design permissions, but
if you connect through code to reset the Connect property, you don't need
any permissions - the error returned in your module code is ignored and the
table is linked anyway. If you use the TransferDatabase macro action, you
need Read Designpermissions on the back-end tables. When using the Linked
Table/Attachment Manager add-in, you must have Modify Design permission on the
front-end table and Read Data on the back-end table.
It is important to realize that you are working with two distinct objects as
far as security is concerned. In other words, you have the base table in the
back-end database to deal with, and the link in the front-end database. The
table and the link are separate objects and have permissions set independently
of each other through the Container and Document objects in DAO.
Granting permissions on the link does not affect any permissions set on the
back-end or base, and vice-versa.
Regardless of the setting in the link, a user cannot inherit higher
permissions than those set on the base table. In other words, granting full
permissions on an attached or linked table in the front-end, including
Administer, will have absolutely no effect on the actual table in the
back-end- if you have removed all permissions in the back-end, users will not be
able to open the table in either Design or Datasheet view in the
front-end where you have granted full permissions. The important thing is to
restrict permissions as necessary in the back-end database (where the tables
actually reside). This will prevent users from opening the back-end tables
directly.
When you attach, or link, a table, it automatically inherits whatever
permissions you have defined in your database for <New Tables> for
each Group or User in the front-end database. It is recommended to grant full
permissions on <New Tables> because this has no effect on the
source tables.
Microsoft Access 2.0: In Microsoft Access 2.0, the act of simply
refreshing attached tables also resets the permissions of the table in the
current database to those assigned to <New Tables>. See Section 16.3,
"Updating or Refreshing Existing Links/Attachments"
for more information.
You can refresh existing table attachments by using the Linked Table Manager
or Attachment Manager provided by Microsoft Access or via code. You need to have
Read Data permissions on the TableDef object in the source database.
The following minimum permissions are necessary before a user can link tables
using the Linked Table/Attachment Manager or the RefreshLink method.
Alongside each permission description is the corresponding Access Basic or
Visual Basic for Applications constant equivalent.
|
Minimum Permission Description for RefreshLink |
Access 2.0 Constants |
Access 95/97/2000 |
|
1. *The ability to create New Tables/ Queries in the destination database
|
DB_SEC_CREATE |
DbSecCreate |
|
2. *Read Design Permissions on the Table in the destination database |
DB_SEC_READDEF |
DbSecReadDef |
|
3. *Read Data permissions on the table in the destination database |
DB_SEC_RETRIEVEDATA |
DbSecRetrieveData |
|
4. Open/Run permissions for the source database |
DB_SEC_DBOPEN |
DbSecDBOpen |
|
5. Read Data permissions on the table in the source database |
DB_SEC_RETRIEVEDATA |
DbSecRetrieveData |
|
*Just set full permissions in the destination database |
DB_SEC_FULLACCESS |
DbSecFullAccess |
The following code is an example of how you can programmatically manipulate
these permission settings. The constants used are Microsoft Access 2.0
constants, but the code will also run in Microsoft Access 95, Microsoft Access
97, and Microsoft Access 2000. It assumes that a member of the Admins group will
be executing the code.
Function faq_SetPermissions (strTable As String, strSourceDB As String, strUsrName As String)
' This function will set permissions on the source table so that
' you can use the RefreshLink method to reattach tables. It grants
' Read Data permissions on the source table, Open/Run permissions
' on the source database and full permissions in the destination
' database on all tables and queries. You need to be a member of the
' Admins group to run this code.
'
' Parameters:
' strTable
' Name of the table for permissions to be set. Assumes the
' table named the same in both source and destination db.
' strSourceDB
' Fully-qualified name of the source database
' strUsrName
' Name of group or user you want to be able to
' use RefreshLink
'
Dim db As Database
Dim con As Container
Dim doc As Document
Dim ws As WorkSpace
Set ws = dbengine.workspaces(0)
' set default full permissions in destination
' (current) database for new tables
Set db = CurrentDB()
Set con = db.Containers("Tables")
con.username = strUsrName
con.permissions = DB_SEC_FULLACCESS
' set full permissions for the linked table
' in the destination database
Set doc = con.Documents(strTable)
doc.username = strUsrName
doc.permissions = DB_SEC_FULLACCESS
' Set open database permissions for the source database
Set db = ws.OpenDatabase(strSourceDB)
Set con = db.Containers("Databases")
Set doc = con.Documents("MSysdb")
doc.username = "Users"
doc.permissions = doc.permissions Or DB_SEC_DBOPEN
' Set read data permissions for the base table
Set con = db.Containers("Tables")
Set doc = con.Documents(strTable)
doc.username = strUsrName
doc.permissions = doc.permissions Or DB_SEC_RETRIEVEDATA
End Function
Now that you have assigned Read Data permissions to your source tables and
full permissions to the destination tables for a given user, the user will be
able to use the RefreshLink method to relink tables. The following code
uses Microsoft Access 2.0 conventions but will run in Microsoft Access 95,
Microsoft Access 97, and Microsoft Access 2000.
Function faq_RefreshLink (strTable As String, strSourceDB As String)
' This function can be run by any user who has Read Data permission
' on the source table and Open/Run permissions on the source database
' Parameters:
' strTable
' Name of the table to be refreshed
' strSourceDB
' Fully-qualified path and filename of the source db
'
Dim ws As WorkSpace
Dim db As Database
Dim tdf As TableDef
Set ws = dbengine.workspaces(0)
Set db = CurrentDB()
Set tdf = db.TableDefs(strTable)
' specify new location of source table
tdf.Connect = ";DATABASE=" & strSourceDB
' refresh the link
tdf.RefreshLink
End Function
Your users only need Read Design permissions on your source tables if you
elect to use the TransferDatabase command to refresh your links. You
could modify the faq_SetPermissions function listed above to grant only Read
Design by modifying the following line
doc.permissions = doc.permissions Or DB_SEC_RETRIEVEDATA
to read
doc.permissions = DB_SEC_READDEF
which will restrict permissions on the source table to Read Design only.
Users will not be able to see the data in the table in Datasheet view or
modify the design of the table. The following function will allow users to use
the TransferDatabase method to relink tables:
Function faq_TransferDatabase (strTable As String, strSourceDB As String)
' TransferDatabase is actually a macro command. This function
' acts as a wrapper, passing the name of the table and the fully-
' qualified path and filename of the new location of the source
' database. Users need Read Design on the source table in order
' to run this code. It is assumed they have full permissions on
' the destination database.
'
Dim db As Database
Dim tdf As TableDef
Set db = CurrentDB()
' Ignore any error that occurs if the table isn't already
' present in the destination database
On Error Resume Next
' remove the old link
db.tabledefs.Delete strTable
' Relink using TransferDatabase
DoCmd.TransferDatabase A_ATTACH, "Microsoft Access", strSourceDB, A_TABLE, strTable, strTable
End Function
If you don't want to grant any permissions at all on your source tables and
you'd still like users to be able to link to the tables, you can employ this
method. To remove all permissions for the source table, change the
faq_SetPermissions line
doc.permissions = doc.permissions Or DB_SEC_RETRIEVEDATA
to
doc.permissions = DB_SEC_NOACCESS
If a user tries to link or attach tables through the user interface, the user
will be told that he or she doesn't have sufficient permissions to do so.
However, when using this technique in code, the On Error Resume Next statement
causes the error returned to be ignored, and the table is linked anyway. The
user cannot open the table in either Datasheet or Design view, so
you need to have previously created RWOP queries for the user to be able to
access the data. A user should be able to run the following code, as long as the
user has full permissions in the destination database and Open/Run permissions
on the source database-no permissions at all are necessary on the source tables.
Function faq_ConnectLink (strTable As String, strSourceDB As String)
' This function can be run by any user who has OpenRun permission
' on the source database. It works equally well to link tables
' from scratch or to relink previously attached tables. In-line
' error handling is used to ignore any errors
' Parameters:
' strTable
' Name of the table to be linked
' strSourceDB
' Fully-qualified path and filename of the source db
'
On Error Resume Next
Dim db As Database
Dim tdf As TableDef
Set db = CurrentDB()
' Delete the link if it already exists
db.tabledefs.Delete strTable
' Create new link
Set tdf = db.CreateTableDef(strTable)
' Set the properties of the new link
' and append to the tabledefs collection
tdf.SourceTableName = strTable
tdf.Connect = ";DATABASE=" & strSourceDB
db.tabledefs.Append tdf
End Function
Regardless of the technique selected, Microsoft Access verifies that you have
the necessary permissions. Remember that you do not need to grant any
permissions on the source tables if you relink by using the Connect
property and that granting full permissions on the destination database will
not, in and of itself, jeopardize data security. If you don't, you may receive
the following error messages:
Microsoft Access 2.0.
|
Error Message |
Error Reason |
|
"Can't find Installable ISAM" |
No Read Design permission on the table in the destination database. |
|
"No permission for ^lt;table>" |
No ability to create New tables in destination database.
No Open/Run permissions on source database. |
|
"No Read Data permissions for <table>" |
No Read Data permissions on table in Remote database. |
Microsoft Access 95: Regardless of which setting is missing, only one
error message and number is returned. This is the generic "Application-defined
or object-defined error" message.
Microsoft Access 97 and 2000.
|
Error Message |
Error Reason |
|
"You don't have the necessary permissions to use the <name> object. Have
your system administrator or the person who created this object establish
the appropriate permissions for you." |
No Open/Run permissions on source database.
No Read Data permissions on table in source database. |
|
"Couldn't create; no modify design permissions for table or query <table>"
|
No ability to create New tables in the Current database. |
Even if you are only refreshing an existing link, Microsoft Access still
treats the attachment as if you were creating a new table. When you set
permissions for tables, the setting <New Tables> refers to permissions a
specific group or user will inherit when a new table is created and has nothing
to do with revoking the ability to create tables in the first place. You can't
remove permission to create new tables in the user interface-you have to do it
from code as shown in Section 18, "How can I help
prevent users from creating new objects in my database?".
Microsoft Access 2.0: The Security Wizard presents a check box that
will exclude this ability from the existing users and groups. If you select this
option at the time that you run the Security Wizard, then only members of the
Admins group will be able to create new tables and queries. This makes it
impossible for any user not in the Admins group to create new links.
Microsoft Access 95 Microsoft Access 97 and Microsoft Access 2000: The
removal of this permission setting can now only be accomplished via DAO code.
The check box available in the Microsoft Access 2.0 Security Wizard is not
present in the new built-in wizard. See Section 24,
"How can I help prevent users from creating new objects in my database?" for
the code listing to set or remove this permission.
Microsoft Access uses a combination of security settings in both the current
database and remote database when ascertaining rights to attached/linked tables.
Permissions set on the back-end tables cannot be over-ridden by granting more
liberal permissions on the front-end tables. In this context, it is helpful to
think of the attachments in the front-end database as merely the connection
strings, or the information needed to locate the actual tables in the back-end
database. Permissions set on connection strings should not, and do not, override
permissions set on actual tables.
Microsoft Access does not treat the combination of permissions between the
base tables and the connection strings by using the "least restrictive" rule,
but rather it determines the most restrictive rights. Microsoft Access first
determines the least restrictive rights for the table in both the current and
remote databases. Then Microsoft Access compares these permissions and combines
them so that, unless a permission is available in both databases, it will
restrict rights to the attached table.
The following table highlights this.
|
Front-End DB |
+ |
Back-End DB |
= |
Permissions on Attached/Linked Table |
|
None |
+ |
Administer |
= |
No Permissions |
|
Read Only |
+ |
Administer |
= |
Read Only |
|
Administer |
+ |
None |
= |
No Permissions |
|
Administer |
+ |
Read Only |
= |
Read Only |
The simplest thing to do is to grant users full permissions on tables in the
front-end and restrict the back-end to the barest minimum needed if users need
to relink tables. If they don't need to relink tables, then removing ALL
permissions and using RWOP queries will help provide strong protection for your
data.
The process of adding a linked table will set the default permissions to
those assigned for <New Tables/Queries>.
The best solution is to provide full permissions for all of your custom
groups on <New Tables/Queries> in the destination database. When a linked
table is refreshed, it will receive full rights, but any access to the linked
table will follow the "most restrictive" rule and access to the data will be
downgraded to rights assigned in the remote database. To provide strong data
security measures, remove all permissions for all groups and users for <New
Tables/Queries> and all tables in the source database. To access the data,
you can use queries with the RunPermission set to Owner's instead of
User's (also known as RWOP queries). Any tables a user creates in the
front-end database will not be affected by the default permission settings
because the user will own the objects and, by default, will have full
permissions on them.
Microsoft Access 2.0: Updating an existing attachment causes the
existing permissions for the attached table to be reset to the default settings
for <New Tables/Queries> for all users and groups other than Admins and
the user actually performing the attachment. Microsoft Access 2.0 treats the
refresh of an attached table as the creation of a new table, and the user
performing the attachment becomes the owner of the object and receives full
permissions, regardless of the settings in <New Tables/Queries>. If you
have followed the suggestions of having full or zero permissions for <New
Tables/Queries> that were discussed in Section 16.2, then this will not be a
problem. Any access to the attached table (including access via a RWOP query)
will follow the "most restrictive" rules and be downgraded to rights of the base
table in the remote database.
Microsoft Access 95, Microsoft Access 97, and Microsoft Access 2000:
This problem has been fixed. The act of updating an existing link has no effect
on the existing permissions previously set on linked tables.
If you need the base table permissions to always be identical to the linked
table permissions after refreshing a link, you must copy the permissions by
using DAO.
The following code sample opens the remote database and retrieves the
permissions set on the base tables, and then copies those permissions to the
front-end linked tables. It assumes that the names of the tables and the names
of the links in both databases are identical. Because only members of the Admins
group can set permissions, you must log on as a member of the Admins group in
order to run the code.
Calling example:
ysnOK = faq_CopyTablePermissions ( "C:\DATA.MDB" )
Function faq_CopyTablePermissions(pstrRemoteDB As String) As Integer
' ------------------------------------------------
' copies permissions from the RemoteDB to the CurrentDB
' the full path to the remote db is passed in the pstrRemoteDB
' string parameter
' -------------------------------------------------
Dim ws As Workspace, db As DATABASE
Dim dbTo As DATABASE, dbFrom As DATABASE
Dim conFrom As Container, docFrom As Document
Dim conTo As Container, docTo As Document
Dim strDBName As String, strTblName As String
Dim i As Integer, k As Integer, m As Integer
On Error GoTo faq_Err_CopyTablePermissions
'--------------------------
' get current database
'--------------------------
Set ws = DBEngine.Workspaces(0)
Set db = CurrentDB()
'----------------------------------------------
' open From/To database under secured workspace
' establish From/To Containers
'----------------------------------------------
Set dbTo = ws.OpenDatabase(db.Name)
Set dbFrom = ws.OpenDatabase(pstrRemoteDB)
Set conFrom = dbFrom.Containers("Tables")
Set conTo = dbTo.Containers("Tables")
'-----------------------------------
' copy permissions for all tables
' - ignore unmatched & system tables
'------------------------------------
For i = 0 To dbFrom.TableDefs.Count - 1
strTblName = dbFrom.TableDefs(i).Name
If Left$(strTblName, 4) <> "MSYS" Then
Set docFrom = conFrom.Documents(strTblName)
strTblName = ""
On Error Resume Next
strTblName = dbTo.TableDefs(i).Name
On Error GoTo faq_Err_CopyTablePermissions
If strTblName <> "" Then
Set docTo = conTo.Documents(strTblName)
For k = 0 To ws.Groups.Count - 1
'-----------------------
' synchronise group names
' copy group permissions
' -----------------------
docFrom.UserName = ws.Groups(k).Name
docTo.UserName = docFrom.UserName
If docTo.Permissions <> docFrom.Permissions Then
docTo.Permissions = docFrom.Permissions
End If
' ------------------------------------------
' following For/Next loop is only necessary
' if User Level permissions are applicable
' ------------------------------------------
For m = 0 To ws.Groups(k).Users.Count - 1
docFrom.UserName = ws.Groups(k).Users(m).Name
docTo.UserName = docFrom.UserName
docTo.Permissions = docFrom.Permissions
Next m
Next k
End If
End If
Next i
faq_CopyTablePermissions = True
faq_Exit_CopyTablePermissions:
If Not dbFrom Is Nothing Then dbFrom.Close
If Not dbTo Is Nothing Then dbTo.Close
If Not ws Is Nothing Then ws.Close
Exit Function
faq_Err_CopyTablePermissions:
MsgBox "Table: [" & strTblName & "]" & Chr(13) & Error
faq_CopyTablePermissions = False
Resume faq_Exit_CopyTablePermissions
End Function
This is a multi-step process but can be useful in setting the Enabled
property of command buttons that might rely on a recordset being updateable.
First, you must retrieve the explicit permissions for the specific user of
the table. Then you must add all group membership or implicit permissions. This
step must be done separately for both the current database and the remote
database.
Once both sets of implicit rights are computed, the two are merged using a
binary AND operator. This will leave only the bits that are set in both
sets and forms the "most restrictive" set of permissions.
Microsoft Access 2.0: If the current user is not a member of the
Admins group, this information can be tricky to obtain. See Section 23,
"How can I obtain the groups that the current user
belongs to without hard-coding an Admins ID and password in the code?" for a
workaround.
Microsoft Access 95, Microsoft Access 97, and Microsoft Access 2000: A
new security setting was added that permits users to retrieve security
information for an object. Without this setting, attempts to retrieve
permissions are rejected. This setting can be addressed via the dbSecReadSec
constant.
The following function will return the "most restrictive" permissions for the
specified table. Optionally, a query name can be provided as an argument. This
would be useful in conditions where RWOP queries controlled permissions to the
data. To obtain the correct permissions, the query is examined to see if it is a
RWOP query by searching for the "WITH OWNERACCESS OPTION" string in the
SQL. If it is, the permissions for the owner of the query rather than the
current user are retrieved and used in the calculations. As a final step, the
current user permissions for the query are applied to account for any further
permission restrictions.
Example Usage:
If (faq_MostRestrictive("Categories","MyQuery") And dbSecReplaceData) = _
dbSecReplaceData then
Msgbox "I can update this table"
End if
Function faq_MostRestrictive(pstrTable As String, pstrQuery As String) As Long
' ------------------------------------------------
' computes "most restrictive" permissions for the
' current user and specified table
' optional query may filter permissions
' -------------------------------------------------
Dim db As DATABASE, tdef As TableDef
Dim con As Container, qdef As QueryDef
Dim doc As Document, docQry As Document
Dim lngImpCurrent As Long, lngImpRemote As Long
Dim lngImpQuery As Long, strUser As String
'--------------------------------------
' open currentdb - set document to table
' compute implicit permissions
'--------------------------------------
Set db = CurrentDB()
Set con = db.Containers!tables
Set doc = con.Documents(pstrTable)
'------------------------------------------------
' if query filter
' ... check for owner or user permissions
' ... adjust for currentuser permissions on query
'-------------------------------------------------
strUser = CurrentUser()
lngImpQuery = DB_SEC_FULLACCESS
If pstrQuery <> "" Then
Set qdef = db.QueryDefs(pstrQuery)
If InStr(qdef.SQL, "WITH OWNERACCESS OPTION") > 0 Then
Set docQry = con.Documents(pstrQuery)
strUser = docQry.Owner
lngImpQuery = faq_Implicit(docQry, CurrentUser())
End If
End If
lngImpCurrent = lngImpQuery And faq_Implicit(doc, strUser)
'--------------------------------------
' get source table name & connect string
' open remote db - set document to table
' compute implicit permissions
'---------------------------------------
Set tdef = db.TableDefs(pstrTable)
pstrTable = tdef.SourceTableName
Set db = OpenDatabase(Mid$(tdef.Connect, 11))
Set con = db.Containers!tables
Set doc = con.Documents(pstrTable)
lngImpRemote = faq_Implicit(doc, strUser)
'--------------------------------
' compute most restrictive rights
'--------------------------------
faq_MostRestrictive = lngImpCurrent And lngImpRemote
End Function
Function faq_Implicit(pdoc As Document, pstrUser As String) As Long
'------------------------------------------------
' computes "implicit" permissions for the
' specified user and document ( table or query )
'------------------------------------------------
Dim ws As Workspace
Dim usr As User
Dim lngPerms As Long
Dim i As Integer
'-------------------------
' get explicit permissions
'-------------------------
pdoc.UserName = pstrUser
lngPerms = pdoc.Permissions
'-------------------------------------
' add in each group where current user
' is a member
'-------------------------------------
Set ws = DBEngine(0)
Set usr = ws.Users(pstrUser)
For i = 0 To usr.Groups.Count - 1
pdoc.UserName = usr.Groups(i).Name
lngPerms = lngperms Or pdoc.Permissions
Next
faq_Implicit = lngPerms
End Function
Users may link tables, as long as they have Open/Run permissions on the
source database itself. They need Read Data permissions to attach through the
user interface, but not in code. See Section 13, "How
do I manage linked tables using Microsoft Access security?" and Section 14,
"What permissions are necessary to update table links?"
for more information.
There is nothing to stop users from simply opening the remote database
directly if they have Open/Run permissions.
You can prevent users from opening the remote database in Datasheet
view and viewing the data by removing Read Data permissions from the tables in
the back-end database.
Create separate shortcuts on your desktop for your security-enhanced
application and the default, unsecured installation of Microsoft Access. You can
only log on to one workgroup information file (System.mda/mdw) at a time, so
your users must understand that they have to quit and restart Microsoft Access
when they want to switch back and forth between security-enhanced and unsecured
applications. A second option is to train your users to use the Workgroup
Administrator, but this is usually confusing to end-users. Creating separate
shortcuts is not that difficult, and you only have to do it once. The techniques
are different for each version of Microsoft Access.
Microsoft Access 2.0: You need to use two different .INI files: one
for the default installation and one for the security-enhanced application. The
easiest way to do this is to make a copy of MSACC20.INI and name it something
else, like SECACC20.INI and leave it in your \Windows directory. Change one line
in the Options section in your SECACC20.INI to point to your
security-enhanced workgroup information file (SECAPP.MDA):
[Options]
SystemDB=C:\ACCESS\SECAPP.MDA
The next step is to create the shortcut for your security-enhanced
application by using the /INI switch on the command line options to point
to the .INI file specifying the security-enhanced workgroup information file. It
should look something like the following:
c:\msoffice\access\msaccess.exe /ini c:\windows\secacc20.ini
Using this icon will start the security-enhanced version of Microsoft Access.
Your original icon is unchanged, and will start the unsecured, default
installation of Microsoft Access.
Microsoft Access 95, Microsoft Access 97, and Microsoft Access 2000:
You don't need .INI files any more; you can use the /WRKGRP switch
directly on the command line of the icon:
c:\msoffice\access\msaccess.exe /wrkgrp c:\myapp\secacc.mdw
Microsoft Access 2.0: You can't prevent users from viewing any code
that you place directly in a form or report module (this code is typically
referred to as Code Behind Forms, or CBF). Unfortunately, there is a bug in
Microsoft Access 2.0 that exposes all CBF if the user includes DebugLibraries=True
in the [Options] section of the MSACC20.INI file, which is impossible to
secure. There is no workaround for this in Microsoft Access 2.0, but it was
fixed in Microsoft Access 95. You can help protect your code by moving sensitive
procedures to global modules, and calling the global procedures from your CBF.
Make sure to remove Read Design permissions from your global modules.
Microsoft Access 95, Microsoft Access 97, and Microsoft Access 2000:
Remove Read Design permissions from your forms.
Microsoft Access 97 and Microsoft Access 2000: You can compile your
database as an MDE file. This removes all of the code so that it cannot be
viewed. In addition, users will be unable to create or modify any forms,
reports, and module code. Compiling as an MDE has no effect on the data.
Microsoft Access has a function, CurrentUser(), that returns the name
of the logged-on user of the current database, but that won't help you if you're
trying to determine all of the users of a shared database centrally located on a
network. Unfortunately, there is no built-in way to do this. In order to
implement this functionality, you would have to include your own special table
that adds a user's name whenever someone starts your application, and then
deletes it when that person logs off.
The problem to this method is that if a user does not exit gracefully (for
instance, if that user's computer has a fatal error (that is a GPF or IPF) or if
that user turns the computer off without first quitting Microsoft Access) then
that user will still be listed as being "logged on" even though that user is
not. To help guard against this, you should delete all names from the table at a
time when you are certain no one is in the database (such as when you are doing
a backup/repair/compact). You may also want to provide a way for your
administrative users to browse and adjust the table to remove such a phantom
logon.
There is a DLL available from Microsoft that will let you see which people
are using a specific database (it will list all the names their computers used
on the network), but although it can check Jet database engine 2.0/2.5/3.5/4.0
applications, it cannot be run from a 16-bit application such as Microsoft
Access 2.0. Check the Microsoft Web site for the most recent version, which
should be compatible with Jet database engine 2.5 through 4.0. The JETLOCK.EXE
file contains the "Understanding Jet Locking" white paper, as well as the
LDBView utility and the MSLDBUsr.dll.
In Access 2000/Jet 4.0, the capabilities of MSLDBUsr.dll have been merged
with Jet and can be accessed via the Jet 4.0 OLE DB provider. To use this "user
roster," simply run the following code:
Const dbSecUserRosterGuid = "{947bb102-5d43-11d1-bdbf-00c04fb92675}"
Dim rs As ADODB.Recordset
Dim con As ADODB.Connection
Set con = CurrentProject.Connection
Set rs = con.OpenSchema(adSchemaProviderSpecific, , dbSecUserRosterGuid)
This GUID is a value defined in a Jet OLE DB header file and is simply a
provider-specific schema view. It will return a recordset with the following
information.
|
Column Name |
Column Type |
Meaning |
|
COMPUTER_NAME |
Text |
The name of the computer |
|
LOGIN_NAME |
Text |
The Jet user name, matching the value that would be returned by the
CurrentUser function (not the operating system user name!) |
|
CONNECTED |
Boolean |
Whether or not the user is currently connected |
|
SUSPECT_STATE |
Boolean |
Whether or not the user has left the database in a suspect state (note: this
column will never have a False value; it will always be either True or
Null). |
This information is the same as that provided by MSLDBUsr.dll, with the
addition of the Jet user name. Obviously, this will only be useful in a
security-enhanced workgroup that has different users defined.
In tandem with the User Roster is another new feature in Jet 4.0 known as
passive connection control. This feature gives you the ability to put the
database in a state where, although users who are already in the database can
continue their work, no new users can be admitted. If anyone tries to open the
database, they will receive errors as if the database were opened exclusively.
Passive connection control can only be used from the Jet OLE DB provider. To use
passive connection control, run code such as the following:
Dim con As ADODB.Connection
Set con = CurrentProject.Connection
con.Properties("Jet OLEDB:Connection Control") = 1
Once you have set this property, you can use the User Roster's list to help
determine who is still connected and then have them disconnect, so that
operations such as Compact and Backup can be performed.
Note There is no method to actively disconnect users from a database.
You must have Open Exclusive permission on the database in order to
change the Connection Control setting of the database.
In Microsoft Access 2.0, only members of the Admins group can view the group
membership and permissions of other users. The following functions assume that
you are a member of the Admins group. This is the only way to ensure that you
have sufficient permissions to run the code without error.
Microsoft Access 95 and later allows all users to retrieve group membership
information, provided that they are using a Microsoft Access 95 or later
workgroup file rather than a Microsoft Access 2.0 workgroup file. List Users in
the system:
Function faq_ListUsersInSystem ()
Dim ws As WorkSpace
Dim i As Integer
Set ws = DBEngine.Workspaces(0)
For i = 0 To ws.Users.count - 1
Debug.Print ws.Users(i).Name
Next i
End Function
List Groups in System:
Function faq_ListGroupsInSystem ()
Dim ws As WorkSpace
Dim i As Integer
Set ws = DBEngine.Workspaces(0)
For i = 0 To ws.Groups.count - 1
Debug.Print ws.Groups(i).Name
Next i
End Function
List Members of Group:
Function faq_ListUsersOfGroup (strGroupName As String)
Dim ws As WorkSpace
Dim grp As Group
Dim i As Integer
Set ws = DBEngine.Workspaces(0)
Set grp = ws.Groups(strGroupName)
For i = 0 To grp.Users.count - 1
Debug.Print grp.Users(i).Name
Next i
End Function
List Groups User is a member of:
Function faq_ListGroupsOfUser (strUserName As String)
Dim ws As WorkSpace
Dim usr As User
Dim i As Integer
Set ws = DBEngine.Workspaces(0)
Set usr = ws.Users(strUserName)
For i = 0 To usr.Groups.count - 1
Debug.Print usr.Groups(i).Name
Next i
End Function
Determine if a User is in a given Group:
Function faq_IsUserInGroup (strGroup As String, strUser as String) As Integer
' Returns True if user is in group, False otherwise
' This only works if you're a member of the Admins group.
Dim ws As WorkSpace
Dim grp As Group
Dim strUserName as string
Set ws = DBEngine.Workspaces(0)
Set grp = ws.Groups(strGroup)
On Error Resume Next
strUserName = ws.groups(strGroup).users(strUser).Name
faq_IsUserInGroup = (Err = 0)
End Function
Microsoft Access 2.0: There is a way to do this; however, Microsoft
does not support it. It will allow all users to view all groups and users
membership. You do not need to use this technique in Microsoft Access 95 or
later because you can now view group and user membership without being a member
of the Admins group.
Note Make sure you have made a backup copy of your workgroup
information file (System.mda) before starting this process. If something goes
terribly wrong, you stand a chance of locking yourself out of your application.
The method relies on granting permissions to the Users group for two tables
that reside in the workgroup file (which store user and group information):
MSysAccounts
MSysGroups
You typically get an error if you try to read the information in these tables
and you are not a member of Admins, because these tables are set up with no
permissions for any other users or groups. Because the workgroup information
file is just a Microsoft Access database, you can open it directly while logged
on as a member of the Admins group Assign Read Data permissions to these two
tables for selected users or groups (assigning permissions to the Users group
will grant it for all users).
These are the steps required to provide group membership information to
selected users and groups. It assumes the name of the workgroup file is
System.mda.
- Make sure you don't have a copy of Microsoft Access running.
- Copy your workgroup file and name it something like "Test.mdb."
- Start Microsoft Access. If you have security enabled, log on as a member
of the Admins group.
- Open Test.mdb. Use View/Options/General, and select
the Show System Objects option. Then use Security/Permissions to
assign permissions to the MSysAccounts and MSysGroups tables for the users or
groups who you want to have privileges to check group membership.
- Quit Microsoft Access
- Delete System.mda and rename Test.mdb to System.mda.
Authorized users or members of authorized groups can now obtain group
membership information by using the methods discussed in Section 22,
"How can I obtain group and user membership information
programmatically?"
Microsoft Access 95, Microsoft Access 97, and Microsoft Access 2000:
You do not need to do this in Access 95, Access 97, and Access 2000. All users
can see group and user membership from the security menus or from code.
The following function will remove permissions to create new tables or
queries in the current database from the specified user. The argument,
strUser, can be either a User or a Group. Remember that you will have to run
the faq_NoNew() function not only for the specified user, but also for
the Users group. This is because the user has both explicit permission to create
new tables or queries, and implicit permission derived from membership in the
Users group.
There is no way to remove permission to create forms, reports, macros, or
modules in Microsoft Access unless you compile your database as an MDE in Access
97 or Access 2000. You need to have Administer permissions in order to run this
code (shown in Microsoft Access 2.0 format).
Function faq_NoNew (strUser as String)
Dim db As Database
Dim con As Container
Set db = DBEngine(0)(0)
Set con = db.Containers("Tables")
con.UserName = strUser
con.Permissions = con.Permissions And Not DB_SEC_CREATE
End Function
If you want to set the permission to create new tables and queries back
again, use:
Function faq_OKNew (strUser as String)
Dim db As Database
Dim con As Container
Set db = DBEngine(0)(0)
Set con = db.Containers("Tables")
con.UserName = strUser
con.Permissions = con.Permissions Or DB_SEC_CREATE
End Function
Microsoft Access 95, Microsoft Access 97, and Access 2000: The
intrinsic constants are the difference here. The correct spelling can be found
in the Object Browser or listed in the table in Section 3,
"What has changed in Microsoft Access security between
Microsoft Access 2.0, 95, 97, and 2000?".
…
con.Permissions = con.Permissions And Not dbSecCreate
…
The following technique requires logging on to a "security-enhanced
workspace" in code as shown in the following code fragment:
Set ws = dbengine.CreateWorkspace("NewWS","DevUser","ValidPwd")
Only members of the Admins group may set permissions, so DevUser must
be a valid user account in the Admins group and ValidPwd must be a valid
password.
Warning Using this technique in Microsoft Access 2.0 could compromise
the security of your application (see Section 7,
"What's all this about a security hole in Microsoft Access 2.0?"). Security
holes in Access 95 also make this technique inadvisable there. In Access 97 and
2000 you can create an MDE, which will help protect security information
embedded in your code.
If you're not using Microsoft Access 2.0, or you don't think your users will
stumble on the security hole, these are the steps to follow after removing ALL
rights to underlying tables for all users and groups:
- Log on as a member of the Admins group.
- Create a query that accesses the tables and has its RunPermissions
property set to Owner's rather than User's. This will provide
sufficient rights to users to read, add, change, and delete data. At this
point all users and groups, except for Admins members, will have no access to
this query.
- Create your form and controls with this query as the record source.
- Include the code hooks for the Activate and Deactivate events as shown
below.
- Assign Open/Run permissions for the form to desired groups and users.
Now the user will not be able to read data from the tables or the query.
When the form opens, it will trigger the Activate event, which will provide
appropriate permissions to the form's record source. The Deactivate event
removes permissions. When the window is brought back into focus, the Activate
event sets them back again. When the form closes, the Deactivate event is
triggered before the Close event.
Pop-up forms present a different problem because they do not trigger the
Activate and Deactivate events. The code for these events would need to be
transferred to the Open and Close events, respectively. They would either need
to have their Modal and PopUp properties set to Yes, or to
be invoked via the A_DIALOG argument of the OpenForm action, which has
the same effect of forcing the form to maintain focus while it is visible.
OnActivate : =faq_TblQueryGivePermissions ("MyQuery", Form)
OnDeactivate : =faq_TblQueryRemovePermissions (Form)
Function faq_TblQueryGivePermissions (pstrRecordSource As String, pfrm As Form)
'----------------------------------------------------
' Use OR operator to assign permissions additively
' Pass permission settings to security-enhanced workspace routine
' If recordsource is null, set it up (first time only)
' ---------------------------------------------------
Dim lngPermissions As LonglngPermissions = lngPermissions Or DB_SEC_RETRIEVEDATA
lngPermissions = lngPermissions Or DB_SEC_INSERTDATA
lngPermissions = lngPermissions Or DB_SEC_REPLACEDATA
lngPermissions = lngPermissions Or DB_SEC_DELETEDATA
'The form's RecordSource should have been left blank. If not,
'the user will receive permissions error(s) opening the form
'since the OnOpen event fires before the OnActivate event,
'which is being used to grant the necessary permissions.
'
faq_SetPermissions pstrRecordSource, lngPermissions
'Now that the user has permissions to use the RecordSource
'query, we can set the form's RecordSource.
'
If IsNull(pfrm.RecordSource) Then 'Access 2.0 and 95
'If Len(pfrm.RecordSource) = 0 Then 'Access 97
pfrm.RecordSource = pstrRecordSource
End If
End Function
Function faq_TblQueryRemovePermissions (pfrm As Form)
' ----------------------------------------------------
' Reset permissions by calling secured workspace routine
' ----------------------------------------------------
faq_SetPermissions pfrm.RecordSource, DB_SEC_NOACCESS
'The line below can be substituted if you wish to allow the users to have
'Read Data on the form's Recordsource after the form is closed.
' faq_SetPermissions pfrm.RecordSource , DB_SEC_RETRIEVEDATA
End Function
Sub faq_SetPermissions (pstrTblQry As Variant, plngPermissions As Long)
' ----------------------------------
' log in to secured workspace
' establish tables container (also contains queries)
' establish table / query document and user
' set permissions to passed value
' ----------------------------------
Dim ws As WorkSpace, db As Database, con As Container, doc As Document
Set db = CurrentDB()
Set ws = DBEngine.CreateWorkspace("Temp","System User","Tricky Pswd")
Set db = ws.OpenDatabase(db.Name)
Set con = db.Containers("Tables")
Set doc = con.Documents(pstrTblQry)
doc.UserName = CurrentUser()
doc.permissions = plngPermissions
con.Documents.Refresh
db.Close
ws.Close
End Sub
You will need two workgroup information files: one that you will use to help
develop and protect your application, and one that you use to distribute it. The
distribution workgroup file can be the default that is installed with Microsoft
Access, if you prefer. When you help protect your application using your
workgroup file, you will want to make sure that the default Users group has only
the permissions that you want all users to have, while full permissions are held
only by a member of your own System.mda/mdw. Granting the Users group
permissions on "public" objects means that the Users group in other workgroup
files (including the default System.mda/mdw) will inherit them. You will need to
log on to your development workgroup file in order to modify your entire
application.
- Follow the steps outlined in Section 1, "What are
the steps to help protect a database?" for securing your application.
- Use Security/Permissions to assign or revoke permissions
from the Users group that you don't want everyone to have.
- Ship your add-in without your System.mda/mdw.
This technique is, unfortunately, not bulletproof in Microsoft Access 2.0
(see Section 7, "What's all this about a security hole
in Microsoft Access 2.0?").
Microsoft Access 2.0: There is no way to keep them from doing this,
unless you distribute your application using the run-time version of Microsoft
Access. In the full retail version of Microsoft Access, there are only a few
methods that you can use to discourage people from doing this:
- You can preface your objects with "USys" so that they will be hidden in
the Database window (as a rule most people will get bored with an empty
Database window quickly and will just run your AutoExec macro). The
disadvantage is that they will still see your objects if they have the Show
system/hidden objects property set to Yes.
- You can implement security-enhancements on all your objects so users
cannot change or harm them, even when they do have access to the Database
window. This is the only way to help provide security when the user has full
retail Microsoft Access.
Microsoft Access 95, Microsoft Access 97, and Microsoft Access 2000:
You can set the AllowBypassKey property to False to prevent a user
from bypassing the startup properties and the AutoExec macro by holding down the
SHIFT key. This option is only available through code.
This function only needs to be run once for each database to set the
AllowByPassKey property. The documented syntax for creating and setting
properties in the Access Help file is CreateProperty(PropertyName, PropertyType,
PropertyValue, DDL) syntax. However, any knowledgeable user can run code to
reverse the AllowByPassKey setting. In order to prevent users from
reversing the property setting, you must set the fourth (DDL) parameter to True:
CreateProperty(PropertyName, PropertyType, PropertyValue, True). The following
function can be run from another database or from the Debug/Immediate window.
Function faq_DisableShiftKeyBypass(strDBName as String, fAllow as Boolean) As Boolean
On Error GoTo errDisableShift
Dim ws As Workspace
Dim db As DATABASE
Dim prop As Property
Const conPropNotFound = 3270
Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase(strDBName)
db.Properties("AllowByPassKey") = Not fAllow
faq_DisableShiftKeyBypass = fAllow
exitDisableShift:
Exit Function
errDisableShift:
'The AllowBypassKey property is a user-defined
' property of the database that must be created
' before it can be set. This error code will execute
' the first time this function is run in a database.
If Err = conPropNotFound Then
' You must set the fourth DDL parameter to True
' to ensure that only administrators
' can modify it later. If it was created wrongly, then
' delete it and re-create it correctly.
Set prop = db.CreateProperty("AllowByPassKey", _
dbBoolean, False, True)
db.Properties.Append prop
Resume
Else
MsgBox "Function DisableShiftKeyBypass did not complete successfully."
Faq_DisableShiftKeyBypass = False
GoTo exitDisableShift
End If
End Function
You'll need to create a function that uses SysCmd() to check to see
whether the database is currently opened under the run-time version:
Function KillIt() As Integer
If Syscmd(SYSCMD_RUNTIME) = 0 Then
' use the following intrinsic constant for Microsoft Access 95 or 97
' If Syscmd(acSysCmdRuntime) = 0 Then
Application.Quit
End If
End Function
Call this function from your Startup form or AutoExec macro as the very first
action. This is the first line of defense. Unfortunately, anyone who knows to
hold down the SHIFTkey will not be kept out by this function, so you will also
want to disable holding down the shift key, as shown in the previous section.
You may also want to put additional calls to KillIt() in your
database-for example, in the Open events of your forms, or as calculated columns
in queries. You may not be able to keep people out, but you can certainly make
it inconvenient for them to be in.
Microsoft Access 95, Microsoft Access 97, and
Microsoft Access 2000: You can set the AllowBypassKey property as
discussed in Section 27, "How do I prevent users
from holding down the SHIFT key to bypass the AutoExec macro?". This will
cause the AutoExec macro to always run, so you wouldn't need to place your kill
function in all your major forms or queries.
Yes. Microsoft Access security is set at the Microsoft Jet database engine
level, which cannot be overridden by any other application. The Jet database
engine verifies that another user or application has sufficient permissions
before it hands over any data. Permissions you set for each object will be
honored
DDE Alert: You must keep one special thing in mind when accessing data
via DDE: because you can connect to a currently running session of Microsoft
Access, all permissions are decided by the permissions of the user who logged on
to that session, not the user who connects via DDE.
Microsoft Access 2.0: After the Security Wizard has run, you can
decrypt the database by choosing File/Encrypt/Decrypt Database,
and selecting your database with the Decrypt option. In order for this to
appear on the File menu, no database must currently be open.
Microsoft Access 95, Microsoft Access 97, and Microsoft Access 2000:
Use the same procedure as above, except the menu option is found under Tools/Security/Encrypt/Decrypt
Database.
Microsoft Access 2.0: This is a bug in the Security Wizard for
Microsoft Access 2.0, which is triggered by not having any objects, other than
tables, in your database. Create another object in your database (it can be an
empty form, report, macro, or module), and then re-run the wizard.
There are certain things that all workgroup (System.mda) files have in
common-the default Admin user and the default Users group. If you give either of
these permissions, you are in effect giving those permissions to the entire
population of Microsoft Access users. (See Section 26,
"How can I help protect some parts of my application (an add-in), yet make
others totally open to any Microsoft Access user?")
In order to keep this from happening, you must follow the steps in Section 1,
"What are the steps to help protect a database?",
using the Security Wizard to keep the Users group from having any permissions.
Microsoft Access is a PC desktop database, so by definition, it can't be
secured because the file can always be copied. The tools and hackers exist out
there to break into any physical file. Most so-called security holes in
Microsoft Access arise because the security that does exist has been
misunderstood and, as a consequence, has not been implemented correctly. As you
can see from Section 1, there are many steps, and it is easy to forget one, and
therefore leave your database vulnerable. In addition, the security hole in
Microsoft Access 2.0 makes non-data objects especially vulnerable (see
Section 7 for more information).
So, the answer is that Microsoft Access security can be counted on, to a
point. If your data security needs are truly stringent, you may want to consider
a server product, such as SQL Server with integrated security implemented on a
locked-up installation of Microsoft Windows NT or Microsoft Windows 2000. To
help protect application security and code security, assuming you are using
Access 97 or later, compile your database as an MDE.
You can set this up by using two different workgroup information files: one
for development, and one for distribution. You help protect your application
using the development file, assigning permissions to custom groups (implicit
permissions) so that all permissions are inherited through group membership. You
use a second workgroup information file to distribute your application. By
entering identical group names and PIDs in both workgroup information files,
permissions to objects in the database are inherited from the developer
workgroup information file for those specific groups. You can then create a site
administrator account in the distribution workgroup file and add it to the
Admins group. The site administrator account will be able to create users and
move them in and out of groups, and will be able to create and delete users and
groups, but will not be able to modify permissions to database objects or even
to open objects unless the site administrator inherits those permissions by
being a member of other groups. The reason this works is that the remote site
administrator is not present in the Admins group of the workgroup file
used to secure the database; therefore, this account cannot administer
permissions on objects in the database. Unlike the Users group, which is the
same across all workgroup files, the Admins group is unique, its SID being
encrypted from the strings used to create the workgroup file. The Admins group
has Administer permissions that cannot be revoked, but only the Admins
group can be used to secure the database. Therefore, the remote site
administrator can manipulate user and group information (stored in the workgroup
file), but not permissions on database objects (which are stored in the
database, which "sees" only the correct Admins group as having the necessary
permissions).
This technique works equally well in all versions of Microsoft Access. In
Microsoft Access 2.0, work group databases have the extension, MDA; in Microsoft
Access 95 and 97, the extension is MDW. Here are the steps to follow:
- Using the Workgroup Administrator, create a workgroup database (DEVELOP.MDA/MDW),
which will be the developer workgroup.
- Log on using DEVELOP.MDA/MDW and take all necessary steps to help protect
your database.
- When you create your own custom groups, make sure that you write down the
exact names of these groups (case-sensitive) and the Personal ID (PIDs) you
use to create them. You will need these strings later.
- Assign the appropriate permissions to these groups, making sure that you
don't grant Administer permissions to any of these groups to any of the
objects in your database. Also, make sure that all permissions are removed
from the Users group and Admin user.
- Using the Workgroup Administrator, create another workgroup database (USER.MDA/MDW)-
this is the one you will distribute with your application. Make sure you use
different strings for the Name, Company Name, and Workgroup
ID than the ones you used for DEVELOP.MDA.
- In USER.MDA/MDW, create the exact same group names that exist in
DEVELOP.MDA/MDW by using the identical case-sensitive names and PIDs you
defined in DEVELOP.
- Create a user account (SiteAdmin) and add it to the Admins group of
USER.MDA/MDW. Remove the Admin user from the Admins group. This will make
SiteAdmin the administrator for the USER.MDA/MDW workgroup.
- Put a password on the Admin user, which will force the logon dialog box to
appear.
- Distribute USER.MDA/MDW with your application, not DEVELOP.MDA/MDW. The
SiteAdmin account will be able to administer user and group accounts, but will
not be able to alter permissions on your database. You should instruct the
individual entrusted with the SiteAdmin ID and password not to delete any of
the custom groups that you created or your application will "break" and users
will not be able to access the objects in the database. If you have to revise
permissions, you must log on using the DEVELOP.MDA/MDW as the Admins user to
make the changes. Just make sure that the users or groups in the User.mda/mdw
don't have design permissions on any database object or the database itself.
You must have Administer permissions and/or be a member of the Admins group
in the workgroup information file that helped protect the database. Grant full
permissions to the Users group and the Admin user account. Put the Admin user
back in the Admins group and remove the password from the Admin user. You will
not be prompted for a logon ID and password once the password is removed from
the Admin user. Optionally, you can also run the Security Wizard again to
transfer ownership of all objects back to the Admin user, but the wizard will
also remove permissions from the Users group, which you may not want.
This can be a real problem if you lost your password after you downloaded
this FAQ, because you probably have fully implemented security, and done so
correctly. There are two approaches you can take:
- Find out if you missed any steps along the way to helping protect your
database. You may want to look over Section 1, "What
are the steps to help protect a database?", and see what steps might have
been forgotten or done incorrectly so that you can see if you have any "holes"
in your database's security.
- If you have kept the user names, group names, and PIDs for your System.mda
workgroup file, you can re-create this file from scratch. If you do so
exactly, you can then use this re-created workgroup file to regain full access
to your MDB file. Even if you can't re-create all the users, if you can either
re-create the owner of the database or the Admins group and create a member of
Admins, then you can use that user to log on, de-secure your database (see
Section 9) and then re-secure it.
If none of the above methods works, posting a message on the Internet
newsgroups will yield web sites where you can find free information to work
around the problem or a third-party vendor who will be willing to help you work
around this problem for a small fee.
Having a separate workgroup database for each database when you have
essentially the same group of people using all of your applications can present
administrative nightmares. It's much more efficient to create one
security-enhanced workgroup database with many groups so that when user accounts
are added or removed, it only has to be done in one place. Bear in mind that
user profiles and group membership are stored in the workgroup database (System.mda/mdw)
while actual permissions to various objects are stored with individual
databases. To ease the pain of trying to figure out which group belongs to which
database, you could tag group names with the database name to make decipherment
easier. An example would be "acctUsers" for your accounting database, "mktUsers"
for your marketing database, and so on. You simply remove all permissions from
the Users group and all groups that don't apply to a particular database, and
only grant permission to those groups that do belong.
If you do end up with separate workgroup databases, you can easily create
icons for each one. See Section 19, "How do I work with
a security-enhanced application and an unsecured application at the same time?"
for more information about how to set this up.
Permissions can be set on the Container and Document objects
and the Containers and Documents collections. Permissions are
properties of Document objects that are assigned to specific Users and
Groups. They are actually bit masks that are represented by security constants
(see Section 3.1, "Table 1: DAO Security Constants"
for a complete listing).
Setting permissions is done by combining the constants using the Visual Basic
for Applications operators AND and OR, and by assigning the
desired permission to the appropriate user or group. For example, the following
code explicitly gives permission to the Users group to open the database in
shared mode. All of the code in this section is shown with Microsoft Access 95,
97, and 2000 intrinsic constants but will work just as well in Microsoft Access
2.0 by substituting the Microsoft Access 2.0 constants (see Section 3,
"What has changed in Microsoft Access security between
Microsoft Access 2.0, 95, 97, and 2000?" for a mapping of Microsoft Access
95, 97, and 2000 and Microsoft Access 2.0 constants).
Dim db As Database
Dim con As Container
Dim doc As Document
Set db = CurrentDb()
Set con = db.Containers("Databases")
Set doc = con.Documents("MSysdb")
doc.UserName = "Users"
doc.Permissions = dbSecDBOpen
You can also combine permissions with existing permissions. For example, the
following lines of code add the permission to open the database exclusively, in
addition to other permissions the Users group might have, by using the OR
operator to add the permission:
con.UserName = "Users"
con.Permissions = con.Permissions Or dbSecDBExclusive
If you wanted to remove just that one permission, you would use the AND
NOT operator to strip the OpenExclusive option while leaving other
permissions intact:
con.UserName = "Users"
con.Permissions = con.Permissions And Not dbSecDBExclusive
If you just want to check to see what permissions a user has, you use the
AND operator and look for the specific permission. The following example
checks to see if the Users group can open the Employees form in the sample
Northwind database:
Dim db as Database
Dim con as Container
Dim doc as Document
Set db = DBEngine(0)(0)
Set con = db.Containers("Forms")
' refresh to make sure the collection is current
con.Documents.Refresh
Set doc = con.Documents("Employees")
con.UserName = "Users"
If (doc.permissions And acSecFrmRptExecute) > 0 Then
Debug.Print "Users group can open Employees form"
Else
Debug.Print "Users group can't open Employees form"
End If
The most likely cause of this is that you need to add the user to the Users
group as a separate step in your code. When you create a user through the user
interface, this step is taken care of for you automatically. The following
function will create a new user account and add it to the Users group.
Access 2.0 Function
Function sCreateUser (ByVal strUser As String, ByVal strPID As String) As Integer
'-----------------------------------------------------------
' Create a new user and add them to the Users group
' Returns True on success, False if user already exists
'===========================================================
Dim db As Database
Dim ws As WorkSpace
Dim usr As User
Dim grpUsers As Group
Dim strSQL As String, varPwd As String
' if the password isn't supplied, make sure you
' pass an empty string for the password argument
varPwd = ""
Set ws = DBEngine.Workspaces(0)
ws.Users.Refresh
On Error Resume Next
' check to see if user already exists by using inline
' error handling to trap any errors caused by setting
' a reference to a possibly non-existent user
strUser = ws.Users(strUser).Name
If Err = 0 Then
MsgBox "The user you are trying to add already exists.", _
vbInformation, "Can't Add User"
sCreateUser = False
Else
' go ahead and create the user account
Set usr = ws.CreateUser(strUser, strPID, varPwd)
ws.Users.Append usr
ws.Users.Refresh
' now add the user to the Users group
Set grpUsers = ws.Groups("Users")
Set usr = grpUsers.CreateUser(strUser)
grpUsers.Users.Append usr
grpUsers.Users.Refresh
sCreateUser = True
End If
End Function
Access 95, 97, and 2000 Function
Public Function sCreateUser(ByVal strUser As String, ByVal _
strPID As String, Optional varPwd As Variant) As Integer
'-----------------------------------------------------------
' Create a new user and add them to the Users group
' Returns True on success, False if user already exists
'===========================================================
Dim db As DATABASE
Dim ws As Workspace
Dim usr As User
Dim grpUsers As GROUP
Dim strSQL As String
' if the password isn't supplied, make sure you
' pass an empty string for the password argument
If IsMissing(varPwd) Then varPwd = ""
Set ws = DBEngine.Workspaces(0)
ws.Users.Refresh
On Error Resume Next
' check to see if user already exists by using inline
' error handling to trap any errors caused by setting
' a reference to a possibly non-existent user
strUser = ws.Users(strUser).Name
If Err.Number = 0 Then
MsgBox "The user you are trying to add already exists.", _
vbInformation, "Can't Add User"
sCreateUser = False
Else
' go ahead and create the user account
Set usr = ws.CreateUser(strUser, strPID, varPwd)
ws.Users.Append usr
ws.Users.Refresh
' now add the user to the Users group
Set grpUsers = ws.Groups("Users")
Set usr = grpUsers.CreateUser(strUser)
grpUsers.Users.Append usr
grpUsers.Users.Refresh
sCreateUser = True
End If
End Function
When you create a user using the Microsoft Access security menus, you are
prompted to fill in a dialog box with the name of the new user and a Personal
ID, or PID. The PID is not a password, and typing it in the Password
dialog box will cause the logon attempt to fail. After you create the user, log
on as the new user and don't type anything in the Password dialog box.
You can then set a password for this new user account using the Microsoft Access
menus. If you create your users through code, you can set both the PID and the
password at the same time, as shown in the code in Section 38,
"I created a user in code but the user isn't in the
Users group and can't start Microsoft Access".
Microsoft Access 2.0, Microsoft Access 95: The reason for this is that
the Security Wizard in both versions of Microsoft Access does not remove the
Open/Run permissions to the database itself from the Users group. If you have
followed all of the steps to help protect your database and are sure you haven't
left anything out, remove the Open/Run permission from the Users group. If you
still have a problem, then possibly you left out a step in helping protect your
application (most commonly neglecting to remove the Admin user from the Admins
group or having the Admin user retain explicit permissions on objects).
Microsoft Access 97 and Microsoft Access 2000: The Security Wizard is
supposed to remove the Open/Run permission from the Users group, but fails to do
so in Access 97. In Access 2000, it appears to have removed Open/Run, but does
not do so fully, and users can open a security-enhanced Access 2000 database
with the default System.mdw. The fix is to create a new database while logged on
as a member of the Admins group and import all of the objects.
Visual Basic 3.0/4.0: You must own a copy of Microsoft Access
(Microsoft Access 2.0 for Visual Basic 3.0 or Visual Basic 4.0-16; Microsoft
Access 95 for Visual Basic 4.0-32) to help protect a database because Visual
Basic doesn't include the Workgroup Administrator program that ships with
Microsoft Access. (See Section 1 for information
about helping protect a database).
Visual Basic 3.0: Visual Basic 3.0 does not let you directly set
permissions or owners for objects. You'll need to use Microsoft Access to set up
your security scheme. For further information, see Knowledge Base article,
"How Visual Basic 3.0 Handles
Security Set by Microsoft Access (Q105990)".
Visual Basic 4.0, 5.0 and 6.0: You can help implement security in
Visual Basic by using DAO, just as you can in Microsoft Access, assuming that
you are using at least 32-bit Visual Basic 4.0 with a Jet 3.0 or later database.
In general, setting up security-enhancements initially by using Microsoft
Access is easier than writing Visual Basic code. You can always modify
permissions later through Visual Basic.
Visual Basic 3.0/4.0: Yes. See below.
Visual Basic 3.0: You can use the SetDataAccessOption statement
within Visual Basic to point to the .INI file that points to the appropriate
system database, and then use the SetDefaultWorkspace statement before
any data access takes place to log on as a particular user.
The .INI file must include the SystemDB topic of the Options
section as follows:
[Options]
SystemDB=C:\msoffice\access\system.mda
Visual Basic 4.0-16 bit: If you're using Visual Basic 4.0-16, you must
create an .INI file as with Visual Basic 3.0. In addition, use the following
code before accessing the Jet 2.5 database:
DBEngine.IniPath = C:\myapp\myapp.ini
DBEngine.DefaultUser = "LogonUserName"
DBEngine.DefaultPassword = "LogonPassword"
The .INI file must include the SystemDB topic of the Options
section, as follows:
[Options]
SystemDB=c:\msoffice\access\system.mda
The user name and password can be variables or references to controls. Note
that you must use the DefaultUser and DefaultPassword methods; the
CreateWorkspace method of DBEngine will not work properly if you
try to pass along the username and password without first using the
DefaultUser and DefaultPassword methods.
Visual Basic 4.0-32 bit through Visual Basic 6.0: If you're using
Visual Basic 4.0-32 bit and later, you must use code similar to the following
code before accessing the Jet 3.0 and later database:
DBEngine.SystemDB = c:\myapp\system.mdw
DBEngine.DefaultUser = "LogonUserName"
DBEngine.DefaultPassword = "LogonPassword"
Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase("DatabaseName.mdb")
Set rs = rs.OpenRecordset("Recordset source")
Notice that you can use the SystemDB method of the DBEngine
object to directly point to the workgroup file, bypassing the need for an .INI
file reference. Again, the user name and password can be variables or references
to form controls. Note that you must initialize the DBEngine object prior
to using the CreateWorkspace or Workspaces(0) methods of the
DBEngine object.
If you have password-protected a database using the simplified password-based
security, you can open the database by using Visual Basic 4.0 and later using
code similar to the following (assuming the database is located at "c:\path\database.mdb"
and the password is "curry":
Dim wrk as Workspace
Dim dbProtected as Database
Set wrk = DBEngine.Workspaces(0)
Set dbProtected = wrk.OpenDatabase("c:\path\database.mdb", _
False, False, ";PWD=curry")
This same code will also work with Microsoft Access 95, Microsoft Access 97,
and Microsoft Access 2000 when attempting to open a password-protected database
programmatically. (The above code won't work with Microsoft Access 2.0 or Visual
Basic 3.0, because the Jet 1.x and Jet 2.x database engines don't support
database passwords.)
Because reports are not part of DAO, you must use automation to open a
report. This will start an instance of Access.
Dim lngReturn As Long
Dim objAccess As Access.Application
lngReturn = Shell("C:\MSACCESS.EXE " _
& "/wrkgrp c:\MyMDW.mdw /User username /Pwd password /Nostartup")
DoEvents
Set objAccess = GetObject(, "Access.Application")
objAccess.OpenCurrentDatabase "c:\MyMDB.mdb", False
objAccess.DoCmd.OpenReport "MyReport", acViewPreview
Set objAccess = Nothing
Although ADOX (ADO Extensions for Data Definition and Security) is supposed
to have functional parity with DAO, it falls far short when it comes to
security. Many features simply don't work, such as assigning permissions to
Access objects (forms, and reports) and creating new users. Stick with DAO when
you need to programmatically manage security. For more specific information
about these limitations, see the white paper, "Migrating from DAO to ADO",
listed in Section 47, "Additional Sources of
Information."
The unsupported privDBEngine object allows you to connect to another
database in code. This does not open the database by starting an instance of
Access, so you only have access to Jet objects (tables and queries). You can
work with any DAO objects.
Sub OpenDBWithNewWorkgroupFile(strPathToFile As String, _
strDefaultUser As String, _
strDefaultPwd As String, _
strPathToDatabase As String)
' This function uses the unsupported PrivDBEngine object to
' open a database using a different workgroup information file
' than the one being used as the default. You might do this if
' you want to access data that is in a encrypted database that uses
' a different workgroup information file.
Dim dbe As PrivDBEngine
Dim wrk As Workspace
Dim dbs As Database
' Return a reference to a new instance of the PrivDBEngine object.
Set dbe = New PrivDBEngine
' Set the SystemDB property to specify the workgroup file.
dbe.SystemDB = strPathToFile
dbe.DefaultUser = strDefaultUser
dbe.DefaultPassword = strDefaultPwd
Set wrk = dbe.Workspaces(0)
' Open the secured database.
Set dbs = wrk.OpenDatabase(strPathToDatabase)
End Sub
Note that the PrivDBEngine object is not doing anything other than
creating a new instance of the Jet engine. You can get the same functionality by
using code such as the following:
Dim dbe As DAO.DBEngine
Set dbe = CreateObject("DAO.DBEngine")
The "DAO.DBEngine" string would be used for Jet 3.0. To use later versions,
you would use "DAO.DBEngine.35" or "DAO.DBEngine.36" for Access 97 and Access
2000, respectively. For more information, see the DAO to ADO migration paper,
listed in Section 47.
- The Security White Paper and Security Wizard are available from Microsoft.
For Microsoft Access 95, Microsoft Access 97, and Microsoft Access 2000, the
Security Wizard is built into the product.
- The online Microsoft Knowledge Base at
http://search.support.microsoft.com/kb/.
- SmartAccess (Pinnacle Publishing). Subscription information
1-800-788-1900. Pinnacle can be found on the Internet at http://www.pinpub.com.
SmartAccess has published a variety of articles dealing with Microsoft Access
security. A catalog of back issues is available upon request.
- Access/VisualBasic SQL Advisor. Subscription information 1-800-336-6060.
Advisor can be found on the Internet at http://www.advisor.com. Back issues
are available as well as product support and information about conferences
hosted by Advisor publications.
- Visual Basic Programmer's Journal. Subscription information
1-800-848-5523. Fawcette (the publishers of VBPJ) can also be found on the
Internet at http://www.vbpj.com.
- The white paper, "Migrating from DAO to ADO," discusses the features and
limitations of DAO-ADO migration. It can be obtained at http://msdn.microsoft.com/library/techart/daotoadoupdate.htm.
- A Security Manager add-in for Access 97 and Access 2000 is available for
download from the Microsoft support site. The add-in simplifies administration
of an encrypted database, and enables the user to back up a database's
security state as well as set the database's security from a backup.
For the Access 97 Security manager add-in see:
ACC97: Microsoft
Access Security Manager Add-in Available in Download Center (Q236010)
For the Access 2000 Security manager add-in see:
ACC2000: Microsoft
Access Security Manager Add-in Available in Download Center (Q235961)
|
