This article describes the recommended best practices for:

• Securing Joget database access using the Principle of Least Privilege
• Avoiding the use of superuser/root accounts in Joget datasource configuration
• Protecting audit trail tables from modification (UPDATE / DELETE)
• Ensuring audit logs remain immutable

This approach has been validated against a fresh Joget instance and confirmed to work without affecting normal system operations.

1. Database User Security Best Practices

1.1 Restrict Root Access

• The root account must be restricted to DBA use only.
• Root must not be used in the Joget datasource configuration.
• Root access should ideally be limited to localhost.

This minimizes damage in case of credential compromise or accidental execution of destructive queries.

1.2 Create a Dedicated Joget Database

As a first step, create the Joget database manually:  

CREATE DATABASE jwdb;

1.3 Create Dedicated Joget DB User (Least Privilege)

Create a separate database user for Joget: 

CREATE USER 'joget_user'@'localhost' IDENTIFIED BY 'newpassword';

1.4 Grant Required Schema-Level Privileges

Grant only the required privileges on the specific Joget database:

GRANT SELECT, INSERT, UPDATE, DELETE,
      CREATE, ALTER, INDEX, REFERENCES, LOCK TABLES
ON jwdb.*
TO 'joget_user'@'localhost';

Why LOCK TABLES?

The Joget initialization scripts and Shark workflow engine use table locking operations on SHK tables.
Therefore, LOCK TABLES privilege is required.

Important Notes

• ON jwdb.* means the user has access to all tables within the jwdb schema only.
• To grant access to all schemas and tables, ON *.* would be used — but this is not recommended.
• Do not grant high-risk privileges such as: 

DROP
GRANT OPTION
SUPER
FILE
CREATE USER
SHUTDOWN
KILL

1.5 Joget Initialization

For a fresh Joget instance:

1. Start Joget.
2. Configure the datasource using:
   • Database: jwdb
   • User: joget_user
   • Password: newpassword
3. Joget will automatically create the required tables.

2. Audit Trail Table Protection 

Why Not Use REVOKE?

MySQL privileges are additive.
Selective REVOKE at the table level is unreliable when schema-level privileges are granted.

Therefore, trigger-based enforcement is the recommended approach.  Goal: Make audit tables append-only (Allow INSERT only).

2.1 Prevent DELETE 

CREATE TRIGGER protect_wf_audit_delete
BEFORE DELETE ON wf_audit_trail
FOR EACH ROW
BEGIN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Deletion not allowed on audit trail';
END;

2.2 Prevent UPDATE

CREATE TRIGGER protect_wf_audit_update
BEFORE UPDATE ON wf_audit_trail
FOR EACH ROW
BEGIN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Update not allowed on audit trail';
END;

2.3 Optional – Allow Only DBA to Modify

Instead of blocking all users, including DBA, modification access can be restricted to a specific administrative account.

CREATE TRIGGER protect_wf_audit_update
BEFORE UPDATE ON wf_audit_trail
FOR EACH ROW
BEGIN
    IF (SELECT USER()) != 'root@localhost' THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Update not allowed on audit trail';
    END IF;
END;

A similar approach can be applied for DELETE.

2.4 View Existing Triggers

SHOW TRIGGERS;