SQL Server Security Setup

Overview

SQL Security rights roughly coincide with the rights assigned to the various Vertican License Manager tiers. This documentation explores the required SQL Server rights for each tier and what vMedia functions are affected by the SQL Server rights.

SQL Server security for vMedia must be set up by the database owner/administrator.

Reader (db_datareader)

Reader rights provide the ability to read tables. All vMedia users need this role at a minimum. Reader rights provide access to the Viewing Module's Search, View, Print, Export, Email, Reports, Tag, and Sort functions. Scanning Module functions are restricted.

Viewer User Tier

Users on this tier need db_datareader (Reader) role membership to view and print (these functions in the Viewing Module: Search, View, Print, Export?, Email, Reports, Tag, Sort.

Writer (db_datawriter)

Some configuration changes require no special rights in SQL at this time, such as making changes to the CF table. Eventually, if the CF table is ever moved exclusively to SQL, CF table changes will require Writer rights at a minimum. Without this right certain functionality such as importing or deletion will fail with a SQL error. Writer does not grant Execute privileges.

Writer rights provide:

  • Access to the Viewing Module's Edit Index Entry, Delete, and Export functions
  • Access to all Scanning Module functions such as Scan, Page Control, Add Reference, and Image Control
  • Access to the Import/Export Module's functions such as Hanna Importer and Class 2 Exporters
  • Ability to make non-structural configuration changes
  • Ability to insert/update records

Standard User Tier

Users on this tier need db_datareader (Reader) and db_datawriter (Writer) to use Print to vMedia and File to vMedia.

Scanner User Tier

Users on this tier need db_datareader (Reader) and db_datawriter (Writer) to scan documents, add and delete pages, edit index entries, and add references.

Power User Tier

Users on this tier need db_datareader (Reader) and db_datawriter (Writer) to import and export media.

Database Administrator/Owner (db_owner )

The administrator determines the necessary security rights for users depending upon their database level roles. The db administrator has complete rights to create, update, delete, change default values, and configure. Database configuration includes structural changes or changes to the indexes/Fast Find features, or new database creation from scratch. Installation utilities such as VM2SQL require db owner/administrator rights if the tables and indexes don't already exist.

Administrator User Tier

Users on this tier need db_datareader and db_datawriter to make non-structural configuration changes. Administrators will need db_owner in order to make structural changes, or changes to the indexes/Fast Find features, or new database creation from scratch. This also includes adding/deleting fields, changing an existing field's default value.

Restricted Role Issues

vMedia performs a consistency check when the database is selected. This includes checking for the required indexes and creating them if needed. In restricted role environments this causes a few issues:

  • If the users are not allowed to query the system tables for basic column state information, vMedia will think there aren't any indexes and will notify the end-user of the issue.
  • Everyone needs the View Definition right, most likely on the database container for the vMedia tables. If the SQL indexes are missing, an attempt is made to create only the missing indexes.

Execute

There are currently no stored procedures in vMedia, but this right will be required for certain tasks. There is no fixed role that contains Execute. You have to grant Execute to the users or groups individually as needed.

View Definition

View Definition is another layer of security. By using View Definition it is possible to allow users that only have public access the ability to see object definitions. Granting View Definition to users, groups, and roles for the database will avoid missing index issues on all vMedia tables created within it.

 

Reclink Key Issuance

  • SQL Server Version 11 (SQL Server 2012) or Greater. (Note: Older versions of SQL server will continue to use the CF.DBF based key issuance mechanism.)
  • The 1st user that saves media in vMedia 2.8 on a qualifying SQL Server version will need to have the CREATE SEQUENCE privilege. If the user does not have the proper rights at the time, vMedia will fall-back to the earlier key issuance mechanism (CF.DBF) and display a warning dialog to the user:

 

  • After the SEQUENCE is established for a vMedia database, regular ALTER rights will suffice. (db_datawriter Role)
  • In lieu of adding the permission for all users, an alternative would be to have a user with db_owner Role save any document using any mechanism in vMedia after vMedia 2.8 is deployed.
  • If the sequence rights are not available and the fall-back mechanism is used, the media will save normally (albeit with a warning displayed). The client can continue to use vMedia in this fashion indefinitely if desired.