Identifiers without schemas should not be used in invoker's rights packages.
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
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.[1] Oracle Oracle Database PL/SQL Language Reference