skip to main content
Administering Hybrid Data Pipeline : User provisioning : Providing query access to an ODBC data source and limited access to the Web UI
  

Try Now

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

The following work flow illustrates how an administrator can provide ODBC query access to a data source and grant limited access to the Hybrid Data Pipeline Web UI.
*Create role for ODBC-only user with access to change password in the Web UI
*Create ODBC-only user
*Create a data source on behalf of ODBC-only user
*Update ODBC-only role to include SQL Editor access
*Grant SQL Editor access explicitly to the ODBC-only user

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.
Note: To use change password functionality in the Web UI, Web UI permission must also be granted.
Request
POST https://MyServer:8443/api/admin/roles
Request Payload
{
"name": "ODBC-only Users",
"tenantId": 56,
"description": "This role has ODBC, WebUI, and change password permissions.",
"permissions": [
6,
8,
9
],
"users": []
}
Response Payload
{
"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.
Request
POST https://MyServer:8443/api/admin/users
Request Payload
{
"userName": "ODBCUser",
"tenantId": 56,
"statusInfo": {
"status": 1,
"accountLocked": false
},
"passwordInfo": {
"password": "TempPassword",
"passwordStatus": 1,
"passwordExpiration": null
},
"permissions": {
"roles": [
42
]
}
}
Response Payload
{
"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.
Request
POST https://MyServer:8443/api/mgmt/datasources?user=ODBCUser
Request Payload
{
"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"
}
Response Payload
{
"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.
Request
PUT https://MyServer:8443/api/admin/roles/42
Request Payload
{
"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]
}
Response Payload
{
"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.
Request
PUT https://MyServer:8443/api/admin/users/963/permissions
Request Payload
{
"roles": [
42
],
"permissions": [
10
]
}
Response Payload
{
"roles": [
42
],
"permissions": [
10
]
}