Externalizing Authorization in PL/SQL? Inconceivable!

Ok, so it’s not a land war in Asia. But for a recent engagement, I received requirements for replacing a home-grown authorization system and doing a proof-of-concept on APEX. Eek, unsupported platform. Alright, let’s roll up our sleeves.

If there’s one thing you can find at Oracle, it’s PL/SQL expertise. I was referred to one of our rock stars, Tyler Muth, for guidance in approaching this requirement. Tyler directed me to the flex-ws-api which allows you to make PL/SQL call to a SOAP interface. OES provides a SOAP interface for authorization, so could this be love at first sight?

Flex-ws-api is pretty straightforward. You put the SOAP envelope right into the body of a CLOB data type. OES Web Service Security Module, hereto forth referred to as the Policy Decision Point or PDP, authenticates using a username-only Identity Asserter. See the OES documentation on setting up Sharepoint for instructions on this.

set serveroutput on

declare

l_BLOB BLOB;

l_CLOB CLOB;

l_envelope CLOB;

l_response_msg varchar2(32767);

l_response_xml XMLType;

procedure show_xml (p_xml in xmltype) is l_str long; begin l_str := p_xml.extract('/*').getstringval(); loop exit when l_str is null; dbms_output.put_line (substr (l_str, 1, instr (l_str, chr(10)) - 1)); l_str := substr (l_str, instr (l_str, chr(10)) + 1); end loop; end show_xml;

BEGIN

l_envelope := q'! <?xml version='1.0' encoding='UTF-8'?>!';

l_envelope := l_envelope || ' <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<soap:Body>

<isAccessAllowed xmlns="http://security.bea.com/ssmws/ssm-soap-types-1.0.xsd">

<IdentityAssertion>

<sampletoken>matt.carter </sampletoken>

</IdentityAssertion>

<RuntimeResource>

<ResourceString>app/claim </ResourceString>

<AuthorityName>ARME_RESOURCE_AUTHORITY </AuthorityName>

</RuntimeResource>

<RuntimeAction>

<ActionString>view </ActionString>

<AuthorityName>ARME_ACTION_AUTHORITY </AuthorityName>

</RuntimeAction>

<RequestedCredentialType>sampletoken </RequestedCredentialType>

<AppContext>

<Record>

<q1:RecordName xmlns="" xmlns:q1="http://security.bea.com/ssmws/ssm-soap-types-1.0.xsd">attrname </q1:RecordName>

<StringValue>attrvalue </StringValue>

</Record>

</AppContext>

<AtzDirection>ALES_ONCE </AtzDirection>

</isAccessAllowed>

</soap:Body>

</soap:Envelope>';

l_response_xml := flex_ws_api.make_request(

p_url => 'http://oes-pdp:9000/Authorization',

p_action => 'isAccessAllowed',

p_envelope => l_envelope);

show_xml(l_response_xml);

END;

/

The OES response would look something like:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<soapenv:Body>

<isAccessAllowedResponse xmlns="http://security.bea.com/ssmws/ssm-soap-types-1.0.xsd">

<AccessAllowed>true </AccessAllowed>

<AtzDecisionData>

<AtzTtlAdvice>300 </AtzTtlAdvice>

</AtzDecisionData>

<ContextRequests xsi:nil="true"/>

</isAccessAllowedResponse>

</soapenv:Body>

</soapenv:Envelope>

From a database permissions side, the 10g database on the client site connected right to the PDP. In our 11g testing we found you had to set up some Access Control Lists on the database to have the database make a network connection. See this article for instructions on setting up this ACL.

The client was happy to PL/SQL and felt they could go the next step to externalizing this authorization in APEX. I haven’t accomplished this myself, so I would welcome readers to take this the next step. APEX has the capability for very fine-grained authorization and the ability to externalize this to a "PL/SQL Function returning a Boolean" type of Authorization Scheme, so it would seem APEX could call to OES for a Permit or Deny.

Special thanks to Tyler Muth for his direction on the PL/SQL side and Sri and the OES team for their guidance. Thanks to the A-Team for helping me get the word out.

mkc