Database Security

Database Roles

The UAAR Enterprise DataLog controls access to the database through SQL Database Roles. Five roles control which tables, views, and stored procedures a specific user can access. The SQL "public" role is not granted any permissions in the UAAR Database.

Database roles can be assigned directly to Windows Domain Accounts or Domain Groups to provide integrated security. Although it is possible to access the Enterprise database though SQL Accounts, it is not recommended.

All of the Database roles can be assigned in the UAAR DataLog directly. However these roles can also be assigned directly in the database using Enterprise Manager or SQL.

UAAR_ReadOnly
This user has access to search for and view sales in the database but cannot execute the stored procedures that insert or update sales data.
UAAR_User
This user has access to search for and view sales and insert or update sales. In order to insert or update sales users must also have permissions to a specific Access Group.
UAAR_PowerUser
This user has all of the same rights as the UAAR_User. In addition, the Power User has read access to additional supporting tables and views. This access is intended for users that need direct access to the database for ad hoc reporting.
UAAR_BusinessAdmin
This user has access to setup Rule Sets and the Sale Index. As part of that setup they can control the Server Responses. This includes marking responses as approved and merging responses to clean up sales data.
The UAAR_BusinessAdmin role is also required to delete sales from the Enterprise database.
UAAR_SystemAdmin
This user has access to setup Database Roles and Access Groups. In order to setup Database Roles for new users a SQL database user may need to be added. To accomplish this the UAAR_SsytemAdmin role is granted the system role of db_securityadmin. If a domain account or group is given access to a database role and that user is not a user in the database the system stored procedure sp_grantDBaccess is executed to grant that user access.
The db_securityadmin role allows Database Users to be added but not Server Logins. The UAAR DataLog does not have the ability to add a Server Login. That needs to be setup using other database management tools. Server Logins do not need to be granted for each user. Access can be granted to a group and then individual access (via user accounts or domain groups) can be granted to roles.

Access Groups

Access Groups control who can see specific sales in the Enterprise Database. Each sale is assigned a single access group by the user entering the sale. Members of that group then have read or read/write access.

Access groups and their members must be setup by a user with the UAAR_SystemAdmin role. Normal users cannot add or change access groups, they can only select a group that defines the access permissions for their sale. When editing a sale the list of access groups will only contain groups where the user has "write" access.

Access group members can be domain users, domain groups, or SQL Users. Domain groups that are added to an access group do not need to match the domain groups used for database roles. Different combinations of groups and roles can be used as convenient when setting up roles and access groups.

If a user is granted access though multiple groups the group with the least restrictive (write access) access will apply.

A user with the UAAR_ReadOnly database role will still only be able to see sales with a matching access group.

Access groups allow a large degree of flexibility in controlling access to sales. At the simplest level all users can be granted read/write access to all sales by creating a single access group and giving a domain group that encompases all users read/write access. If multiple offices need to see but not edit each other's sales a group per office can be setup granting the office users read/write access and all other users read only access.

To add a new group click on the cell at the top of the grid that reads "Click Here to add a new row". Type the name of the group and press enter. Then expand the plus sign next to the group name. At the bottom of the area that opens their will be a blue plus sign. Click on the plus to add members to this group.