Skip to content

Standardized database view

When an application doesn't have permission files that could be used to import data into Permission Assist, this option may be used in specific situations. It allows Permission Assist to import information directly from a database view.

To connect to a database view, complete the following steps:

  1. Add the application as usual and select the Standardized Database View privilege reader. The application is created and the Settings tab is displayed.

  2. Change the Name field to the name of the application.

  3. Select the Priority field and pick a level of priority from the list.

  4. In the Connection String field, enter a standard ODBC connection string to connect to the database. Typically, this will look something like the following:

    • When using the Permission Assist service account to connect to the database: Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;

    • When using a SQL authenticated account to connect to the database: Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;

  5. In the View Name field, enter the name of the database view to which Permission Assist is connecting.

    Tip

    A schema can be specified by entering the schema and view name. Example: [schema].[ViewName]

  6. If you want to import inactive users, select the Inactive Users option.

    Note

    If the Inactive Users option is selected, the Active column must include the appropriate status for each user. If a status is not provided, Permission Assist assumes the account is active.

  7. When all information is complete, select the Save button.

Database view specifications

The following table describes the columns of information that can be imported and whether the column is required or optional.

Column Name Alternatively Accepted Column Names Required/Optional Description
Email E-mail, Email Address Optional Contains the user's email address.
Active Optional Indicates whether the user's account is active or disabled. If this column is missing or if the value returned is blank, Permission Assist assumes the user's account is active. Supported values: True/False, Y/N, Yes/No, 1/0.
Locked Optional Indicates whether the user's account is active but locked. If this column is missing or if the value returned is blank, Permission Assist assumes the user's account is not locked. Supported values: True/False, Y/N, Yes/No, 1/0.
Full Name Name Optional Contains the user's name. Typically, this is the user's full name, but could be entered in whatever format needed (first initial, last name; first name and last initial, etc.).
First Name Optional Contains the user's first name when a full name (in the Full Name column) isn't provided.
Last Name Optional Contains the user's last name when a full name (in the Full Name column) isn't provided.
Username User Name Required Contains the user's unique ID within the application.
Last Login Login Date, Last Login, Last Login Date Optional Contains the date of the last time the user logged into the application. Supported formats: M/d/yy, MM/dd/yy, M/d/yyyy, MM/dd/yyyy, 2019-12-31 14:43:35.863 (SQL).
Password Changed Password Changed Date, Password Changed On, Last Password Change Optional Contains the date of the last time the user's password was updated. Supported formats: M/d/yy, MM/dd/yy, M/d/yyyy, MM/dd/yyyy, 2019-12-31 14:43:35.863 (SQL).
Heading Optional Used to create headings for related sections and/or sets of permissions. Headings are displayed in dark grey within the Privileges tab.
Section Role Required only if the Permission column is absent; otherwise, optional Used to describe a set of defined privileges that the user has access to such as a role or template within the application. This column is required if a Permission column doesn't exist.
Permission Privilege Required only if the Section column is absent; otherwise, optional Defines the specific point of access/privilege the user has within the application. This column is required if a Section column doesn't exist.
Access Authorized Required Indicates whether the user has access to the role/privilege. Officially supports values: Y (has access), N (no access), RO (read-only access), RW (read-write access), and blank.
Job Title Title Optional Contains the user's job title.
Account Expiration Account Expires, Account Expires On, Account Expiration Date Optional Contains the date the user's account expires. Supported formats: M/d/yy, MM/dd/yy, M/d/yyyy, MM/dd/yyyy, 2019-12-31 14:43:35.863 (SQL).
Password Expiration Password Expires, Password Expires On, Password Expiration Date Optional Contains the date the user's password expires. Supported formats: M/d/yy, MM/dd/yy, M/d/yyyy, MM/dd/yyyy, 2019-12-31 14:43:35.863 (SQL).
Account Created Account Created On, Account Created Date, Created Date Optional Contains the date the account was created within the application. Supported formats: M/d/yy, MM/dd/yy, M/d/yyyy, MM/dd/yyyy, 2019-12-31 14:43:35.863 (SQL).
Account Updated Updated On, Last Updated, Account Updated Date Optional Contains the date of the last time the user's account was changed. Supported formats: M/d/yy, MM/dd/yy, M/d/yyyy, MM/dd/yyyy, 2019-12-31 14:43:35.863 (SQL).
Token Physical Token, Access Token Optional If the application uses a physical token for authentication, this column contains the token.
User Description Comments Optional Contains an additional description of the user's account.
Permission Description Privilege Description Optional Contains an additional description of the permission.