Direct Open SQL write operations (Insert/Update/Modify/Delete) are in general bad practice and should be avoided. They undermine the integrity and security of the system and should not be allowed.



Direct Open SQL write operations are also error prone and can cause unexpected system behavior. Some of the issues to watch out for in SAP include:

- SAP recommends using 'update bundling' techniques to ensure data integrity within an SAP LUW (logical unit of work) that may span multiple database LUWs. Direct modifications to table entries without update bundling may leave the SAP transaction in an inconsistent state.

- Direct Open SQL write operations only set database level locks and bypass the SAP application locks. This may lead to deadlocks and corrupted data.

- Direct Open SQL write operations bypass SAP authorization checks within the application program.

- When standard mechanisms are used to write table entries, edit checks, audit trails, dependent updates (like change documents, for example) are all correctly performed. This is not the case when Direct Open SQL write operations are used.

In an invoker's rights, or AUTHID CURRENT_USER package, identifiers are first resolved against the current user's schema. This can cause unexpected behavior if the definer of the code does not explicitly say which schema an identifier belongs to.

Example 1: The following code checks whether a user has permissions to perform an action by looking up the user in a permissions table. Most users will only have read access to SYS.PERMISSIONS and be unable to modify the defined permissions.

CREATE or REPLACE FUNCTION check_permissions(
  p_name IN VARCHAR2, p_action IN VARCHAR2)
  RETURN BOOLEAN
  AUTHID CURRENT_USER
IS
  r_count NUMBER;
  perm BOOLEAN := FALSE;
BEGIN
  SELECT count(*) INTO r_count FROM PERMISSIONS
    WHERE name = p_name AND action = p_action;
  IF r_count > 0 THEN
    perm := TRUE;
  END IF;
  RETURN perm;
END check_permissions

If the user calling the check_permissions function defines a PERMISSIONS table in their schema, the database will resolve the identifier to refer to the local table. The user would have write access to the new table and could modify it to gain permissions they wouldn't otherwise have.