DEV Community

Michael
Michael

Posted on • Originally published at gbase.cn

Stored Procedure Execution Context and Permission Chain Risks in GBase 8a

#gbase #database #数据库 #security

Problems with stored procedures in GBase 8a are rarely about syntax. More often, the culprit is the execution context: who defined the procedure, whose privileges are used at runtime, and whether dependent objects or accounts have changed. This article focuses on SQL SECURITY, definer mismatches, and permission chain checks to help you troubleshoot and govern your gbase database procedures.

Common Symptoms and Root Causes

Symptom Likely Root Cause First Check
Procedure created, but a business account can't call it Missing EXECUTE or wrong security mode GRANT EXECUTE, SQL SECURITY
Call succeeds but internal table access fails Unclear definer/invoker boundary Procedure definition, dependent object grants
Works in test, fails after migration or account change DEFINER mismatch or object ownership change SHOW CREATE PROCEDURE, gbase.proc
Procedure unusable after a user rename Definer still points to the old name definer column in gbase.proc

SQL SECURITY: The Key Switch

GBase 8a supports SQL SECURITY DEFINER (default) and SQL SECURITY INVOKER. This setting determines which account's privileges are used during execution.

  • DEFINER: Runs with the definer's privileges. Good for consistent data access interfaces. Risk: if the definer account is disabled or renamed, the procedure breaks.
  • INVOKER: Runs with the caller's privileges. Clearer boundaries, ideal for ops scripts or multi‑tenant isolation. Cost: the caller must have direct access to all objects referenced inside.

The most common failure pattern: a procedure created by a DBA account in development runs fine in test. After migration, a lower‑privileged business account calls it. If the procedure is DEFINER, only EXECUTE is needed. But if the definer account later changes or is removed, every dependent procedure fails.

Definer Mismatch After Account Changes

When a user is renamed with RENAME USER, the definer column in gbase.proc still holds the old name. First, inspect the procedure definition:

SHOW CREATE PROCEDURE app_core.proc_sync_order;
Enter fullscreen mode Exit fullscreen mode

Then check the system catalog:

SELECT db, name, definer
FROM gbase.proc
WHERE db = 'app_core' AND name = 'proc_sync_order';
Enter fullscreen mode Exit fullscreen mode

Fixes include recreating the procedure or, for emergency recovery, directly updating the definer field. If updating the system table, always follow up with FLUSH PRIVILEGES and verify dependent object grants.

Permission Chain: More Than Just EXECUTE

A successful CALL doesn't mean the permission chain is intact. Check layer by layer:

  • Procedure level: Does the caller have EXECUTE?
  • Object level: If INVOKER, does the caller have direct access to every table, view, and function used? If DEFINER, does the definer account still have those privileges?
  • Account level: Does the definer account still exist and is it active?
  • Environment level: Are the current database, VC, and call method consistent with development?

Recommended Patterns

Unified entry point for business (DEFINER):

DELIMITER //
CREATE DEFINER = 'svc_proc'@'%'
PROCEDURE app_core.proc_sync_order(IN p_batch_id VARCHAR(32))
SQL SECURITY DEFINER
BEGIN
    INSERT INTO app_core.order_stage_hist
    SELECT * FROM app_core.order_stage WHERE batch_id = p_batch_id;

    UPDATE app_core.order_stage SET sync_flag = 'Y'
    WHERE batch_id = p_batch_id;
END //
DELIMITER ;
Enter fullscreen mode Exit fullscreen mode

Accompanying grants:

GRANT EXECUTE ON PROCEDURE app_core.proc_sync_order TO 'svc_job'@'%';
GRANT SELECT, INSERT, UPDATE ON app_core.order_stage TO 'svc_proc'@'%';
GRANT INSERT ON app_core.order_stage_hist TO 'svc_proc'@'%';
Enter fullscreen mode Exit fullscreen mode

Decentralized access (INVOKER):

DELIMITER //
CREATE PROCEDURE app_core.proc_check_batch(IN p_batch_id VARCHAR(32))
SQL SECURITY INVOKER
BEGIN
    SELECT batch_id, COUNT(*) AS row_cnt
    FROM app_core.order_stage
    WHERE batch_id = p_batch_id
    GROUP BY batch_id;
END //
DELIMITER ;
Enter fullscreen mode Exit fullscreen mode

Here the caller needs direct SELECT on app_core.order_stage.

Standard Troubleshooting Sequence

  1. Retrieve the procedure definition: SHOW CREATE PROCEDURE.
  2. Check system metadata: SELECT db, name, definer FROM gbase.proc WHERE db = '...'.
  3. Verify grants: SHOW GRANTS FOR 'caller'; SHOW GRANTS FOR 'definer'.
  4. Confirm dependent objects exist and their permissions are correct.

Common Pitfalls and Governance

Pitfall Recommendation
Creating procedures with personal admin accounts Use dedicated procedure owner accounts
Cross‑database references with inconsistent names Standardize naming, use fully qualified names
User renames without rebuilding procedures Recreate or bulk‑check definer values
Granting only EXECUTE Simultaneously verify object‑level grants
No DDL history Version‑control procedure definitions

Stable stored procedure operation in a gbase database depends on clear management of the "creator, executor, object privilege" chain. Treating procedures as operable assets — with planned ownership and explicit security modes — is far more reliable than last‑minute privilege adjustments.

In GBASE's GBase 8a, taking these precautions ensures that your analytical routines keep running smoothly even as accounts evolve and environments shift. Looking ahead, a little discipline in procedure design will save you from mysterious failures and late‑night firefighting.

Top comments (0)