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.

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 UseDataSourceWithODBC, WebUI, and ChangePassword permissions.",
    "permissions": [
        6,
        8,
        9
    ],
    "users": []
}

Response Payload

{
    "id": 42,
    "name": "ODBC-only Users",
    "tenantId": 56,
    "description": "This role has UseDataSourceWithODBC, WebUI, and ChangePassword 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_v4\":{\"schemas\":[{\"name\":\"D2CQA01\",
\"tables\":{\"Dept_Emp\":{},\"Employees\":{},\"Departments\":{},\"Salaries\":{},
\"Titles\":{},\"Dept_Manager\":{}}}]}}",
        "ODataVersion": "4",
        "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_v4\":{\"schemas\":[{\"name\":\"D2CQA01\",
\"tables\":{\"Dept_Emp\":{},\"Employees\":{},\"Departments\":{},\"Salaries\":{},
\"Titles\":{},\"Dept_Manager\":{}}}]}}",
        "ODataVersion": "4",
        "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 and Management API permissions 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 UseDataSourceWithODBC, WebUI, ChangePassword, MgmtAPI,
and SQL editor permissions.",
    "permissions": [
        6,
        8,
        9,
        10,
        11
    ],
    "users": [963]
}

Response Payload

{
    "id": 42,
    "name": "ODBC-only Users",
    "tenantId": 56,
    "description": "This role has UseDataSourceWithODBC, WebUI, ChangePassword, MgmtAPI,
and SQL editor permissions.",
    "permissions": [
        6,
        8,
        9,
        10,
        11
    ],
    "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 and Management API permissions 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) and Mgmt (11) permissions are 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,
        11
    ]
}

Response Payload

{
    "roles": [
        42
    ],
    "permissions": [
        10,
        11
    ]
}