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.

• Create role for query-only access
• Create user account
• Create data source
• Share data source

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.

Request

POST https://MyServer:8443/api/admin/roles

Request Payload

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

Response Payload

{
  "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.

Request

POST https://MyServer:8443/api/admin/users

Request Payload

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

Response Payload

{
  "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.

Request

POST https://MyServer:8443/api/mgmt/datasources

Request Payload

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

Response Payload

{
  "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 by creating a list of shared users. 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.

Request

POST https://MyServer:8443/api/mgmt/datasources/6334/sharedUsers

Request Payload

{
  "sharedUsers": [
    {
      "userId": 921,
      "permissions": [
        6
      ]
    }

Response Payload

  Status code: 201
  Successful response

{
  "sharedUsers": [
    {
      "userId": 921,
      "permissions": [
        6
      ]
    }