Four tables manage Stonefield Query security:

  • Users: contains the names of the users who can access Stonefield Query.

  • Roles: contains the names of the roles in Stonefield Query. These are not the roles defined in the data dictionary but additional roles defined in the Security dialog.

  • UserRoles: specifies which users are members of which roles.

  • Permissions: contains the security access of roles to various things.

In addition to these, the roles that can access a particular table or field are stored in the Roles column of the appropriate data dictionary table.

Do not write to these tables directly. Instead, use the Security dialog to make changes visually or the Stonefield Query API to do it programmatically. The database containing these tables doesn't need to be distributed to your users; it's automatically created the first time a user runs Stonefield Query, and the tables are populated with default records:

  • Two roles are automatically defined and cannot be renamed or deleted: Administrator and Everyone.

  • One user is automatically defined: ADMIN, which belongs to the Administrator role. This user can be renamed but can only be deleted if there's at least one other member of the Administrator role.

By default, Stonefield Query uses a SQLite database named SQData.dat in the App_Data folder for these tables (also by default, that same database is also used for tags and formulas). However, you can use a different database engine if you wish; see the Using Other Databases for Security and Other Data topic for details.

The structure of each table is discussed next. In all tables, IDs are Globally Unique Identifiers (Guid) stored as Char(36).

Users

ColumnTypePurpose
ID char(36) The unique ID.
Name varchar(80) The user's login name.
Password varchar(32) The password (encrypted).
Active bit 1 if the user is active, 0 if not.
LicenseType int The type of license the user uses: 2 for Report Designer, 3 for Report Viewer.
LastLogin datetime When the user last logged in.

Roles

ColumnTypePurpose
ID char(36) The unique ID.
Name varchar(60) The role name.
Internal bit 1 if this is an internal role, meaning it can't be removed, or 0 if not.

UserRoles

ColumnTypePurpose
ID char(36) The unique ID.
UserID char(36) The ID of the user this record is for.
RoleID char(36) The ID of the role this record is for.

Permissions

ColumnTypePurpose
ID char(36) The unique ID.
Resource varchar(60) The resource this record is for. This could be a ID or a resource name.
RoleID char(36) The ID of the role this record is for.
Access int The type of access the role has for the resource: 0 means none, 1 means read-only, and 2 means full access.