Providing query-only access by sharing a data source

The following operations show the provisioning of a query-only user for ODBC access to a SQL Server database. The administrator begins by creating a role for the user account, creates a user account, creates a data source, and then shares the data source with the user account.

Note: A data source can also be shared with a tenant, in effect sharing the data source with all the users in the tenant. See Sharing data sources for details.

Create role for query-only access

The administrator begins by creating a role for query-only access with the following operation. The administrator must have the Administrator (12) permission, or the CreateRole (17) permission and administrative access on the tenant.

{
"name": "Query access",
"tenantId": 59,
"description": "This role permits only query access.",
"permissions": [
5,
6,
7
],
"users": []
}

{
"id": 62,
"name": "Query access",
"tenantId": 59,
"description": "This role permits only query access.",
"permissions": [
5,
6,
7
],
"users": []
}

Create user account

The administrator then provisions a user account with the "Query access" role. The administrator must have the Administrator (12) permission, or the CreateUsers (13) permission and administrative access on the tenant.

{
"userName": "QueryOnlyUser",
"tenantId": 59,
"statusInfo": {
"status": 1,
"accountLocked": false
},
"passwordInfo": {
"password": "TempPassword",
"passwordStatus": 1,
"passwordExpiration": null
},
"permissions": {
"roles": [
44
]
}
}

{
"id": 921,
"userName": "QueryOnlyUser",
"tenantId": 56,
"statusInfo": {
"status": 1,
"accountLocked": false
},
"passwordInfo": {
"passwordStatus": 1,
"passwordExpiration": null
},
"permissions": {
"roles": [
44
]
},
"authenticationInfo": {
"authUsers": [
{
"authUserName": "QueryOnlyUser",
"authServiceId": 1
}
]
}
}

Create a data source

The administrator then creates a data source. The administrator will be the owner of this data source, but will share the data source with ODBCUser in the next operation.

The administrator must have the Administrator (12) permission, or the MgmtAPI (11) and CreateDataSource (1) permissions.

{
"name": "SQLServer2",
"dataStore": "46",
"connectionType": "Hybrid",
"description": "Test SQL Server access",
"options": {
"Database": "CustomerData",
"User": "MySQLServerUserId",
"Password": "MySQLServerPassword"
}
}

{
"id": "6334",
"name": "SQLServer2",
"dataStore": "46",
"connectionType": "Hybrid",
"description": "Test SQL Server access",
"options": {
"Database": "CustomerData",
"User": "MySQLServerUserId",
"Password": "MySQLServerPassword"
}
}

Share a data source

The administrator then shares the data source with the QueryOnlyUser. The administrator limits access to ODBC-only queries by setting the UseDataSourceWithODBC (6) permission on the data source. The data source ID 6334 is passed in the request URL, while the user ID 921 and the data source permission are passed in the request payload.

The administrator must have the Administrator (12) permission; or the administrator must have the MgmtAPI (11) permission, the ModifyDataSource (3) permission, the UseDataSourceWithODBC (6) permission, and administrative access to the tenant to which the shared user belongs.