The security database contains security-related tables for Stonefield Query. The records in this database are maintained in the Security dialog of Stonefield Query as well as step 6 of the report wizards. The database can be SQLite, Microsoft SQL Server, or any other database with a .NET provider; see the Using Other Databases for Security and Other Data topic for details. The connection information is stored in ApplicationSettings.xml in the App_Data folder. Because the data dictionary database and the security database both contain a table named Roles, they cannot be the same database. Tags and formulas, however, are normally stored in the same database as the security tables.

The security database consists of the following tables:

  • Users: contains the users.

  • Roles: contains the roles.

  • UserRoles: defines which users belong to which roles.

  • Permissions: contains the permissions roles have for various resources, including reports.

  • Tenants: contains the tenants.

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. In addition, if the Support multi-tenant environment configuration setting is set to True, a role named Tenant Administrator is also created.

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

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 name.
Password varchar(32) The user's password, stored as an encrypted value.
Active bit 1 (true) if the user can log in, 0 (false) if not.
LicenseType int The type of license this user uses: 2 for Report Designer, 3 for Report Viewer.
LastLogin datetime When the user last logged in.
Tenant char(36) The ID of the tenant this user belongs to; blank if the Support Multi-Tenant Environment configuration setting is False or if the user doesn't belong to any tenant (typically only the ADMIN or other administrative users).
Email varchar(80) The user's email address; this is used for technical support purposes.

Roles

ColumnTypePurpose
ID char(36) The unique ID.
Name varchar(60) The role name.
Internal bit 1 (true) if this is a built-in role and cannot be removed (Administrator, Everyone, and Tenant Administrator), 0 (false) if not.
Tenant char(36) The ID of the tenant this role belongs to; blank if the Support Multi-Tenant Environment configuration setting is False or if the role doesn't belong to any tenant (typically only Administrator and Everyone).

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 ID of the resource in the case of reports or data groups, the name of the resource otherwise.
RoleID char(36) The ID of the role this record is for.
Access int The type of access the role has to the resource: 0 for no access, 1 for read-only, 2 for full access.
ResourceName text The name of the resource, provided for readability only.
RoleName varchar(60) The name of the role, provided for readability only.

Tenants

ColumnTypePurpose
ID char(36) The unique ID.
Name varchar(60) The tenant name.
TenantID text The ID of the tenant in the target database; blank if the Filter Field for Queries in Multi-Tenant configuration setting isn't filled in.
DataSource text The name of the data source to use for this tenant; blank if the Filter Field for Queries in Multi-Tenant configuration setting is filled in.
LogoPath text The path for the logo used by this tenant for reports using a logo.