12.7.1 Access and Authorization Procedures and Functions

Use the network access control lists (ACL) API to control access by users to external network services and resources from the database. Use the token store API to persist the authorization token issued by a cloud host so it can be used with subsequent SQL calls.

Use the following to manage ACL privileges. An ADMIN user is required.

• pyqAppendHostACE Procedure
• pyqGetHostACE Function
• pyqRemoveHostACE Procedure

Use the following to manage authorization tokens:

• pyqSetAuthToken Procedure
• pyqIsTokenSet Function

Workflow

The typical workflow for using the SQL API for Embedded Python Execution with Autonomous Database is:

1. Connect to PDB as the ADMIN user, and add a normal user OMLUSER to the ACL list of the cloud host of which the root domain is adb.us-region-1.oraclecloudapps.com:

   exec pyqAppendHostAce('OMLUSER','adb.us-region-1.oraclecloudapps.com');

2. The OML Rest URLs can be obtained from the Oracle Autonomous Database that is provisioned.

   1. Sign into your Oracle Cloud Infrastructure account. You will need your OCI user name and password.
   2. Click the hamburger menu and select Autonomous Database instance that is provisioned. For more information on provisioning an Autonomous Database, see: Provision an Oracle Autonomous Database.
   3. Click Service Console and then click Devlopment.
   4. Scroll down to Oracle Machine Learning RESTful Services tile and click Copy to obtain the following URLs for:

      • Obtaining the REST authentication token for REST APIs provided by OML:

        <oml-cloud-service-location-url>/omlusers/

   The URL <oml-cloud-service-location-url> includes the tenancy ID, location, and database name. For example, https://qtraya2braestch-omldb.adb.us-sanjose-1.oraclecloudapps.com.

   In this example,

   • qtraya2braestch is the tenancy ID
   • omldb is the database name
   • us-sanjose-1 is the datacenter region
   • oraclecloudapps.com is the root domain

3. The Oracle Machine Learning REST API uses tokens to authenticate an Oracle Machine Learning user. To authenticate and obtain an access token, send a POST request to the Oracle Machine Learning User Management Cloud Service REST endpoint /oauth2/v1/token with your OML username and password.

   curl -X POST --header 'Content-Type: application/json' --header 'Accept: application/json' 
   -d '{"grant_type":"password", "username":"'${username}'", "password":"'${password}'"}' 
   "<oml-cloud-service-location-url>/omlusers/api/oauth2/v1/token"

   The example uses the following values:

   • username is the OML username.
   • password is the OML user password.
   • oml-cloud-service-location-url is a variable containing the REST server portion of the Oracle Machine Learning User Management Cloud Service instance URL that includes the tenancy ID, database name, and the location name. You can obtain the omlserver URL from the Development tab in the Service Console of your Oracle Autonomous Database instance.

   Note:

   When a token expires, all calls to the OML Services REST endpoints with return a message stating that the token has expired along with the HTTP error: HTTP/1.1 401 Unauthorized

4. Connect to PDB as OMLUSER, set the access token, and run pyqIndexEval:

   exec pyqSetAuthToken('<access token>');
   select *
       from table(pyqIndexEval(
           par_lst => NULL,
           out_fmt => '{"ID":"number", "RES":"varchar2(3)"}',
           times_num => 3,
           scr_name => 'idx_ret_df'));
   
           ID RES
   ---------- ---
            1 a
            2 b
            3 c
   
   3 rows selected.

• pyqAppendHostACE Procedure
  The pyqAppendHostACE procedure appends an access control entry (ACE) to the access control list (ACL) of the cloud host. The ACL controls access to the cloud host from the database, and the ACE specifies the connect privilege granted to the specified user name.
• pyqGetHostACE Function
  The pyqGetHostACE function gets the existing host access control entry (ACE) for the specified user. An exception is raised if the host ACE doesn't exist for the specified user.
• pyqRemoveHostACE Procedure
  
• pyqSetAuthToken Procedure
  The pyqSetAuthToken procedure sets the access token in the token store.
• pyqIsTokenSet Function
  The pyqIsTokenSet function returns whether the authorization token is set or not.