SQL Server database role permissions

Each database role requires a specific set of permissions to complete the actions attributed to them.

DBA_Role

The DBA_Role requires permissions to:

  • Set up and maintain the database management system and Information Store database.
  • Create and modify the database management system objects. For example, bufferpools, tablespoons, and filegroups.
  • Create and modify database objects. For example, tables, views, indexes, sequences.
  • Troubleshoot performance or other issues. For example, has all privileges on all tables. This can be restricted in some environments.
  • Configure high availability.
  • Manage recovery activities.

Additionally, the role requires access to two roles in the msdb database:

These roles are required for database creation, to initialize the deletion-by-rule objects, and to create the SQL Server Agent jobs for the deletion rules. Note: The configure_dba_roles_and_permissions.sh script is run during deploy to grant the correct permissions.

The following table provides an overview of the permissions required on the schemas in the Information Store database:

Schema Permissions Notes
All CREATE TABLE, CREATE VIEW, CREATE SYNONYM Required to create the database objects.
All ALTER, SELECT, UPDATE, INSERT, DELETE, REFERENCES Required to make changes for maintaining the database.
IS_Core EXECUTE Required for deletion-by-rule and database configuration.
IS_Public EXECUTE Required to run the stored procedures for deletion-by-rule.

The following table provides an overview of the permissions required on the schemas in the msdb database:

Schema Permissions Notes
dbo SQLAgentUserRole Required to create the deletion jobs during deployment, and to manage the deletion job schedule.
dbo db_datareader Required to create the deletion job schedule.

The following table provides an overview of the permissions required on the schemas in the master database:

Schema Permissions Notes
All VIEW SERVER STATE Required for deletion-by-rule automated jobs via the SQL Server Agent.
sys EXECUTE ON fn_hadr_is_primary_replica Required for deletion-by-rule automated jobs.

The configure_dba_roles_and_permissions.sh script is used to configure the DBA user with all the required role memberships and permissions.

External_ETL_Role

The External_ETL_Role requires permissions to move data from external systems into the Information Store staging tables.

For example, it can be used by an ETL tool - such as DataStage or Informatica - to move and transform data that results in populated staging tables in the Information Store staging schema.

The following table provides an overview of the permissions required on the schemas in the Information Store database:

Schema Permissions Notes
IS_Staging SELECT, UPDATE, INSERT, DELETE Required to populate the staging tables with date to be ingested or deleted.

In addition to these permissions, in an environment running SQL server in a Linux container, users with this role must also be a member of the sysadmin group in order to perform BULK INSERT into the external staging tables.

The add_etl_user_to_sys_admin_role.sh script is used to make the etl user a member of the sysadmin fixed-server role.

i2_ETL_Role

The i2_ETL_Role requires permissions to use the i2 Analyze ingestion tools to ingest data from the staging tables into the Information Store.

The following table provides an overview of the permissions required on the schemas in the Information Store database:

Schema Permissions Notes
IS_Staging ALTER, SELECT, UPDATE, INSERT, DELETE Required by the ingestion tools to create and modify objects during the ingestion process.
IS_Stg ALTER, SELECT, UPDATE, INSERT, DELETE Required by the ingestion tools to create and modify objects during the ingestion process.
IS_Meta SELECT, UPDATE, INSERT UPDATE and INSERT are required to update the ingestion history table. SELECT is required to read the schema meta data.
IS_Data ALTER, SELECT, UPDATE, INSERT, DELETE ALTER is required to drop and create indexes and update statistics as part of the ingestion process.
IS_Public ALTER, SELECT ALTER is required to delete and create synonyms when enabling merged property views.
IS_Core SELECT, EXECUTE Required to check configuration of the database.

i2analyze_Role

The i2analyze_Role requires permissions to complete actions required by the Liberty application. These actions include:

  • Visual Query, Find Path, Expand, Add to chart, Upload, and Online upgrade.

The following table provides an overview of the permissions required on the schemas in the Information Store database:

Schema Permissions Notes
IS_Staging ALTER, SELECT, UPDATE, INSERT, DELETE Required to run deletion-by-rule jobs.
IS_Stg ALTER, SELECT, UPDATE, INSERT, DELETE Required to upload and delete records via Analyst's Notebook and to run deletion-by-rule jobs.
IS_Meta SELECT, UPDATE, INSERT, DELETE DELETE is required to process the ingestion history queue.
IS_Data SELECT, UPDATE, INSERT, DELETE UPDATE, INSERT, and DELETE are required by deletion-by-rule and to upload and delete records via Analyst's Notebook.
IS_Core SELECT, UPDATE, INSERT, DELETE Required for online upgrade.
IS_VQ SELECT, UPDATE, INSERT, DELETE Required to complete Visual Queries.
IS_FP SELECT, INSERT, EXEC Required to complete Find Path operations.
IS_WC SELECT, UPDATE, INSERT, DELETE Required to work with Web Charts.
IS_PUBLIC EXEC Grants execute permission to stored procedures in the IS_PUBLIC schema. Required to create and send Alerts.

Deletion_By_Rule

The Deletion_By_Rule requires permissions to access/execute objects in the Information Store public schema.

The following table provides an overview of the permissions required on the schemas in the Information Store database:

Schema Permissions Notes
IS_Public SELECT, EXEC Require to run deletion-by-rule jobs.

The add_user_to_db_role.sh script is used to make the i2etl user a member of the Deletion_By_Rule database role.

i2_Public_Role

The i2_Public_Role requires permissions to access/update objects in the Information Store public schema.

The following table provides an overview of the permissions required on the schemas in the Information Store database:

Schema Permissions Notes
IS_Public ALTER, SELECT, UPDATE, INSERT, DELETE, EXEC Required to complete actions that require access, modification, and execution permissions to objects in the IS_Public schema. For example, to use custom task scheduling.

The add_user_to_db_role.sh script is used to make the i2analyze user a member of the i2_Public_Role database role.

The database backup operator role

The example also demonstrates how to perform a database backup. The dbb user will perform this action and is a member of the SQL Server built-in role, db_backupoperator. This gives this user the correct permissions for performing a backup and nothing else.

For more information, see Fixed-Database Roles for more details.

Database users and logins

In the example, a user is created for each role described previously. These users are then used throughout the deployment and administration steps to provide a reference for when each role is required.

The following users and logins are used in the example:

User and login Description Secrets
sa The system administrator user. The sa user has full permissions on the database instance. This user creates the Information Store database, roles, users, and logins. The password is in the sa_PASSWORD file in the environment-secrets/generated-secrets/sqlserver directory.
i2analyze The i2analyze user is a member of the i2analyze_Role and i2_Public_Role. The password is in the i2analyze_PASSWORD file in the environment-secrets/generated-secrets/sqlserver directory.
etl The etl user is a member of External_ETL_Role. The password is in the etl_PASSWORD file in the environment-secrets/generated-secrets/sqlserver directory.
i2etl The i2etl user is a member of i2_ETL_Role and Deletion_By_Rule The password is in the i2etl_PASSWORD file in the environment-secrets/generated-secrets/sqlserver directory.
dba The dba user is a member of DBA_Role. The password is in the dba_PASSWORD file in the environment-secrets/generated-secrets/sqlserver directory.
dbb The dbb user is the database backup user, it is a member of the SQL Server built in role: db_backupoperator. The password is in the dbb_PASSWORD file in the environment-secrets/generated-secrets/sqlserver directory.

The sa user and login exists on the base SQL Server image. The sa user is used to create the following artifacts:

  • Database: ISTORE
  • Roles: i2analyze_Role, External_ETL_Role,, i2_ETL_Role, DBA_Role, Deletion_By_Rule and i2_Public_Role
  • Logins: i2analyze, etl, i2etl, dba, and dbb
  • Users: i2analyze, etl, i2etl, dba, and dbb

The roles and users must be created after the Information Store database is created.

Creating the roles

The sa user is used to run the create_db_roles.sh client function that creates the i2Analyze_Role, External_ETL_Role, i2_ETL_Role, i2_Public_Role and DBA_Role roles.

To create the roles, the create_db_roles.sh script is run using the run_sql_server_command_as_sa client function, and the grant_permissions_to_roles.sh scripts is used to give the correct permissions. This function uses an ephemeral SQL Server client container to create the database roles. For more information about the client function, see: run_sql_server_command_as_sa

In the example, the create_db_roles.sh script is called in deploy.

Note: All the secrets required at runtime by the client container can be made available by providing a file path to the secret which is converted to an environment variable by the docker container.

For example, to provide the SA_USERNAME environment variable to the client container, a file containing the secret is declared in the docker run command:

-e "SA_USERNAME_FILE=/run/secrets/SA_USERNAME_FILE"

The file name can be anything, but the environment variable is fixed. For more information see, managing container security

Create the login and user

Use the sa user to create the login and the user on the ISTORE, and make the user a member of the role.

You can use an ephemeral SQL Client container to create the login and the user.

The create_db_login_and_user.sh script is built into the sql client image in /opt/db-scripts location and is used to create the login and user. The script is called from the deploy commands.

The create_db_login_and_user function

The create_db_login_and_user function uses an ephemeral SQL Client container to create the database administrator login and user. The login and user are created by the sa user.

For more information about running a SQL Client container and the environment variables required for the container, see SQL Client.

The create_db_login_and_user.sh script is used to create the login and user.

The function requires the following environment variables to run:

Environment variable Description
SA_USERNAME The sa username.
SA_PASSWORD The sa user password.
DB_USERNAME The database user name.
DB_PASSWORD The database user password.
DB_SSL_CONNECTION Whether to use SSL for connection.
SSL_CA_CERTIFICATE The path to the CA certificate.
DB_SERVER The fully qualified domain name of the database server.
DB_PORT Specifies the port number to connect to the Information Store.
DB_NAME The name of the Information Store database.
DB_ROLE The name of the role that user will be added to. It has to be one of the roles from this list.

Changing SA password

In a Docker environment, you must start the SQL Server as the existing sa user before you can modify the password.

The change_sa_password function

The change_sa_password function uses an ephemeral SQL Client to change the sa user password.

For more information about running a SQL Client container and the environment variables required for the container, see SQL Client.

The change_sa_password.sh script is used to change the password.

The function requires the following environment variables to run:

Environment variable Description
SA_USERNAME The sa username
SA_OLD_PASSWORD The current sa password.
SA_NEW_PASSWORD The new sa password.
DB_SSL_CONNECTION Whether to use SSL for connection.
SSL_CA_CERTIFICATE The path to the CA certificate.
DB_SERVER The fully qualified domain name of the database server.
DB_PORT Specifies the port number to connect to the Information Store.