Home > Designing and creating solutions > Protecting databases > Managing privilege sets > Editing record access privileges

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 need to create accounts and privilege sets in the other files to protect those tables. See About protecting databases.

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.

 

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

3.	Click OK, then click OK in the Manage Security dialog box.

Or, to assign the privilege set to one or more accounts, see Creating and editing accounts.

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.)

•	To select one table, click its name.

•	To select multiple tables, Ctrl-click (Windows) or Command-click (OS X) each name.

•	To select a range of adjacent tables, click the first name, and then Shift-click the last name in the range.

•	To select all tables, click Select All.

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:

 

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.

5.	To limit access or modification of certain fields within a table, continue with the next step. Otherwise, click OK and skip ahead to step 9.

6.	Select the table that contains the fields for which you want to limit access. Then, for Field Access, choose Limited.

7.	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:

 

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.

8.	Click OK twice.

You see the Edit Privilege Set dialog box.

9.	Click OK, then click OK in the Manage Security dialog box.

Or, to assign the privilege set to one or more accounts, see Creating and editing accounts.

Notes

•	In the Custom Record Privileges and Custom Field Privileges dialog boxes, you can change the order of tables or fields in the list by clicking one of the column headings. To switch between ascending and descending order, click the column heading again.

•	If you prohibit creating records in a file or table, then duplicating records is prohibited as well.

•	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 Pro 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 Pro does not 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, you need to 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:

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:

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. (There is no single summary value for a single file as in FileMaker Pro 6 and earlier versions.) 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.