Using AUTHID Parameter in Oracle PL/SQL

In Oracle, the AUTHID clause is a powerful option to manage DB security and access control. It defines who is considered a current user for execution purposes within stored procedures and functions. This article explores the basics of AUTHID, different settings, and how to use it effectively.

About AUTHID

The AUTHID clause specifies whether the current user for authorization checks is the owner of the procedure or function (AUTHID DEFINER) or the user who invokes it (AUTHID CURRENT_USER).

AUTHID Definer (Default)

The current owner of the procedure/function is the current user for the execution authorization checks. This means it is the procedure or function is executed in the schema where the object exists.

AUTHID CURRENT_USER

The user who executes the procedure/function is the current user for the authorization. This enforces a more fine-grained approach of access control as the invoker's privileges are checked.

Real-World Use Case

In an enterprise, you may be dealing with an application running in a standalone database that has a single schema. The DB developer typically codes PL/SQL blocks that include procedures, functions, and packages with the consideration that the store proc objects will only be used by their own DB users (1 schema user in this case) and not across DBs that may be connected via DB links. With this knowledge, it is common that the code will consider the basic parameters that are mandatory. As the application landscape grows, there will be scenarios when the application talks to other databases through DB links.

Options to Use AUTHID

Sample Code

PL/SQL Procedure (Named Block)

-- Create a Proc with AUTHID Option
CREATE OR REPLACE PROCEDURE myProc (i_Directory IN VARCHAR2)
AUTHID DEFINER | CURRENT_USER
AS
-- procedure code
BEGIN
-- statements
END myProc;
/

PL/SQL Function

-- Create a Function with AUTHID Option
CREATE OR REPLACE FUNCTION myFunc (i_Directory IN VARCHAR2)
AUTHID DEFINER | CURRENT_USER
AS
-- Function code
BEGIN
-- statements
END myFunc;
/

Definer (Default)

In a store procedure where AUTHID DEFINER is used, when the stored proc is executed, the statements are applied to the DB object owner's schema or database. Figure 1 below shows where the stored proc exists on the Target DB but is being executed from the Source DB. In this case, all of the statements are executed on the Target DB, as the definer of the stored procedure is the Target DB. The stored proc at the Target DB may have a combination of DDL and/or DML statements. This could mean the table updates if the table exists, or creating new objects in the Target database.

Current_USER

In a store procedure where AUTHID Current_USER is used, when the stored proc is executed, the statements are applied to the DB object schema or database. Figure 2 below shows where the stored proc exists on the Target DB but is being executed from the Source DB. In this case, all of the statements are executed on the Source DB, as the executor of the stored procedure is the Source DB. If the store proc does not exist on the Source DB, then the execution would fail. If the store proc already exists on the Source DB, then the DDL and DML statements are applied on the source schema.

When the user tries to perform a DDL or DML operation on another user’s object within the same DB or altogether on a different database, the application will halt causing runtime problems that may halt the entire process. It does not necessarily mean a problem but can be viewed as prevention.

Key Considerations

• Security: Carefully consider the security implications of your chosen AUTHID setting. Grant privileges judiciously to avoid unauthorized access to the users of schemas of different DBs.
• Performance: In some cases, AUTHID CURRENT_USER might incur a slight performance overhead due to the additional privilege checks or maybe using DB links.
• Flexibility: AUTHID CURRENT_USER provides greater flexibility and granularity in managing access control and unauthorized runtime executions.

Best Practices

• Use AUTHID CURRENT_USER as the default unless you have a specific reason to use AUTHID DEFINER.
• Grant privileges to roles rather than individual users for easier management.
• Regularly review and audit your database security to ensure it aligns with your organization's policies.

Conclusion

The AUTHID clause is a valuable tool for implementing robust security measures in Oracle databases. By understanding its different settings and how to use them effectively, you can ensure that your data is protected and accessed only by authorized users. Remember to choose the appropriate AUTHID setting based on your specific requirements and security considerations.