Providing query access to an ODBC data source and limited access to the Web UI

The following operations show the provisioning of a direct-access user. The user is initially granted access to query ODBC data sources and to change their password via the Web UI. Then, the user is subsequently granted access to the SQL Editor.

Create role for ODBC-only user with access to change password in the Web UI

With the following request, an administrator can create a role for an ODBC-only user with Web UI access to change their password. The administrator must have the Administrator (12) permission, or the CreateRole (17) permission and administrative access on the tenant.

{
"name": "ODBC-only Users",
"tenantId": 56,
"description": "This role has ODBC, WebUI, and change password permissions.",
"permissions": [
6,
8,
9
],
"users": []
}

{
"id": 42,
"name": "ODBC-only Users",
"tenantId": 56,
"description": "This role has ODBC, WebUI, and change password permissions.",
"permissions": [
6,
8,
9
],
"users": []
}

Create ODBC-only user

An administrator can create a user with the ODBC-only role with the following request. The administrator must have the Administrator (12) permission, or the CreateUsers (13) permission and administrative access on the tenant.

{
"userName": "ODBCUser",
"tenantId": 56,
"statusInfo": {
"status": 1,
"accountLocked": false
},
"passwordInfo": {
"password": "TempPassword",
"passwordStatus": 1,
"passwordExpiration": null
},
"permissions": {
"roles": [
42
]
}
}

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

Create a data source on behalf of ODBC-only user

An administrator can create a data source on behalf of ODBCUser with the following request. While the user will not be able to view data source information or modify the data source, ODBCUser will be able to execute ODBC queries on the data source and change their password in the Web UI.

The user query parameter (?user) is used to specify the owner of the data source. The administrator must have the Administrator (12) permission; or the administrator must have the MgmtAPI (11) permission, the OnBehalfOf (21) permission, administrative access on the tenant to which the user belongs, and the CreateDataSource (1) permission.

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

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

Update ODBC-only role to include SQL Editor access

With the following request, an administrator can update the ODBC-only role to include SQL editor access. The SQLEditor permission allows the user to pass SQL queries with the SQL Editor in the Web UI. To use the SQL Editor functionality, Web UI permission must also be granted. The administrator must have the Administrator (12) permission, or the ModifyRole (19) permission and administrative access on the tenant.

Note: The payload should also include any previously set permissions that need to be retained, as well as the user or users assigned the role.

{
"name": "ODBC-only Users",
"tenantId": 56,
"description": "This role has ODBC, WebUI, change password, and SQL editor
permissions.",
"permissions": [
6,
8,
9,
10
],
"users": [963]
}

{
"id": 42,
"name": "ODBC-only Users",
"tenantId": 56,
"description": "This role has ODBC, WebUI, change password, and SQL editor
permissions.",
"permissions": [
6,
8,
9,
10
],
"users": [963]
}

Grant SQL Editor access explicitly to the ODBC-only user

Alternatively, an administrator could explicitly set the SQLEditor permission on the user. To use the SQL Editor functionality, Web UI permission must also be granted. In this example, the user inherits ODBC, WebUI, and change password permissions through the ODBC-only Users role (42), while the SQLEditor (10) permission is set explicitly on the user. The administrator must have the Administrator (12) permission, or the ModifyUsers (15) permission and administrative access on the tenant to which the user belongs.

Note: The request payload must include the roles the user needs to retain. The payload should also include any previously set explicit permissions the user needs to retain.