MySQL Stored Object Access Control

MySQL Stored Object Access Control

In MySQL, access control for stored objects like stored procedures, functions, triggers, and events is governed by privileges and the security context under which these objects execute. This ensures that only authorized users can create, execute, or modify these objects, while also determining whose privileges are used during execution.

Key Concepts of Access Control

1. Privileges for Stored Objects:

   • Specific privileges are required to create, alter, execute, and drop stored objects.
   • Privileges include:
     • CREATE ROUTINE: Required to create stored procedures or functions.
     • ALTER ROUTINE: Required to alter stored procedures or functions.
     • EXECUTE: Required to execute a stored procedure or function.
     • DROP: Required to drop a stored object.

2. Security Context:

   • Determines whose privileges are used during the execution of the stored object.
   • MySQL supports two security contexts:
     • DEFINER: The stored object executes with the privileges of the user who defined it.
     • INVOKER: The stored object executes with the privileges of the user who invoked it.

Privileges Required for Different Operations

1. Stored Procedures and Functions

Operation	Required Privileges
Create Procedure/Function	CREATE ROUTINE
Alter Procedure/Function	ALTER ROUTINE
Execute Procedure/Function	EXECUTE
Drop Procedure/Function	DROP
Call Stored Procedure	EXECUTE on the procedure
Use Stored Function	EXECUTE on the function

2. Triggers

Operation	Required Privileges
Create Trigger	CREATE TRIGGER
Drop Trigger	DROP on the associated table

3. Events

Operation	Required Privileges
Create Event	EVENT
Alter Event	EVENT
Drop Event	EVENT

Security Context: DEFINER vs INVOKER

DEFINER Security Context (Default)

• The object executes with the privileges of the DEFINER user (the user who created the object).
• Defined using the DEFINER = 'user_name'@'host_name' clause.

Example:

CREATE DEFINER = 'admin'@'localhost' PROCEDURE update_balance()
BEGIN
   -- Logic here
END;

• Implication:
  • Even if the invoking user has limited privileges, they can execute the procedure if the DEFINER have sufficient privileges.
  • Suitable for centralizing logic and ensuring consistent behavior.

INVOKER Security Context

• The object executes with the privileges of the user who invokes it.
• Defined using the SQL SECURITY INVOKER clause.

Example:

CREATE PROCEDURE get_user_data()
SQL SECURITY INVOKER
BEGIN
   SELECT * FROM user_data WHERE user_id = CURRENT_USER();
END;

• Implication:
  • Privileges of the invoking user are considered.
  • Useful when execution behavior should be restricted based on the caller's access.

View Privileges and Security Context

1. Check Object Privileges

To check which privileges a user has:

SHOW GRANTS FOR 'user_name'@'host_name';

2. View Stored Object Definitions

Use the SHOW CREATE statement:

• For a procedure or function:

  SHOW CREATE PROCEDURE procedure_name;
  SHOW CREATE FUNCTION function_name;
  

• For a trigger:

  SHOW TRIGGERS LIKE 'table_name';
  

• For an event:

  SHOW CREATE EVENT event_name;
  

Granting and Revoking Privileges

Grant Privileges

GRANT CREATE ROUTINE, EXECUTE ON database_name.* TO 'user_name'@'host_name';

Revoke Privileges

REVOKE EXECUTE ON database_name.* FROM 'user_name'@'host_name';

Best Practices

1. Least Privilege Principle:

   • Grant only the privileges required for users to perform their tasks.
   • Avoid granting GRANT OPTION unless necessary.

2. Use DEFINER Carefully:

   • Ensure the DEFINER user has the appropriate privileges and that no sensitive operations are exposed unnecessarily.

3. Audit Privileges:

   • Regularly review privileges granted to users and stored objects.

4. Test Security:

   • Test the behavior of stored objects under different user contexts to ensure proper access control.

Example: Access Control in Action

Scenario: Restricted Access to Sensitive Data

1. A function calculates salaries and only an admin can invoke it:

   CREATE DEFINER = 'admin'@'localhost' FUNCTION calculate_salary(emp_id INT)
   RETURNS DECIMAL(10,2)
   DETERMINISTIC
   BEGIN
       -- Logic for salary calculation
       RETURN 1000.00; -- Example
   END;
   

2. Grant EXECUTE privilege to a specific user:

   GRANT EXECUTE ON FUNCTION calculate_salary TO 'manager'@'localhost';
   

3. Users without EXECUTE privilege cannot use the function:

   SELECT calculate_salary(101);
   -- Error: FUNCTION calculate_salary does not exist
   

Conclusion

MySQL's access control for stored objects ensures that you can manage security and privileges effectively. By understanding and configuring the DEFINER and INVOKER contexts and managing permissions, you can build secure and robust database applications.

Let me know if you have any questions or need further clarification!