myIdea Logo

My Filemaker Pro Programming Standards: Database Security

This page is part of the internal FileMaker Pro programming standards of Lutz Pietschker. No claims of any sort can be derived from the description of these standards. In particular, no claim can be made that these standards are complete and without errors, and that any of my software projects follow to these standards in part or completely.
The page content was last revised on (ca. 2008)

Go to start page


Document Content

This document describes the security model for database access. It does not deal with general security issues such as backups, physical site security etc.

General Security Model

The model is built on the assumption that the database is always opened from the same point of access. Opening individual files is discouraged by omitting the file type extension; if users still try to open the files directly, they will have only very limited access (if any) if they do not open the file with a developer account and password.

Accounts

The following accounts and privilege sets exist in the database files:

File Account Privilege Set Remarks
start.fp7 (developers) [Full Access] Account names and passwords disclosed only to developer(s) and project manager
Admin Admin Password disclosed only to on-site administrator, may be changed by user
Layouter Layouter Special access privilege: May modify layouts in $rep file(s)
Password disclosed only to on-site administrator, may not be changed by user
EditAll EditAll Special access privilege: May edit all data in $dat file(s)
Password disclosed only to on-site administrator, may not be changed by user
ReadAll ReadAll Special access privilege: May read all data in $dat file(s)
Password disclosed only to on-site administrator, may not be changed by user
(username) (xxxUser) Password disclosed only to user, may be changed by user
xxxUser = any of FullUser, EditUser, ReadUser, ...
Default (project-dependent) Auto-Login; exists only for certain projects. Password may or may not be disclosed to user.
Guest BasicAccess (account usually disabled)
all other files (developer) [Full Access] Account name and password disclosed only to developers
account names are equal to priv. set name; passwords are only disclosed to developers except as noted otherwise [Full Access] [Full Access] pre-defined privilege set
Admin Admin Privileges vary by database file type (see below); password may be disclosed to and changed by local admin user
Layouter Layouter Special access privilege: May modify layouts
EditAll EditAll Special access privilege: May edit all data
ReadAll ReadAll Special access privilege: May read all data
FullUser FullUser May create and edit records
EditUser LimitedUser May edit records
ReadUser LimitedUser May view, but not edit, records
(xxxUser) (xxxUser) special privileges as needed
BasicAccess BasicAccess Execute scripts and view layout access only; default auto-login account
Guest BasicAccess (account usually disabled)

As you can see, access privilege set names in the start.fp7 file translate to account names in all "other files": Each access privilege set used in start.fp7 corresponds to one account in the "other files". The only explicit users defined in the "other files" are the developer account(s).
Note also that the passwords in the "other files" are not known to the users or even to the on-site administrator. This prevents that any on-site users opens one of the other files directly with any account other than than "BasicAccess" (if this account is defined as the default access account); since the access privileges of this account are extremely limited, damage by using a "side-entrance" into the database is not likely.

Please note that not all privilege sets are necessarily present in all files. The ReLogin script of each file takes care of that by assigning an appropriate access type (usually BasicAccess) to all accounts not actually present in the file.

Privileges By File Type

These are the privilege sets that exist in the standard database files. In addition, at least one developer account is defined in all files and it always has the pre-defined [Full Access] privilege. Also, the BasicAccess privilege set exists in all files.

File Priv. Sets Remarks
start.fp7 (all) In the start file all privilege sets need to be pre-defined because they are used to access the corresponding user/priv.set combinations in the other files; however, access of all privilege sets is equal to BasicAccess
$gui (none)
$rep Admin, Layouter Layouts and scripts changeable by Layouter (to create custom reports etc.)
$app (none) Special layout(s) with view access for Admin may exist
$app_<...> (none) Special layout(s) with view access for Admin may exist
$dat Admin, EditAll, ReadAll, xxxUser sets This is the main data repository; access regulations for the xxxUser privilege sets may be quite elaborate here
$dat_<...> Admin, EditAll, ReadAll, xxxUser sets similar to $dat; however, access may be restricted in certain cases, e.g. for non-changeable reference data
$hist Admin, ReadAll, xxxUser sets usually managed by scripts only; xxxUser sets allow access to own entries or all entries, depending on the database
$trans Admin usually managed by scripts only
$roll (none) only managed by scripts
$sys (none) only managed by scripts
$lng Admin BasicAccess includes ReadAll in this file
$lng_<...> Admin BasicAccess includes ReadAll in these files

Detailed List of Privileges by Privilege Set

Developers have the pre-defined [Full Access] privilege set in all files.

Admin Layouter EditAll ReadAll FullUser EditUser ReadUser (xxxUser) BasicAccess
Records
Create, Edit, Delete + - - - + - - - -
Create, Edit + - + - + + - - -
View only + + + + + + + - -
Custom Access (depends on database) - - - (depends on database) -
Layouts
Modify - + - - - - - - -
View + + + + + + + + +
Custom Access (depends on database) - - - - - - - -
Value Lists
Modify - - - - - - - - -
View + + + + + + + + -
Custom Access (depends on database) - - - (depends on database) -
Scripts
Modify - + - - - - - - -
Execute + + + + + + + + +
Custom Access (depends on database) - - - - - - - -
Others
Allow Printing + + + + + + + (depends on database) -
Allow Exporting + - + + + + + -
Manage Extended Priv's - - - - - - - - -
Override Data Validation - - - - - - - - -
Disconnect from Server When Idle + + + + + + + + +
Allow to Change Own Password Admin may be allowed to change these passwords for the start.fp7 file (only) Users may be allowed to change their passwords for the start.fp7 file (only) -
All Menu Commands + + - - - - - (depends on database) -
Editing Menu Commands + + + + + + - -
Minimum Menu Commands + + + + + + + +
Extended Privileges
via Inst. Web Publishing (depends on database, by default these privileges are switched off for all users)
via ODBC/JDBC
via FM Network
via FM Mobile

Log-In Procedure

The "OnOpen" script of the start.fp7 file performs the following security-related steps:

  1. If no auto-login is defined, or if the auto-login password is missing, or if the file was opened with the "Option" key (Mac) or "Shift" key (Win), FileMaker asks for the log-in username and password.
    The following steps assume a correct username and password was entered at this step, or that a working auto-login was defined.
  2. The script dis-allows user abort.
  3. The script performs an "Open File" script step plus, immediately after that, an "Excecute Script(Re-Login)" for each of the following files:
    $gui, $app, $dat, $hist, $rep, $lng, plus for any $app_<...> and $dat_<...> files that may be part of the database.
    Note that $sys, $trans and $roll are not opened at this time; see remark below this list.
  4. The script runs the Re-Login script of each file just opened. As a parameter, it gives the privilege set name of the user that logged into start.fp7. The Re-Login script logs into the account of the same name in this file.
    Note: The Re-Login script knows the passwords for all the accounts it manages. (This password is only disclosed to the developers.)
  5. The script writes the account name to a field in the $app file. Whenever the original log-in account name is needed later, it is fetched from this field.
  6. The script allows user abort again and perform any remaining (not security-relevant) tasks.

Remark: The $sys, $trans, and $roll files are never accessed by the user directly, values are fetched and written there by scripts only, or they are global values that need no explicit relation to access them. $lng_<code> files are only used temporarily to import new or updated language resources (see chapter on localisation).


This page is copyrighted by the author according to the copyright note.
All rights reserved. Lutz Pietschker, Berlin/Germany, 2011 ff.

, last change 2011-03-12