Oracle ADB Pre-Authenticated URL - Part III

Providing temporary access to the Oracle database has become safe, secure, and easy with Pre-Authenticated Request (PAR) URL on Autonomous database. However it is still possible to provide access to only a subset of data, by defining VPD policies for the data shared with PAR URL.
 
Oracle Virtual private database (VPD) feature lets us to control data access dynamically (by applying filters) on the data sets. When the PAR URL is accessed, the value passed to the parameter application_user_id  during PAR generation, can be made available through sys_context function calls, using this we can define VPD policy function to restrict the data, visible to the application users.
 
Created a policy function to be associated with PAR URL
 
demo@ATP19C> create or replace function rls_get_emp_by_deptno( p_schema in varchar2, p_object in varchar2 )
  2  return varchar2
  3  as
  4  begin
  5     if sys_context('DATA_ACCESS_CONTEXT$', 'USER_IDENTITY') is not null then
  6             return q'# deptno = to_number( sys_context('DATA_ACCESS_CONTEXT$', 'USER_IDENTITY') ) #';
  7     else
  8             return ' 1 = 1 ';
  9     end if;
 10  end;
 11  /
 
Function created.
 
Register the policy to the data set.
 
demo@ATP19C> begin
  2     dbms_rls.add_policy(
  3             object_name => 'EMP',
  4             policy_name => 'EMP_BY_DEPT_ACCESS_PLCY',
  5             policy_function => 'RLS_GET_EMP_BY_DEPTNO',
  6             statement_types => 'SELECT' );
  7  end;
  8  /
 
PL/SQL procedure successfully completed.
 
Generate the PAR URL by invoking GET_PREAUTHENTICATED_URL method from DBMS_DATA_ACCESS package. To restrict the data to be available, we pass the value of 10 to application_user_id parameter, doing so we restrict the PAR URL to share the data for those employees working for DEPTNO = 10
 
demo@ATP19C> declare
  2     l_output clob;
  3  begin
  4     dbms_data_access.get_preauthenticated_url(
  5             schema_name => user,
  6             schema_object_name => 'EMP',
  7             application_user_id => '10',
  8             expiration_count => 2 ,
  9             result => l_output );
 10
 11     dbms_output.put_line( l_output );
 12  end;
 13  /
{"status":"SUCCESS","id":"_oX6seQIl7OFF1cV9-x5EqK--WstrnMkfMlYvsD5PtwV0ATUsvxidQjp1Wi7JIHu","preauth_url":"https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/uro35AHvQm34s1saoIZMsN42vsNA8PfLNwBo7mbREmpmwgZpAaPsaZfofGqiqT0DbUQaSpBgvCM/data","expiration_ts":"2024-08-05T13:50:51.847Z","expir
ation_count":2}
 
PL/SQL procedure successfully completed.
 
 
 
demo@ATP19C> $ curl -i --location https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/uro35AHvQm34s1saoIZMsN42vsNA8PfLNwBo7mbREmpmwgZpAaPsaZfofGqiqT0DbUQaSpBgvCM/data
HTTP/1.1 200 OK
Date: Tue, 07 May 2024 13:51:45 GMT
Content-Type: application/json
Content-Length: 568
Connection: keep-alive
Strict-Transport-Security: max-age=31536000;includeSubDomains
X-Content-Type-Options: nosniff
Request-Id: defaultOi6bb3S5MG0ssAO91GKAaxnJOIBYjEWRk6rR74Df8K08oUyqvmdeJPFn
X-Request-ID: f83669d475565ca1acebcecc0c53531f
 
 
'{
  "items" :
  [
    {
      "EMPNO" : 7839,
      "ENAME" : "KING",
      "JOB" : "PRESIDENT",
      "HIREDATE" : "1981-11-17T00:00:00",
      "SAL" : 5001,
      "DEPTNO" : 10
    },
    {
      "EMPNO" : 7782,
      "ENAME" : "CLARK",
      "JOB" : "MANAGER",
      "MGR" : 7839,
      "HIREDATE" : "1981-06-09T00:00:00",
      "SAL" : 2451,
      "DEPTNO" : 10
    },
    {
      "EMPNO" : 7934,
      "ENAME" : "MILLER",
      "JOB" : "CLERK",
      "MGR" : 7782,
      "HIREDATE" : "1982-01-23T00:00:00",
      "SAL" : 1301,
      "DEPTNO" : 10
    }
  ],
  "hasMore" : false,
  "limit" : 100,
  "offset" : 0,
  "count" : 3,
  "links" :
  [
    {
      "rel" : "self",
      "href" : "https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/uro35AHvQm34s1saoIZMsN42vsNA8PfLNwBo7mbREmpmwgZpAaPsaZfofGqiqT0DbUQaSpBgvCM/data"
    }
  ]
}'
 
 

Passing the value of 20 to the parameter application_user_id restrict the data for employees working for DEPTNO = 20.
 
demo@ATP19C> declare
  2     l_output clob;
  3  begin
  4     dbms_data_access.get_preauthenticated_url(
  5             schema_name => user,
  6             schema_object_name => 'EMP',
  7             application_user_id => '20',
  8             expiration_count => 2 ,
  9             result => l_output );
 10
 11     dbms_output.put_line( l_output );
 12  end;
 13  /
{"status":"SUCCESS","id":"8RdcrMP8-RqVSP-k1orB05vFWTnK8Qt3PNNpWJANx3DfflxLfTkufqz-xEDxNvb8","preauth_url":"https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/bSTqpwCeSCA3byXUWNt9il89NOy0ajcDnQAKHxcnN2nbrhhaanCJsHgyMDuJSEC1bUQaSpBgvCM/data","expiration_ts":"2024-08-05T13:52:02.400Z","expir
ation_count":2}
 
PL/SQL procedure successfully completed.
 
 
demo@ATP19C> $ curl -i --location https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/bSTqpwCeSCA3byXUWNt9il89NOy0ajcDnQAKHxcnN2nbrhhaanCJsHgyMDuJSEC1bUQaSpBgvCM/data
HTTP/1.1 200 OK
Date: Tue, 07 May 2024 13:52:32 GMT
Content-Type: application/json
Content-Length: 801
Connection: keep-alive
Strict-Transport-Security: max-age=31536000;includeSubDomains
X-Content-Type-Options: nosniff
Request-Id: defaultFk5azR74sCd4IVAp5T3nLwlwhxiWLUEWCYou4Z8JKC2dil3AQL01Lv0B
X-Request-ID: 7709a7911c36047d7cab3dce0a54fec9
 
 
'{
  "items" :
  [
    {
      "EMPNO" : 7566,
      "ENAME" : "JONES",
      "JOB" : "MANAGER",
      "MGR" : 7839,
      "HIREDATE" : "1981-04-02T00:00:00",
      "SAL" : 2975,
      "DEPTNO" : 20
    },
    {
      "EMPNO" : 7788,
      "ENAME" : "SCOTT",
      "JOB" : "ANALYST",
      "MGR" : 7566,
      "HIREDATE" : "1987-04-19T00:00:00",
      "SAL" : 3000,
      "DEPTNO" : 20
    },
    {
      "EMPNO" : 7902,
      "ENAME" : "FORD",
      "JOB" : "ANALYST",
      "MGR" : 7566,
      "HIREDATE" : "1981-12-03T00:00:00",
      "SAL" : 3000,
      "DEPTNO" : 20
    },
    {
      "EMPNO" : 7369,
      "ENAME" : "SMITH",
      "JOB" : "CLERK",
      "MGR" : 7902,
      "HIREDATE" : "1980-12-17T00:00:00",
      "SAL" : 800,
      "DEPTNO" : 20
    },
    {
      "EMPNO" : 7876,
      "ENAME" : "ADAMS",
      "JOB" : "CLERK",
      "MGR" : 7788,
      "HIREDATE" : "1987-05-23T00:00:00",
      "SAL" : 1100,
      "DEPTNO" : 20
    }
  ],
  "hasMore" : false,
  "limit" : 100,
  "offset" : 0,
  "count" : 5,
  "links" :
  [
    {
      "rel" : "self",
      "href" : "https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/bSTqpwCeSCA3byXUWNt9il89NOy0ajcDnQAKHxcnN2nbrhhaanCJsHgyMDuJSEC1bUQaSpBgvCM/data"
    }
  ]
}'
 
 
 
So, this shows that using VPD policies for the data share with PAR URL provides the fine-grained access control to only a subset of data (rows).