Editing record access privileges

Privilege sets can limit access to records in a file. For a file, you can set:

  • Privileges for all tables: you can limit whether a privilege set allows creating, editing, and deleting records in all the tables in a file.
  • Custom privileges for individual tables: you can set individual record access limits for each table. For example, the privilege set can limit the ability to:
    • View, edit, create, or delete all records in each table.
    • View, edit, and delete certain records within each table. The privileges are limited by means of calculation that returns a Boolean result for each record. When the calculation evaluates to True, access is allowed for that specific activity (such as viewing the record). When the calculation evaluates to False, access to that activity is prohibited.
    • Access or modify certain fields within each table. When access to one or more fields is restricted in a table that is otherwise visible, the user will see <No Access> instead of the field data.

You can only set record access privileges for tables defined in the current file. If the file contains relationships to tables in other files that you want to protect, you must create account access entries and privilege sets in the other files to protect those tables. See About accounts, privilege sets, and extended privileges.

To edit record access privileges for all tables:

  1. Start editing a new or existing privilege set in the Edit Privilege Set dialog box.

    See Creating and editing privilege sets.

  2. In the Data Access and Design area, for Records, choose an option.
  3. To edit the privilege set to

    Choose

    Permit creating, editing, and deleting records in all tables

    Create, edit, and delete in all tables

    Permit creating and editing records only (prohibit deleting records) in all tables

    Create and edit in all tables

    Permit viewing records only (prohibit creating, editing, and deleting records) in all tables

    View only in all tables

    Prohibit accessing records in all tables

    All no access

To edit record access privileges for individual tables:

  1. Start editing a new or existing privilege set in the Edit Privilege Set dialog box.

    See Creating and editing privilege sets.

  2. In the Data Access and Design area, for Records, choose Custom privileges.
  3. In the Custom Record Privileges dialog box, select one or more tables for which you want to edit custom privileges. (Or select Any New Table to set privileges for any table created later.)
  4. To set custom privileges for viewing, editing, creating, and deleting records, choose one of the following options from the View, Edit, Create, and Delete lists in the Set Privileges area:
  5. Choose

    To

    Yes

    Allow the privilege (viewing, editing, creating, or deleting records).

    Limited (available when only a single table is selected)

    Allow or prohibit the privilege (viewing, editing, and deleting records) for certain records within the table.

    In the Specify Calculation dialog box, enter a calculation, and click OK. See Entering a formula for limiting access on a record-by-record basis, below.

    No

    Prohibit the privilege (viewing, editing, creating, or deleting records).

    Note  Avoid creating inconsistent combinations of view, edit, create, and delete privileges. For example, you most likely do not want to permit users to delete records that they cannot view.

  6. To limit access or modification of certain fields within a table, continue with the next step.
  7. Select the table that contains the fields for which you want to limit access. Then, for Field Access, choose Limited.
  8. In the Custom Field Privileges dialog box, select each field for which you want to limit access (or select Any New Field to set custom privileges for any field created later). Then choose one of the following options:
  9. Choose

    To

    Modifiable

    Allow viewing and editing of the field data.

    View only

    Allow viewing of the field data. (Editing of field data is not allowed.)

    No access

    Prohibit access to the field.

    Note  When access to one or more fields is prohibited in a table that is otherwise visible, the user will see <No Access> instead of the field data.

Notes 

  • Users that cannot view certain records can still browse these records, but the user will see <No Access> instead of the field data within each record.
  • Many features in FileMaker clients are affected when you limit access to records, fields, and tables within a file. The following table summarizes the effects for some features.
  • Feature

    Effect of limiting data access

    Lookups and Relookups

    Lookups and relookups are defined by three components, all of which can be affected by data access privileges:

    • At a minimum, the match fields used in the relationship must have view privileges. Also, no lookup will occur for any records that are not viewable in both the source and destination tables because a match cannot occur under these circumstances. This includes the first related matching record, as well as any records used to copy the next higher or lower value.
    • At a minimum, the source field for the lookup must have view privileges.
    • The destination field for the lookup must have edit privileges.

    FileMaker clients don’t display an error message if a lookup operation skips certain records due to access privilege restrictions.

    Accessing related data

    At a minimum, both matching fields in a relationship must have view privileges in order for the relationship to work. Any related fields you want to display based on the matching fields must also have view privileges at a minimum.

    Finds

    The found set is always filtered to display only those records that have view privileges at a minimum. The results of the Get(FoundCount) function are also affected if access is limited.

    Value lists

    When a value list is based on the content of a field, and view privileges are prohibited on the field, then the value list does not display any values. This is so that users do not inadvertently gain access to confidential information. The results of the ValueListItems function are also hidden when access is limited.

    Summary fields and calculated fields

    The results displayed in summary fields as well as calculated fields which summarize over multiple records will exclude data from records for which view privileges have been prohibited.

    Find/Replace, Replace Contents, and spelling checking

    A user can only change data using these features in:

    • records that have edit privileges.
    • fields that are modifiable.

    Scripts

    You can set an option for a script to run with full access privileges, which permits a script to perform operations that a user with limited privileges would not usually be able to do. If you don’t enable this option, any script steps that attempt to access or change restricted data will fail. See Creating and editing scripts.

    Apple events

    Data access privileges can affect the ability of a user to get, set, and delete data using Apple events. Some Apple event commands may fail if the user does not have appropriate access privileges.

Entering a formula for limiting access on a record-by-record basis

To allow or prohibit record viewing, editing, and deleting privileges to certain records within a table as described above, specify a Boolean calculation that determines whether the privilege is permitted. For each record in the database, access is allowed when the calculation evaluates to True or to a non-zero result, and access is prohibited when the calculation evaluates to False or zero. Here are two examples:

  • To limit access to only those records created by the current account: Define a text field named Record_Created_By, and set the auto-enter option for the field to automatically enter the account name when the record is created. (See Defining automatic data entry.) Then use the following calculation when defining custom record access privileges:
    Copy
    Record_Created_By = Get(AccountName)

    The user will only have Browse access to records for which the above calculation evaluates as True.

  • To limit access to only those records created on the current date: Define a date field named Record_creation_date, and set the auto-enter option for the field to automatically enter the creation date when the record is created. (See Defining automatic data entry.) Then use the following calculation when defining custom record access privileges:
    Copy
    Record_creation_date = Get(CurrentDate)

    The user will only have Browse access to records for which the above calculation evaluates as True.

Tip  If you specify a calculation to prohibit viewing of certain records within a table, in most cases you should use the same calculation to prohibit editing and deleting of the same types of records. Otherwise, you may inadvertently allow users to edit or delete records that they cannot view.

Notes 

  • The Boolean calculation that determines record access privileges can have unexpected results, particularly if it is based on a user-editable field. For example, it’s possible for a user with access privileges for only certain records to inadvertently deny themselves access to a record after editing it. The user could make a change to a record’s content that changes the result of the Boolean record access calculation so that it evaluates to false; then that user would no longer be able to view, edit, or delete the record once he or she exits that record.

    Because changes are committed as soon as a user exits a record, a user making changes of this type will not be able to return to the record. (In most cases, you should base record access calculations on fields that are not directly editable by users, such as auto-entered fields that contain account names, creation dates, and modification dates.)

    Also, if the file is shared and the Boolean calculation that determines record access privileges contains one or more global fields, you may be able to improve network performance by moving some global fields into a separate table. See the Notes section in Defining global fields (fields with global storage).

  • To fully support multiple windows that have their own found sets, summary values are a part of the found set. A record may appear in multiple windows, and each window has its own found set and sort order. For these reasons, do not base privilege calculations on summary or Get functions that rely on values using a particular found set.