Lit Window Productions

wxHowto
Intellisense
wxDoxygen
wxSocket
HowTo
Fedora, DSL, 1&1
Prevent the linker from removing unused static objects
Create a secure database for MS-Jet (Access 2000, *.mdb) from a program with ODBC

BugLister

Small, fast and simple bug tracking tool
for MS-Windows. From Lit Window Productions.

Contact
Privacy

Howto: Create a secure database for MS-Jet (Access 2000) from a program.

Date: 15-th, January, 2005

This article shows you how to create a new MS-Access compatible database file (*.mdb) from a C++ or VBA or Java program using ODBC drivers and set up detailed access rights based on users and roles using a workgroup file (*.mdw). After reading this article you should be able to:

  • Create a new MS-Jet database and a new workgroup file with ODBC SQLConfigDataSource
  • Create the database in a way that prevents MS-Access from opening it without a proper password and that is reasonably ‘secure’, as secure as can be achieved with MS-Jet.
  • Set basic access privileges
  • Understand the subtle pitfalls caused by the default ‘admin’ user and ‘admins’ and ‘users’ group and avoid exposing the database through them

Security summary for MS-Jet ODBC drivers:

  • The default user ‘admin’ and default groups ‘admins’, ‘users’ (or PUBLIC) should have all access privileges revoked!
  • Never use ‘admin’, ‘admins’, ‘users’ or PUBLIC as user- or group names in your program. Create your own administrator and user groups. Consider ‘admin’, ‘admins’, ‘users’, PUBLIC reserved words that are inherently insecure.
  • Always specify a user id when creating a database (CREATE_DB) to make this user the owner of the new database.
  • Always specify a full 20-character long random sequence as pid when creating users and groups.

Experienced users can skip right to: Summary

Note: The article assumes that you are using an ODBC driver, but contains information about MS-Jet security mechanisms in general and might be of interest even if you do not use ODBC.

Introduction:

I have written a small bug tracking utility called BugLister that uses MS-Jet as a database engine. For version 2 of BugLister I decided I wanted better security and also give users finer control over who can view and modify their bug reports. MS-Jet 4.0 SQL supports statements to create users and groups and set detailed access rights for all objects in the database. This seemed to be the ticket.

So I wrote some code that created a new database and workgroup file through ODBC and set up the access privileges. Everything seemed fine until I tried to open my supposedly secure database with MS-Access. Imagine my surprise when Access happily opened the database, not even asking for a password, and let me modify and delete everything in it. I had complete and unrestricted access and none of my CREATE USER or GRANT privileges statements seemed to have any effect at all.

It took me almost three days of googling, studying docs and newsgroups and experimenting before I finally figured out all the subtle details and pitfalls you have to avoid to make this work. I found so little on this subject that I decided to write this article and share what I have learned.

Creating a new database:

To create a new database from within a program, you can use the CREATE_DB keyword with the SQLConfigDataSource function from the ODBC installer functions. Example (C++):

const char *config_string=”CREATE_DB=\”c:\my database.mdb\”\0\0”;
SQLConfigDataSource(NULL, ODBC_ADD_DSN, “Microsoft Access Driver (*.mdb)”,
                   config_string);

After the call there will exist a new, empty database at c:\my database.mdb. The problem is, this database can be opened, read and modified by everyone with access to the file.

Securing access - the workgroup file:

The MS-Jet engine has a simple access rights management system built in. When it is enabled for a database, users need to log in with a password and their access rights are restricted based on the privileges stored in the database.

Users belong to groups. To determine if a user is allowed to perform a particular database operation, the MS-Jet engine performs a “logical-or” operation on the user and all his groups rights in order to calculate the least restrictive access rights for that user. If the user or any of the groups the user belongs to has permission for the operation, the operation is carried out.

Usernames, groups and passwords are not stored in the database itself, but in a special workgroup file, usually with *.mdw extension. In the “Common” folder of every computer there is a file SYSTEM.MDW, which serves as the default workgroup file. It is used with every database, unless you specify a different workgroup file in the ODBC connection string.

Creating a new workgroup file:

To use the security features you have to create your own workgroup file. To do so, call SQLConfigDataSource with the CREATE_SYSDB keyword from your program. Here is an example in C++ format. It will create a new workgroup file c:\my workgroup.mdw

“CREATE_SYSDB=\”c:\my workgroup.mdw\”\0\0”

Opening a database in secured mode with a workgroup file:

When you open a database you must pass the path to the workgroup file with the keyword “SystemDB=” to the ODBC connection string. You must also specify the user name to log on, using the UID= keyword. Here is an example. For better readability I will omit the C++ escape characters in this and all following examples. For brevity I will also omit the usual ODBC parameters specifying the Access driver and other parameters, such as “Driver=”, “Fil=” etc... and only include the relevant keywords.

DQB=c:\my database.mdb;SystemDB=c:\my workgroup.mdw;uid=MyLogon

Enabling the new ANSI SQL statements:

To avoid breaking old applications the ODBC driver disables the new ANSI SQL statements by default. You must enable the new syntax explicitly by setting ExtendedAnsiSQL=1 in the ODBC connection string or the driver will return an error for these statements.

Here is an incomplete list of the new statements:

  • CREATE USER username password pid
  • CREATE GROUP groupname pid
  • ADD USER username TO groupname
  • GRANT access-privileges ON database-object TO user_or_group
  • REVOKE access-privileges ON database-object FROM user_or_group

To give a user full rights on the database you would execute “GRANT ALL PRIVILEGES ON DATABASE TO user”. The user will be able to logon, logon exclusively and administer the database.

Search the MSDN for a Microsoft article called “Advanced Microsoft JET SQL for MS Access 2000”, which explains the new statements - unfortunately without the security aspects mentioned here.

Creating new users and groups:

To create a new user, execute the SQL statement “CREATE USER username password pid”. “pid” is a speciality of the MS-Jet engine. It is a unique identifier, 4-20 characters long, which is used to calculate the identity of the new user or group entry. This sounds very innocent, but it isn’t!

MS-Jet un-security. The “pid” catch:

So far everything seems to be pretty straightforward. You create a new workgroup and database file, open them, add a few users and groups and set the desired access privileges. But the innocent “pid” you specify when you add a new user is a security hole of the first degree.

A hacker can create a new workgroup file and add a user with the same pid and name but no password to it. MS-Jet does not prevent anyone from opening the original database with a different new workgroup file. If the hacker finds the correct pid he can then log in to the database under the users identitiy, with no password. If the pid is short enough, a brute force attack - simply trying out all possible combinations - can easily break into a database in less than an hour.

In order to have at least some security you should always create random pids that are 20 characters long. One possible way to do that is to create a GUID for each user and use the first 20 characters of the string representation as a pid. Or you could calculate an MD5 hash code over the user name, the current date-time and a random number. No matter how you do it, the pid should be a unique, random sequence of 20 characters that cannot be guessed and takes some time to be broken into with brute force. Fortunately - unlike passwords -, once you have created the pid you can forget it.

Shouldn’t the MS-Jet engine do that itself? What happens when you omit the pid from the CREATE USER statement? I don’t know, it might be worth a try, but then you never know if all versions of the MS-Jet engine behave the same. So I am just following the MS documentation: Specifying a PID when creating a user or group account ensures that the account is unique. and assume it is better to specify a pid.

The MS documentation goes on: Specifying a PID also allows you to re-create an identical account if the workgroup file becomes damaged, ... It should have added that guessing the PID allows a hacker to re-create an identical account and log on to the database without a password. Limitations in security are okay if they are clearly documented so I can decide if I can live with them. Failing to mention them is very bad, because this pretents a security that is not actually there.

The troublesome default ‘admin’ user:

By default, every database has a user ‘admin’ with full access rights. This user cannot be deleted. Somewhere, in one of the very few Microsoft documents I could find that bother to deal with MS-Jet security, you will find a hint that you should revoke access rights for the admin user. Okay, so “REVOKE ALL PRIVILEGES ON DATABASE FOR admin” as one of the first statements after setting up the database and you are done! Right?

Wrong. ‘admin’ is member of two default groups, ‘admins’ and ‘users’. Note that the reserved keyword PUBLIC is synonymous to ‘users’, so all of the following applies also to PUBLIC. The default group ‘admins’ has full privileges on the database and the default group ‘users’ has full privileges on all objects in the database (tables, views etc...). MS-Jet uses the least restrictive permissions and so the user ‘admin’, because it is part of ‘admins’ and ‘users’, is still allowed everything. Okay, so you “DROP USER admin FROM admins” and also “DROP USER admin FROM users” and you are done, right?

Very wrong. The pid issue strikes again and with force. Because ‘admin’, ‘admins’ and ‘users’ are default entries in a workgroup file, they all have a default pid. If you replace your special workgroup file with a plain vanilla one, the ‘admin’ user is again member of ‘admins’ and ‘users’ and its access rights are back. I don’t usually write in bold characters, much less in capitals, but this is so important:

  • There is NO WAY to remove the default user ‘admin’ or the groups ‘admins’, ‘users’ or drop the user ‘admin’ from those groups.
  • Because of this, ’admin’ will ALWAYS have the access rights that are defined for ‘admins’ and ‘users’ (or PUBLIC).
  • And there is no way to prevent a malicious user with read-access to the database file from opening your database with a new, vanilla workgroup file and thus from getting ‘admins’, ‘users’ and ‘admin’ rights WITHOUT A PASSWORD.

To secure the database you have to revoke all privileges from the ‘admin’ user AND the ‘admins’ and ‘users’ groups as well and never use any of them in your program! And if your program lets users modify the access rights you must also prevent your users from giving any privileges to ‘admins’ or ‘users’.

The SQL statements to revoke all rights are: “REVOKE ALL PRIVILEGES ON DATABASE FROM admins” and “REVOKE ALL PRIVILEGES ON DATABASE FROM users”!

To sum things up:

If you want to secure access, revoke all access privileges from the ‘admin’ user and the ‘admins’ or ‘users’ groups and never use them in your code. Consider ‘admin’, ‘admins’ and ‘users’ (and PUBLIC) reserved words that should never appear in any of your SQL statements!

The MS-Jet engine refuses to revoke privileges if the operation would leave the database without an administrator. Thats a good thing as it avoids locking yourself out. To revoke rights from ‘admin’, ‘admins’ and ‘users’ you need to create a new administrator, a different user, not admin, with full administrative privileges.

To do that, create a new administrative group ‘my_admins’, grant full privileges to this new group, create a new administrator ‘my_admin’ and make it member of the ‘my_admins’ group. You basically create your own version of the ‘admin’ user and ‘admins’ group. Then, if you revoke all privileges from the default ‘admin’, ‘admins’ and ‘users’ you are done. Right? Well, you guessed it... another ‘gotcha’. <Sigh.>

Admin, the owner: “Hey! This is my database! I do what I want!”

When you create a new database with CREATE_DB, the default user ‘admin’ becomes the owner of this database. And owners have full access rights. Always. You cannot revoke rights from an owner of an object. So revoking all privileges on database for ‘admin’, ‘admins’ and ‘users’ has no effect if admin is the owner of the database. admin will always be able to set the privileges back again. To avoid that you have to change the owner of the database. But...

Changing the owner of a database:

You cannot change the owner of the database. The owner is set when the database is created and it stays owner for the lifetime of the file. So if you want a new database to be owned by a different user than ‘admin’, you have to be logged in as this user when you create the database with CREATE_DB. Fortunately SQLConfigDataSource accepts a workgroup file and UID when creating a new database to do this.

The precise syntax of this statement is very tricky. To create a database “c:\my files\database.mdb” with ‘myself’ as owner, you must pass (this is not C++ format, except the nul-char)

CREATE_DB=”c:\my files\database.mdb”\0SystemDB=c:\my files\database.mdw\0UID=myself\0\0

to SQLConfigDataSource. Note several things:

  • You must enclose the path for CREATE_DB in quotes “ (if it contains spaces) as is described in the documentation for the MS-Jet ODBC driver (ODBC desktop drivers).
  • But you must not use quotes for SystemDB, regardless of whether the workgroup path contains spaces or not. The ODBC driver returns ‘invalid file name’ if you do.
  • And you must not confuse the ODBC connection string syntax, which uses ; as separators, with the SQLConfigDataSource syntax, which uses \0 as separators. Separate the entries with nul-char. And don’t forget the double nul-char \0\0 at the end!
  • One final catch: The UID account must have administrative rights or it will not be allowed to create a new database.

Then, and only then, will the database be created with a different owner and revoking all rights for the default user and groups will have any effect. But...

Creating a secure database programmatically:

By now, if you have actually read through the entire explanation so far, you will probably be almost as exhausted as I was when I tried to find out how to make this work. Fortunately we are almost finished.

When you first create a new workgroup file, there are no users that could be made owner of a database, except admin. You cannot create a database with a different owner until you have added the owner to the workgroup. But to do that you need a database or you won’t be able to log in and execute SQL statements. To solve this you have to create a temporary database first (owned by ‘admin’) to create the new admin user. After you have created the new user you can delete the temporary and create the real database.

Summary: How to create a secure database with the MS-Jet ODBC driver.

 

If you follow these steps to create a new database, only the user accounts you create and grant access privileges to will be able to open the database. The default user ‘admin’ will have no rights, even when a hacker tries to open your database with a different workgroup file.

  1. Call SQLConfigDataSource with “CREATE_SYSDB=”<path-to-mdw>”\0\0” to create a new workgroup file. Enclose the path to the workgroup in quotes and use \0 as a separator character.
  2. Call SQLConfigDataSource with “CREATE_DB=”<path-to-database>”\0\0” to create a temporary database, so you can log on and add users. Enclose the path to the workgroup in quotes and use \0 as a separator character.
  3. Log in as the default ‘admin’ user with the following ODBC connection string (only relevant keywords are shown): “DBQ=<path-to-database>;SystemDB=<path-to-mdw>;UID=admin;ExtendedAnsiSQL=1”. Do not use quotes for the paths and do use ; as a separator character. And don’t forget ExtendedAnsiSQL=1 or the CREATE USER SQL statements will return an error.
  4. Create a new group and user without a password and add the user to the new group. Also add the new user to the ‘admins’ group. Execute the following SQL Statements to do so:
    • CREATE GROUP my_admin_group a_good_pid
    • CREATE USER my_admin “” another_good_pid
    • ADD USER my_admin TO my_admin_group
    • ADD USER my_admin TO admins
  5. Close the database and delete it! It was only a temporary database neccessary to log on to be able to execute the CREATE GROUP/USER statements above. The user and group information is stored in the workgroup file.
  6. Call SQLConfigDataSource to create the new database with the new user as owner. Make sure you use quotes only at the right places. Here is the string you have to pass to SQLConfigDataSource:

    SystemDB=<path-to-mdw>\0UID=my_admin\0CREATE_DB=”<path-to-mdb>”\0\0

    Use \0 as a separator, enclose the path for CREATE_DB in quotes but do not use quotes for the SystemDB keyword, even if the path to the system db does contain spaces.
  7. Login to the new database as the new database admin with the following ODBC connection string:
    DBQ=<path-to-mdb>;SystemDB=<path-to-mdw>;UID=my_admin;ExtendedAnsiSQL=1;
  8. Execute the following SQL statements to set the access rights:
    • GRANT ALL PRIVILEGES ON DATABASE TO my_admin_group
    • REVOKE ALL PRIVILEGES ON DATABASE FROM admin
    • REVOKE ALL PRIVILEGES ON DATABASE FROM admins
    • REVOKE ALL PRIVILEGES ON DATABASE FROM users
  9. Protect the admin account with a password:
    • ALTER USER my_admin new_password “”
  10. Your database is now secure. But while you are at it, you could create your own version of a ‘users’ group as well. All new users should be made part of this group.
    • CREATE GROUP my_users a_good_pid
    • GRANT whatever privileges ON whatever objects TO my_users
    • ADD my_admin TO my_users

Testing security:

Finally you should test the security. This is very easy if you have MS-Access installed. If you try to open the database with MS-Access, you should immediately get an error that you do not have sufficient access rights to open the file. If you are able to create new tables or even see the MS-Access tables-view then your database is not secure.

If you don’t have Access installed, try to open the database through ODBC without specifying a workgroup file and use ‘admin’ as the uid. Here is the ODBC connection string: “DBQ=database;UID=admin”. The connection attempt should fail.

Encrypting the database:

A final thing you might want to do is encrypt the database. To do so you must change the string for SQLConfigDataSource in step 6 above. Add the keyword “ENCRYPT” to the CREATE_DB statement after the path to the new database, separated by a space and the database will be encrypted
CREATE_DB=”<path-to-mdb>” ENCRYPT

This protects the database from prying eyes, but only a little! If you use a hex editor to view the binary contents of the database file, the information will no longer be visible as plain text. But do not assume that just because you specified ‘ENCRYPT’ your database is safe. Since there does not seem to be a way to specify an encryption password, you can assume that somewhere on the internet exists a tool that decrypts MS-Access databases.

Conclusion:

It is possible to protect the database and use a role based access rights model with the MS-Jet ODBC driver. This mechanism is quite suitable for small applications like a defect trackers such as my BugLister, or for simple web-pages.

And because it uses ANSI compliant statements, it is also a very good way to start learning and thinking about database security without having to buy and install a full blown SQL server.

However if you need real security and want to store sensitive information (social security numbers, credit cards, passwords, email addresses), there is absolutely no way around a high-quality database server. You cannot achieve this kind of security with MS-Jet. The pid can be broken into and the database ‘encryption’ is nothing more than a thin veil against casual prying eyes. These are just two of the ways one can break into an MS-Jet database.

About the author:

Hajo Kirchhoff has been a software developer for the past 15 years. His specialities are general problem solving, object oriented design and software architecture, deep C++ knowledge, GUI libraries and OS workings. Apart from contract work he also works as a software process consultant, helping clients to deliver their projects on time and within budget. His experiences with the software development process are also influencing the tools he writes.

His email address is   hajo [dot] kirchhoff {at} litwindow [dot] com

About the bug tracking tool BugLister:

This small tool was born out of frustration over the lack of a simple bug tracking solution that is easy to set up and use and does not require a database or webserver. Originally developed only as a case study in MFC and OLE DB it has found many customers since 2003. If you are looking for a small, efficient and affordable bug tracking tool you can find more information about it at http://www.litwindow.library/buglister

A full rewrite is currently in progress using wxWidgets as a GUI library to provide a cross platform defect tracking solution for Windows, Linux and Apple Mac.

[Home] [Products] [Shop] [Knowhow] [Library] [About]

© 2004, Hajo Kirchhoff, last modified Mar 06, 2008

email

back to top