Administer Custom Databases

Manage custom database security on managed SQL Server instances

Overview

Data Bridge enables Intelligent Risk tenants to store and manage data in a centralized, cloud-based store. Intelligent Risk tenants may use the Data Bridge integration service to share data between managed SQL Server instances and on-premise SQL Server instances.

RMS does not restrict the types of data that your organization stores on the cloud or the data that you upload and download using the Data Bridge API. Intelligent Risk tenants may create custom databases on managed SQL Server instances and store and manage custom data (e.g. proprietary reference data) in those custom databases.

Importing and exporting custom databases

The Data Bridge API defines operations that enable authorized clients to transfer databases between on-premise and managed SQL Server instances. These operations may be used for custom SQL Server databases as well as EDM and RDM data modules.

Uploading custom data to Data Bridge enables your organization to manage and share data in a common repository on the cloud. Members of globally distributed organizations can access and share the same pool of data.

800800

The same Data Bridge API operations that enable you to import EDM and RDM databases allows you to share custom databases with other members of your organization. Users with Contributor privileges can import or export custom databases.

Custom databases may be uploaded to and downloaded from managed SQL Server instances as database artifacts.

  • The Import Small Databases workflow enables you to upload and import data from relatively small database artifacts. Use this workflow for one-off uploads of smaller database artifacts (less than 5GB in size).
  • The Import Large Databases workflow enables you to upload and import large files (greater than 5GB in size) in multiple parts.
  • The Export Databases workflow enables you to export custom databases as database artifacts.

Data Bridge employs role-based access controls based on group membership to manage API access to data hosted on managed SQL Server instances.A user must must belong to the Contributor group to import or export databases using the Data Bridge API. For details see Administer Logins.

Custom database security

An Intelligent Risk tenant has complete control over all custom databases on all managed SQL Server instances. Tenant administrators may implement and manage access at the schema-level by means of two custom database roles that are deployed and managed by the Data Bridge service.

A database role is database-level principal that has access to resources on a SQL Server instance. Data Bridge uses custom database roles to administer database access for contributors or consumers. The Contributor and Consumer groups are mapped to the corresponding database roles:

  • Users assigned the Contributor group are automatically assigned to the SQL Server RMS_CONTRIBUTOR _PERMISSIONS database role and are granted all permissions assigned to that role.
  • Users assigned the Consumer group are automatically assigned to the SQL Server RMS_CONSUMER_PERMISSIONS database role and are granted all permissions assigned to that role.

📘

RMS restricts access to databases and tables used by the Intelligent Risk platform (e.g. the EDM and RDM data modules). Clients may read or write data, depending on their login type, however, users cannot alter objects in the dbo schema.

Schema-level security

RMS recommends that you implement security on managed SQL Server instances by means of role-based access controls that are defined at the schema level.

Granting permissions to database roles on a schema-by-schema basis enables you to manage data access at a granular level. A schema is a collection of database objects (e.g. tables, views, indexes) that describes the organization of data within a database. The schema specifies permissions for all of the database objects logically associated with that schema. For example, a schema may define access to a single table.

The following Transact-SQL script uses CREATE SCHEMA statement to create a new schema and grant the CONTROL permission to all contributors accessing that schema and its database objects (e.g. My_TABLE):

CREATE SCHEMA MY_CUSTOM_SCHEMA; 

GO 

CREATE TABLE [MY_CUSTOM_SCHEMA].My_TABLE ( C1 int); 

GO 

GRANT CONTROL ON SCHEMA::MY_CUSTOM_SCHEMA TO RMS_CONTRIBUTOR_PERMISSIONS 

All users assigned the RMS_CONTRIBUTOR_PERMISSIONS database role now have the CONTROL permission for the MY_CUSTOM_SCHEMA schema and its database objects including the newly created My_TABLE table.

Schema-level access controls are granted to database roles. The Contributor and Consumer roles are mapped to the RMS_CONTRIBUTOR _PERMISSIONS and RMS_CONSUMER _PERMISSIONS database roles. Permissions granted to database roles are automatically granted to users assigned the corresponding Intelligent Risk roles.

Users with CONTROL permission have virtual ownership of that schema and all logically related objects. They may grant, deny, or revoke permissions to database roles for that schema. SQL Server permissions include CONTROL, SELECT, UPDATE, and DELETE. To learn more, see GRANT Schema Permissions (Transact-SQL).


Did this page help you?