GBase 8a Security Hardening: Permissions, Password Policies, SSL Encryption, and Audit Logs

-- User from a specific subnet

CREATE USER 'analyst'@'10.168.10.%' IDENTIFIED BY 'Str0ng#Pwd!2024';



-- Read‑only

GRANT SELECT ON sales_db.* TO 'analyst'@'10.168.10.%';



-- Read‑write

GRANT SELECT, INSERT, UPDATE, DELETE ON sales_db.* TO 'app_user'@'10.168.10.%';



-- Data loader (FILE privilege is required for LOAD DATA)

GRANT SELECT, INSERT, FILE ON sales_db.* TO 'loader'@'10.168.10.%';



-- Schema administrator (cannot manage users)

GRANT ALL PRIVILEGES ON sales_db.* TO 'db_admin'@'10.168.10.%';



FLUSH PRIVILEGES;

SHOW GRANTS FOR 'analyst'@'10.168.10.%';

REVOKE INSERT, UPDATE, DELETE ON sales_db.* FROM 'analyst'@'10.168.10.%';

DROP USER 'old_user'@'%';

password_min_length = 8

password_format_option = 15       # 1=upper, 2=lower, 4=digit, 8=special — 15 = all

password_max_contain_continuous_char = 3

password_not_same_reverse_username = 1

password_life_time = 90           # days

password_reuse_max = 5

password_reuse_time = 180

login_attempt_max = 5

login_locked_time = 300           # seconds; 0 = permanent

login_locked_factor = 2           # exponential backoff

SELECT user, host, account_locked FROM gclusterdb.user;

ALTER USER 'app_user'@'%' ACCOUNT UNLOCK;

-- Reset password and unlock simultaneously

ALTER USER 'app_user'@'%' IDENTIFIED BY 'New#Pwd!2024';

-- Own password

SET PASSWORD = PASSWORD('New#Pwd!2024');



-- Administrator changing another user's password

ALTER USER 'analyst'@'10.168.10.%' IDENTIFIED BY 'New#Pwd!2024';

cd /opt/gbase/gcluster/config/ssl



# CA private key and certificate

openssl genrsa -out ca-key.pem 4096

openssl req -new -x509 -days 3650 -key ca-key.pem -out ca.pem \

    -subj "/CN=GBase-CA/O=MyCompany"



# Server private key and CSR

openssl genrsa -out server-key.pem 4096

openssl req -new -key server-key.pem -out server-req.pem \

    -subj "/CN=gcluster-server/O=MyCompany"



# Sign the server certificate

openssl x509 -req -days 3650 \

    -in server-req.pem -CA ca.pem -CAkey ca-key.pem \

    -CAcreateserial -out server-cert.pem

ssl-ca   = /opt/gbase/gcluster/config/ssl/ca.pem

ssl-cert = /opt/gbase/gcluster/config/ssl/server-cert.pem

ssl-key  = /opt/gbase/gcluster/config/ssl/server-key.pem

SHOW VARIABLES LIKE 'have_ssl';   -- YES

SHOW STATUS LIKE 'Ssl_cipher';    -- non‑empty means SSL is active

CREATE USER 'secure_user'@'%' IDENTIFIED BY 'Str0ng#Pwd!2024' REQUIRE SSL;

ALTER USER 'analyst'@'10.168.10.%' REQUIRE SSL;

String url = "jdbc:gbase://10.168.10.26:5258/sales_db"

           + "?useSSL=true"

           + "&requireSSL=true"

           + "&verifyServerCertificate=true"

           + "&trustCertificateKeyStoreUrl=file:/path/to/truststore.jks"

           + "&trustCertificateKeyStorePassword=changeit";

keytool -importcert -alias gbase-server \

    -file /path/to/server-cert.pem \

    -keystore truststore.jks \

    -storepass changeit -noprompt

audit_log = ON

log_output = FILE          # or TABLE

long_query_time = 0        # 0 logs everything; tune in production

-- Recent operations

SELECT start_time, user_host,

       ROUND(query_time, 3) AS query_sec,

       ROUND(lock_time, 6)  AS lock_sec,

       rows_sent, rows_examined,

       db, LEFT(sql_text, 300) AS sql_snippet

FROM gclusterdb.slow_log

ORDER BY start_time DESC LIMIT 50;



-- Find DDL statements

SELECT start_time, user_host, db, sql_text

FROM gclusterdb.slow_log

WHERE sql_text REGEXP '^(DROP|ALTER|CREATE|TRUNCATE)'

ORDER BY start_time DESC;



-- User activity summary

SELECT SUBSTRING_INDEX(user_host, '[', 1) AS user_name,

       COUNT(*) AS sql_count,

       ROUND(AVG(query_time), 3) AS avg_sec

FROM gclusterdb.slow_log

WHERE start_time >= CURDATE()

GROUP BY user_name

ORDER BY sql_count DESC;

-- Restrict by source IP via the host portion of the user

CREATE USER 'app_user'@'10.168.10.%' IDENTIFIED BY '...';



-- Limit maximum concurrent connections for a user

CREATE USER 'app_user'@'%'

    IDENTIFIED BY '...'

    WITH MAX_USER_CONNECTIONS 20;

-- Empty passwords

SELECT user, host FROM gclusterdb.user WHERE password = '';



-- Wildcard host for privileged users

SELECT user, host FROM gclusterdb.user WHERE host = '%';



-- Root allowed from anywhere?

SELECT user, host FROM gclusterdb.user WHERE user = 'root';



-- Password and login policies

SHOW VARIABLES LIKE 'password%';

SHOW VARIABLES LIKE 'login%';



-- SSL status

SHOW VARIABLES LIKE 'have_ssl';

SHOW VARIABLES LIKE 'ssl_%';



-- Audit log settings

SHOW VARIABLES LIKE 'audit_log';

SHOW VARIABLES LIKE 'log_output';

SHOW VARIABLES LIKE 'long_query_time';