ABSTRACT

Identifiers without schemas should not be used in invoker's rights packages.

EXPLANATION

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: 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.

REFERENCES

[1] Oracle Oracle Database PL/SQL Language Reference