GBase 8a Security Hardening: Permissions, Password Polici...

This guide provides a complete security hardening checklist for a gbase database cluster, covering user privileges, password policies, SSL encryption, audit logging, and network access control — all with ready‑to‑run commands.

1. User Privilege Management

A GBase 8a user is identified by both username and source IP. Always follow the principle of least privilege.

Creating Users and Granting Permissions

-- 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;

Inspecting and Revoking Privileges

SHOW GRANTS FOR 'analyst'@'10.168.10.%';
REVOKE INSERT, UPDATE, DELETE ON sales_db.* FROM 'analyst'@'10.168.10.%';
DROP USER 'old_user'@'%';

Common Privilege Reference

Privilege	Description	Safe for ordinary users?
SELECT	Query data	✅
INSERT	Write data	On demand
UPDATE / DELETE	Modify / remove rows	On demand, tightly controlled
FILE	LOAD DATA / SELECT INTO OUTFILE	Load accounts only
SUPER	Change system variables, kill connections	❌
GRANT OPTION	Grant privileges to others	❌ Administrators only
ALL PRIVILEGES	Everything	❌ DBA accounts only

2. Password Policy

Configure in gbase.cnf (both gcluster and gnode):

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

Unlocking an Account

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';

Changing Passwords

-- 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';

3. SSL Encryption

Generate Self‑Signed Certificates (on the gcluster primary)

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

Enable SSL in gcluster

Add to gbase.cnf:

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

Restart gcluster and verify:

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

Enforce SSL for Specific Users

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

JDBC SSL Configuration

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";

Import the server certificate into a Java truststore:

keytool -importcert -alias gbase-server \
    -file /path/to/server-cert.pem \
    -keystore truststore.jks \
    -storepass changeit -noprompt

4. Audit Logging

Configuration

Set in gbase.cnf on both gcluster and gnode:

audit_log = ON
log_output = FILE          # or TABLE
long_query_time = 0        # 0 logs everything; tune in production

Querying Audit Logs (when log_output = TABLE)

-- 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;

5. Network Access Control

-- 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;

6. Security Checklist

-- 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';

7. Common Mistakes

Mistake	Risk	Correct Practice
Using root for applications	Excessive privileges	Create per‑application least‑privilege accounts
All users with host='%'	Any IP can attempt login	Restrict to application server IP ranges
No password expiration	Credentials stagnate	Set 90–180 day expiration
Audit log only on gcluster	gnode activity untracked	Configure on both gcluster and gnode
SSL certificate files world‑readable	Certificates leaked	chmod 600, owned by gbase
Forgetting FLUSH PRIVILEGES	Grant changes not applied	Execute after every GRANT/REVOKE

A hardened gbase database cluster requires consistent application of these controls across all nodes. Use the checklist above as part of your regular security audit to keep your GBASE environment protected.

推荐订阅源

DEV Community