Secure server methods and dynamic SQL

Development and modifications

26R1 reference · Documentation-based walkthrough · Sources reviewed 2026-09-28 · Not executed in IFS or Oracle. Match the database, build home, and tools to your installed release. Example names are fictional.

Review a customer-owned lookup or update method before exposing it to a page or integration. A method must enforce the intended access and input contract even when a caller bypasses the visible control.

Before you start

Use the actual generated service/package signature, one authorized test identity, and another without the required access. Work in a nonproduction environment. Identify the affected business data and expected company/site restrictions; keep test tokens and private records out of shared examples.

1. Trace entry points and authorization

1. List the callable projection operation and the server methods it invokes.
2. Record the required permission set and effective caller, including any workflow or background execution identity.
3. Inspect generated method checks and row-level restrictions.
4. Invoke the operation as the permitted and denied users, including direct-key requests to out-of-scope data.
5. Repeat a write attempt independently from read/list tests.

Method-name suffixes express framework scope, but do not replace authorization. Keep default generated method security unless a reviewed requirement justifies an exception.

2. Review security annotations explicitly

Annotation or mechanism	Review question
Normal generated method checks	Are the intended callers granted this operation, and are others denied?
@UncheckedAccess	Why is bypassing method-level checks acceptable for every caller with package access?
@ServerOnlyAccess	Is the operation genuinely restricted to server-code invocation, and does it still enforce required data rules?
Business security checks	Does the operation verify the relevant company/site/business relationship?

A method being read-only does not automatically make its data safe for all package callers. Do not add @UncheckedAccess merely to fix a permissions error. Check the grant and underlying access requirement first.

3. Prefer static SQL and bind runtime values

Use static SQL for a known query. When dynamic SQL is genuinely required, keep values outside the SQL text.

Run the read-only safe-bind.sql exercise. It supplies a string containing quotes and SQL-like text, then returns that exact string through a bind:

EXECUTE IMMEDIATE 'SELECT :reference FROM dual'
   INTO received_ USING supplied_;

Expected output is the unchanged supplied text, demo' OR '1'='1. The input remains data. The full anonymous block asserts equality and creates no objects.

This fixture demonstrates binding, not a complete authorization design. A perfectly bound query can still expose data to the wrong caller if its scope is wrong.

4. Treat identifiers differently from values

Bind placeholders cannot stand for table names, column names, sort directions, or arbitrary SQL fragments. If a requirement permits two sort choices, map the user’s choice to two fixed approved identifiers in code. Reject every other choice; do not concatenate arbitrary caller text after a superficial pattern check.

Use applicable Assert_SYS routines for the real input contract, but distinguish syntactic validity, object existence, and authorization. A valid view name is not proof that the caller should query it. Prefer a small allowlist of supported targets.

For IFS dynamic execution, follow the documented review process and @ApproveDynamicStatement convention with real review evidence. The annotation records an assertion; it does not sanitize the statement. Do not add a fabricated reviewer/date solely to silence tooling.

5. Define null, missing, and duplicate outcomes

For every public input, specify requiredness, length, allowed values, and datatype. Returning null early is appropriate only when it is the documented result for a missing optional value. Required keys should produce the approved validation response instead of silent success.

For a unique lookup, test zero, one, and two matches. Handle an expected absence deliberately; treat an unexpected duplicate as a data/contract problem. Do not use an unordered first-row limit to hide it.

Use framework error handling for IFS business errors so callers receive appropriate translatable messages. Unexpected errors should retain a failure signal and useful diagnostic context. WHEN OTHERS THEN NULL turns a failed operation into misleading success.

6. Test errors and logging

Test permitted, denied, missing, malformed, over-length, duplicate, and unexpected-failure cases. Check that failure does not leave a partially committed operation; transaction ownership is part of the review.

Capture operation name, a safe correlation identifier, timing, and a sanitized error code. Restrict detailed diagnostics to authorized operators. Do not return SQL text, tokens, credentials, or sensitive row contents to a caller merely to make troubleshooting easier.

Troubleshooting

A denied valid request calls for an identity/grant check, not a security bypass. A bound query returning too much data needs scope review. An error disappearing needs exception-handler inspection. A dynamic statement that passed syntax validation still needs an allowlist and authorization review.

Exercise and solution

Exercise: can a client safely choose any table if the name passes an identifier validator?

Show the solution

No. Validation of syntax or existence does not authorize access. Map an approved business choice to a fixed supported query/target and apply the caller’s scope. Bind values separately and reject unsupported choices.

Cleanup and delivery evidence

The bind fixture creates no objects. Remove temporary test grants and tokens, retain sanitized allowed/denied results, and deliver permission changes alongside the source. Keep dynamic-code review evidence traceable to the deployed version.

Sources

• IFS database object and method security
• IFS dynamic SQL validation and approval
• IFS bind variable examples
• IFS error handling