¶Security

¶Authentication

ElyraSQL implements the MySQL mysql_native_password handshake. Passwords are never stored in plaintext — only SHA1(SHA1(password)), the same digest MySQL keeps — and are verified via the challenge/response without reconstruction. Each connection uses a fresh salt.

Configure users on the command line:

# a single admin user
elyrasql serve --user root --password s3cret

# multiple users with roles
elyrasql serve \
  --auth admin:adminpw:admin \
  --auth app:apppw:write \
  --auth analyst:ropw:read

!!! danger "Open mode" With no users configured, ElyraSQL accepts any login and logs a loud warning. This is for local development only. Always configure credentials before exposing the server.

¶Roles

Privileges are hierarchical: read < write < admin. The engine enforces the minimum privilege per statement.

A denied statement returns an access-denied error and is not executed.

¶Managing users with SQL

Besides the startup --auth flags (which define bootstrap accounts that always work), accounts can be created at runtime and are persisted in the database file, so they survive restarts:

CREATE USER 'app'@'%' IDENTIFIED BY 's3cret';   -- created read-only
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'app';  -- promote to write
GRANT ALL PRIVILEGES ON *.* TO 'admin_user';          -- promote to admin
REVOKE ALL PRIVILEGES ON *.* FROM 'app';              -- back to read-only
SET PASSWORD FOR 'app' = 'newsecret';
SHOW GRANTS FOR 'app';
DROP USER 'app';

Notes and current limitations:

• New accounts start read-only; use GRANT to raise them.
• Global grants track the individual privileges granted as a set, so GRANT/REVOKE ON *.* add/remove exactly the named privileges. Revoking one privilege no longer collapses the account: e.g. REVOKE INSERT from an admin keeps every other privilege. SHOW GRANTS lists the precise set.
• Enforcement itself is still evaluated at a coarse tier (read/write/admin) derived from that set: GRANT ALL/GRANT OPTION/SUPER → admin; any write action (INSERT, UPDATE, DELETE, CREATE, ...) present → write; SELECT-only → read. So revoking one of several write privileges keeps the others but does not yet block only that one action.
• Scope: GRANT ... ON *.* (or db.*) sets the account's global privileges; GRANT ... ON <table> (or db.table) is a per-table grant that raises the tier for that table only. Reads are always allowed at the global baseline, so table grants are used to give a read-only account write/admin on specific tables. REVOKE ON <table> removes a table grant.
• DROP USER purges the account's global, per-table, per-column, and role- membership grants, so recreating a user with the same name does not inherit stale privileges.
• Enforcement is deny-safe: a write/DDL statement whose target table can't be determined (e.g. a multi-table UPDATE) requires the global privilege. SHOW GRANTS lists the global grant and each table grant.
• The host part of 'user'@'host' is accepted but ignored (accounts are host-independent).
• Passwords are stored only as SHA1(SHA1(password)).
• A privilege change takes effect on the account's next connection.
• Managing users requires the admin privilege. Creating the first account (in an otherwise open/dev server) turns authentication on for subsequent connections — keep a bootstrap --auth admin so you don't lock yourself out.

¶TLS

Provide a PEM certificate and key to enable TLS. Clients that request SSL are upgraded to an encrypted connection; others continue in plaintext.

elyrasql serve --tls-cert server.crt --tls-key server.key

Generate a self-signed certificate for testing:

openssl req -x509 -newkey rsa:2048 -nodes -days 365 \
  -keyout server.key -out server.crt -subj "/CN=localhost"

mysql -h 127.0.0.1 -P 3307 -u root -p --ssl-mode=REQUIRED

¶Hardening checklist

• Configure --user/--password or --auth (never run open in production).
• Enable TLS with a real certificate.
• Bind to a private interface or firewall the port; only bind 0.0.0.0 when intended.
• Run under the dedicated elyrasql system user (the systemd unit does).
• Grant each application the least privilege it needs.