Providing query-only access by creating data sources on behalf of users

The following operations show the provisioning of a query-only user for OData access to an Oracle database. The administrator begins by creating a role for the user account, next creates the user account, and then creates a data source on behalf of the user. (See also Managing resources on behalf of users.)

Create role for OData query-only access

The administrator begins by creating a role for OData 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": "OData query",
  "tenantId": 56,
  "description": "This role permits only OData query access.",
  "permissions": [
    7
  ],
  "users": []
}

Response Payload

{
  "id": 21,
  "name": "OData-only Users",
  "tenantId": 56,
  "description": "This role permits only OData query access.",
  "permissions": [
    7
  ],
  "users": []
}

Create user account

The administrator then provisions a user account with the "OData query" 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": "ODataUser",
  "tenantId": 56,
  "statusInfo": {
    "status": 1,
    "accountLocked": false
  },
  "passwordInfo": {
    "password": "TempPassword",
    "passwordStatus": 1,
    "passwordExpiration": null
  },
  "permissions": {
    "roles": [
      21
    ]
  }
}

Response Payload

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

Create a data source on behalf of the user account

The administrator then creates a data source on behalf of ODataUser. Since the only permission associated with the assigned role is UseDataSourceWithOData (7), the user will be able to access data through this data source with OData queries, but will not be able to view data source information or access other Hybrid Data Pipeline features.

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=ODataUser

Request Payload

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

Response Payload

{
  "id": "1681",
  "name": "Oracle_OData",
  "dataStore": 43,
  "connectionType": "Hybrid",
  "description": "",
  "options": {
    "User": "OracleTest",
    "Password": "Secret",
    "ODataSchemaMap": "{\"odata_mapping_v4\":{\"schemas\":[{\"name\":\"D2CQA01\",
\"tables\":{\"Dept_Emp\":{},\"Employees\":{},\"Departments\":{},\"Salaries\":{},
\"Titles\":{},\"Dept_Manager\":{}}}]}}",
    "ServerName": "TestServer",
    "ExtendedOptions": "EncryptionMethod=noEncryption",
    "SID": "UNI",
    "ODataVersion": "4"
  }
}

Retrieve data source information on behalf of the user account

The administrator can then retrieve data source details on behalf of ODataUser. 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 ViewDataSource (2) permission. (Note that ODataUser cannot retrieve this information because the user does not have ViewDataSource (2) permission.)

Request

GET https://MyServer:8443/api/mgmt/datasources?user=ODataUser

Response Payload

{
  "id": "1681",
  "name": "Oracle_OData",
  "dataStore": 43,
  "connectionType": "Hybrid",
  "description": "",
  "options": {
    "User": "OracleTest",
    "Password": "Secret",
    "ODataSchemaMap": "{\"odata_mapping_v4\":{\"schemas\":[{\"name\":\"D2CQA01\",
\"tables\":{\"Dept_Emp\":{},\"Employees\":{},\"Departments\":{},\"Salaries\":{},
\"Titles\":{},\"Dept_Manager\":{}}}]}}",
    "ServerName": "TestServer",
    "ExtendedOptions": "EncryptionMethod=noEncryption",
    "SID": "UNI",
    "ODataVersion": "4"
  }
}

User queries the OData endpoint

With the appropriate connection information as supplied by the administrator, the ODataUser can now query the OData endpoint. With the following request, ODataUser retrieves an XML document from the Oracle_OData data source.

Important: The new user must authenticate using basic authentication to execute API queries.

Request

GET https://MyServer:8443/api/odata/Oracle_OData/Employees

Response Payload

<?xml version="1.0" encoding="utf-8"?>
<feed xmlns="http://www.w3.org/2005/Atom"
      xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"
      xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices"
      xml:base="https://MyServer:8443/api/odata/Oracle_OData/">
  <title type="text">Employees</title>
  <id>https://MyServer:8443/api/odata/Oracle_OData/Oracle_OData/Employees</id>
  <updated>2018-03-29T17:58:44Z</updated>
  <link rel="self" title="Employeeses" href="Employeeses"/>
  <entry>
    <id>https://MyServer:8443/api/odata/Oracle_OData/Employees(10001M)</id>
    <title type="text"/>
    <updated>2018-03-29T17:58:44Z</updated>
    <author>
      <name/>
    </author>
    <link rel="edit" title="Employees" href="Employeeses(10001M)"/>
  </entry>
  ...
</feed>