Administer Custom Databases

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.

Moody's 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.

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 Manage 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 a 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.

📷

RESTRICTION

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

Moody's RMS recommends that you implement security of managed SQL Server instances at the schema 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 CREATE SCHEMA statement enables you to create a new schema and grant the CONTROL permission to a database-level principal, that will own the schema.

Role-based security

Moody's 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.

In this scenario, control of the schema is assigned to a database role, RMS_CONTRIBUTOR _PERMISSIONS or RMS_CONSUMER_PERMISSION.

In this configuration, 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.

The following Transact-SQL script uses creates a new schema and grants 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 

In this example, 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.

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).

Login-based security

Alternatively, you may grant schema-level access controls to individual logins.

CREATE SCHEMA MY_CUSTOM_SCHEMA; 

GO 

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

GO 

GRANT CONTROL ON SCHEMA::MY_CUSTOM_SCHEMA TO MY_LOGIN 

In this example, the specified login (MY_LOGIN) is granted CONTROL and consequently ownership of schema and all related objects.

🍊

Warning

If you do assign ownership of a schema to a login, ensure that you do not subsequently delete that login because you will lose access to that schema and all of its objects.