SQL Function Requirement

SQL Function Requirement

This requirement executes a database function to obtain the answer to the security requirement.  The function should be written to return a bit field.  A value of 1 indicates the requirement passes and a value of 0 indicates failure.  The function can take any number of input parameters.

SQL Function Requirement Metadata

Property Name	Value	Description
functionName	string	The name of the SQL function to execute.
functionParameters	object	An object that describes the parameters and values to pass to the SQL function. The property names on the object should match the input parameters of the function. The values should be what you want the framework to pass for those parameter values.

Below is some example metadata for a SQL Function requirement.  This metadata uses Input Context Objects for populating values.  

SQL Function Metadata Example

{
  "endpoints": {
    "ExamplesUpdateCart": {
      "inputEntityDefinition": {
        "name": "updateShoppingCartInput",
        "fields": {
          "shippingAddressId": {
            "type": "long",
            "description": "The address id where the order will be shipped.",
            "sourceField": "ShipToAddressID",
            "input": {
              "source": "body",
              "httpMethods": [
                "PATCH"
              ]
            },
            "security": {
              "canShipToAddress": {
                "type": "SQLFunction",
                "parameters": {
                  "functionName": "fnSecurityCanShipToAddress",
                  "functionParameters": {
                    "@AuthenticatedPersonID": "@AuthenticatedAttributes.AuthenticatedPrincipalRecordId",
                    "@AddressID": "@request.shippingAddressId"
                  }
                }
              }
            }
          },
          "billingAddressId": {
            "type": "long",
            "description": "The address id to charge the order to.",
            "sourceField": "BillToAddressID",
            "input": {
              "source": "body",
              "httpMethods": [
                "PATCH"
              ]
            },
            "security": {
              "canBillToAddress": {
                "type": "SQLFunction",
                "parameters": {
                  "functionName": "fnSecurityCanBillToAddress",
                  "functionParameters": {
                    "@AuthenticatedPersonID": "@AuthenticatedAttributes.AuthenticatedPrincipalRecordId",
                    "@AddressID": "@request.shippingAddressId"
                  }
                }
              }
            }
          }
        }
      }
    }
    //remaining metadata omitted
  }
}

Why did we add this security block to the input entity fields instead of the endpoint or the route.

We put the security at the input entity fields level here because of how security requirements are grouped together and executed. The Endpoint requirements are evaluated as a single collection. Route segment requirements are evaluated as a single collection. Input entity field requirements are evaluated as a collection for each field.

If you look at the metadata you can see we're securing two input fields: billingAddressId and shippingAddressId. Both fields are optional input parameters on a PATCH request. Let's assume that an authenticated user is sending a valid value for shippingAddressId and an invalid value for billingAddressId. If the requirements were at the route level we would have:

Collection Name	Requirements	Result
Endpoint	Require Authentication	Pass
Route	CanBillToAddress, CanShipToAddress	CanShipToAddress passes but CanBillToAddress fails so this passes.
Input Fields	None	Pass

This passes but that isn't what we want when the user is sending an invalid value for billingAddressId.  

When we move each requirement to the specific input entity field they apply to we have:

Collection Name	Requirements	Result
Endpoint	Require Authentication	Pass
Route	None	Pass
Input Fields - BillingAddressId	CanBillToAddress	Fail
Input Fields - ShippingAddressId	CanShipToAddress	Pass

And the request fails as expected.