Providing limited direct access to data sources and features

The following operations show the provisioning of a direct-access user. The user is granted permission to query data sources and use a number of features, including the Web UI, but is not granted permission to create, view, or modify data sources.

Create query-based role

With the following request, an administrator can create a role that gives a user permissions to query OData, ODBC, and JDBC data sources. In addition, the user has access to the Web UI, can change their password in the Web UI, and can query data sources they own using the SQL Editor. However, the role does not permit the user to create, modify, or delete data sources. The administrator must have the Administrator (12) permission, or the CreateRole (17) permission and administrative access on the tenant.

{
"name": "QueryBasedRole",
"tenantId": 56,
"description": "This role allows query access and direct access for the
Web UI, password, SQL editor, and Management API features",
"permissions": [
5,6,7,8,9,10,11
],
"users": []
}

{
"id": 88,
"name": "QueryBasedRole",
"tenantId": 56,
"description": "This role allows query access and direct access for the
Web UI, password, SQL editor, and Management API features",
"permissions": [
5,
6,
7,
8,
9,
10,
11
],
"users": []
}

Create SQL user

With the following request, an administrator creates a user called SQLUser with the QueryBasedRole role. SQLUser inherits the permissions of the QueryBasedRole role described above. The administrator must have the Administrator (12) permission, or the CreateUsers (13) permission and administrative access on the tenant.

{
"userName": "SQLUser",
"tenantId": 56,
"statusInfo": {
"status": 1,
"accountLocked": false
},
"passwordInfo": {
"password": "Secret",
"passwordStatus": 1
},
"permissions": {
"roles": [
88
]
}
}

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

Create a data source

An administrator can then create a data source. The administrator will be the owner of this data source, but will share the data source with SQLUser in the next operation.

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

{
"name": "Oracle_Test",
"dataStore": 43,
"connectionType": "Hybrid",
"description": "",
"options": {
"User": "Test",
"Password": "Test",
"ServerName": "OracleTest",
"ODataSchemaMap": "{\"odata_mapping_v2\":{\"schemas\":[{\"name\":\"D2CQA
01\",\"tables\":{\"Dept_Emp\":{},\"Employees\":{},\"Departments\":{},\"Salaries\
":{},\"Titles\":{},\"Dept_Manager\":{}}}]}}",
"ODataVersion": "2",
"SID": "UNI",
"ExtendedOptions": "EncryptionMethod=noEncryption"
}
}

{
"id": "13",
"name": "Oracle_Test",
"dataStore": 43,
"connectionType": "Hybrid",
"description": "",
"options": {
"User": "Test",
"Password": "Test",
"ServerName": "OracleTest",
"ODataSchemaMap": "{\"odata_mapping_v2\":{\"schemas\":[{\"name\":\"D2CQA
01\",\"tables\":{\"Dept_Emp\":{},\"Employees\":{},\"Departments\":{},\"Salaries\
":{},\"Titles\":{},\"Dept_Manager\":{}}}]}}",
"ODataVersion": "2",
"SID": "UNI",
"ExtendedOptions": "EncryptionMethod=noEncryption"
}

Share a data source

The administrator can then share the data source with the SQLUser. The administrator limits access to queries by setting the UseDataSourceWithJDBC (5), UseDataSourceWithODBC (6), and UseDataSourceWithOData (7) permissions on the data source. The data source ID 13 is passed in the request URL, while the user ID 1297 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 query permissions, and administrative access to the tenant to which the shared user belongs.