sql: Data Integrity & Security Using Oracle 21c
-- ============================================================
-- Oracle 21c 珠宝行业「数据完整性与安全」
-- 完整合并脚本 - 一次性执行全部模块 geovindu
-- 执行方式: sqlplus jewelry_app/jewelry_pwd123 @oracle21c_security_full.sql
-- ============================================================
-- 模块列表:
-- 模块1: 建库建表 (schema)
-- 模块2: 批量测试数据 (data)
-- 模块3: 约束验证 (constraints)
-- 模块4: 触发器 (triggers)
-- 模块5: 行级安全/列加密/注入检测 (RLS & Security)
-- ============================================================
SET SERVEROUTPUT ON SIZE UNLIMITED
SET FEEDBACK ON
SET DEFINE ON
-- ============================================================
-- [模块1] 建库建表
-- ============================================================
-- ============================================================
-- Oracle 21c 珠宝行业「数据完整性与安全」
-- 模块1: 建库建表 - 数据库用户/表空间/表/视图/序列/同义词/目录对象/存储过程包/触发器/索引
-- ============================================================
-- 1.1 创建表空间
CREATE TABLESPACE jewelry_ts
DATAFILE '/u01/app/oracle/oradata/ORCL/jewelry_ts.dbf'
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 2G
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
-- 1.2 创建应用用户并授权
CREATE USER jewelry_app IDENTIFIED BY jewelry_pwd123
DEFAULT TABLESPACE jewelry_ts
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON jewelry_ts;
GRANT CONNECT, RESOURCE, DBA TO jewelry_app;
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE PROCEDURE TO jewelry_app;
GRANT CREATE TRIGGER, CREATE SEQUENCE, CREATE SYNONYM TO jewelry_app;
GRANT CREATE DIRECTORY, CREATE DATABASE LINK TO jewelry_app;
GRANT EXECUTE ON DBMS_CRYPTO TO jewelry_app;
GRANT EXECUTE ON DBMS_SESSION TO jewelry_app;
GRANT EXECUTE ON DBMS_RLS TO jewelry_app;
GRANT EXECUTE ON DBMS_ASSERT TO jewelry_app;
GRANT AUDIT ADMIN TO jewelry_app;
GRANT SYSTEM AUDIT TO jewelry_app;
-- 1.3 创建自定义类型
CREATE OR REPLACE TYPE jewelry_obj_type AS OBJECT (
product_id NUMBER,
product_name VARCHAR2(100),
category VARCHAR2(50),
price NUMBER(12,2),
weight_carat NUMBER(8,2),
certification VARCHAR2(100)
);
/
CREATE OR REPLACE TYPE jewelry_ntype AS TABLE OF jewelry_obj_type;
/
-- 1.4 创建表
-- 1.4.1 珠宝分类表
CREATE TABLE jewelry_categories (
category_id NUMBER(6) CONSTRAINT pk_cat PRIMARY KEY,
category_name VARCHAR2(80) NOT NULL,
category_code VARCHAR2(20) CONSTRAINT uk_cat_code UNIQUE,
description VARCHAR2(500),
parent_category NUMBER(6),
is_active VARCHAR2(1) DEFAULT 'Y' CONSTRAINT chk_active CHECK (is_active IN ('Y','N')),
created_by VARCHAR2(30) DEFAULT USER,
created_date DATE DEFAULT SYSDATE,
updated_by VARCHAR2(30),
updated_date DATE
);
-- 1.4.2 珠宝产品表
CREATE TABLE jewelry_products (
product_id NUMBER(10) CONSTRAINT pk_prod PRIMARY KEY,
product_name VARCHAR2(200) NOT NULL,
category_id NUMBER(6) CONSTRAINT fk_prod_cat REFERENCES jewelry_categories(category_id),
product_code VARCHAR2(30) CONSTRAINT uk_prod_code UNIQUE,
material VARCHAR2(50) CONSTRAINT chk_material CHECK (material IN ('18K金','24K金','铂金Pt950','铂金Pt900','18K玫瑰金','14K金','银Ag925','钛Ti','钨W','陶瓷')),
gemstone_type VARCHAR2(60),
carat_weight NUMBER(8,2) CONSTRAINT chk_carat CHECK (carat_weight > 0 AND carat_weight <= 500),
clarity VARCHAR2(20) CONSTRAINT chk_clarity CHECK (clarity IN ('FL','IF','VVS1','VVS2','VS1','VS2','SI1','SI2','I1','I2','I3')),
color_grade VARCHAR2(20) CONSTRAINT chk_color CHECK (color_grade IN ('D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z')),
cut_grade VARCHAR2(20) CONSTRAINT chk_cut CHECK (cut_grade IN ('Excellent','Very Good','Good','Fair','Poor')),
certification_no VARCHAR2(50),
certification_body VARCHAR2(50),
price NUMBER(12,2) CONSTRAINT chk_price CHECK (price >= 0),
cost_price NUMBER(12,2),
stock_quantity NUMBER(8) DEFAULT 0 CONSTRAINT chk_stock_min CHECK (stock_quantity >= 0),
reorder_level NUMBER(6) DEFAULT 5,
is_available VARCHAR2(1) DEFAULT 'Y' CONSTRAINT chk_avail CHECK (is_available IN ('Y','N')),
description VARCHAR2(1000),
image_url VARCHAR2(500),
created_by VARCHAR2(30) DEFAULT USER,
created_date DATE DEFAULT SYSDATE,
updated_by VARCHAR2(30),
updated_date DATE
);
-- 1.4.3 客户表
CREATE TABLE jewelry_customers (
customer_id NUMBER(10) CONSTRAINT pk_cust PRIMARY KEY,
customer_code VARCHAR2(20) CONSTRAINT uk_cust_code UNIQUE,
first_name VARCHAR2(50) NOT NULL,
last_name VARCHAR2(50) NOT NULL,
email VARCHAR2(100) CONSTRAINT uk_cust_email UNIQUE,
phone VARCHAR2(20),
gender VARCHAR2(1) CONSTRAINT chk_gender CHECK (gender IN ('M','F','Other')),
date_of_birth DATE,
membership_level VARCHAR2(20) DEFAULT 'Bronze' CONSTRAINT chk_member CHECK (membership_level IN ('Bronze','Silver','Gold','Platinum','VIP')),
total_purchases NUMBER(12,2) DEFAULT 0,
loyalty_points NUMBER(8) DEFAULT 0,
address_line1 VARCHAR2(200),
address_line2 VARCHAR2(200),
city VARCHAR2(50),
state VARCHAR2(50),
postal_code VARCHAR2(20),
country VARCHAR2(50) DEFAULT 'China',
customer_since DATE DEFAULT SYSDATE,
is_active VARCHAR2(1) DEFAULT 'Y' CONSTRAINT chk_cust_active CHECK (is_active IN ('Y','N')),
notes VARCHAR2(500),
created_by VARCHAR2(30) DEFAULT USER,
created_date DATE DEFAULT SYSDATE
);
-- 1.4.4 员工表
CREATE TABLE jewelry_employees (
employee_id NUMBER(8) CONSTRAINT pk_emp PRIMARY KEY,
emp_code VARCHAR2(20) CONSTRAINT uk_emp_code UNIQUE,
first_name VARCHAR2(50) NOT NULL,
last_name VARCHAR2(50) NOT NULL,
email VARCHAR2(100) CONSTRAINT uk_emp_email UNIQUE,
phone VARCHAR2(20),
hire_date DATE DEFAULT SYSDATE,
department VARCHAR2(50),
position VARCHAR2(50),
salary NUMBER(10,2),
manager_id NUMBER(8),
is_active VARCHAR2(1) DEFAULT 'Y' CONSTRAINT chk_emp_active CHECK (is_active IN ('Y','N')),
created_by VARCHAR2(30) DEFAULT USER,
created_date DATE DEFAULT SYSDATE
);
-- 1.4.5 账户表(系统账户)
CREATE TABLE security_accounts (
account_id NUMBER(10) CONSTRAINT pk_acct PRIMARY KEY,
account_name VARCHAR2(100) NOT NULL,
account_type VARCHAR2(20) CONSTRAINT chk_acct_type CHECK (account_type IN ('Customer','Employee','Admin','Service','External')),
account_email VARCHAR2(100),
customer_id NUMBER(10) CONSTRAINT fk_acct_cust REFERENCES jewelry_customers(customer_id),
employee_id NUMBER(8) CONSTRAINT fk_acct_emp REFERENCES jewelry_employees(employee_id),
password_hash VARCHAR2(512) NOT NULL,
password_salt VARCHAR2(128),
security_question VARCHAR2(200),
security_answer_hash VARCHAR2(512),
login_attempts NUMBER(3) DEFAULT 0 CONSTRAINT chk_attempts CHECK (login_attempts >= 0 AND login_attempts <= 10),
is_locked VARCHAR2(1) DEFAULT 'N' CONSTRAINT chk_locked CHECK (is_locked IN ('Y','N')),
lockout_date DATE,
last_login DATE,
created_date DATE DEFAULT SYSDATE,
updated_date DATE
);
-- 1.4.6 角色表
CREATE TABLE security_roles (
role_id NUMBER(6) CONSTRAINT pk_role PRIMARY KEY,
role_name VARCHAR2(80) CONSTRAINT uk_role_name UNIQUE,
role_code VARCHAR2(30) CONSTRAINT uk_role_code UNIQUE,
description VARCHAR2(200),
is_system VARCHAR2(1) DEFAULT 'N' CONSTRAINT chk_sys_role CHECK (is_system IN ('Y','N')),
created_date DATE DEFAULT SYSDATE
);
-- 1.4.7 权限表
CREATE TABLE security_permissions (
permission_id NUMBER(8) CONSTRAINT pk_perm PRIMARY KEY,
permission_name VARCHAR2(100) NOT NULL,
permission_code VARCHAR2(50) CONSTRAINT uk_perm_code UNIQUE,
resource VARCHAR2(100),
action VARCHAR2(20) CONSTRAINT chk_action CHECK (action IN ('CREATE','READ','UPDATE','DELETE','EXECUTE','ADMIN')),
description VARCHAR2(200),
created_date DATE DEFAULT SYSDATE
);
-- 1.4.8 角色权限关联表
CREATE TABLE role_permissions (
role_id NUMBER(6) CONSTRAINT fk_rp_role REFERENCES security_roles(role_id) ON DELETE CASCADE,
permission_id NUMBER(8) CONSTRAINT fk_rp_perm REFERENCES security_permissions(permission_id) ON DELETE CASCADE,
granted_by VARCHAR2(30) DEFAULT USER,
granted_date DATE DEFAULT SYSDATE,
CONSTRAINT pk_rp PRIMARY KEY (role_id, permission_id)
);
-- 1.4.9 用户角色关联表
CREATE TABLE user_roles (
account_id NUMBER(10) CONSTRAINT fk_ur_acct REFERENCES security_accounts(account_id) ON DELETE CASCADE,
role_id NUMBER(6) CONSTRAINT fk_ur_role REFERENCES security_roles(role_id) ON DELETE CASCADE,
granted_by VARCHAR2(30) DEFAULT USER,
granted_date DATE DEFAULT SYSDATE,
expires_date DATE,
CONSTRAINT pk_ur PRIMARY KEY (account_id, role_id)
);
-- 1.4.10 订单表
CREATE TABLE jewelry_orders (
order_id NUMBER(12) CONSTRAINT pk_order PRIMARY KEY,
order_number VARCHAR2(30) CONSTRAINT uk_order_num UNIQUE,
customer_id NUMBER(10) CONSTRAINT fk_order_cust REFERENCES jewelry_customers(customer_id),
employee_id NUMBER(8) CONSTRAINT fk_order_emp REFERENCES jewelry_employees(employee_id),
order_date DATE DEFAULT SYSDATE,
order_status VARCHAR2(20) DEFAULT 'Pending' CONSTRAINT chk_order_status CHECK (order_status IN ('Pending','Confirmed','Processing','Shipped','Delivered','Cancelled','Refunded','Partial_Refund')),
subtotal NUMBER(12,2) DEFAULT 0,
tax_rate NUMBER(5,4) DEFAULT 0.13,
tax_amount NUMBER(12,2) GENERATED ALWAYS AS (ROUND(subtotal * tax_rate, 2)) VIRTUAL,
discount_amount NUMBER(12,2) DEFAULT 0 CONSTRAINT chk_discount CHECK (discount_amount >= 0),
shipping_cost NUMBER(8,2) DEFAULT 0,
total_amount NUMBER(12,2) GENERATED ALWAYS AS (ROUND(subtotal + tax_amount + shipping_cost - discount_amount, 2)) VIRTUAL,
shipping_address VARCHAR2(500),
notes VARCHAR2(500),
created_by VARCHAR2(30) DEFAULT USER,
created_date DATE DEFAULT SYSDATE,
updated_by VARCHAR2(30),
updated_date DATE
);
-- 1.4.11 订单明细表
CREATE TABLE order_items (
item_id NUMBER(12) CONSTRAINT pk_item PRIMARY KEY,
order_id NUMBER(12) CONSTRAINT fk_item_order REFERENCES jewelry_orders(order_id) ON DELETE CASCADE,
product_id NUMBER(10) CONSTRAINT fk_item_prod REFERENCES jewelry_products(product_id),
quantity NUMBER(4) CONSTRAINT chk_qty CHECK (quantity > 0 AND quantity <= 100),
unit_price NUMBER(12,2) NOT NULL CONSTRAINT chk_item_price CHECK (unit_price >= 0),
discount_pct NUMBER(5,2) DEFAULT 0 CONSTRAINT chk_item_disc CHECK (discount_pct >= 0 AND discount_pct <= 100),
line_total NUMBER(12,2) GENERATED ALWAYS AS (ROUND(unit_price * quantity * (1 - discount_pct/100), 2)) VIRTUAL,
created_date DATE DEFAULT SYSDATE
);
-- 1.4.12 库存表
CREATE TABLE inventory (
inventory_id NUMBER(12) CONSTRAINT pk_inv PRIMARY KEY,
product_id NUMBER(10) CONSTRAINT fk_inv_prod REFERENCES jewelry_products(product_id) ON DELETE CASCADE,
warehouse_location VARCHAR2(50),
quantity_on_hand NUMBER(8) DEFAULT 0 CONSTRAINT chk_inv_qty CHECK (quantity_on_hand >= 0),
quantity_allocated NUMBER(8) DEFAULT 0 CONSTRAINT chk_inv_alloc CHECK (quantity_allocated >= 0),
quantity_available NUMBER(8) GENERATED ALWAYS AS (quantity_on_hand - quantity_allocated) VIRTUAL,
reorder_point NUMBER(6) DEFAULT 5,
max_stock_level NUMBER(8),
last_count_date DATE,
updated_date DATE
);
-- 1.4.13 库存流水表
CREATE TABLE inventory_transactions (
transaction_id NUMBER(12) CONSTRAINT pk_inv_txn PRIMARY KEY,
product_id NUMBER(10) CONSTRAINT fk_inv_txn_prod REFERENCES jewelry_products(product_id),
transaction_type VARCHAR2(20) CONSTRAINT chk_txn_type CHECK (transaction_type IN ('Purchase','Sale','Return','Adjustment','Transfer','Damage','Audit')),
quantity NUMBER(8) NOT NULL,
reference_type VARCHAR2(30),
reference_id NUMBER(12),
performed_by VARCHAR2(30) DEFAULT USER,
transaction_date DATE DEFAULT SYSDATE,
notes VARCHAR2(200)
);
-- 1.4.14 客户评价表
CREATE TABLE product_reviews (
review_id NUMBER(12) CONSTRAINT pk_review PRIMARY KEY,
product_id NUMBER(10) CONSTRAINT fk_review_prod REFERENCES jewelry_products(product_id) ON DELETE CASCADE,
customer_id NUMBER(10) CONSTRAINT fk_review_cust REFERENCES jewelry_customers(customer_id),
order_id NUMBER(12) CONSTRAINT fk_review_order REFERENCES jewelry_orders(order_id),
rating NUMBER(1) CONSTRAINT chk_rating CHECK (rating BETWEEN 1 AND 5),
review_title VARCHAR2(200),
review_text VARCHAR2(2000),
is_verified VARCHAR2(1) DEFAULT 'N' CONSTRAINT chk_verified CHECK (is_verified IN ('Y','N')),
is_approved VARCHAR2(1) DEFAULT 'Y' CONSTRAINT chk_review_approved CHECK (is_approved IN ('Y','N','Pending')),
helpful_count NUMBER(5) DEFAULT 0,
created_date DATE DEFAULT SYSDATE,
updated_date DATE,
CONSTRAINT uk_review UNIQUE (product_id, customer_id, order_id)
);
-- 1.4.15 安全配置表
CREATE TABLE security_config (
config_id NUMBER(6) CONSTRAINT pk_config PRIMARY KEY,
config_key VARCHAR2(100) CONSTRAINT uk_config_key UNIQUE,
config_value VARCHAR2(500),
config_type VARCHAR2(30) CONSTRAINT chk_config_type CHECK (config_type IN ('String','Number','Boolean','JSON','Encrypted')),
description VARCHAR2(200),
is_active VARCHAR2(1) DEFAULT 'Y' CONSTRAINT chk_config_active CHECK (is_active IN ('Y','N')),
updated_date DATE DEFAULT SYSDATE
);
-- 1.4.16 SQL注入检测日志表
CREATE TABLE sql_injection_logs (
log_id NUMBER(12) CONSTRAINT pk_sqli_log PRIMARY KEY,
source_ip VARCHAR2(50),
user_agent VARCHAR2(500),
request_url VARCHAR2(500),
attack_pattern VARCHAR2(200),
attack_type VARCHAR2(30) CONSTRAINT chk_attack_type CHECK (attack_type IN ('SQL_Injection','XSS','Path_Traversal','Command_Injection','Header_Injection','Invalid_Input')),
payload CLOB,
risk_level VARCHAR2(20) DEFAULT 'Medium' CONSTRAINT chk_risk CHECK (risk_level IN ('Low','Medium','High','Critical')),
blocked VARCHAR2(1) DEFAULT 'Y' CONSTRAINT chk_blocked CHECK (blocked IN ('Y','N')),
detected_by VARCHAR2(100),
created_date DATE DEFAULT SYSDATE
);
-- 1.4.17 审计日志表
CREATE TABLE audit_log (
audit_id NUMBER(18) CONSTRAINT pk_audit PRIMARY KEY,
audit_timestamp TIMESTAMP DEFAULT SYSDATE,
audit_user VARCHAR2(100) DEFAULT SYS_CONTEXT('USERENV','SESSION_USER'),
audit_action VARCHAR2(50) NOT NULL,
audit_table VARCHAR2(100),
audit_record_id NUMBER(18),
old_values CLOB,
new_values CLOB,
audit_ip VARCHAR2(50) DEFAULT SYS_CONTEXT('USERENV','IP_ADDRESS'),
audit_module VARCHAR2(50),
success_flag VARCHAR2(1) DEFAULT 'Y' CONSTRAINT chk_audit_success CHECK (success_flag IN ('Y','N')),
error_message VARCHAR2(500)
);
-- 1.5 创建视图
CREATE OR REPLACE VIEW v_product_details AS
SELECT
p.product_id, p.product_name, p.product_code, c.category_name,
p.material, p.gemstone_type, p.carat_weight, p.clarity, p.color_grade, p.cut_grade,
p.certification_no, p.certification_body, p.price, p.cost_price, p.stock_quantity, p.is_available,
CASE WHEN p.price >= 50000 THEN 'Luxury' WHEN p.price >= 10000 THEN 'Premium' WHEN p.price >= 3000 THEN 'Mid-Range' ELSE 'Entry_Level' END AS price_tier,
CASE WHEN p.stock_quantity <= p.reorder_level THEN 'Reorder' WHEN p.stock_quantity > 50 THEN 'In_Stock' ELSE 'Low_Stock' END AS stock_status
FROM jewelry_products p JOIN jewelry_categories c ON p.category_id = c.category_id
WHERE c.is_active = 'Y' AND p.is_available = 'Y';
CREATE OR REPLACE VIEW v_sales_summary AS
SELECT p.product_id, p.product_name, p.product_code,
COUNT(oi.item_id) AS total_orders, SUM(oi.quantity) AS total_quantity_sold, SUM(oi.line_total) AS total_revenue,
AVG(oi.unit_price) AS avg_selling_price, MAX(o.order_date) AS last_sale_date
FROM jewelry_products p LEFT JOIN order_items oi ON p.product_id = oi.product_id
LEFT JOIN jewelry_orders o ON oi.order_id = o.order_id AND o.order_status NOT IN ('Cancelled','Refunded')
GROUP BY p.product_id, p.product_name, p.product_code;
CREATE OR REPLACE VIEW v_customer_value AS
SELECT c.customer_id, c.customer_code, c.first_name || ' ' || c.last_name AS customer_name,
c.membership_level, c.total_purchases, c.loyalty_points,
COUNT(o.order_id) AS total_orders, MAX(o.order_date) AS last_order_date, AVG(o.total_amount) AS avg_order_value
FROM jewelry_customers c LEFT JOIN jewelry_orders o ON c.customer_id = o.customer_id AND o.order_status NOT IN ('Cancelled')
GROUP BY c.customer_id, c.customer_code, c.first_name, c.last_name, c.membership_level, c.total_purchases, c.loyalty_points;
CREATE OR REPLACE VIEW v_inventory_alert AS
SELECT i.inventory_id, p.product_name, p.product_code, i.warehouse_location, i.quantity_on_hand, i.quantity_allocated, i.quantity_available, i.reorder_point,
CASE WHEN i.quantity_available <= 0 THEN 'Out_of_Stock' WHEN i.quantity_available <= i.reorder_point THEN 'Reorder_Needed' WHEN i.quantity_available > i.max_stock_level THEN 'Over_Stocked' ELSE 'OK' END AS alert_status
FROM inventory i JOIN jewelry_products p ON i.product_id = p.product_id;
CREATE OR REPLACE VIEW v_audit_summary AS
SELECT TRUNC(audit_timestamp) AS audit_date, audit_user, audit_table, audit_action, COUNT(*) AS event_count,
SUM(CASE WHEN success_flag = 'N' THEN 1 ELSE 0 END) AS error_count
FROM audit_log GROUP BY TRUNC(audit_timestamp), audit_user, audit_table, audit_action;
CREATE OR REPLACE VIEW v_security_monitor AS
SELECT sa.account_name, sa.account_type, sa.login_attempts, sa.is_locked, sa.last_login,
COUNT(DISTINCT ur.role_id) AS role_count, MAX(audit.audit_timestamp) AS last_audit_activity
FROM security_accounts sa LEFT JOIN user_roles ur ON sa.account_id = ur.account_id
LEFT JOIN audit_log audit ON sa.account_name = audit.audit_user
GROUP BY sa.account_name, sa.account_type, sa.login_attempts, sa.is_locked, sa.last_login;
CREATE OR REPLACE VIEW v_injection_stats AS
SELECT TO_CHAR(created_date, 'YYYY-MM-DD') AS attack_date, attack_type, risk_level, COUNT(*) AS attack_count,
SUM(CASE WHEN blocked = 'Y' THEN 1 ELSE 0 END) AS blocked_count
FROM sql_injection_logs GROUP BY TO_CHAR(created_date, 'YYYY-MM-DD'), attack_type, risk_level;
CREATE OR REPLACE VIEW v_employee_performance AS
SELECT e.employee_id, e.first_name || ' ' || e.last_name AS employee_name, e.department, e.position,
COUNT(DISTINCT o.order_id) AS total_orders_handled, SUM(oi.line_total) AS total_sales,
AVG(o.total_amount) AS avg_order_value
FROM jewelry_employees e LEFT JOIN jewelry_orders o ON e.employee_id = o.employee_id
LEFT JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY e.employee_id, e.first_name, e.last_name, e.department, e.position;
-- 1.6 创建序列
CREATE SEQUENCE seq_category START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE seq_product START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE seq_customer START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE seq_employee START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE seq_account START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE seq_role START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE seq_permission START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE seq_order START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE seq_item START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE seq_inventory START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE seq_txn START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE seq_review START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE seq_config START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE seq_sqli START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE seq_audit START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
-- 1.7 创建同义词
CREATE SYNONYM syn_categories FOR jewelry_categories;
CREATE SYNONYM syn_products FOR jewelry_products;
CREATE SYNONYM syn_customers FOR jewelry_customers;
CREATE SYNONYM syn_employees FOR jewelry_employees;
CREATE SYNONYM syn_accounts FOR security_accounts;
CREATE SYNONYM syn_roles FOR security_roles;
CREATE SYNONYM syn_permissions FOR security_permissions;
CREATE SYNONYM syn_orders FOR jewelry_orders;
CREATE SYNONYM syn_items FOR order_items;
CREATE SYNONYM syn_inventory FOR inventory;
CREATE SYNONYM syn_transactions FOR inventory_transactions;
CREATE SYNONYM syn_reviews FOR product_reviews;
CREATE SYNONYM syn_config FOR security_config;
CREATE SYNONYM syn_sqli_logs FOR sql_injection_logs;
CREATE SYNONYM syn_audit FOR audit_log;
-- 1.8 创建目录对象
CREATE OR REPLACE DIRECTORY jewelry_docs AS '/u01/app/oracle/jewelry/documents';
CREATE OR REPLACE DIRECTORY jewelry_backups AS '/u01/app/oracle/jewelry/backups';
CREATE OR REPLACE DIRECTORY jewelry_logs AS '/u01/app/oracle/jewelry/logs';
-- 1.9 创建存储过程包
CREATE OR REPLACE PACKAGE security_pkg AS
FUNCTION authenticate_user(p_account_name VARCHAR2, p_password VARCHAR2) RETURN BOOLEAN;
FUNCTION hash_password(p_password VARCHAR2, p_salt VARCHAR2) RETURN VARCHAR2;
FUNCTION verify_password_strength(p_password VARCHAR2) RETURN VARCHAR2;
PROCEDURE write_audit_log(p_action VARCHAR2, p_table VARCHAR2, p_record_id NUMBER, p_old_values CLOB, p_new_values CLOB, p_success VARCHAR2 DEFAULT 'Y', p_error VARCHAR2 DEFAULT NULL);
FUNCTION detect_sql_injection(p_input CLOB) RETURN VARCHAR2;
PROCEDURE log_injection_attempt(p_source_ip VARCHAR2, p_user_agent VARCHAR2, p_request_url VARCHAR2, p_attack_pattern VARCHAR2, p_attack_type VARCHAR2, p_payload CLOB, p_risk_level VARCHAR2, p_blocked VARCHAR2, p_detected_by VARCHAR2);
FUNCTION encrypt_data(p_data VARCHAR2, p_key VARCHAR2) RETURN RAW;
FUNCTION decrypt_data(p_encrypted RAW, p_key VARCHAR2) RETURN VARCHAR2;
FUNCTION has_permission(p_account_id NUMBER, p_permission_code VARCHAR2) RETURN BOOLEAN;
PROCEDURE lock_account(p_account_id NUMBER);
PROCEDURE unlock_account(p_account_id NUMBER);
PROCEDURE reset_login_attempts(p_account_id NUMBER);
END security_pkg;
/
CREATE OR REPLACE PACKAGE BODY security_pkg AS
FUNCTION hash_password(p_password VARCHAR2, p_salt VARCHAR2) RETURN VARCHAR2 IS
v_hash RAW(32);
BEGIN
v_hash := DBMS_CRYPTO.HASH(src => UTL_RAW.CAST_TO_RAW(p_password || p_salt), typ => DBMS_CRYPTO.HASH_SHA256);
RETURN RAWTOHEX(v_hash);
END hash_password;
FUNCTION verify_password_strength(p_password VARCHAR2) RETURN VARCHAR2 IS
BEGIN
IF LENGTH(p_password) < 8 THEN RETURN 'TOO_SHORT';
ELSIF LENGTH(p_password) > 128 THEN RETURN 'TOO_LONG';
ELSIF NOT REGEXP_LIKE(p_password, '[A-Z]') THEN RETURN 'NO_UPPERCASE';
ELSIF NOT REGEXP_LIKE(p_password, '[a-z]') THEN RETURN 'NO_LOWERCASE';
ELSIF NOT REGEXP_LIKE(p_password, '[0-9]') THEN RETURN 'NO_NUMBER';
ELSIF NOT REGEXP_LIKE(p_password, '[^A-Za-z0-9]') THEN RETURN 'NO_SPECIAL_CHAR';
END IF;
RETURN 'STRONG';
END verify_password_strength;
PROCEDURE write_audit_log(p_action VARCHAR2, p_table VARCHAR2, p_record_id NUMBER, p_old_values CLOB, p_new_values CLOB, p_success VARCHAR2 DEFAULT 'Y', p_error VARCHAR2 DEFAULT NULL) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO audit_log (audit_id, audit_action, audit_table, audit_record_id, old_values, new_values, success_flag, error_message)
VALUES (seq_audit.NEXTVAL, p_action, p_table, p_record_id, p_old_values, p_new_values, p_success, p_error);
COMMIT;
EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE;
END write_audit_log;
FUNCTION detect_sql_injection(p_input CLOB) RETURN VARCHAR2 IS
v_pattern VARCHAR2(500);
BEGIN
v_pattern := '%'' OR ''''=''%' || '%UNION%SELECT%' || '%DROP%TABLE%' || '%INSERT%INTO%' || '%DELETE%FROM%' || '%UPDATE%SET%' || '%EXEC%xp%' || '%1=1%' || '%OR%1=1%' || '%WAITFOR%DELAY%' || '%BENCHMARK%' || '%SLEEP%' || '%LOAD_FILE%' || '%INTO%OUTFILE%' || '%information_schema%' || '%sys.tables%' || '%CONCAT%' || '%CHAR%' || '%0x%' || '%--%';
IF UPPER(p_input) LIKE UPPER(v_pattern) THEN RETURN 'POTENTIAL_SQL_INJECTION';
END IF;
RETURN 'SAFE';
END detect_sql_injection;
PROCEDURE log_injection_attempt(p_source_ip VARCHAR2, p_user_agent VARCHAR2, p_request_url VARCHAR2, p_attack_pattern VARCHAR2, p_attack_type VARCHAR2, p_payload CLOB, p_risk_level VARCHAR2, p_blocked VARCHAR2, p_detected_by VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO sql_injection_logs (log_id, source_ip, user_agent, request_url, attack_pattern, attack_type, payload, risk_level, blocked, detected_by)
VALUES (seq_sqli.NEXTVAL, p_source_ip, p_user_agent, p_request_url, p_attack_pattern, p_attack_type, p_payload, p_risk_level, p_blocked, p_detected_by);
COMMIT;
EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE;
END log_injection_attempt;
FUNCTION encrypt_data(p_data VARCHAR2, p_key VARCHAR2) RETURN RAW IS
v_key RAW(32);
BEGIN
v_key := UTL_RAW.CAST_TO_RAW(p_key);
RETURN DBMS_CRYPTO.ENCRYPT(src => UTL_RAW.CAST_TO_RAW(p_data), typ => DBMS_CRYPTO.AES_256_CBC, key => v_key, iv => UTL_RAW.CAST_TO_RAW(SUBSTR(p_key, 1, 16)));
END encrypt_data;
FUNCTION decrypt_data(p_encrypted RAW, p_key VARCHAR2) RETURN VARCHAR2 IS
v_key RAW(32);
BEGIN
v_key := UTL_RAW.CAST_TO_RAW(p_key);
RETURN UTL_RAW.CAST_TO_VARCHAR2(DBMS_CRYPTO.DECRYPT(src => p_encrypted, typ => DBMS_CRYPTO.AES_256_CBC, key => v_key, iv => UTL_RAW.CAST_TO_RAW(SUBSTR(p_key, 1, 16))));
END decrypt_data;
FUNCTION has_permission(p_account_id NUMBER, p_permission_code VARCHAR2) RETURN BOOLEAN IS
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM security_accounts sa JOIN user_roles ur ON sa.account_id = ur.account_id
JOIN role_permissions rp ON ur.role_id = rp.role_id JOIN security_permissions sp ON rp.permission_id = sp.permission_id
WHERE sa.account_id = p_account_id AND sp.permission_code = p_permission_code AND (ur.expires_date IS NULL OR ur.expires_date > SYSDATE);
RETURN v_count > 0;
EXCEPTION WHEN NO_DATA_FOUND THEN RETURN FALSE;
END has_permission;
PROCEDURE lock_account(p_account_id NUMBER) IS
BEGIN
UPDATE security_accounts SET is_locked = 'Y', lockout_date = SYSDATE WHERE account_id = p_account_id;
COMMIT;
END lock_account;
PROCEDURE unlock_account(p_account_id NUMBER) IS
BEGIN
UPDATE security_accounts SET is_locked = 'N', lockout_date = NULL WHERE account_id = p_account_id;
COMMIT;
END unlock_account;
PROCEDURE reset_login_attempts(p_account_id NUMBER) IS
BEGIN
UPDATE security_accounts SET login_attempts = 0 WHERE account_id = p_account_id;
COMMIT;
END reset_login_attempts;
END security_pkg;
/
-- 1.10 创建安全登录验证触发器
CREATE OR REPLACE TRIGGER trg_account_login_check
BEFORE INSERT OR UPDATE OF password_hash, login_attempts ON security_accounts
FOR EACH ROW
DECLARE
v_strength VARCHAR2(20);
BEGIN
IF INSERTING AND :NEW.password_hash IS NOT NULL THEN
v_strength := security_pkg.verify_password_strength(:NEW.password_hash);
IF v_strength != 'STRONG' THEN
RAISE_APPLICATION_ERROR(-20001, 'Password does not meet strength requirements: ' || v_strength);
END IF;
END IF;
IF UPDATING AND :NEW.login_attempts >= 5 THEN
:NEW.is_locked := 'Y';
:NEW.lockout_date := SYSDATE;
END IF;
END;
/
-- 1.11 创建索引
CREATE INDEX idx_prod_category ON jewelry_products(category_id);
CREATE INDEX idx_prod_code ON jewelry_products(product_code);
CREATE INDEX idx_prod_available ON jewelry_products(is_available);
CREATE INDEX idx_cust_email ON jewelry_customers(email);
CREATE INDEX idx_cust_code ON jewelry_customers(customer_code);
CREATE INDEX idx_order_cust ON jewelry_orders(customer_id);
CREATE INDEX idx_order_status ON jewelry_orders(order_status);
CREATE INDEX idx_order_date ON jewelry_orders(order_date);
CREATE INDEX idx_item_order ON order_items(order_id);
CREATE INDEX idx_item_product ON order_items(product_id);
CREATE INDEX idx_inv_product ON inventory(product_id);
CREATE INDEX idx_inv_txn_product ON inventory_transactions(product_id);
CREATE INDEX idx_review_product ON product_reviews(product_id);
CREATE INDEX idx_review_cust ON product_reviews(customer_id);
CREATE INDEX idx_audit_table ON audit_log(audit_table);
CREATE INDEX idx_audit_date ON audit_log(audit_timestamp);
CREATE INDEX idx_sqli_date ON sql_injection_logs(created_date);
CREATE INDEX idx_acct_email ON security_accounts(account_email);
CREATE INDEX idx_role_code ON security_roles(role_code);
CREATE INDEX idx_perm_code ON security_permissions(permission_code);
-- 1.12 配置初始安全参数
INSERT INTO security_config (config_id, config_key, config_value, config_type, description) VALUES
(seq_config.NEXTVAL, 'MAX_LOGIN_ATTEMPTS', '5', 'Number', 'Maximum failed login attempts before lockout'),
(seq_config.NEXTVAL, 'LOCKOUT_DURATION_MINUTES', '30', 'Number', 'Account lockout duration in minutes'),
(seq_config.NEXTVAL, 'PASSWORD_MIN_LENGTH', '8', 'Number', 'Minimum password length'),
(seq_config.NEXTVAL, 'PASSWORD_REQUIRE_UPPER', 'TRUE', 'Boolean', 'Require uppercase letters'),
(seq_config.NEXTVAL, 'PASSWORD_REQUIRE_LOWER', 'TRUE', 'Boolean', 'Require lowercase letters'),
(seq_config.NEXTVAL, 'PASSWORD_REQUIRE_NUMBER', 'TRUE', 'Boolean', 'Require numbers'),
(seq_config.NEXTVAL, 'PASSWORD_REQUIRE_SPECIAL', 'TRUE', 'Boolean', 'Require special characters'),
(seq_config.NEXTVAL, 'SESSION_TIMEOUT_MINUTES', '30', 'Number', 'Session timeout in minutes'),
(seq_config.NEXTVAL, 'AUDIT_LOG_RETENTION_DAYS', '365', 'Number', 'Audit log retention period in days'),
(seq_config.NEXTVAL, 'SQL_INJECTION_BLOCK', 'TRUE', 'Boolean', 'Enable SQL injection blocking');
COMMIT;
-- 验证
SELECT table_name FROM user_tables ORDER BY table_name;
SELECT view_name FROM user_views ORDER BY view_name;
SELECT synonym_name FROM user_synonyms ORDER BY synonym_name;
SELECT object_name FROM user_objects WHERE object_type = 'PACKAGE' ORDER BY object_name;
-- ============================================================
-- [模块2] 批量测试数据
-- ============================================================
-- ============================================================
-- Oracle 21c 珠宝行业「数据完整性与安全」
-- 模块2: 批量测试数据
-- ============================================================
-- 2.1 珠宝分类数据
INSERT INTO jewelry_categories (category_id, category_name, category_code, description, parent_category, is_active) VALUES
(seq_category.NEXTVAL, '戒指', 'RING', '各类戒指', NULL, 'Y');
INSERT INTO jewelry_categories (category_id, category_name, category_code, description, parent_category, is_active) VALUES
(seq_category.NEXTVAL, '项链', 'NECKLACE', '各类项链', NULL, 'Y');
INSERT INTO jewelry_categories (category_id, category_name, category_code, description, parent_category, is_active) VALUES
(seq_category.NEXTVAL, '手链', 'BRACELET', '各类手链', NULL, 'Y');
INSERT INTO jewelry_categories (category_id, category_name, category_code, description, parent_category, is_active) VALUES
(seq_category.NEXTVAL, '耳环', 'EARRINGS', '各类耳环', NULL, 'Y');
INSERT INTO jewelry_categories (category_id, category_name, category_code, description, parent_category, is_active) VALUES
(seq_category.NEXTVAL, '腕表', 'WATCH', '珠宝腕表', NULL, 'Y');
INSERT INTO jewelry_categories (category_id, category_name, category_code, description, parent_category, is_active) VALUES
(seq_category.NEXTVAL, '胸针', 'BROOCH', '各类胸针', NULL, 'Y');
INSERT INTO jewelry_categories (category_id, category_name, category_code, description, parent_category, is_active) VALUES
(seq_category.NEXTVAL, '头饰', 'HEADPIECE', '头饰发饰', NULL, 'Y');
INSERT INTO jewelry_categories (category_id, category_name, category_code, description, parent_category, is_active) VALUES
(seq_category.NEXTVAL, '珠宝套装', 'SET', '珠宝套装', NULL, 'Y');
INSERT INTO jewelry_categories (category_id, category_name, category_code, description, parent_category, is_active) VALUES
(seq_category.NEXTVAL, '婚嫁珠宝', 'WEDDING', '婚嫁系列', NULL, 'Y');
INSERT INTO jewelry_categories (category_id, category_name, category_code, description, parent_category, is_active) VALUES
(seq_category.NEXTVAL, '定制珠宝', 'CUSTOM', '定制珠宝服务', NULL, 'Y');
-- 2.2 珠宝产品数据
INSERT INTO jewelry_products (product_id, product_name, category_id, product_code, material, gemstone_type, carat_weight, clarity, color_grade, cut_grade, certification_no, certification_body, price, cost_price, stock_quantity, reorder_level, is_available, description) VALUES
(seq_product.NEXTVAL, '经典六爪钻石戒指', 1, 'RING-001', '铂金Pt950', '钻石', 1.50, 'VVS1', 'E', 'Excellent', 'GF2024001', 'GIA', 89999.00, 45000.00, 5, 3, 'Y', '经典六爪镶嵌1.5克拉钻石戒指');
INSERT INTO jewelry_products (product_id, product_name, category_id, product_code, material, gemstone_type, carat_weight, clarity, color_grade, cut_grade, certification_no, certification_body, price, cost_price, stock_quantity, reorder_level, is_available, description) VALUES
(seq_product.NEXTVAL, '玫瑰金翡翠吊坠', 2, 'NECK-001', '18K玫瑰金', '翡翠', NULL, NULL, NULL, NULL, 'NGTC2024001', 'NGTC', 12800.00, 6500.00, 12, 5, 'Y', '18K玫瑰金镶嵌天然翡翠吊坠');
INSERT INTO jewelry_products (product_id, product_name, category_id, product_code, material, gemstone_type, carat_weight, clarity, color_grade, cut_grade, certification_no, certification_body, price, cost_price, stock_quantity, reorder_level, is_available, description) VALUES
(seq_product.NEXTVAL, '蓝宝石手链', 3, 'BRAC-001', '铂金Pt950', '蓝宝石', 2.30, 'VS1', 'N', 'Very Good', 'GIA2024002', 'GIA', 35600.00, 18000.00, 3, 5, 'Y', '铂金镶嵌天然蓝宝石手链');
INSERT INTO jewelry_products (product_id, product_name, category_id, product_code, material, gemstone_type, carat_weight, clarity, color_grade, cut_grade, certification_no, certification_body, price, cost_price, stock_quantity, reorder_level, is_available, description) VALUES
(seq_product.NEXTVAL, '红宝石耳环', 4, 'EAR-001', '18K金', '红宝石', 1.80, 'VS2', NULL, 'Good', 'GIA2024003', 'GIA', 28500.00, 14000.00, 8, 4, 'Y', '18K金镶嵌天然红宝石耳环');
INSERT INTO jewelry_products (product_id, product_name, category_id, product_code, material, gemstone_type, carat_weight, clarity, color_grade, cut_grade, certification_no, certification_body, price, cost_price, stock_quantity, reorder_level, is_available, description) VALUES
(seq_product.NEXTVAL, '钻石腕表', 5, 'WATCH-001', '18K金', '钻石', 0.50, 'SI1', 'G', 'Very Good', 'GF2024002', 'GIA', 158000.00, 85000.00, 2, 3, 'Y', '18K金钻石镶嵌腕表');
INSERT INTO jewelry_products (product_id, product_name, category_id, product_code, material, gemstone_type, carat_weight, clarity, color_grade, cut_grade, certification_no, certification_body, price, cost_price, stock_quantity, reorder_level, is_available, description) VALUES
(seq_product.NEXTVAL, '珍珠胸针', 6, 'BROO-001', '银Ag925', '珍珠', NULL, NULL, NULL, NULL, 'NGTC2024002', 'NGTC', 3200.00, 1500.00, 20, 10, 'Y', '银镶天然珍珠胸针');
INSERT INTO jewelry_products (product_id, product_name, category_id, product_code, material, gemstone_type, carat_weight, clarity, color_grade, cut_grade, certification_no, certification_body, price, cost_price, stock_quantity, reorder_level, is_available, description) VALUES
(seq_product.NEXTVAL, '祖母绿戒指', 1, 'RING-002', '铂金Pt950', '祖母绿', 2.00, 'VS2', NULL, 'Excellent', 'GIA2024004', 'GIA', 68000.00, 35000.00, 4, 3, 'Y', '铂金镶嵌2克拉祖母绿戒指');
INSERT INTO jewelry_products (product_id, product_name, category_id, product_code, material, gemstone_type, carat_weight, clarity, color_grade, cut_grade, certification_no, certification_body, price, cost_price, stock_quantity, reorder_level, is_available, description) VALUES
(seq_product.NEXTVAL, '黄金项链', 2, 'NECK-002', '24K金', NULL, NULL, NULL, NULL, NULL, NULL, 8500.00, 4200.00, 15, 8, 'Y', '24K黄金经典项链');
INSERT INTO jewelry_products (product_id, product_name, category_id, product_code, material, gemstone_type, carat_weight, clarity, color_grade, cut_grade, certification_no, certification_body, price, cost_price, stock_quantity, reorder_level, is_available, description) VALUES
(seq_product.NEXTVAL, '钛钢手链', 3, 'BRAC-002', '钛Ti', NULL, NULL, NULL, NULL, NULL, NULL, 1800.00, 800.00, 30, 15, 'Y', '钛钢时尚手链');
INSERT INTO jewelry_products (product_id, product_name, category_id, product_code, material, gemstone_type, carat_weight, clarity, color_grade, cut_grade, certification_no, certification_body, price, cost_price, stock_quantity, reorder_level, is_available, description) VALUES
(seq_product.NEXTVAL, '钻石婚戒套装', 9, 'WEDD-001', '铂金Pt950', '钻石', 3.00, 'VVS1', 'D', 'Excellent', 'GIA2024005', 'GIA', 258000.00, 130000.00, 1, 2, 'Y', '铂金3克拉钻石婚戒套装');
-- 2.3 客户数据
INSERT INTO jewelry_customers (customer_id, customer_code, first_name, last_name, email, phone, gender, date_of_birth, membership_level, total_purchases, loyalty_points, city, country, is_active) VALUES
(seq_customer.NEXTVAL, 'CUST-001', 'Wei', 'Zhang', 'zhangwei@email.com', '13800138001', 'M', DATE '1985-03-15', 'Gold', 125000.00, 1250, 'Beijing', 'China', 'Y');
INSERT INTO jewelry_customers (customer_id, customer_code, first_name, last_name, email, phone, gender, date_of_birth, membership_level, total_purchases, loyalty_points, city, country, is_active) VALUES
(seq_customer.NEXTVAL, 'CUST-002', 'Xia', 'Li', 'lixia@email.com', '13800138002', 'F', DATE '1990-07-22', 'Platinum', 358000.00, 3580, 'Shanghai', 'China', 'Y');
INSERT INTO jewelry_customers (customer_id, customer_code, first_name, last_name, email, phone, gender, date_of_birth, membership_level, total_purchases, loyalty_points, city, country, is_active) VALUES
(seq_customer.NEXTVAL, 'CUST-003', 'Jun', 'Wang', 'wangjun@email.com', '13800138003', 'M', DATE '1988-11-08', 'Silver', 45000.00, 450, 'Guangzhou', 'China', 'Y');
INSERT INTO jewelry_customers (customer_id, customer_code, first_name, last_name, email, phone, gender, date_of_birth, membership_level, total_purchases, loyalty_points, city, country, is_active) VALUES
(seq_customer.NEXTVAL, 'CUST-004', 'Mei', 'Chen', 'chenmei@email.com', '13800138004', 'F', DATE '1992-05-30', 'VIP', 520000.00, 5200, 'Shenzhen', 'China', 'Y');
INSERT INTO jewelry_customers (customer_id, customer_code, first_name, last_name, email, phone, gender, date_of_birth, membership_level, total_purchases, loyalty_points, city, country, is_active) VALUES
(seq_customer.NEXTVAL, 'CUST-005', 'Hao', 'Liu', 'liuhao@email.com', '13800138005', 'M', DATE '1987-09-12', 'Gold', 98000.00, 980, 'Hangzhou', 'China', 'Y');
INSERT INTO jewelry_customers (customer_id, customer_code, first_name, last_name, email, phone, gender, date_of_birth, membership_level, total_purchases, loyalty_points, city, country, is_active) VALUES
(seq_customer.NEXTVAL, 'CUST-006', 'Ying', 'Yang', 'yangying@email.com', '13800138006', 'F', DATE '1995-01-25', 'Bronze', 12000.00, 120, 'Chengdu', 'China', 'Y');
INSERT INTO jewelry_customers (customer_id, customer_code, first_name, last_name, email, phone, gender, date_of_birth, membership_level, total_purchases, loyalty_points, city, country, is_active) VALUES
(seq_customer.NEXTVAL, 'CUST-007', 'Fang', 'Huang', 'huangfang@email.com', '13800138007', 'F', DATE '1991-06-18', 'Gold', 156000.00, 1560, 'Nanjing', 'China', 'Y');
INSERT INTO jewelry_customers (customer_id, customer_code, first_name, last_name, email, phone, gender, date_of_birth, membership_level, total_purchases, loyalty_points, city, country, is_active) VALUES
(seq_customer.NEXTVAL, 'CUST-008', 'Lei', 'Zhao', 'zhaolei@email.com', '13800138008', 'M', DATE '1986-12-03', 'Silver', 32000.00, 320, 'Wuhan', 'China', 'Y');
INSERT INTO jewelry_customers (customer_id, customer_code, first_name, last_name, email, phone, gender, date_of_birth, membership_level, total_purchases, loyalty_points, city, country, is_active) VALUES
(seq_customer.NEXTVAL, 'CUST-009', 'Na', 'Wu', 'wuna@email.com', '13800138009', 'F', DATE '1993-04-14', 'Platinum', 285000.00, 2850, 'XiAn', 'China', 'Y');
INSERT INTO jewelry_customers (customer_id, customer_code, first_name, last_name, email, phone, gender, date_of_birth, membership_level, total_purchases, loyalty_points, city, country, is_active) VALUES
(seq_customer.NEXTVAL, 'CUST-010', 'Qiang', 'Zhou', 'zhouqiang@email.com', '13800138010', 'M', DATE '1989-08-27', 'Gold', 178000.00, 1780, 'Chongqing', 'China', 'Y');
-- 2.4 员工数据
INSERT INTO jewelry_employees (employee_id, emp_code, first_name, last_name, email, phone, department, position, salary, is_active) VALUES
(seq_employee.NEXTVAL, 'EMP-001', 'Jing', 'Sun', 'sunjing@jewelry.com', '13900139001', 'Sales', 'Sales Director', 35000.00, 'Y');
INSERT INTO jewelry_employees (employee_id, emp_code, first_name, last_name, email, phone, department, position, salary, is_active) VALUES
(seq_employee.NEXTVAL, 'EMP-002', 'Ming', 'Ma', 'maming@jewelry.com', '13900139002', 'Sales', 'Sales Manager', 25000.00, 'Y');
INSERT INTO jewelry_employees (employee_id, emp_code, first_name, last_name, email, phone, department, position, salary, is_active) VALUES
(seq_employee.NEXTVAL, 'EMP-003', 'Xin', 'Zhu', 'zhuxin@jewelry.com', '13900139003', 'Inventory', 'Inventory Manager', 20000.00, 'Y');
INSERT INTO jewelry_employees (employee_id, emp_code, first_name, last_name, email, phone, department, position, salary, is_active) VALUES
(seq_employee.NEXTVAL, 'EMP-004', 'Hua', 'Lin', 'linhua@jewelry.com', '13900139004', 'Finance', 'Finance Manager', 28000.00, 'Y');
INSERT INTO jewelry_employees (employee_id, emp_code, first_name, last_name, email, phone, department, position, salary, is_active) VALUES
(seq_employee.NEXTVAL, 'EMP-005', 'Rui', 'Gao', 'gaorui@jewelry.com', '13900139005', 'IT', 'IT Security', 22000.00, 'Y');
INSERT INTO jewelry_employees (employee_id, emp_code, first_name, last_name, email, phone, department, position, salary, is_active) VALUES
(seq_employee.NEXTVAL, 'EMP-006', 'Ling', 'Luo', 'luoling@jewelry.com', '13900139006', 'HR', 'HR Manager', 23000.00, 'Y');
INSERT INTO jewelry_employees (employee_id, emp_code, first_name, last_name, email, phone, department, position, salary, is_active) VALUES
(seq_employee.NEXTVAL, 'EMP-007', 'Peng', 'Hu', 'hupeng@jewelry.com', '13900139007', 'Sales', 'Sales Associate', 12000.00, 'Y');
INSERT INTO jewelry_employees (employee_id, emp_code, first_name, last_name, email, phone, department, position, salary, is_active) VALUES
(seq_employee.NEXTVAL, 'EMP-008', 'Yue', 'Zheng', 'zhengyue@jewelry.com', '13900139008', 'Inventory', 'Inventory Clerk', 8000.00, 'Y');
INSERT INTO jewelry_employees (employee_id, emp_code, first_name, last_name, email, phone, department, position, salary, is_active) VALUES
(seq_employee.NEXTVAL, 'EMP-009', 'Tao', 'He', 'hetao@jewelry.com', '13900139009', 'Finance', 'Accountant', 15000.00, 'Y');
INSERT INTO jewelry_employees (employee_id, emp_code, first_name, last_name, email, phone, department, position, salary, is_active) VALUES
(seq_employee.NEXTVAL, 'EMP-010', 'Fei', 'Xu', 'xufei@jewelry.com', '13900139010', 'IT', 'System Admin', 20000.00, 'Y');
-- 2.5 安全角色与权限
INSERT INTO security_roles (role_id, role_name, role_code, description) VALUES
(seq_role.NEXTVAL, '系统管理员', 'ADMIN', '系统管理员，拥有所有权限');
INSERT INTO security_roles (role_id, role_name, role_code, description) VALUES
(seq_role.NEXTVAL, '销售经理', 'SALES_MGR', '销售经理角色');
INSERT INTO security_roles (role_id, role_name, role_code, description) VALUES
(seq_role.NEXTVAL, '销售人员', 'SALES', '销售人员角色');
INSERT INTO security_roles (role_id, role_name, role_code, description) VALUES
(seq_role.NEXTVAL, '库存管理员', 'INVENTORY', '库存管理员角色');
INSERT INTO security_roles (role_id, role_name, role_code, description) VALUES
(seq_role.NEXTVAL, '财务人员', 'FINANCE', '财务人员角色');
INSERT INTO security_roles (role_id, role_name, role_code, description) VALUES
(seq_role.NEXTVAL, 'IT安全', 'IT_SECURITY', 'IT安全管理员');
INSERT INTO security_roles (role_id, role_name, role_code, description) VALUES
(seq_role.NEXTVAL, '客户', 'CUSTOMER', '客户角色');
INSERT INTO security_roles (role_id, role_name, role_code, description) VALUES
(seq_role.NEXTVAL, '审计员', 'AUDITOR', '审计员角色');
INSERT INTO security_permissions (permission_id, permission_name, permission_code, resource, action, description) VALUES
(seq_permission.NEXTVAL, '查看产品', 'PRODUCT_READ', 'products', 'READ', '查看珠宝产品信息');
INSERT INTO security_permissions (permission_id, permission_name, permission_code, resource, action, description) VALUES
(seq_permission.NEXTVAL, '创建产品', 'PRODUCT_CREATE', 'products', 'CREATE', '创建新珠宝产品');
INSERT INTO security_permissions (permission_id, permission_name, permission_code, resource, action, description) VALUES
(seq_permission.NEXTVAL, '更新产品', 'PRODUCT_UPDATE', 'products', 'UPDATE', '更新珠宝产品信息');
INSERT INTO security_permissions (permission_id, permission_name, permission_code, resource, action, description) VALUES
(seq_permission.NEXTVAL, '删除产品', 'PRODUCT_DELETE', 'products', 'DELETE', '删除珠宝产品');
INSERT INTO security_permissions (permission_id, permission_name, permission_code, resource, action, description) VALUES
(seq_permission.NEXTVAL, '查看订单', 'ORDER_READ', 'orders', 'READ', '查看订单信息');
INSERT INTO security_permissions (permission_id, permission_name, permission_code, resource, action, description) VALUES
(seq_permission.NEXTVAL, '创建订单', 'ORDER_CREATE', 'orders', 'CREATE', '创建新订单');
INSERT INTO security_permissions (permission_id, permission_name, permission_code, resource, action, description) VALUES
(seq_permission.NEXTVAL, '更新订单', 'ORDER_UPDATE', 'orders', 'UPDATE', '更新订单状态');
INSERT INTO security_permissions (permission_id, permission_name, permission_code, resource, action, description) VALUES
(seq_permission.NEXTVAL, '查看客户', 'CUSTOMER_READ', 'customers', 'READ', '查看客户信息');
INSERT INTO security_permissions (permission_id, permission_name, permission_code, resource, action, description) VALUES
(seq_permission.NEXTVAL, '管理库存', 'INVENTORY_MANAGE', 'inventory', 'UPDATE', '管理库存');
INSERT INTO security_permissions (permission_id, permission_name, permission_code, resource, action, description) VALUES
(seq_permission.NEXTVAL, '查看审计', 'AUDIT_READ', 'audit_log', 'READ', '查看审计日志');
INSERT INTO security_permissions (permission_id, permission_name, permission_code, resource, action, description) VALUES
(seq_permission.NEXTVAL, '安全管理', 'SECURITY_ADMIN', 'security', 'ADMIN', '安全管理配置');
INSERT INTO security_permissions (permission_id, permission_name, permission_code, resource, action, description) VALUES
(seq_permission.NEXTVAL, '查看报表', 'REPORT_READ', 'reports', 'READ', '查看业务报表');
-- 角色权限分配
INSERT INTO role_permissions (role_id, permission_id) VALUES (1, 1); INSERT INTO role_permissions (role_id, permission_id) VALUES (1, 2); INSERT INTO role_permissions (role_id, permission_id) VALUES (1, 3); INSERT INTO role_permissions (role_id, permission_id) VALUES (1, 4); INSERT INTO role_permissions (role_id, permission_id) VALUES (1, 5); INSERT INTO role_permissions (role_id, permission_id) VALUES (1, 6); INSERT INTO role_permissions (role_id, permission_id) VALUES (1, 7); INSERT INTO role_permissions (role_id, permission_id) VALUES (1, 8); INSERT INTO role_permissions (role_id, permission_id) VALUES (1, 9); INSERT INTO role_permissions (role_id, permission_id) VALUES (1, 10); INSERT INTO role_permissions (role_id, permission_id) VALUES (1, 11); INSERT INTO role_permissions (role_id, permission_id) VALUES (1, 12);
INSERT INTO role_permissions (role_id, permission_id) VALUES (2, 1); INSERT INTO role_permissions (role_id, permission_id) VALUES (2, 5); INSERT INTO role_permissions (role_id, permission_id) VALUES (2, 6); INSERT INTO role_permissions (role_id, permission_id) VALUES (2, 7); INSERT INTO role_permissions (role_id, permission_id) VALUES (2, 8); INSERT INTO role_permissions (role_id, permission_id) VALUES (2, 12);
INSERT INTO role_permissions (role_id, permission_id) VALUES (3, 1); INSERT INTO role_permissions (role_id, permission_id) VALUES (3, 5); INSERT INTO role_permissions (role_id, permission_id) VALUES (3, 6); INSERT INTO role_permissions (role_id, permission_id) VALUES (3, 8);
INSERT INTO role_permissions (role_id, permission_id) VALUES (4, 9); INSERT INTO role_permissions (role_id, permission_id) VALUES (4, 1); INSERT INTO role_permissions (role_id, permission_id) VALUES (4, 12);
INSERT INTO role_permissions (role_id, permission_id) VALUES (5, 5); INSERT INTO role_permissions (role_id, permission_id) VALUES (5, 10); INSERT INTO role_permissions (role_id, permission_id) VALUES (5, 12);
INSERT INTO role_permissions (role_id, permission_id) VALUES (6, 10); INSERT INTO role_permissions (role_id, permission_id) VALUES (6, 11);
INSERT INTO role_permissions (role_id, permission_id) VALUES (7, 1); INSERT INTO role_permissions (role_id, permission_id) VALUES (7, 5);
INSERT INTO role_permissions (role_id, permission_id) VALUES (8, 10); INSERT INTO role_permissions (role_id, permission_id) VALUES (8, 12);
-- 2.6 安全账户
INSERT INTO security_accounts (account_id, account_name, account_type, account_email, customer_id, password_hash, password_salt, login_attempts, is_locked, last_login) VALUES
(seq_account.NEXTVAL, 'admin_jewelry', 'Admin', 'admin@jewelry.com', NULL, 'E3B0C44298FC1C149AFBF4C8996FB92427AE41E4649B934CA495991B7852B855', 'salt_admin_001', 0, 'N', SYSDATE - 1);
INSERT INTO security_accounts (account_id, account_name, account_type, account_email, customer_id, password_hash, password_salt, login_attempts, is_locked, last_login) VALUES
(seq_account.NEXTVAL, 'sales_mgr01', 'Employee', 'sales.mgr@jewelry.com', NULL, 'A591A6D40BF42040450D8A7E8E9B90D1', 'salt_sales_001', 0, 'N', SYSDATE - 2);
INSERT INTO security_accounts (account_id, account_name, account_type, account_email, customer_id, password_hash, password_salt, login_attempts, is_locked, last_login) VALUES
(seq_account.NEXTVAL, 'zhangwei_cust', 'Customer', 'zhangwei@email.com', 1, 'B7E151628AED28A71F9DE8CBBD88E6FA', 'salt_cust_001', 0, 'N', SYSDATE - 5);
INSERT INTO security_accounts (account_id, account_name, account_type, account_email, customer_id, password_hash, password_salt, login_attempts, is_locked, last_login) VALUES
(seq_account.NEXTVAL, 'lixia_cust', 'Customer', 'lixia@email.com', 2, 'C6B17D3F22F97A1B090C5E7D5B7C8E9F', 'salt_cust_002', 3, 'N', SYSDATE - 1);
INSERT INTO security_accounts (account_id, account_name, account_type, account_email, customer_id, password_hash, password_salt, login_attempts, is_locked, last_login) VALUES
(seq_account.NEXTVAL, 'inventory_mgr', 'Employee', 'inventory@jewelry.com', NULL, 'D4C49C33E0F73A66B7E4B8E5C6D7E8F9', 'salt_inv_001', 0, 'N', SYSDATE - 3);
INSERT INTO security_accounts (account_id, account_name, account_type, account_email, customer_id, password_hash, password_salt, login_attempts, is_locked, last_login) VALUES
(seq_account.NEXTVAL, 'it_security', 'Service', 'itsec@jewelry.com', NULL, 'E5D58D44F1G84B77C8F5C9F6D7E8F9A0', 'salt_it_001', 0, 'N', SYSDATE);
INSERT INTO security_accounts (account_id, account_name, account_type, account_email, customer_id, password_hash, password_salt, login_attempts, is_locked, last_login) VALUES
(seq_account.NEXTVAL, 'finance_mgr', 'Employee', 'finance@jewelry.com', NULL, 'F6E69E55G2H95C88D9G6DAG7E8F9A0B1', 'salt_fin_001', 1, 'N', SYSDATE - 4);
INSERT INTO security_accounts (account_id, account_name, account_type, account_email, customer_id, password_hash, password_salt, login_attempts, is_locked, last_login) VALUES
(seq_account.NEXTVAL, 'auditor', 'Service', 'auditor@jewelry.com', NULL, 'A7F7AF66H3IA6D99EAH7EBH8F9A0B1C2', 'salt_aud_001', 0, 'N', SYSDATE - 1);
-- 用户角色分配
INSERT INTO user_roles (account_id, role_id) VALUES (1, 1);
INSERT INTO user_roles (account_id, role_id) VALUES (2, 2);
INSERT INTO user_roles (account_id, role_id) VALUES (3, 7);
INSERT INTO user_roles (account_id, role_id) VALUES (4, 7);
INSERT INTO user_roles (account_id, role_id) VALUES (5, 4);
INSERT INTO user_roles (account_id, role_id) VALUES (6, 6);
INSERT INTO user_roles (account_id, role_id) VALUES (7, 5);
INSERT INTO user_roles (account_id, role_id) VALUES (8, 8);
-- 2.7 订单数据
INSERT INTO jewelry_orders (order_id, order_number, customer_id, employee_id, order_date, order_status, subtotal, discount_amount, shipping_cost) VALUES
(seq_order.NEXTVAL, 'ORD-2024-001', 1, 2, SYSDATE - 30, 'Delivered', 89999.00, 0, 50.00);
INSERT INTO jewelry_orders (order_id, order_number, customer_id, employee_id, order_date, order_status, subtotal, discount_amount, shipping_cost) VALUES
(seq_order.NEXTVAL, 'ORD-2024-002', 2, 2, SYSDATE - 25, 'Delivered', 35600.00, 1780.00, 50.00);
INSERT INTO jewelry_orders (order_id, order_number, customer_id, employee_id, order_date, order_status, subtotal, discount_amount, shipping_cost) VALUES
(seq_order.NEXTVAL, 'ORD-2024-003', 4, 7, SYSDATE - 20, 'Shipped', 68000.00, 3400.00, 80.00);
INSERT INTO jewelry_orders (order_id, order_number, customer_id, employee_id, order_date, order_status, subtotal, discount_amount, shipping_cost) VALUES
(seq_order.NEXTVAL, 'ORD-2024-004', 3, 7, SYSDATE - 15, 'Processing', 12800.00, 0, 30.00);
INSERT INTO jewelry_orders (order_id, order_number, customer_id, employee_id, order_date, order_status, subtotal, discount_amount, shipping_cost) VALUES
(seq_order.NEXTVAL, 'ORD-2024-005', 5, 2, SYSDATE - 10, 'Confirmed', 158000.00, 7900.00, 100.00);
INSERT INTO jewelry_orders (order_id, order_number, customer_id, employee_id, order_date, order_status, subtotal, discount_amount, shipping_cost) VALUES
(seq_order.NEXTVAL, 'ORD-2024-006', 9, 7, SYSDATE - 8, 'Pending', 28500.00, 0, 50.00);
INSERT INTO jewelry_orders (order_id, order_number, customer_id, employee_id, order_date, order_status, subtotal, discount_amount, shipping_cost) VALUES
(seq_order.NEXTVAL, 'ORD-2024-007', 7, 2, SYSDATE - 5, 'Delivered', 258000.00, 12900.00, 150.00);
INSERT INTO jewelry_orders (order_id, order_number, customer_id, employee_id, order_date, order_status, subtotal, discount_amount, shipping_cost) VALUES
(seq_order.NEXTVAL, 'ORD-2024-008', 10, 7, SYSDATE - 3, 'Shipped', 8500.00, 0, 30.00);
INSERT INTO jewelry_orders (order_id, order_number, customer_id, employee_id, order_date, order_status, subtotal, discount_amount, shipping_cost) VALUES
(seq_order.NEXTVAL, 'ORD-2024-009', 6, 2, SYSDATE - 2, 'Processing', 3200.00, 0, 20.00);
INSERT INTO jewelry_orders (order_id, order_number, customer_id, employee_id, order_date, order_status, subtotal, discount_amount, shipping_cost) VALUES
(seq_order.NEXTVAL, 'ORD-2024-010', 8, 7, SYSDATE - 1, 'Pending', 1800.00, 0, 20.00);
-- 2.8 订单明细
INSERT INTO order_items (item_id, order_id, product_id, quantity, unit_price, discount_pct) VALUES
(seq_item.NEXTVAL, 1, 1, 1, 89999.00, 0);
INSERT INTO order_items (item_id, order_id, product_id, quantity, unit_price, discount_pct) VALUES
(seq_item.NEXTVAL, 2, 3, 1, 35600.00, 5);
INSERT INTO order_items (item_id, order_id, product_id, quantity, unit_price, discount_pct) VALUES
(seq_item.NEXTVAL, 3, 7, 1, 68000.00, 5);
INSERT INTO order_items (item_id, order_id, product_id, quantity, unit_price, discount_pct) VALUES
(seq_item.NEXTVAL, 4, 2, 1, 12800.00, 0);
INSERT INTO order_items (item_id, order_id, product_id, quantity, unit_price, discount_pct) VALUES
(seq_item.NEXTVAL, 5, 5, 1, 158000.00, 5);
INSERT INTO order_items (item_id, order_id, product_id, quantity, unit_price, discount_pct) VALUES
(seq_item.NEXTVAL, 6, 4, 1, 28500.00, 0);
INSERT INTO order_items (item_id, order_id, product_id, quantity, unit_price, discount_pct) VALUES
(seq_item.NEXTVAL, 7, 10, 1, 258000.00, 5);
INSERT INTO order_items (item_id, order_id, product_id, quantity, unit_price, discount_pct) VALUES
(seq_item.NEXTVAL, 8, 8, 1, 8500.00, 0);
INSERT INTO order_items (item_id, order_id, product_id, quantity, unit_price, discount_pct) VALUES
(seq_item.NEXTVAL, 9, 6, 1, 3200.00, 0);
INSERT INTO order_items (item_id, order_id, product_id, quantity, unit_price, discount_pct) VALUES
(seq_item.NEXTVAL, 10, 9, 1, 1800.00, 0);
-- 2.9 库存数据
INSERT INTO inventory (inventory_id, product_id, warehouse_location, quantity_on_hand, quantity_allocated, reorder_point, max_stock_level) VALUES
(seq_inventory.NEXTVAL, 1, 'Beijing_Vault', 5, 1, 3, 20);
INSERT INTO inventory (inventory_id, product_id, warehouse_location, quantity_on_hand, quantity_allocated, reorder_point, max_stock_level) VALUES
(seq_inventory.NEXTVAL, 2, 'Shanghai_Vault', 12, 1, 5, 30);
INSERT INTO inventory (inventory_id, product_id, warehouse_location, quantity_on_hand, quantity_allocated, reorder_point, max_stock_level) VALUES
(seq_inventory.NEXTVAL, 3, 'Guangzhou_Vault', 3, 1, 5, 15);
INSERT INTO inventory (inventory_id, product_id, warehouse_location, quantity_on_hand, quantity_allocated, reorder_point, max_stock_level) VALUES
(seq_inventory.NEXTVAL, 4, 'Beijing_Vault', 8, 1, 4, 25);
INSERT INTO inventory (inventory_id, product_id, warehouse_location, quantity_on_hand, quantity_allocated, reorder_point, max_stock_level) VALUES
(seq_inventory.NEXTVAL, 5, 'Shanghai_Vault', 2, 1, 3, 10);
INSERT INTO inventory (inventory_id, product_id, warehouse_location, quantity_on_hand, quantity_allocated, reorder_point, max_stock_level) VALUES
(seq_inventory.NEXTVAL, 6, 'Guangzhou_Vault', 20, 0, 10, 50);
INSERT INTO inventory (inventory_id, product_id, warehouse_location, quantity_on_hand, quantity_allocated, reorder_point, max_stock_level) VALUES
(seq_inventory.NEXTVAL, 7, 'Beijing_Vault', 4, 1, 3, 15);
INSERT INTO inventory (inventory_id, product_id, warehouse_location, quantity_on_hand, quantity_allocated, reorder_point, max_stock_level) VALUES
(seq_inventory.NEXTVAL, 8, 'Shanghai_Vault', 15, 1, 8, 40);
INSERT INTO inventory (inventory_id, product_id, warehouse_location, quantity_on_hand, quantity_allocated, reorder_point, max_stock_level) VALUES
(seq_inventory.NEXTVAL, 9, 'Guangzhou_Vault', 30, 1, 15, 60);
INSERT INTO inventory (inventory_id, product_id, warehouse_location, quantity_on_hand, quantity_allocated, reorder_point, max_stock_level) VALUES
(seq_inventory.NEXTVAL, 10, 'Beijing_Vault', 1, 1, 2, 5);
-- 2.10 库存流水
INSERT INTO inventory_transactions (transaction_id, product_id, transaction_type, quantity, reference_type, reference_id, performed_by, notes) VALUES
(seq_txn.NEXTVAL, 1, 'Purchase', 10, 'PO', 1001, 'EMP-003', 'Initial stock purchase');
INSERT INTO inventory_transactions (transaction_id, product_id, transaction_type, quantity, reference_type, reference_id, performed_by, notes) VALUES
(seq_txn.NEXTVAL, 1, 'Sale', 1, 'Order', 1, 'EMP-002', 'Sold to customer');
INSERT INTO inventory_transactions (transaction_id, product_id, transaction_type, quantity, reference_type, reference_id, performed_by, notes) VALUES
(seq_txn.NEXTVAL, 2, 'Purchase', 20, 'PO', 1002, 'EMP-003', 'Initial stock purchase');
INSERT INTO inventory_transactions (transaction_id, product_id, transaction_type, quantity, reference_type, reference_id, performed_by, notes) VALUES
(seq_txn.NEXTVAL, 2, 'Sale', 1, 'Order', 2, 'EMP-002', 'Sold to customer');
INSERT INTO inventory_transactions (transaction_id, product_id, transaction_type, quantity, reference_type, reference_id, performed_by, notes) VALUES
(seq_txn.NEXTVAL, 3, 'Purchase', 10, 'PO', 1003, 'EMP-003', 'Initial stock purchase');
INSERT INTO inventory_transactions (transaction_id, product_id, transaction_type, quantity, reference_type, reference_id, performed_by, notes) VALUES
(seq_txn.NEXTVAL, 3, 'Sale', 1, 'Order', 2, 'EMP-002', 'Sold to customer');
INSERT INTO inventory_transactions (transaction_id, product_id, transaction_type, quantity, reference_type, reference_id, performed_by, notes) VALUES
(seq_txn.NEXTVAL, 7, 'Purchase', 8, 'PO', 1004, 'EMP-003', 'Initial stock purchase');
INSERT INTO inventory_transactions (transaction_id, product_id, transaction_type, quantity, reference_type, reference_id, performed_by, notes) VALUES
(seq_txn.NEXTVAL, 7, 'Sale', 1, 'Order', 3, 'EMP-007', 'Sold to customer');
INSERT INTO inventory_transactions (transaction_id, product_id, transaction_type, quantity, reference_type, reference_id, performed_by, notes) VALUES
(seq_txn.NEXTVAL, 10, 'Purchase', 3, 'PO', 1005, 'EMP-003', 'Initial stock purchase');
INSERT INTO inventory_transactions (transaction_id, product_id, transaction_type, quantity, reference_type, reference_id, performed_by, notes) VALUES
(seq_txn.NEXTVAL, 10, 'Sale', 1, 'Order', 7, 'EMP-007', 'Sold to customer');
-- 2.11 评价数据
INSERT INTO product_reviews (review_id, product_id, customer_id, order_id, rating, review_title, review_text, is_verified, is_approved) VALUES
(seq_review.NEXTVAL, 1, 1, 1, 5, 'Excellent ring!', 'Beautiful diamond ring, excellent craftsmanship.', 'Y', 'Y');
INSERT INTO product_reviews (review_id, product_id, customer_id, order_id, rating, review_title, review_text, is_verified, is_approved) VALUES
(seq_review.NEXTVAL, 3, 2, 2, 4, 'Nice sapphire', 'Good quality sapphire bracelet.', 'Y', 'Y');
INSERT INTO product_reviews (review_id, product_id, customer_id, order_id, rating, review_title, review_text, is_verified, is_approved) VALUES
(seq_review.NEXTVAL, 7, 4, 3, 5, 'Stunning emerald', 'Amazing emerald ring, highly recommend.', 'Y', 'Y');
INSERT INTO product_reviews (review_id, product_id, customer_id, order_id, rating, review_title, review_text, is_verified, is_approved) VALUES
(seq_review.NEXTVAL, 5, 5, 5, 5, 'Luxury watch', 'Exquisite watch, worth every penny.', 'Y', 'Y');
INSERT INTO product_reviews (review_id, product_id, customer_id, order_id, rating, review_title, review_text, is_verified, is_approved) VALUES
(seq_review.NEXTVAL, 10, 7, 7, 5, 'Perfect wedding set', 'Our dream wedding rings!', 'Y', 'Y');
-- 2.12 SQL注入检测日志
INSERT INTO sql_injection_logs (log_id, source_ip, user_agent, request_url, attack_pattern, attack_type, payload, risk_level, blocked, detected_by) VALUES
(seq_sqli.NEXTVAL, '192.168.1.100', 'Mozilla/5.0', '/api/products?id=1', "' OR '1'='1'", 'SQL_Injection', "' OR '1'='1'", 'High', 'Y', 'WAF_Module');
INSERT INTO sql_injection_logs (log_id, source_ip, user_agent, request_url, attack_pattern, attack_type, payload, risk_level, blocked, detected_by) VALUES
(seq_sqli.NEXTVAL, '192.168.1.101', 'Mozilla/5.0', '/api/orders', "UNION SELECT * FROM users", 'SQL_Injection', "UNION SELECT * FROM users", 'Critical', 'Y', 'WAF_Module');
INSERT INTO sql_injection_logs (log_id, source_ip, user_agent, request_url, attack_pattern, attack_type, payload, risk_level, blocked, detected_by) VALUES
(seq_sqli.NEXTVAL, '192.168.1.102', 'Mozilla/5.0', '/api/login', "admin' --", 'SQL_Injection', "admin' --", 'High', 'Y', 'WAF_Module');
INSERT INTO sql_injection_logs (log_id, source_ip, user_agent, request_url, attack_pattern, attack_type, payload, risk_level, blocked, detected_by) VALUES
(seq_sqli.NEXTVAL, '192.168.1.103', 'Mozilla/5.0', '/api/search', "DROP TABLE products", 'SQL_Injection', "DROP TABLE products", 'Critical', 'Y', 'WAF_Module');
INSERT INTO sql_injection_logs (log_id, source_ip, user_agent, request_url, attack_pattern, attack_type, payload, risk_level, blocked, detected_by) VALUES
(seq_sqli.NEXTVAL, '192.168.1.104', 'Mozilla/5.0', '/api/products', "1; EXEC xp_cmdshell", 'SQL_Injection', "1; EXEC xp_cmdshell", 'Critical', 'Y', 'WAF_Module');
-- 2.13 审计日志
INSERT INTO audit_log (audit_id, audit_action, audit_table, audit_record_id, old_values, new_values, audit_module, success_flag) VALUES
(seq_audit.NEXTVAL, 'INSERT', 'jewelry_products', 1, NULL, '{"product_name":"经典六爪钻石戒指","price":89999}', 'Product_Mgmt', 'Y');
INSERT INTO audit_log (audit_id, audit_action, audit_table, audit_record_id, old_values, new_values, audit_module, success_flag) VALUES
(seq_audit.NEXTVAL, 'INSERT', 'jewelry_orders', 1, NULL, '{"customer_id":1,"total":89999}', 'Order_Mgmt', 'Y');
INSERT INTO audit_log (audit_id, audit_action, audit_table, audit_record_id, old_values, new_values, audit_module, success_flag) VALUES
(seq_audit.NEXTVAL, 'UPDATE', 'security_accounts', 1, '{"login_attempts":0}', '{"login_attempts":1}', 'Auth_Module', 'Y');
INSERT INTO audit_log (audit_id, audit_action, audit_table, audit_record_id, old_values, new_values, audit_module, success_flag) VALUES
(seq_audit.NEXTVAL, 'INSERT', 'sql_injection_logs', 1, NULL, '{"attack_type":"SQL_Injection","risk_level":"High"}', 'Security_Monitor', 'Y');
INSERT INTO audit_log (audit_id, audit_action, audit_table, audit_record_id, old_values, new_values, audit_module, success_flag) VALUES
(seq_audit.NEXTVAL, 'DELETE', 'jewelry_products', 5, '{"product_id":5}', NULL, 'Product_Mgmt', 'N');
-- 2.14 加密交易数据(使用DBMS_CRYPTO模拟)
-- 注意: 实际加密需要在数据库中执行, 这里存储加密后的模拟数据
INSERT INTO security_config (config_id, config_key, config_value, config_type, description) VALUES
(seq_config.NEXTVAL, 'ENCRYPTION_MASTER_KEY', 'JewelryMasterKey2024!', 'Encrypted', 'Master encryption key'),
(seq_config.NEXTVAL, 'AES_KEY_VERSION', '1', 'Number', 'Current AES key version'),
(seq_config.NEXTVAL, 'KEY_ROTATION_DAYS', '90', 'Number', 'Key rotation period'),
(seq_config.NEXTVAL, 'BACKUP_ENCRYPTION', 'TRUE', 'Boolean', 'Enable backup encryption'),
(seq_config.NEXTVAL, 'AUDIT_RETENTION_YEARS', '7', 'Number', 'Audit retention in years'),
(seq_config.NEXTVAL, 'MAX_CONCURRENT_SESSIONS', '50', 'Number', 'Max concurrent sessions'),
(seq_config.NEXTVAL, 'SESSION_IDLE_TIMEOUT', '15', 'Number', 'Idle session timeout minutes'),
(seq_config.NEXTVAL, 'PASSWORD_HISTORY_COUNT', '12', 'Number', 'Password history count'),
(seq_config.NEXTVAL, 'ACCOUNT_LOCKOUT_TIME', '30', 'Number', 'Account lockout minutes'),
(seq_config.NEXTVAL, 'FAILED_LOGIN_WINDOW', '5', 'Number', 'Failed login attempt window');
COMMIT;
-- 验证数据
SELECT 'categories' AS table_name, COUNT(*) AS row_count FROM jewelry_categories
UNION ALL SELECT 'products', COUNT(*) FROM jewelry_products
UNION ALL SELECT 'customers', COUNT(*) FROM jewelry_customers
UNION ALL SELECT 'employees', COUNT(*) FROM jewelry_employees
UNION ALL SELECT 'accounts', COUNT(*) FROM security_accounts
UNION ALL SELECT 'roles', COUNT(*) FROM security_roles
UNION ALL SELECT 'permissions', COUNT(*) FROM security_permissions
UNION ALL SELECT 'orders', COUNT(*) FROM jewelry_orders
UNION ALL SELECT 'order_items', COUNT(*) FROM order_items
UNION ALL SELECT 'inventory', COUNT(*) FROM inventory
UNION ALL SELECT 'transactions', COUNT(*) FROM inventory_transactions
UNION ALL SELECT 'reviews', COUNT(*) FROM product_reviews
UNION ALL SELECT 'config', COUNT(*) FROM security_config
UNION ALL SELECT 'sqli_logs', COUNT(*) FROM sql_injection_logs
UNION ALL SELECT 'audit_log', COUNT(*) FROM audit_log;
-- ============================================================
-- [模块3] 约束验证
-- ============================================================
-- ============================================================
-- Oracle 21c 珠宝行业「数据完整性与安全」
-- 模块3: CHECK约束验证/FOREIGN KEY/UNIQUE/DEFAULT/生成列/完整性检查
-- ============================================================
-- ============================================================
-- 3.1 CHECK约束验证 (10个约束测试)
-- ============================================================
-- 测试1: chk_active - 珠宝分类is_active约束
BEGIN
-- 合法值应成功
UPDATE jewelry_categories SET is_active = 'Y' WHERE category_id = 1;
DBMS_OUTPUT.PUT_LINE('Test 1a PASS: is_active=Y accepted');
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Test 1a FAIL: ' || SQLERRM);
END;
/
BEGIN
-- 非法值应失败
UPDATE jewelry_categories SET is_active = 'X' WHERE category_id = 1;
DBMS_OUTPUT.PUT_LINE('Test 1b FAIL: is_active=X should have been rejected');
EXCEPTION WHEN ORA-02290 THEN
DBMS_OUTPUT.PUT_LINE('Test 1b PASS: is_active=X correctly rejected (CHECK constraint)');
END;
/
-- 测试2: chk_material - 珠宝产品material约束
BEGIN
UPDATE jewelry_products SET material = '18K金' WHERE product_id = 1;
DBMS_OUTPUT.PUT_LINE('Test 2a PASS: material=18K金 accepted');
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Test 2a FAIL: ' || SQLERRM);
END;
/
BEGIN
UPDATE jewelry_products SET material = '塑料' WHERE product_id = 1;
DBMS_OUTPUT.PUT_LINE('Test 2b FAIL: material=塑料 should have been rejected');
EXCEPTION WHEN ORA-02290 THEN
DBMS_OUTPUT.PUT_LINE('Test 2b PASS: material=塑料 correctly rejected (CHECK constraint)');
END;
/
-- 测试3: chk_carat - 克拉重量约束
BEGIN
UPDATE jewelry_products SET carat_weight = 2.00 WHERE product_id = 1;
DBMS_OUTPUT.PUT_LINE('Test 3a PASS: carat_weight=2.00 accepted');
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Test 3a FAIL: ' || SQLERRM);
END;
/
BEGIN
UPDATE jewelry_products SET carat_weight = -1.00 WHERE product_id = 1;
DBMS_OUTPUT.PUT_LINE('Test 3b FAIL: carat_weight=-1 should have been rejected');
EXCEPTION WHEN ORA-02290 THEN
DBMS_OUTPUT.PUT_LINE('Test 3b PASS: carat_weight=-1 correctly rejected (CHECK constraint)');
END;
/
-- 测试4: chk_price - 价格非负约束
BEGIN
UPDATE jewelry_products SET price = 1000 WHERE product_id = 1;
DBMS_OUTPUT.PUT_LINE('Test 4a PASS: price=1000 accepted');
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Test 4a FAIL: ' || SQLERRM);
END;
/
BEGIN
UPDATE jewelry_products SET price = -100 WHERE product_id = 1;
DBMS_OUTPUT.PUT_LINE('Test 4b FAIL: price=-100 should have been rejected');
EXCEPTION WHEN ORA-02290 THEN
DBMS_OUTPUT.PUT_LINE('Test 4b PASS: price=-100 correctly rejected (CHECK constraint)');
END;
/
-- 测试5: chk_rating - 评价评分约束(1-5)
BEGIN
UPDATE product_reviews SET rating = 5 WHERE review_id = 1;
DBMS_OUTPUT.PUT_LINE('Test 5a PASS: rating=5 accepted');
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Test 5a FAIL: ' || SQLERRM);
END;
/
BEGIN
UPDATE product_reviews SET rating = 6 WHERE review_id = 1;
DBMS_OUTPUT.PUT_LINE('Test 5b FAIL: rating=6 should have been rejected');
EXCEPTION WHEN ORA-02290 THEN
DBMS_OUTPUT.PUT_LINE('Test 5b PASS: rating=6 correctly rejected (CHECK constraint)');
END;
/
-- 测试6: chk_order_status - 订单状态约束
BEGIN
UPDATE jewelry_orders SET order_status = 'Delivered' WHERE order_id = 1;
DBMS_OUTPUT.PUT_LINE('Test 6a PASS: order_status=Delivered accepted');
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Test 6a FAIL: ' || SQLERRM);
END;
/
BEGIN
UPDATE jewelry_orders SET order_status = 'Invalid' WHERE order_id = 1;
DBMS_OUTPUT.PUT_LINE('Test 6b FAIL: order_status=Invalid should have been rejected');
EXCEPTION WHEN ORA-02290 THEN
DBMS_OUTPUT.PUT_LINE('Test 6b PASS: order_status=Invalid correctly rejected (CHECK constraint)');
END;
/
-- 测试7: chk_gender - 性别约束
BEGIN
UPDATE jewelry_customers SET gender = 'M' WHERE customer_id = 1;
DBMS_OUTPUT.PUT_LINE('Test 7a PASS: gender=M accepted');
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Test 7a FAIL: ' || SQLERRM);
END;
/
BEGIN
UPDATE jewelry_customers SET gender = 'X' WHERE customer_id = 1;
DBMS_OUTPUT.PUT_LINE('Test 7b FAIL: gender=X should have been rejected');
EXCEPTION WHEN ORA-02290 THEN
DBMS_OUTPUT.PUT_LINE('Test 7b PASS: gender=X correctly rejected (CHECK constraint)');
END;
/
-- 测试8: chk_txn_type - 库存流水类型约束
BEGIN
INSERT INTO inventory_transactions (transaction_id, product_id, transaction_type, quantity, performed_by)
VALUES (seq_txn.NEXTVAL, 1, 'Purchase', 5, 'TEST');
DBMS_OUTPUT.PUT_LINE('Test 8a PASS: transaction_type=Purchase accepted');
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Test 8a FAIL: ' || SQLERRM);
END;
/
BEGIN
INSERT INTO inventory_transactions (transaction_id, product_id, transaction_type, quantity, performed_by)
VALUES (seq_txn.NEXTVAL, 1, 'InvalidType', 5, 'TEST');
DBMS_OUTPUT.PUT_LINE('Test 8b FAIL: transaction_type=InvalidType should have been rejected');
EXCEPTION WHEN ORA-02290 THEN
DBMS_OUTPUT.PUT_LINE('Test 8b PASS: transaction_type=InvalidType correctly rejected (CHECK constraint)');
END;
/
-- 测试9: chk_attack_type - 攻击类型约束
BEGIN
INSERT INTO sql_injection_logs (log_id, source_ip, attack_type, payload, detected_by)
VALUES (seq_sqli.NEXTVAL, '1.2.3.4', 'SQL_Injection', 'test payload', 'TestModule');
DBMS_OUTPUT.PUT_LINE('Test 9a PASS: attack_type=SQL_Injection accepted');
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Test 9a FAIL: ' || SQLERRM);
END;
/
BEGIN
INSERT INTO sql_injection_logs (log_id, source_ip, attack_type, payload, detected_by)
VALUES (seq_sqli.NEXTVAL, '1.2.3.4', 'InvalidType', 'test payload', 'TestModule');
DBMS_OUTPUT.PUT_LINE('Test 9b FAIL: attack_type=InvalidType should have been rejected');
EXCEPTION WHEN ORA-02290 THEN
DBMS_OUTPUT.PUT_LINE('Test 9b PASS: attack_type=InvalidType correctly rejected (CHECK constraint)');
END;
/
-- 测试10: chk_config_type - 安全配置类型约束
BEGIN
UPDATE security_config SET config_type = 'String' WHERE config_id = 1;
DBMS_OUTPUT.PUT_LINE('Test 10a PASS: config_type=String accepted');
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Test 10a FAIL: ' || SQLERRM);
END;
/
BEGIN
UPDATE security_config SET config_type = 'Invalid' WHERE config_id = 1;
DBMS_OUTPUT.PUT_LINE('Test 10b FAIL: config_type=Invalid should have been rejected');
EXCEPTION WHEN ORA-02290 THEN
DBMS_OUTPUT.PUT_LINE('Test 10b PASS: config_type=Invalid correctly rejected (CHECK constraint)');
END;
/
-- ============================================================
-- 3.2 FOREIGN KEY引用完整性验证
-- ============================================================
-- 测试外键约束 - 合法引用
BEGIN
INSERT INTO order_items (item_id, order_id, product_id, quantity, unit_price)
VALUES (seq_item.NEXTVAL, 1, 1, 1, 89999.00);
DBMS_OUTPUT.PUT_LINE('FK Test 1 PASS: Valid order_id and product_id references accepted');
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('FK Test 1 FAIL: ' || SQLERRM);
END;
/
-- 测试外键约束 - 非法order_id引用
BEGIN
INSERT INTO order_items (item_id, order_id, product_id, quantity, unit_price)
VALUES (seq_item.NEXTVAL, 99999, 1, 1, 89999.00);
DBMS_OUTPUT.PUT_LINE('FK Test 2 FAIL: Invalid order_id should have been rejected');
EXCEPTION WHEN ORA-02291 THEN
DBMS_OUTPUT.PUT_LINE('FK Test 2 PASS: Invalid order_id correctly rejected (FOREIGN KEY constraint)');
END;
/
-- 测试外键约束 - 非法product_id引用
BEGIN
INSERT INTO order_items (item_id, order_id, product_id, quantity, unit_price)
VALUES (seq_item.NEXTVAL, 1, 99999, 1, 89999.00);
DBMS_OUTPUT.PUT_LINE('FK Test 3 FAIL: Invalid product_id should have been rejected');
EXCEPTION WHEN ORA-02291 THEN
DBMS_OUTPUT.PUT_LINE('FK Test 3 PASS: Invalid product_id correctly rejected (FOREIGN KEY constraint)');
END;
/
-- 测试外键级联删除 - 删除订单应删除明细
DECLARE
v_count NUMBER;
BEGIN
-- 先插入测试数据
INSERT INTO order_items (item_id, order_id, product_id, quantity, unit_price)
VALUES (seq_item.NEXTVAL, 1, 1, 1, 89999.00);
-- 删除订单(有ON DELETE CASCADE)
DELETE FROM jewelry_orders WHERE order_id = 1;
-- 检查明细是否被级联删除
SELECT COUNT(*) INTO v_count FROM order_items WHERE order_id = 1;
IF v_count = 0 THEN
DBMS_OUTPUT.PUT_LINE('FK Cascade Test PASS: Order items deleted when order deleted');
ELSE
DBMS_OUTPUT.PUT_LINE('FK Cascade Test FAIL: Order items not deleted');
END IF;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('FK Cascade Test INFO: ' || SQLERRM || ' (Expected if order already deleted)');
END;
/
-- ============================================================
-- 3.3 UNIQUE唯一性验证
-- ============================================================
-- 测试product_code唯一性
BEGIN
INSERT INTO jewelry_products (product_id, product_name, category_id, product_code, material, price)
VALUES (seq_product.NEXTVAL, 'Test Product', 1, 'RING-001', '18K金', 1000);
DBMS_OUTPUT.PUT_LINE('UNIQUE Test 1 FAIL: Duplicate product_code should have been rejected');
EXCEPTION WHEN ORA-00001 THEN
DBMS_OUTPUT.PUT_LINE('UNIQUE Test 1 PASS: Duplicate product_code correctly rejected');
END;
/
-- 测试email唯一性(客户)
BEGIN
INSERT INTO jewelry_customers (customer_id, customer_code, first_name, last_name, email, gender)
VALUES (seq_customer.NEXTVAL, 'CUST-TEST', 'Test', 'User', 'zhangwei@email.com', 'M');
DBMS_OUTPUT.PUT_LINE('UNIQUE Test 2 FAIL: Duplicate email should have been rejected');
EXCEPTION WHEN ORA-00001 THEN
DBMS_OUTPUT.PUT_LINE('UNIQUE Test 2 PASS: Duplicate customer email correctly rejected');
END;
/
-- 测试role_code唯一性
BEGIN
INSERT INTO security_roles (role_id, role_name, role_code, description)
VALUES (seq_role.NEXTVAL, 'Test Role', 'ADMIN', 'Test');
DBMS_OUTPUT.PUT_LINE('UNIQUE Test 3 FAIL: Duplicate role_code should have been rejected');
EXCEPTION WHEN ORA-00001 THEN
DBMS_OUTPUT.PUT_LINE('UNIQUE Test 3 PASS: Duplicate role_code correctly rejected');
END;
/
-- ============================================================
-- 3.4 DEFAULT默认值验证
-- ============================================================
-- 测试created_by默认值(USER)
DECLARE
v_user VARCHAR2(30);
BEGIN
INSERT INTO jewelry_categories (category_id, category_name, category_code, description)
VALUES (seq_category.NEXTVAL, 'Default Test', 'DEFT', 'Testing defaults');
SELECT created_by INTO v_user FROM jewelry_categories WHERE category_code = 'DEFT';
IF v_user = USER THEN
DBMS_OUTPUT.PUT_LINE('DEFAULT Test 1 PASS: created_by defaults to USER (' || v_user || ')');
ELSE
DBMS_OUTPUT.PUT_LINE('DEFAULT Test 1 FAIL: created_by = ' || v_user);
END IF;
END;
/
-- 测试created_date默认值(SYSDATE)
DECLARE
v_date DATE;
BEGIN
INSERT INTO jewelry_categories (category_id, category_name, category_code, description)
VALUES (seq_category.NEXTVAL, 'Default Test 2', 'DEFT2', 'Testing defaults');
SELECT created_date INTO v_date FROM jewelry_categories WHERE category_code = 'DEFT2';
IF v_date >= SYSDATE - 1 THEN
DBMS_OUTPUT.PUT_LINE('DEFAULT Test 2 PASS: created_date defaults to SYSDATE (' || TO_CHAR(v_date, 'YYYY-MM-DD HH24:MI:SS') || ')');
ELSE
DBMS_OUTPUT.PUT_LINE('DEFAULT Test 2 FAIL: created_date = ' || TO_CHAR(v_date, 'YYYY-MM-DD'));
END IF;
END;
/
-- 测试stock_quantity默认值(0)
DECLARE
v_qty NUMBER;
BEGIN
INSERT INTO jewelry_products (product_id, product_name, category_id, product_code, material, price)
VALUES (seq_product.NEXTVAL, 'Default Stock Test', 1, 'DEFT-STK', '18K金', 1000);
SELECT stock_quantity INTO v_qty FROM jewelry_products WHERE product_code = 'DEFT-STK';
IF v_qty = 0 THEN
DBMS_OUTPUT.PUT_LINE('DEFAULT Test 3 PASS: stock_quantity defaults to 0');
ELSE
DBMS_OUTPUT.PUT_LINE('DEFAULT Test 3 FAIL: stock_quantity = ' || v_qty);
END IF;
END;
/
-- ============================================================
-- 3.5 生成列(VIRTUAL)验证
-- ============================================================
-- 测试tax_amount生成列
DECLARE
v_tax NUMBER;
v_expected NUMBER;
BEGIN
UPDATE jewelry_orders SET subtotal = 10000 WHERE order_id = 1;
SELECT tax_amount INTO v_tax FROM jewelry_orders WHERE order_id = 1;
v_expected := ROUND(10000 * 0.13, 2);
IF v_tax = v_expected THEN
DBMS_OUTPUT.PUT_LINE('VIRTUAL Test 1 PASS: tax_amount = ' || v_tax || ' (expected ' || v_expected || ')');
ELSE
DBMS_OUTPUT.PUT_LINE('VIRTUAL Test 1 FAIL: tax_amount = ' || v_tax || ' (expected ' || v_expected || ')');
END IF;
END;
/
-- 测试total_amount生成列
DECLARE
v_total NUMBER;
v_expected NUMBER;
BEGIN
UPDATE jewelry_orders SET subtotal = 10000, discount_amount = 500, shipping_cost = 100 WHERE order_id = 1;
SELECT total_amount INTO v_total FROM jewelry_orders WHERE order_id = 1;
v_expected := ROUND(10000 + ROUND(10000 * 0.13, 2) + 100 - 500, 2);
IF v_total = v_expected THEN
DBMS_OUTPUT.PUT_LINE('VIRTUAL Test 2 PASS: total_amount = ' || v_total || ' (expected ' || v_expected || ')');
ELSE
DBMS_OUTPUT.PUT_LINE('VIRTUAL Test 2 FAIL: total_amount = ' || v_total || ' (expected ' || v_expected || ')');
END IF;
END;
/
-- 测试line_total生成列
DECLARE
v_line_total NUMBER;
v_expected NUMBER;
BEGIN
UPDATE order_items SET unit_price = 1000, quantity = 2, discount_pct = 10 WHERE item_id = 1;
SELECT line_total INTO v_line_total FROM order_items WHERE item_id = 1;
v_expected := ROUND(1000 * 2 * (1 - 10/100), 2);
IF v_line_total = v_expected THEN
DBMS_OUTPUT.PUT_LINE('VIRTUAL Test 3 PASS: line_total = ' || v_line_total || ' (expected ' || v_expected || ')');
ELSE
DBMS_OUTPUT.PUT_LINE('VIRTUAL Test 3 FAIL: line_total = ' || v_line_total || ' (expected ' || v_expected || ')');
END IF;
END;
/
-- 测试quantity_available生成列
DECLARE
v_avail NUMBER;
BEGIN
SELECT quantity_available INTO v_avail FROM inventory WHERE inventory_id = 1;
DBMS_OUTPUT.PUT_LINE('VIRTUAL Test 4 PASS: quantity_available = ' || v_avail);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('VIRTUAL Test 4 INFO: ' || SQLERRM);
END;
/
-- ============================================================
-- 3.6 约束禁用与重新验证
-- ============================================================
-- 查看当前约束状态
SELECT constraint_name, constraint_type, status, validated
FROM user_constraints
WHERE table_name IN ('JEWELRY_PRODUCTS', 'JEWELRY_ORDERS', 'SECURITY_ACCOUNTS')
ORDER BY table_name, constraint_name;
-- 禁用CHECK约束(演示用途，生产环境慎用)
ALTER TABLE jewelry_products DISABLE CONSTRAINT chk_price;
SELECT constraint_name, status FROM user_constraints WHERE constraint_name = 'CHK_PRICE';
-- 重新启用并验证
ALTER TABLE jewelry_products ENABLE CONSTRAINT chk_price;
SELECT constraint_name, status FROM user_constraints WHERE constraint_name = 'CHK_PRICE';
-- 禁用外键约束(演示用途)
ALTER TABLE order_items DISABLE CONSTRAINT fk_item_order;
SELECT constraint_name, status FROM user_constraints WHERE constraint_name = 'FK_ITEM_ORDER';
-- 重新启用外键约束
ALTER TABLE order_items ENABLE CONSTRAINT fk_item_order;
SELECT constraint_name, status FROM user_constraints WHERE constraint_name = 'FK_ITEM_ORDER';
-- 使用DBMS_ASSERT包验证输入
DECLARE
v_safe_input VARCHAR2(100);
BEGIN
-- 安全输入
v_safe_input := DBMS_ASSERT.SIMPLE_SQL_NAME('SELECT');
DBMS_OUTPUT.PUT_LINE('DBMS_ASSERT Test 1 PASS: Simple SQL name validated');
-- 验证模式对象名
v_safe_input := DBMS_ASSERT.ENQUAL_NAME('jewelry_products', 'TABLE');
DBMS_OUTPUT.PUT_LINE('DBMS_ASSERT Test 2 PASS: Encoded SQL name validated');
-- 验证数值
DECLARE
v_num NUMBER;
BEGIN
v_num := DBMS_ASSERT.NUMERIC_ASSERT('12345');
DBMS_OUTPUT.PUT_LINE('DBMS_ASSERT Test 3 PASS: Numeric assertion validated = ' || v_num);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('DBMS_ASSERT Test 3 FAIL: ' || SQLERRM);
END;
-- 验证SCHEMA名
DECLARE
v_schema VARCHAR2(30);
BEGIN
v_schema := DBMS_ASSERT.SCHEMA_NAME('PUBLIC');
DBMS_OUTPUT.PUT_LINE('DBMS_ASSERT Test 4 PASS: Schema name validated = ' || v_schema);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('DBMS_ASSERT Test 4 INFO: ' || SQLERRM);
END;
END;
/
-- ============================================================
-- 3.7 自定义数据类型验证
-- ============================================================
-- 测试自定义对象类型
DECLARE
v_product jewelry_obj_type;
BEGIN
v_product := jewelry_obj_type(1, 'Test Ring', 'Ring', 89999.00, 1.50, 'GIA');
DBMS_OUTPUT.PUT_LINE('Object Type Test PASS: jewelry_obj_type created - ' || v_product.product_name);
-- 测试嵌套表类型
DECLARE
v_products jewelry_ntype := jewelry_ntype(
jewelry_obj_type(1, 'Ring', 'Ring', 89999, 1.5, 'GIA'),
jewelry_obj_type(2, 'Necklace', 'Necklace', 12800, NULL, 'NGTC')
);
BEGIN
DBMS_OUTPUT.PUT_LINE('Nested Table Test PASS: jewelry_ntype created with ' || v_products.COUNT || ' elements');
END;
END;
/
-- ============================================================
-- 3.8 数据完整性检查与统计信息更新
-- ============================================================
-- 检查表完整性
ANALYZE TABLE jewelry_categories VALIDATE CONSTRAINTS;
ANALYZE TABLE jewelry_products VALIDATE CONSTRAINTS;
ANALYZE TABLE jewelry_customers VALIDATE CONSTRAINTS;
ANALYZE TABLE jewelry_orders VALIDATE CONSTRAINTS;
ANALYZE TABLE order_items VALIDATE CONSTRAINTS;
ANALYZE TABLE security_accounts VALIDATE CONSTRAINTS;
ANALYZE TABLE security_roles VALIDATE CONSTRAINTS;
ANALYZE TABLE security_permissions VALIDATE CONSTRAINTS;
ANALYZE TABLE audit_log VALIDATE CONSTRAINTS;
ANALYZE TABLE sql_injection_logs VALIDATE CONSTRAINTS;
-- 收集统计信息
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 'JEWELRY_CATEGORIES', cascade => TRUE);
DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 'JEWELRY_PRODUCTS', cascade => TRUE);
DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 'JEWELRY_CUSTOMERS', cascade => TRUE);
DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 'JEWELRY_ORDERS', cascade => TRUE);
DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 'ORDER_ITEMS', cascade => TRUE);
DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 'SECURITY_ACCOUNTS', cascade => TRUE);
DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 'SECURITY_ROLES', cascade => TRUE);
DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 'SECURITY_PERMISSIONS', cascade => TRUE);
DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 'AUDIT_LOG', cascade => TRUE);
DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 'SQL_INJECTION_LOGS', cascade => TRUE);
DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 'INVENTORY', cascade => TRUE);
DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 'INVENTORY_TRANSACTIONS', cascade => TRUE);
DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 'PRODUCT_REVIEWS', cascade => TRUE);
DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 'SECURITY_CONFIG', cascade => TRUE);
DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 'ROLE_PERMISSIONS', cascade => TRUE);
DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 'USER_ROLES', cascade => TRUE);
DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 'JEWELRY_EMPLOYEES', cascade => TRUE);
DBMS_OUTPUT.PUT_LINE('Statistics collection completed for all tables');
END;
/
-- 生成数据完整性汇总报告
SELECT
table_name,
COUNT(*) AS total_constraints,
SUM(CASE WHEN constraint_type = 'P' THEN 1 ELSE 0 END) AS primary_keys,
SUM(CASE WHEN constraint_type = 'R' THEN 1 ELSE 0 END) AS foreign_keys,
SUM(CASE WHEN constraint_type = 'U' THEN 1 ELSE 0 END) AS unique_constraints,
SUM(CASE WHEN constraint_type = 'C' THEN 1 ELSE 0 END) AS check_constraints,
SUM(CASE WHEN status = 'ENABLED' THEN 1 ELSE 0 END) AS enabled_constraints,
SUM(CASE WHEN status = 'DISABLED' THEN 1 ELSE 0 END) AS disabled_constraints
FROM user_constraints
WHERE owner = USER
GROUP BY table_name
ORDER BY table_name;
-- 查看约束详情
SELECT
constraint_name,
constraint_type,
table_name,
search_condition,
status
FROM user_constraints
WHERE owner = USER
ORDER BY table_name, constraint_type;
-- ============================================================
-- [模块4] 触发器
-- ============================================================
-- ============================================================
-- Oracle 21c 珠宝行业「数据完整性与安全」
-- 模块4: AFTER触发器/BEFORE触发器/INSTEAD OF触发器/DDL触发器/递归触发器
-- ============================================================
-- ============================================================
-- 4.1 AFTER触发器 (DML审计日志)
-- ============================================================
-- 触发器1: 产品表AFTER审计触发器
CREATE OR REPLACE TRIGGER trg_product_audit
AFTER INSERT OR UPDATE OR DELETE ON jewelry_products
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
v_action VARCHAR2(20);
v_old_vals CLOB;
v_new_vals CLOB;
BEGIN
IF INSERTING THEN
v_action := 'INSERT';
v_new_vals := 'product_id=' || :NEW.product_id || ',name=' || :NEW.product_name || ',price=' || :NEW.price;
ELSIF UPDATING THEN
v_action := 'UPDATE';
v_old_vals := 'price=' || :OLD.price || ',stock=' || :OLD.stock_quantity;
v_new_vals := 'price=' || :NEW.price || ',stock=' || :NEW.stock_quantity;
ELSIF DELETING THEN
v_action := 'DELETE';
v_old_vals := 'product_id=' || :OLD.product_id || ',name=' || :OLD.product_name;
END IF;
security_pkg.write_audit_log(
p_action => v_action,
p_table => 'JEWELRY_PRODUCTS',
p_record_id => COALESCE(:NEW.product_id, :OLD.product_id),
p_old_values => v_old_vals,
p_new_values => v_new_vals,
p_success => 'Y'
);
EXCEPTION
WHEN OTHERS THEN
NULL; -- 审计失败不应影响主业务
END;
/
-- 触发器2: 订单表AFTER审计触发器
CREATE OR REPLACE TRIGGER trg_order_audit
AFTER INSERT OR UPDATE OR DELETE ON jewelry_orders
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
v_action VARCHAR2(20);
v_old_vals CLOB;
v_new_vals CLOB;
BEGIN
IF INSERTING THEN
v_action := 'INSERT';
v_new_vals := 'order_number=' || :NEW.order_number || ',customer=' || :NEW.customer_id || ',total=' || :NEW.total_amount;
ELSIF UPDATING THEN
v_action := 'UPDATE';
v_old_vals := 'status=' || :OLD.order_status;
v_new_vals := 'status=' || :NEW.order_status;
ELSIF DELETING THEN
v_action := 'DELETE';
v_old_vals := 'order_number=' || :OLD.order_number;
END IF;
security_pkg.write_audit_log(
p_action => v_action,
p_table => 'JEWELRY_ORDERS',
p_record_id => COALESCE(:NEW.order_id, :OLD.order_id),
p_old_values => v_old_vals,
p_new_values => v_new_vals,
p_success => 'Y'
);
EXCEPTION
WHEN OTHERS THEN NULL;
END;
/
-- 触发器3: 库存流水AFTER触发器(自动更新库存)
CREATE OR REPLACE TRIGGER trg_inventory_txn_audit
AFTER INSERT ON inventory_transactions
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
-- 根据流水类型更新库存
IF :NEW.transaction_type = 'Sale' THEN
UPDATE inventory SET
quantity_on_hand = quantity_on_hand - :NEW.quantity,
updated_date = SYSDATE
WHERE product_id = :NEW.product_id;
ELSIF :NEW.transaction_type = 'Return' THEN
UPDATE inventory SET
quantity_on_hand = quantity_on_hand + :NEW.quantity,
updated_date = SYSDATE
WHERE product_id = :NEW.product_id;
ELSIF :NEW.transaction_type = 'Purchase' THEN
UPDATE inventory SET
quantity_on_hand = quantity_on_hand + :NEW.quantity,
updated_date = SYSDATE
WHERE product_id = :NEW.product_id;
ELSIF :NEW.transaction_type = 'Damage' THEN
UPDATE inventory SET
quantity_on_hand = quantity_on_hand - :NEW.quantity,
updated_date = SYSDATE
WHERE product_id = :NEW.product_id;
END IF;
-- 记录库存变动审计
security_pkg.write_audit_log(
p_action => 'INVENTORY_TXN',
p_table => 'INVENTORY_TRANSACTIONS',
p_record_id => :NEW.transaction_id,
p_old_values => 'type=' || :NEW.transaction_type || ',qty=' || :NEW.quantity,
p_new_values => 'product=' || :NEW.product_id || ',date=' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'),
p_success => 'Y'
);
EXCEPTION
WHEN OTHERS THEN NULL;
END;
/
-- ============================================================
-- 4.2 BEFORE触发器 (数据验证与业务规则)
-- ============================================================
-- 触发器4: 订单BEFORE验证触发器
CREATE OR REPLACE TRIGGER trg_order_before_validate
BEFORE INSERT OR UPDATE ON jewelry_orders
FOR EACH ROW
BEGIN
-- 验证订单金额
IF INSERTING THEN
IF :NEW.subtotal < 0 THEN
RAISE_APPLICATION_ERROR(-20002, 'Order subtotal cannot be negative');
END IF;
IF :NEW.discount_amount < 0 THEN
RAISE_APPLICATION_ERROR(-20003, 'Discount amount cannot be negative');
END IF;
-- 自动设置订单号
IF :NEW.order_number IS NULL THEN
:NEW.order_number := 'ORD-' || TO_CHAR(SYSDATE, 'YYYY') || '-' || LPAD(seq_order.CURRVAL + 1, 3, '0');
END IF;
END IF;
-- 更新时记录修改
IF UPDATING THEN
:NEW.updated_date := SYSDATE;
:NEW.updated_by := USER;
-- 订单状态变更审计
IF :OLD.order_status != :NEW.order_status THEN
security_pkg.write_audit_log(
p_action => 'STATUS_CHANGE',
p_table => 'JEWELRY_ORDERS',
p_record_id => :NEW.order_id,
p_old_values => 'status=' || :OLD.order_status,
p_new_values => 'status=' || :NEW.order_status,
p_success => 'Y',
p_error => NULL
);
END IF;
END IF;
END;
/
-- 触发器5: 评价BEFORE防重复触发器
CREATE OR REPLACE TRIGGER trg_review_before_validate
BEFORE INSERT ON product_reviews
FOR EACH ROW
DECLARE
v_count NUMBER;
BEGIN
-- 检查同一客户对同一订单是否已有评价
SELECT COUNT(*) INTO v_count
FROM product_reviews
WHERE customer_id = :NEW.customer_id
AND order_id = :NEW.order_id;
IF v_count > 0 THEN
RAISE_APPLICATION_ERROR(-20004, 'Customer has already reviewed this order');
END IF;
-- 自动设置审核状态
IF :NEW.is_approved IS NULL THEN
:NEW.is_approved := 'Pending';
END IF;
IF :NEW.is_verified IS NULL THEN
:NEW.is_verified := 'N';
END IF;
END;
/
-- 触发器6: 敏感数据审计触发器(账户密码修改审计)
CREATE OR REPLACE TRIGGER trg_account_security_audit
BEFORE UPDATE OF password_hash, login_attempts, is_locked ON security_accounts
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
-- 密码修改审计
IF UPDATING('password_hash') AND :OLD.password_hash != :NEW.password_hash THEN
security_pkg.write_audit_log(
p_action => 'PASSWORD_CHANGE',
p_table => 'SECURITY_ACCOUNTS',
p_record_id => :NEW.account_id,
p_old_values => 'password_changed=TRUE',
p_new_values => 'account=' || :NEW.account_name || ',timestamp=' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'),
p_success => 'Y'
);
END IF;
-- 账户锁定审计
IF UPDATING('is_locked') AND :OLD.is_locked != :NEW.is_locked THEN
security_pkg.write_audit_log(
p_action => 'ACCOUNT_LOCK',
p_table => 'SECURITY_ACCOUNTS',
p_record_id => :NEW.account_id,
p_old_values => 'locked=' || :OLD.is_locked,
p_new_values => 'locked=' || :NEW.is_locked || ',attempts=' || :NEW.login_attempts,
p_success => 'Y'
);
END IF;
-- 登录尝试计数
IF UPDATING('login_attempts') THEN
IF :NEW.login_attempts >= 5 THEN
:NEW.is_locked := 'Y';
:NEW.lockout_date := SYSDATE;
END IF;
END IF;
END;
/
-- ============================================================
-- 4.3 INSTEAD OF触发器 (视图DML控制)
-- ============================================================
-- 触发器7: 订单视图INSTEAD OF触发器(控制订单状态更新)
CREATE OR REPLACE TRIGGER trg_order_view_update
INSTEAD OF UPDATE ON v_sales_summary -- 实际应创建可更新视图
BEGIN
RAISE_APPLICATION_ERROR(-20005, 'View v_sales_summary is read-only. Update base table jewelry_orders instead.');
END;
/
-- 创建可更新的安全视图触发器
CREATE OR REPLACE VIEW v_secure_customer AS
SELECT customer_id, customer_code, first_name, last_name, email, phone, city, membership_level
FROM jewelry_customers
WHERE is_active = 'Y';
/
CREATE OR REPLACE TRIGGER trg_secure_customer_insert
INSTEAD OF INSERT ON v_secure_customer
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO jewelry_customers (customer_id, customer_code, first_name, last_name, email, phone, city, is_active)
VALUES (:NEW.customer_id, :NEW.customer_code, :NEW.first_name, :NEW.last_name, :NEW.email, :NEW.phone, :NEW.city, 'Y');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
/
CREATE OR REPLACE TRIGGER trg_secure_customer_update
INSTEAD OF UPDATE ON v_secure_customer
FOR EACH ROW
BEGIN
UPDATE jewelry_customers SET
first_name = :NEW.first_name,
last_name = :NEW.last_name,
email = :NEW.email,
phone = :NEW.phone,
city = :NEW.city
WHERE customer_id = :NEW.customer_id;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
/
-- ============================================================
-- 4.4 DDL触发器 (架构变更审计)
-- ============================================================
-- 创建DDL审计表
CREATE TABLE ddl_audit_log (
ddl_id NUMBER(18) CONSTRAINT pk_ddl PRIMARY KEY,
ddl_timestamp TIMESTAMP DEFAULT SYSDATE,
ddl_user VARCHAR2(100) DEFAULT SYS_CONTEXT('USERENV','SESSION_USER'),
ddl_operation VARCHAR2(30),
ddl_object_type VARCHAR2(30),
ddl_object_name VARCHAR2(100),
ddl_sql_text CLOB,
ddl_ip VARCHAR2(50) DEFAULT SYS_CONTEXT('USERENV','IP_ADDRESS'),
success_flag VARCHAR2(1) DEFAULT 'Y'
);
-- DDL触发器: 架构变更审计
CREATE OR REPLACE TRIGGER trg_ddl_audit
AFTER CREATE OR ALTER OR DROP ON DATABASE
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
v_obj_type VARCHAR2(30);
v_obj_name VARCHAR2(100);
v_sql CLOB;
BEGIN
v_obj_type := ORA_DICT_OBJ_TYPE;
v_obj_name := ORA_DICT_OBJ_NAME;
-- 只审计当前用户的对象
IF ORA_SYSEVENT IN ('CREATE', 'ALTER', 'DROP') AND USER = SYS_CONTEXT('USERENV','SESSION_USER') THEN
INSERT INTO ddl_audit_log (ddl_id, ddl_operation, ddl_object_type, ddl_object_name, ddl_sql_text)
VALUES (seq_audit.NEXTVAL, ORA_SYSEVENT, v_obj_type, v_obj_name, NULL);
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
-- 阻止危险DDL操作触发器
CREATE OR REPLACE TRIGGER trg_block_dangerous_ddl
BEFORE DROP OR ALTER ON DATABASE
DECLARE
v_obj_type VARCHAR2(30) := ORA_DICT_OBJ_TYPE;
v_obj_name VARCHAR2(100) := ORA_DICT_OBJ_NAME;
BEGIN
-- 阻止删除核心安全表
IF ORA_SYSEVENT = 'DROP' AND UPPER(v_obj_name) IN ('AUDIT_LOG', 'SQL_INJECTION_LOGS', 'SECURITY_ACCOUNTS', 'SECURITY_ROLES', 'SECURITY_PERMISSIONS') THEN
RAISE_APPLICATION_ERROR(-20010, 'Cannot drop security-critical table: ' || v_obj_name);
END IF;
-- 阻止修改安全账户表结构
IF ORA_SYSEVENT = 'ALTER' AND UPPER(v_obj_name) IN ('AUDIT_LOG', 'SQL_INJECTION_LOGS', 'SECURITY_ACCOUNTS') THEN
RAISE_APPLICATION_ERROR(-20011, 'Cannot alter security-critical table: ' || v_obj_name);
END IF;
-- 记录DDL尝试
security_pkg.write_audit_log(
p_action => 'DDL_ATTEMPT:' || ORA_SYSEVENT,
p_table => v_obj_type,
p_record_id => NULL,
p_old_values => 'object=' || v_obj_name,
p_new_values => 'action=' || ORA_SYSEVENT,
p_success => CASE WHEN ORA_SYSEVENT = 'DROP' AND UPPER(v_obj_name) IN ('AUDIT_LOG', 'SQL_INJECTION_LOGS', 'SECURITY_ACCOUNTS', 'SECURITY_ROLES', 'SECURITY_PERMISSIONS') THEN 'N' ELSE 'Y' END
);
EXCEPTION
WHEN ORA-20010 THEN RAISE;
WHEN ORA-20011 THEN RAISE;
WHEN OTHERS THEN NULL;
END;
/
-- ============================================================
-- 4.5 递归触发器 (评价更新VIP等级)
-- ============================================================
-- 创建评价统计临时表
CREATE TABLE review_stats (
customer_id NUMBER(10) CONSTRAINT pk_rs_cust PRIMARY KEY,
total_reviews NUMBER(8) DEFAULT 0,
avg_rating NUMBER(3,2) DEFAULT 0,
last_review_date DATE
);
-- 递归触发器: 评价插入/更新时自动更新客户VIP等级
CREATE OR REPLACE TRIGGER trg_review_update_vip
AFTER INSERT OR UPDATE OR DELETE ON product_reviews
FOR EACH ROW
DECLARE
v_total_reviews NUMBER;
v_avg_rating NUMBER(3,2);
v_current_level VARCHAR2(20);
v_new_level VARCHAR2(20);
BEGIN
-- 计算客户评价统计
SELECT COUNT(*), NVL(AVG(rating), 0), MAX(created_date)
INTO v_total_reviews, v_avg_rating, :NEW.updated_date
FROM product_reviews
WHERE customer_id = COALESCE(:NEW.customer_id, :OLD.customer_id);
-- 根据评价数量和评分更新VIP等级
v_current_level := NULL;
SELECT membership_level INTO v_current_level
FROM jewelry_customers
WHERE customer_id = COALESCE(:NEW.customer_id, :OLD.customer_id);
IF v_total_reviews >= 50 AND v_avg_rating >= 4.5 THEN
v_new_level := 'VIP';
ELSIF v_total_reviews >= 30 AND v_avg_rating >= 4.0 THEN
v_new_level := 'Platinum';
ELSIF v_total_reviews >= 20 THEN
v_new_level := 'Gold';
ELSIF v_total_reviews >= 10 THEN
v_new_level := 'Silver';
ELSE
v_new_level := 'Bronze';
END IF;
-- 只有等级变化时才更新
IF v_new_level != v_current_level THEN
UPDATE jewelry_customers SET membership_level = v_new_level
WHERE customer_id = COALESCE(:NEW.customer_id, :OLD.customer_id);
security_pkg.write_audit_log(
p_action => 'VIP_LEVEL_UPDATE',
p_table => 'JEWELRY_CUSTOMERS',
p_record_id => COALESCE(:NEW.customer_id, :OLD.customer_id),
p_old_values => 'level=' || v_current_level || ',reviews=' || v_total_reviews || ',avg=' || v_avg_rating,
p_new_values => 'level=' || v_new_level,
p_success => 'Y'
);
END IF;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
/
-- ============================================================
-- 4.6 库存预警触发器
-- ============================================================
-- 创建库存预警日志表
CREATE TABLE inventory_alerts (
alert_id NUMBER(12) CONSTRAINT pk_alert PRIMARY KEY,
product_id NUMBER(10) CONSTRAINT fk_alert_prod REFERENCES jewelry_products(product_id),
alert_type VARCHAR2(20) CONSTRAINT chk_alert_type CHECK (alert_type IN ('Low_Stock','Out_of_Stock','Over_Stock','Reorder')),
current_qty NUMBER(8),
threshold NUMBER(8),
alert_message VARCHAR2(500),
is_resolved VARCHAR2(1) DEFAULT 'N' CONSTRAINT chk_alert_resolved CHECK (is_resolved IN ('Y','N')),
created_date DATE DEFAULT SYSDATE,
resolved_date DATE
);
-- 库存预警触发器
CREATE OR REPLACE TRIGGER trg_inventory_alert
AFTER UPDATE ON inventory
FOR EACH ROW
DECLARE
v_product_name VARCHAR2(200);
v_alert_msg VARCHAR2(500);
BEGIN
SELECT product_name INTO v_product_name FROM jewelry_products WHERE product_id = :NEW.product_id;
-- 缺货预警
IF :NEW.quantity_available <= 0 THEN
v_alert_msg := 'OUT OF STOCK: ' || v_product_name || ' (ID:' || :NEW.product_id || ') - Available: 0';
INSERT INTO inventory_alerts (alert_id, product_id, alert_type, current_qty, threshold, alert_message)
VALUES (seq_audit.NEXTVAL, :NEW.product_id, 'Out_of_Stock', :NEW.quantity_available, 0, v_alert_msg);
security_pkg.write_audit_log(
p_action => 'INVENTORY_ALERT',
p_table => 'INVENTORY',
p_record_id => :NEW.inventory_id,
p_old_values => 'available=' || (:OLD.quantity_on_hand - :OLD.quantity_allocated),
p_new_values => v_alert_msg,
p_success => 'Y'
);
ELSIF :NEW.quantity_available <= :NEW.reorder_point THEN
v_alert_msg := 'LOW STOCK: ' || v_product_name || ' (ID:' || :NEW.product_id || ') - Available: ' || :NEW.quantity_available || ', Reorder point: ' || :NEW.reorder_point;
INSERT INTO inventory_alerts (alert_id, product_id, alert_type, current_qty, threshold, alert_message)
VALUES (seq_audit.NEXTVAL, :NEW.product_id, 'Reorder', :NEW.quantity_available, :NEW.reorder_point, v_alert_msg);
END IF;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
/
-- ============================================================
-- 4.7 审计日志归档触发器
-- ============================================================
-- 创建审计归档表
CREATE TABLE audit_log_archive (
audit_id NUMBER(18),
audit_timestamp TIMESTAMP,
audit_user VARCHAR2(100),
audit_action VARCHAR2(50),
audit_table VARCHAR2(100),
audit_record_id NUMBER(18),
old_values CLOB,
new_values CLOB,
audit_ip VARCHAR2(50),
audit_module VARCHAR2(50),
success_flag VARCHAR2(1),
error_message VARCHAR2(500),
archived_date DATE DEFAULT SYSDATE
);
-- 审计日志归档存储过程
CREATE OR REPLACE PROCEDURE sp_archive_audit_log(p_days_old NUMBER DEFAULT 365) IS
v_count NUMBER;
BEGIN
-- 归档旧数据
INSERT INTO audit_log_archive (audit_id, audit_timestamp, audit_user, audit_action, audit_table, audit_record_id, old_values, new_values, audit_ip, audit_module, success_flag, error_message)
SELECT audit_id, audit_timestamp, audit_user, audit_action, audit_table, audit_record_id, old_values, new_values, audit_ip, audit_module, success_flag, error_message
FROM audit_log
WHERE audit_timestamp < SYSDATE - p_days_old;
v_count := SQL%ROWCOUNT;
-- 删除已归档数据
DELETE FROM audit_log WHERE audit_timestamp < SYSDATE - p_days_old;
DBMS_OUTPUT.PUT_LINE('Archived ' || v_count || ' audit records older than ' || p_days_old || ' days');
security_pkg.write_audit_log(
p_action => 'AUDIT_ARCHIVE',
p_table => 'AUDIT_LOG',
p_record_id => NULL,
p_old_values => 'records_before=' || v_count,
p_new_values => 'archived=' || v_count || ',retention=' || p_days_old || 'days',
p_success => 'Y'
);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
/
-- ============================================================
-- 4.8 账户安全监控触发器
-- ============================================================
-- 登录失败监控触发器
CREATE OR REPLACE TRIGGER trg_login_failure_monitor
AFTER UPDATE ON security_accounts
FOR EACH ROW
DECLARE
v_failure_count NUMBER;
BEGIN
-- 检查连续登录失败
IF :NEW.login_attempts >= 5 AND :NEW.is_locked = 'Y' THEN
-- 记录安全事件
security_pkg.write_audit_log(
p_action => 'ACCOUNT_LOCKED',
p_table => 'SECURITY_ACCOUNTS',
p_record_id => :NEW.account_id,
p_old_values => 'account=' || :NEW.account_name || ',attempts=' || :OLD.login_attempts,
p_new_values => 'account=' || :NEW.account_name || ',locked=TRUE,attempts=' || :NEW.login_attempts,
p_success => 'Y'
);
-- 如果是管理员账户，发送高级别告警
IF :NEW.account_type = 'Admin' THEN
security_pkg.write_audit_log(
p_action => 'ADMIN_ACCOUNT_LOCKED',
p_table => 'SECURITY_ACCOUNTS',
p_record_id => :NEW.account_id,
p_old_values => 'admin_account=' || :NEW.account_name,
p_new_values => 'admin_locked=TRUE',
p_success => 'Y'
);
END IF;
END IF;
END;
/
-- ============================================================
-- 验证触发器状态
-- ============================================================
SELECT trigger_name, trigger_type, table_name, status, triggering_event
FROM user_triggers
WHERE owner = USER
ORDER BY trigger_name;
SELECT object_name, object_type, status
FROM user_objects
WHERE owner = USER AND object_type IN ('TRIGGER', 'PROCEDURE', 'PACKAGE', 'PACKAGE BODY')
ORDER BY object_type, object_name;
-- ============================================================
-- [模块5] 行级安全/列加密/注入检测/安全存储过程
-- ============================================================
-- ============================================================
-- Oracle 21c 珠宝行业「数据完整性与安全」
-- 模块5: 行级安全(RLS)/列级安全/列加密/哈希函数/SQL注入检测/参数化查询/安全存储过程
-- ============================================================
-- ============================================================
-- 5.1 行级安全性(RLS)谓词函数与安全策略
-- ============================================================
-- 谓词函数1: 客户数据行级访问控制
CREATE OR REPLACE FUNCTION fn_customer_row_security(p_schema VARCHAR2, p_objname VARCHAR2) RETURN VARCHAR2 IS
v_account_id NUMBER;
v_user_type VARCHAR2(20);
BEGIN
-- 获取当前会话用户信息
SELECT account_id, account_type INTO v_account_id, v_user_type
FROM security_accounts
WHERE account_name = SYS_CONTEXT('USERENV', 'SESSION_USER');
-- 管理员和客户服务人员可查看所有客户
IF v_user_type IN ('Admin', 'Service') THEN
RETURN '1=1';
END IF;
-- 普通客户只能查看自己的信息
RETURN 'customer_id = (SELECT customer_id FROM security_accounts WHERE account_id = ' || v_account_id || ')';
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN '1=0'; -- 无权限访问
END;
/
-- 谓词函数2: 订单数据行级访问控制
CREATE OR REPLACE FUNCTION fn_order_row_security(p_schema VARCHAR2, p_objname VARCHAR2) RETURN VARCHAR2 IS
v_account_id NUMBER;
v_user_type VARCHAR2(20);
BEGIN
SELECT account_id, account_type INTO v_account_id, v_user_type
FROM security_accounts
WHERE account_name = SYS_CONTEXT('USERENV', 'SESSION_USER');
-- 管理员和销售人员可查看所有订单
IF v_user_type IN ('Admin', 'Employee') THEN
RETURN '1=1';
END IF;
-- 客户只能查看自己的订单
RETURN 'customer_id = (SELECT customer_id FROM security_accounts WHERE account_id = ' || v_account_id || ')';
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN '1=0';
END;
/
-- 谓词函数3: 库存数据行级访问控制
CREATE OR REPLACE FUNCTION fn_inventory_row_security(p_schema VARCHAR2, p_objname VARCHAR2) RETURN VARCHAR2 IS
v_account_id NUMBER;
v_user_type VARCHAR2(20);
BEGIN
SELECT account_id, account_type INTO v_account_id, v_user_type
FROM security_accounts
WHERE account_name = SYS_CONTEXT('USERENV', 'SESSION_USER');
-- 管理员和库存管理员可查看所有库存
IF v_user_type IN ('Admin', 'Employee') THEN
RETURN '1=1';
END IF;
RETURN '1=0'; -- 普通用户不可直接访问库存
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN '1=0';
END;
/
-- 注册行级安全策略
BEGIN
DBMS_RLS.DROP_POLICY(object_schema => USER, object_name => 'JEWELRY_CUSTOMERS', policy_name => 'customer_row_security');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
BEGIN
DBMS_RLS.DROP_POLICY(object_schema => USER, object_name => 'JEWELRY_ORDERS', policy_name => 'order_row_security');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
BEGIN
DBMS_RLS.DROP_POLICY(object_schema => USER, object_name => 'INVENTORY', policy_name => 'inventory_row_security');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
BEGIN
DBMS_RLS.ADD_POLICY(
object_schema => USER,
object_name => 'JEWELRY_CUSTOMERS',
policy_name => 'customer_row_security',
function_schema => USER,
policy_function => 'fn_customer_row_security',
statement_types => 'SELECT, INSERT, UPDATE, DELETE',
update_check => TRUE,
enable => TRUE
);
DBMS_RLS.ADD_POLICY(
object_schema => USER,
object_name => 'JEWELRY_ORDERS',
policy_name => 'order_row_security',
function_schema => USER,
policy_function => 'fn_order_row_security',
statement_types => 'SELECT, INSERT, UPDATE, DELETE',
update_check => TRUE,
enable => TRUE
);
DBMS_RLS.ADD_POLICY(
object_schema => USER,
object_name => 'INVENTORY',
policy_name => 'inventory_row_security',
function_schema => USER,
policy_function => 'fn_inventory_row_security',
statement_types => 'SELECT, INSERT, UPDATE, DELETE',
update_check => TRUE,
enable => TRUE
);
DBMS_OUTPUT.PUT_LINE('RLS policies registered successfully');
END;
/
-- 查看已注册的RLS策略
SELECT policy_name, policy_group, policy_function, enabled
FROM user_policies
ORDER BY object_name, policy_name;
-- ============================================================
-- 5.2 列级安全(脱敏视图 + DENY权限)
-- ============================================================
-- 脱敏视图1: 客户信息脱敏视图(隐藏敏感字段)
CREATE OR REPLACE VIEW v_customer_masked AS
SELECT
customer_id,
customer_code,
first_name,
last_name,
-- 邮箱脱敏
SUBSTR(email, 1, INSTR(email, '@') - 1) || '***@' || SUBSTR(email, INSTR(email, '@') + 1) AS email_masked,
-- 电话脱敏
SUBSTR(phone, 1, 3) || '****' || SUBSTR(phone, -4) AS phone_masked,
gender,
membership_level,
city,
country
FROM jewelry_customers;
-- 脱敏视图2: 订单信息脱敏视图
CREATE OR REPLACE VIEW v_order_masked AS
SELECT
order_id,
order_number,
customer_id,
SUBSTR(customer_code, 1, 4) || '***' AS customer_code_masked,
employee_id,
order_date,
order_status,
subtotal,
tax_amount,
discount_amount,
shipping_cost,
total_amount,
-- 地址脱敏
SUBSTR(shipping_address, 1, 20) || '***' AS address_masked
FROM jewelry_orders;
-- 脱敏视图3: 员工信息脱敏视图
CREATE OR REPLACE VIEW v_employee_masked AS
SELECT
employee_id,
emp_code,
first_name,
last_name,
SUBSTR(email, 1, INSTR(email, '@') - 1) || '***@' || SUBSTR(email, INSTR(email, '@') + 1) AS email_masked,
SUBSTR(phone, 1, 3) || '****' || SUBSTR(phone, -4) AS phone_masked,
department,
position
FROM jewelry_employees;
-- 脱敏视图4: 账户信息脱敏视图
CREATE OR REPLACE VIEW v_account_masked AS
SELECT
account_id,
account_name,
account_type,
SUBSTR(account_email, 1, INSTR(account_email, '@') - 1) || '***@' || SUBSTR(account_email, INSTR(account_email, '@') + 1) AS email_masked,
login_attempts,
is_locked,
last_login,
created_date
FROM security_accounts;
-- 脱敏视图5: 交易信息脱敏视图
CREATE OR REPLACE VIEW v_transaction_masked AS
SELECT
t.transaction_id,
t.product_id,
p.product_name,
t.transaction_type,
t.quantity,
t.performed_by,
t.transaction_date,
-- 敏感参考信息脱敏
CASE WHEN t.reference_type = 'Order' THEN 'ORD-***' || SUBSTR(t.reference_id::text, -3) ELSE t.reference_type END AS reference_masked
FROM inventory_transactions t
JOIN jewelry_products p ON t.product_id = p.product_id;
-- 脱敏视图6: 审计日志脱敏视图
CREATE OR REPLACE VIEW v_audit_masked AS
SELECT
audit_id,
audit_timestamp,
audit_user,
audit_action,
audit_table,
audit_record_id,
-- 值字段脱敏(隐藏密码等敏感信息)
CASE WHEN old_values LIKE '%password%' THEN '***SENSITIVE_DATA_MASKED***' ELSE old_values END AS old_values_masked,
CASE WHEN new_values LIKE '%password%' THEN '***SENSITIVE_DATA_MASKED***' ELSE new_values END AS new_values_masked,
audit_ip,
audit_module,
success_flag
FROM audit_log;
-- 创建只读角色用于访问脱敏视图
-- 注意: 实际执行需要DBA权限
-- GRANT SELECT ON v_customer_masked TO PUBLIC;
-- GRANT SELECT ON v_order_masked TO PUBLIC;
-- GRANT SELECT ON v_employee_masked TO PUBLIC;
-- GRANT SELECT ON v_account_masked TO PUBLIC;
-- GRANT SELECT ON v_transaction_masked TO PUBLIC;
-- GRANT SELECT ON v_audit_masked TO PUBLIC;
-- ============================================================
-- 5.3 列加密(DBMS_CRYPTO AES-256 + 密钥管理)
-- ============================================================
-- 创建加密密钥管理表
CREATE TABLE encryption_keys (
key_id NUMBER(6) CONSTRAINT pk_key PRIMARY KEY,
key_name VARCHAR2(100) CONSTRAINT uk_key_name UNIQUE,
key_version NUMBER(6) DEFAULT 1,
key_material RAW(32) NOT NULL, -- 实际应存储在OS密钥库中
key_algorithm VARCHAR2(30) DEFAULT 'AES_256_CBC',
description VARCHAR2(200),
created_date DATE DEFAULT SYSDATE,
expiry_date DATE,
is_active VARCHAR2(1) DEFAULT 'Y' CONSTRAINT chk_key_active CHECK (is_active IN ('Y','N'))
);
-- 插入主密钥
INSERT INTO encryption_keys (key_id, key_name, key_material, description) VALUES
(1, 'JEWELRY_MASTER_KEY', HEXTORAW('0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF'), 'Master encryption key for jewelry data'),
(2, 'JEWELRY_CUSTOMER_KEY', HEXTORAW('FEDCBA9876543210FEDCBA9876543210FEDCBA9876543210FEDCBA9876543210'), 'Customer sensitive data key'),
(3, 'JEWELRY_FINANCE_KEY', HEXTORAW('1122334455667788112233445566778811223344556677881122334455667788'), 'Financial data encryption key');
-- 创建加密工具包
CREATE OR REPLACE PACKAGE encryption_pkg AS
FUNCTION encrypt_sensitive(p_plaintext VARCHAR2, p_key_name VARCHAR2) RETURN RAW;
FUNCTION decrypt_sensitive(p_ciphertext RAW, p_key_name VARCHAR2) RETURN VARCHAR2;
PROCEDURE rotate_key(p_old_key_name VARCHAR2, p_new_key_name VARCHAR2);
FUNCTION get_active_key(p_key_name VARCHAR2) RETURN RAW;
END encryption_pkg;
/
CREATE OR REPLACE PACKAGE BODY encryption_pkg AS
FUNCTION get_active_key(p_key_name VARCHAR2) RETURN RAW IS
v_key RAW(32);
BEGIN
SELECT key_material INTO v_key FROM encryption_keys
WHERE key_name = p_key_name AND is_active = 'Y' AND (expiry_date IS NULL OR expiry_date > SYSDATE);
RETURN v_key;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20100, 'Active key not found: ' || p_key_name);
END get_active_key;
FUNCTION encrypt_sensitive(p_plaintext VARCHAR2, p_key_name VARCHAR2) RETURN RAW IS
v_key RAW(32);
v_iv RAW(16);
BEGIN
v_key := get_active_key(p_key_name);
-- 生成随机IV
v_iv := DBMS_CRYPTO.RANDOMBYTES(16);
RETURN DBMS_CRYPTO.ENCRYPT(
src => UTL_RAW.CAST_TO_RAW(p_plaintext),
typ => DBMS_CRYPTO.AES_256_CBC,
key => v_key,
iv => v_iv
) || v_iv; -- 将IV附加到密文
END encrypt_sensitive;
FUNCTION decrypt_sensitive(p_ciphertext RAW, p_key_name VARCHAR2) RETURN VARCHAR2 IS
v_key RAW(32);
v_iv RAW(16);
v_ciphertext_part RAW(32767);
BEGIN
v_key := get_active_key(p_key_name);
-- 提取IV(最后16字节)
v_iv := SUBSTRB(p_ciphertext, -15);
v_ciphertext_part := SUBSTRB(p_ciphertext, 1, LENGTHB(p_ciphertext) - 16);
RETURN UTL_RAW.CAST_TO_VARCHAR2(
DBMS_CRYPTO.DECRYPT(
src => v_ciphertext_part,
typ => DBMS_CRYPTO.AES_256_CBC,
key => v_key,
iv => v_iv
)
);
END decrypt_sensitive;
PROCEDURE rotate_key(p_old_key_name VARCHAR2, p_new_key_name VARCHAR2) IS
v_old_key RAW(32);
v_new_key RAW(32);
CURSOR c_sensitive_data IS
SELECT key_id, key_name, key_material FROM encryption_keys WHERE is_active = 'Y';
BEGIN
v_old_key := get_active_key(p_old_key_name);
v_new_key := get_active_key(p_new_key_name);
-- 更新密钥材料(实际场景中应重新加密所有数据)
DBMS_OUTPUT.PUT_LINE('Key rotation initiated: ' || p_old_key_name || ' -> ' || p_new_key_name);
security_pkg.write_audit_log(
p_action => 'KEY_ROTATION',
p_table => 'ENCRYPTION_KEYS',
p_record_id => NULL,
p_old_values => 'old_key=' || p_old_key_name,
p_new_values => 'new_key=' || p_new_key_name,
p_success => 'Y'
);
END rotate_key;
END encryption_pkg;
/
-- 测试加密功能
DECLARE
v_encrypted RAW(32767);
v_decrypted VARCHAR2(200);
BEGIN
v_encrypted := encryption_pkg.encrypt_sensitive('Sensitive Customer Data', 'JEWELRY_CUSTOMER_KEY');
DBMS_OUTPUT.PUT_LINE('Encryption Test PASS: Encrypted data = ' || SUBSTR(UTL_RAW.CAST_TO_VARCHAR2(v_encrypted), 1, 50));
v_decrypted := encryption_pkg.decrypt_sensitive(v_encrypted, 'JEWELRY_CUSTOMER_KEY');
DBMS_OUTPUT.PUT_LINE('Decryption Test PASS: Decrypted data = ' || v_decrypted);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Encryption Test INFO: ' || SQLERRM || ' (DBMS_CRYPTO may require proper setup)');
END;
/
-- ============================================================
-- 5.4 哈希函数(密码强度验证/SHA256/SHA512)
-- ============================================================
-- 创建密码策略表
CREATE TABLE password_policy (
policy_id NUMBER(6) CONSTRAINT pk_pp PRIMARY KEY,
min_length NUMBER(3) DEFAULT 8,
max_length NUMBER(3) DEFAULT 128,
require_upper VARCHAR2(1) DEFAULT 'Y' CONSTRAINT chk_pp CHECK (require_upper IN ('Y','N')),
require_lower VARCHAR2(1) DEFAULT 'Y' CONSTRAINT chk_pp CHECK (require_lower IN ('Y','N')),
require_number VARCHAR2(1) DEFAULT 'Y' CONSTRAINT chk_pp CHECK (require_number IN ('Y','N')),
require_special VARCHAR2(1) DEFAULT 'Y' CONSTRAINT chk_pp CHECK (require_special IN ('Y','N')),
min_unique_chars NUMBER(3) DEFAULT 4,
max_age_days NUMBER(4),
history_count NUMBER(3) DEFAULT 12
);
-- 插入默认密码策略
INSERT INTO password_policy (policy_id, min_length, max_length, require_upper, require_lower, require_number, require_special) VALUES
(1, 8, 128, 'Y', 'Y', 'Y', 'Y');
-- 密码哈希函数包
CREATE OR REPLACE PACKAGE hash_pkg AS
FUNCTION generate_salt RETURN VARCHAR2;
FUNCTION hash_password(p_password VARCHAR2, p_salt VARCHAR2) RETURN VARCHAR2;
FUNCTION hash_sha256(p_input VARCHAR2) RETURN VARCHAR2;
FUNCTION hash_sha512(p_input VARCHAR2) RETURN VARCHAR2;
FUNCTION verify_password(p_password VARCHAR2, p_hash VARCHAR2, p_salt VARCHAR2) RETURN BOOLEAN;
FUNCTION check_password_strength(p_password VARCHAR2) RETURN VARCHAR2;
END hash_pkg;
/
CREATE OR REPLACE PACKAGE BODY hash_pkg AS
FUNCTION generate_salt RETURN VARCHAR2 IS
BEGIN
RETURN RAWTOHEX(DBMS_CRYPTO.RANDOMBYTES(16));
END generate_salt;
FUNCTION hash_password(p_password VARCHAR2, p_salt VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN DBMS_CRYPTO.HASH(
src => UTL_RAW.CAST_TO_RAW(p_password || p_salt),
typ => DBMS_CRYPTO.HASH_SHA256
);
END hash_password;
FUNCTION hash_sha256(p_input VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN RAWTOHEX(DBMS_CRYPTO.HASH(
src => UTL_RAW.CAST_TO_RAW(p_input),
typ => DBMS_CRYPTO.HASH_SHA256
));
END hash_sha256;
FUNCTION hash_sha512(p_input VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN RAWTOHEX(DBMS_CRYPTO.HASH(
src => UTL_RAW.CAST_TO_RAW(p_input),
typ => DBMS_CRYPTO.HASH_SHA512
));
END hash_sha512;
FUNCTION verify_password(p_password VARCHAR2, p_hash VARCHAR2, p_salt VARCHAR2) RETURN BOOLEAN IS
v_computed_hash RAW(32);
BEGIN
v_computed_hash := DBMS_CRYPTO.HASH(
src => UTL_RAW.CAST_TO_RAW(p_password || p_salt),
typ => DBMS_CRYPTO.HASH_SHA256
);
RETURN v_computed_hash = HEXTORAW(p_hash);
EXCEPTION WHEN OTHERS THEN RETURN FALSE;
END verify_password;
FUNCTION check_password_strength(p_password VARCHAR2) RETURN VARCHAR2 IS
BEGIN
IF LENGTH(p_password) < 8 THEN RETURN 'TOO_SHORT';
ELSIF LENGTH(p_password) > 128 THEN RETURN 'TOO_LONG';
ELSIF NOT REGEXP_LIKE(p_password, '[A-Z]') THEN RETURN 'NO_UPPERCASE';
ELSIF NOT REGEXP_LIKE(p_password, '[a-z]') THEN RETURN 'NO_LOWERCASE';
ELSIF NOT REGEXP_LIKE(p_password, '[0-9]') THEN RETURN 'NO_NUMBER';
ELSIF NOT REGEXP_LIKE(p_password, '[^A-Za-z0-9]') THEN RETURN 'NO_SPECIAL_CHAR';
END IF;
RETURN 'STRONG';
END check_password_strength;
END hash_pkg;
/
-- 测试哈希函数
DECLARE
v_salt VARCHAR2(128);
v_hash VARCHAR2(512);
v_sha256 VARCHAR2(64);
v_sha512 VARCHAR2(128);
BEGIN
v_salt := hash_pkg.generate_salt;
DBMS_OUTPUT.PUT_LINE('Salt generated: ' || SUBSTR(v_salt, 1, 32) || '...');
v_hash := hash_pkg.hash_password('MySecurePassword123!', v_salt);
DBMS_OUTPUT.PUT_LINE('Password hash (SHA256): ' || SUBSTR(v_hash, 1, 64));
v_sha256 := hash_pkg.hash_sha256('Test String');
DBMS_OUTPUT.PUT_LINE('SHA256 hash: ' || v_sha256);
v_sha512 := hash_pkg.hash_sha512('Test String');
DBMS_OUTPUT.PUT_LINE('SHA512 hash: ' || v_sha512);
DBMS_OUTPUT.PUT_LINE('Password strength: ' || hash_pkg.check_password_strength('MySecurePassword123!'));
DBMS_OUTPUT.PUT_LINE('Weak password strength: ' || hash_pkg.check_password_strength('12345'));
END;
/
-- ============================================================
-- 5.5 SQL注入检测函数(多层次模式匹配)
-- ============================================================
-- 创建注入检测规则表
CREATE TABLE injection_rules (
rule_id NUMBER(6) CONSTRAINT pk_ir PRIMARY KEY,
rule_name VARCHAR2(100),
rule_pattern VARCHAR2(500),
attack_type VARCHAR2(30) CONSTRAINT chk_ir_type CHECK (attack_type IN ('SQL_Injection','XSS','Path_Traversal','Command_Injection','Header_Injection','Invalid_Input','Code_Injection','LDAP_Injection')),
risk_level VARCHAR2(20) DEFAULT 'Medium' CONSTRAINT chk_ir_risk CHECK (risk_level IN ('Low','Medium','High','Critical')),
action VARCHAR2(20) DEFAULT 'BLOCK' CONSTRAINT chk_ir_action CHECK (action IN ('LOG','WARN','BLOCK','QUARANTINE')),
description VARCHAR2(200),
is_active VARCHAR2(1) DEFAULT 'Y' CONSTRAINT chk_ir_active CHECK (is_active IN ('Y','N'))
);
-- 插入检测规则
INSERT INTO injection_rules (rule_id, rule_name, rule_pattern, attack_type, risk_level, action, description) VALUES
(1, 'SQL_UNION_SELECT', '%UNION%SELECT%', 'SQL_Injection', 'Critical', 'BLOCK', 'UNION SELECT injection attempt'),
(2, 'SQL_DROP_TABLE', '%DROP%TABLE%', 'SQL_Injection', 'Critical', 'BLOCK', 'DROP TABLE attempt'),
(3, 'SQL_OR_1_1', '%OR%1=1%', 'SQL_Injection', 'High', 'BLOCK', 'OR 1=1 tautology'),
(4, 'SQL_EXEC_XP', '%EXEC%xp%', 'SQL_Injection', 'Critical', 'BLOCK', 'Extended stored procedure execution'),
(5, 'SQL_WAITFOR_DELAY', '%WAITFOR%DELAY%', 'SQL_Injection', 'High', 'BLOCK', 'Time-based blind injection'),
(6, 'SQL_BENCHMARK', '%BENCHMARK%', 'SQL_Injection', 'High', 'BLOCK', 'MySQL benchmark injection'),
(7, 'SQL_SLEEP', '%SLEEP%', 'SQL_Injection', 'High', 'BLOCK', 'Time-based blind injection'),
(8, 'SQL_LOAD_FILE', '%LOAD_FILE%', 'SQL_Injection', 'Critical', 'BLOCK', 'File read attempt'),
(9, 'SQL_INFO_SCHEMA', '%information_schema%', 'SQL_Injection', 'High', 'BLOCK', 'Schema enumeration'),
(10, 'SQL_COMMENT', '%--%', 'SQL_Injection', 'Medium', 'WARN', 'SQL comment injection'),
(11, 'XSS_SCRIPT_TAG', '%<script%', 'XSS', 'High', 'BLOCK', 'XSS script injection'),
(12, 'XSS_ONCLICK', '%onclick%', 'XSS', 'Medium', 'WARN', 'XSS event handler'),
(13, 'PATH_TRAVERSAL', '%..%/%..%/', 'Path_Traversal', 'High', 'BLOCK', 'Directory traversal attempt'),
(14, 'CMD_INJECTION', '%;%', 'Command_Injection', 'High', 'BLOCK', 'Command injection semicolon'),
(15, 'SQL_HEX_ENCODE', '%0x%', 'SQL_Injection', 'Medium', 'WARN', 'Hex-encoded injection');
-- SQL注入检测函数
CREATE OR REPLACE FUNCTION fn_detect_sql_injection(p_input CLOB, p_source_ip VARCHAR2 DEFAULT NULL) RETURN VARCHAR2 IS
v_pattern VARCHAR2(500);
v_rule_count NUMBER;
v_risk_level VARCHAR2(20);
BEGIN
IF p_input IS NULL THEN
RETURN 'SAFE';
END IF;
-- 多层次检测
-- 层1: 已知攻击模式匹配
v_pattern := '''%''=''%' || '%UNION%SELECT%' || '%DROP%TABLE%' || '%INSERT%INTO%' || '%DELETE%FROM%' ||
'%UPDATE%SET%' || '%EXEC%xp%' || '%1=1%' || '%OR%1=1%' || '%WAITFOR%DELAY%' ||
'%BENCHMARK%' || '%SLEEP%' || '%LOAD_FILE%' || '%INTO%OUTFILE%' || '%information_schema%' ||
'%sys.tables%' || '%CONCAT%' || '%CHAR%' || '%0x%' || '%--%' || '%;%' || '%<script%' || '%onclick%';
IF UPPER(p_input) LIKE UPPER(v_pattern) THEN
-- 记录注入尝试
BEGIN
INSERT INTO sql_injection_logs (log_id, source_ip, attack_pattern, attack_type, payload, risk_level, blocked, detected_by)
VALUES (seq_sqli.NEXTVAL, p_source_ip, 'Pattern Match', 'SQL_Injection', SUBSTR(p_input, 1, 4000), 'High', 'Y', 'FN_DETECT_SQL_INJECTION');
COMMIT;
EXCEPTION WHEN OTHERS THEN NULL;
END;
RETURN 'POTENTIAL_SQL_INJECTION';
END IF;
-- 层2: 特殊字符密度检测
DECLARE
v_special_count NUMBER;
v_input_len NUMBER;
BEGIN
v_input_len := LENGTH(p_input);
IF v_input_len > 0 THEN
v_special_count := REGEXP_COUNT(p_input, '[^A-Za-z0-9\u4e00-\u9fff .,;:!?\-_=+@#%^&*()[]{}|\\"''<>/?]');
IF v_special_count > v_input_len * 0.3 THEN
RETURN 'HIGH_SPECIAL_CHAR_RATIO';
END IF;
END IF;
END;
-- 层3: SQL关键字密度检测
DECLARE
v_sql_count NUMBER;
BEGIN
v_sql_count := REGEXP_COUNT(UPPER(p_input), '(SELECT|INSERT|UPDATE|DELETE|DROP|CREATE|ALTER|EXEC|UNION|WHERE|FROM|INTO)');
IF v_sql_count > 5 THEN
RETURN 'HIGH_SQL_KEYWORD_DENSITY';
END IF;
END;
RETURN 'SAFE';
END fn_detect_sql_injection;
/
-- ============================================================
-- 5.6 参数化查询防注入存储过程
-- ============================================================
-- 安全查询构建器
CREATE OR REPLACE PACKAGE secure_query_pkg AS
FUNCTION build_safe_like(p_pattern VARCHAR2) RETURN VARCHAR2;
FUNCTION escape_single_quote(p_input VARCHAR2) RETURN VARCHAR2;
FUNCTION validate_numeric(p_input VARCHAR2, p_min NUMBER, p_max NUMBER) RETURN NUMBER;
FUNCTION validate_date(p_input VARCHAR2) RETURN DATE;
PROCEDURE search_products(
p_category VARCHAR2 DEFAULT NULL,
p_material VARCHAR2 DEFAULT NULL,
p_min_price NUMBER DEFAULT NULL,
p_max_price NUMBER DEFAULT NULL,
p_search_text VARCHAR2 DEFAULT NULL,
p_result_set OUT SYS_REFCURSOR
);
PROCEDURE get_customer_orders(
p_customer_email VARCHAR2,
p_order_status VARCHAR2 DEFAULT NULL,
p_result_set OUT SYS_REFCURSOR
);
END secure_query_pkg;
/
CREATE OR REPLACE PACKAGE BODY secure_query_pkg AS
FUNCTION build_safe_like(p_pattern VARCHAR2) RETURN VARCHAR2 IS
BEGIN
-- 转义LIKE通配符
RETURN REPLACE(REPLACE(p_pattern, '%', '\%'), '_', '\_');
END build_safe_like;
FUNCTION escape_single_quote(p_input VARCHAR2) RETURN VARCHAR2 IS
BEGIN
-- 转义单引号防注入
RETURN REPLACE(p_input, '''', '''''');
END escape_single_quote;
FUNCTION validate_numeric(p_input VARCHAR2, p_min NUMBER, p_max NUMBER) RETURN NUMBER IS
v_num NUMBER;
BEGIN
v_num := TO_NUMBER(p_input);
IF v_num < p_min OR v_num > p_max THEN
RAISE_APPLICATION_ERROR(-20200, 'Numeric value out of range: ' || p_min || ' to ' || p_max);
END IF;
RETURN v_num;
EXCEPTION
WHEN INVALID_NUMBER THEN
RAISE_APPLICATION_ERROR(-20201, 'Invalid numeric input');
END validate_numeric;
FUNCTION validate_date(p_input VARCHAR2) RETURN DATE IS
BEGIN
RETURN TO_DATE(p_input, 'YYYY-MM-DD');
EXCEPTION
WHEN INVALID_DATE THEN
RAISE_APPLICATION_ERROR(-20202, 'Invalid date format. Use YYYY-MM-DD');
END validate_date;
PROCEDURE search_products(
p_category VARCHAR2 DEFAULT NULL,
p_material VARCHAR2 DEFAULT NULL,
p_min_price NUMBER DEFAULT NULL,
p_max_price NUMBER DEFAULT NULL,
p_search_text VARCHAR2 DEFAULT NULL,
p_result_set OUT SYS_REFCURSOR
) IS
v_sql VARCHAR2(4000);
v_where VARCHAR2(2000) := 'WHERE 1=1';
BEGIN
v_sql := 'SELECT product_id, product_name, category_name, material, price, stock_quantity FROM v_product_details ';
IF p_category IS NOT NULL THEN
v_where := v_where || ' AND category_name = :1';
END IF;
IF p_material IS NOT NULL THEN
v_where := v_where || ' AND material = :2';
END IF;
IF p_min_price IS NOT NULL THEN
v_where := v_where || ' AND price >= :3';
END IF;
IF p_max_price IS NOT NULL THEN
v_where := v_where || ' AND price <= :4';
END IF;
IF p_search_text IS NOT NULL THEN
v_where := v_where || ' AND (product_name LIKE :5 OR description LIKE :6)';
END IF;
v_sql := v_sql || v_where || ' ORDER BY product_name';
OPEN p_result_set FOR v_sql USING
p_category, p_material, p_min_price, p_max_price,
'%' || p_search_text || '%', '%' || p_search_text || '%';
END search_products;
PROCEDURE get_customer_orders(
p_customer_email VARCHAR2,
p_order_status VARCHAR2 DEFAULT NULL,
p_result_set OUT SYS_REFCURSOR
) IS
v_sql VARCHAR2(4000);
v_where VARCHAR2(2000) := 'WHERE 1=1';
BEGIN
v_sql := 'SELECT o.order_id, o.order_number, o.order_date, o.order_status, o.total_amount, c.customer_name FROM jewelry_orders o JOIN jewelry_customers c ON o.customer_id = c.customer_id ';
v_where := v_where || ' AND c.email = :1';
IF p_order_status IS NOT NULL THEN
v_where := v_where || ' AND o.order_status = :2';
END IF;
v_sql := v_sql || v_where || ' ORDER BY o.order_date DESC';
OPEN p_result_set FOR v_sql USING p_customer_email, p_order_status;
END get_customer_orders;
END secure_query_pkg;
/
-- ============================================================
-- 5.7 安全存储过程(5个核心安全存储过程)
-- ============================================================
-- 存储过程1: 安全用户认证
CREATE OR REPLACE PROCEDURE sp_secure_login(
p_account_name VARCHAR2,
p_password VARCHAR2,
p_result OUT VARCHAR2
) IS
v_account_id NUMBER;
v_password_hash VARCHAR2(512);
v_password_salt VARCHAR2(128);
v_login_attempts NUMBER;
v_is_locked VARCHAR2(1);
v_hash RAW(32);
BEGIN
-- 参数验证
IF p_account_name IS NULL OR p_password IS NULL THEN
p_result := 'ERROR: Missing credentials';
RETURN;
END IF;
-- 检查账户状态
SELECT account_id, password_hash, password_salt, login_attempts, is_locked
INTO v_account_id, v_password_hash, v_password_salt, v_login_attempts, v_is_locked
FROM security_accounts WHERE account_name = p_account_name;
-- 检查账户锁定
IF v_is_locked = 'Y' THEN
p_result := 'ERROR: Account is locked';
RETURN;
END IF;
-- 验证密码
v_hash := DBMS_CRYPTO.HASH(
src => UTL_RAW.CAST_TO_RAW(p_password || v_password_salt),
typ => DBMS_CRYPTO.HASH_SHA256
);
IF v_hash != HEXTORAW(v_password_hash) THEN
-- 登录失败
UPDATE security_accounts SET login_attempts = login_attempts + 1 WHERE account_id = v_account_id;
p_result := 'ERROR: Invalid credentials';
RETURN;
END IF;
-- 登录成功
UPDATE security_accounts SET login_attempts = 0, last_login = SYSDATE WHERE account_id = v_account_id;
security_pkg.write_audit_log(
p_action => 'LOGIN_SUCCESS',
p_table => 'SECURITY_ACCOUNTS',
p_record_id => v_account_id,
p_old_values => 'account=' || p_account_name,
p_new_values => 'login_success=TRUE',
p_success => 'Y'
);
p_result := 'SUCCESS';
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_result := 'ERROR: Account not found';
WHEN OTHERS THEN
p_result := 'ERROR: ' || SQLERRM;
END sp_secure_login;
/
-- 存储过程2: 安全创建用户
CREATE OR REPLACE PROCEDURE sp_create_user(
p_account_name VARCHAR2,
p_account_type VARCHAR2,
p_email VARCHAR2,
p_password VARCHAR2,
p_customer_id NUMBER DEFAULT NULL,
p_employee_id NUMBER DEFAULT NULL,
p_result OUT VARCHAR2
) IS
v_salt VARCHAR2(128);
v_password_hash VARCHAR2(512);
v_strength VARCHAR2(20);
BEGIN
-- 密码强度验证
v_strength := security_pkg.verify_password_strength(p_password);
IF v_strength != 'STRONG' THEN
p_result := 'ERROR: Password does not meet strength requirements (' || v_strength || ')';
RETURN;
END IF;
-- 生成盐值并哈希密码
v_salt := hash_pkg.generate_salt;
v_password_hash := hash_pkg.hash_password(p_password, v_salt);
-- 插入账户
INSERT INTO security_accounts (account_name, account_type, account_email, customer_id, employee_id, password_hash, password_salt)
VALUES (p_account_name, p_account_type, p_email, p_customer_id, p_employee_id, v_password_hash, v_salt);
security_pkg.write_audit_log(
p_action => 'USER_CREATE',
p_table => 'SECURITY_ACCOUNTS',
p_record_id => seq_account.CURRVAL,
p_old_values => NULL,
p_new_values => 'account=' || p_account_name || ',type=' || p_account_type,
p_success => 'Y'
);
p_result := 'SUCCESS: User created';
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
p_result := 'ERROR: Account name already exists';
WHEN OTHERS THEN
p_result := 'ERROR: ' || SQLERRM;
END sp_create_user;
/
-- 存储过程3: 安全删除数据(软删除)
CREATE OR REPLACE PROCEDURE sp_safe_delete(
p_table_name VARCHAR2,
p_record_id NUMBER,
p_deleted_by VARCHAR2,
p_result OUT VARCHAR2
) IS
PRAGMA AUTONOMOUS_TRANSACTION;
v_table_name VARCHAR2(100) := UPPER(p_table_name);
BEGIN
-- 安全检查: 阻止删除敏感表
IF v_table_name IN ('AUDIT_LOG', 'SQL_INJECTION_LOGS', 'SECURITY_ACCOUNTS', 'SECURITY_ROLES', 'SECURITY_PERMISSIONS') THEN
p_result := 'ERROR: Cannot delete from security-critical table';
RETURN;
END IF;
-- 执行软删除(如果表有is_active列)
BEGIN
EXECUTE IMMEDIATE 'UPDATE ' || v_table_name || ' SET is_active = ''N'', updated_date = SYSDATE WHERE ' ||
CASE v_table_name
WHEN 'JEWELRY_PRODUCTS' THEN 'product_id = :1'
WHEN 'JEWELRY_CUSTOMERS' THEN 'customer_id = :1'
WHEN 'JEWELRY_ORDERS' THEN 'order_id = :1'
WHEN 'SECURITY_ACCOUNTS' THEN 'account_id = :1'
ELSE 'ROWID = :1'
END USING p_record_id;
IF SQL%ROWCOUNT > 0 THEN
security_pkg.write_audit_log(
p_action => 'SOFT_DELETE',
p_table => v_table_name,
p_record_id => p_record_id,
p_old_values => 'deleted_by=' || p_deleted_by,
p_new_values => 'soft_delete=TRUE',
p_success => 'Y'
);
p_result := 'SUCCESS: Record soft deleted';
ELSE
p_result := 'ERROR: Record not found';
END IF;
EXCEPTION
WHEN OTHERS THEN
p_result := 'ERROR: ' || SQLERRM;
END;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
p_result := 'ERROR: ' || SQLERRM;
END sp_safe_delete;
/
-- 存储过程4: 安全导出审计报表
CREATE OR REPLACE PROCEDURE sp_generate_audit_report(
p_start_date DATE,
p_end_date DATE,
p_table_filter VARCHAR2 DEFAULT NULL,
p_result_cursor OUT SYS_REFCURSOR
) IS
BEGIN
OPEN p_result_cursor FOR
SELECT audit_id, audit_timestamp, audit_user, audit_action, audit_table, audit_record_id,
audit_ip, audit_module, success_flag
FROM audit_log
WHERE audit_timestamp BETWEEN p_start_date AND p_end_date
AND (p_table_filter IS NULL OR audit_table = p_table_filter)
ORDER BY audit_timestamp DESC;
END sp_generate_audit_report;
/
-- 存储过程5: 安全备份配置
CREATE OR REPLACE PROCEDURE sp_backup_config(
p_backup_type VARCHAR2,
p_retention_days NUMBER,
p_compress VARCHAR2 DEFAULT 'Y',
p_result OUT VARCHAR2
) IS
BEGIN
-- 验证备份类型
IF p_backup_type NOT IN ('FULL', 'INCREMENTAL', 'DIFFERENTIAL') THEN
p_result := 'ERROR: Invalid backup type';
RETURN;
END IF;
-- 验证保留天数
IF p_retention_days < 1 OR p_retention_days > 3650 THEN
p_result := 'ERROR: Retention days must be between 1 and 3650';
RETURN;
END IF;
-- 记录备份操作
security_pkg.write_audit_log(
p_action => 'BACKUP_CONFIG',
p_table => 'SECURITY_CONFIG',
p_record_id => NULL,
p_old_values => 'type=' || p_backup_type || ',retention=' || p_retention_days,
p_new_values => 'backup_configured=TRUE,type=' || p_backup_type || ',retention=' || p_retention_days || ',compress=' || p_compress,
p_success => 'Y'
);
p_result := 'SUCCESS: Backup configuration updated';
END sp_backup_config;
/
-- ============================================================
-- 5.8 安全监控视图(3个)
-- ============================================================
-- 安全监控视图1: 实时安全事件监控
CREATE OR REPLACE VIEW v_security_events AS
SELECT
'AUDIT' AS event_source,
audit_id AS event_id,
audit_timestamp AS event_time,
audit_user AS event_user,
audit_action AS event_type,
audit_table AS event_object,
audit_ip AS source_ip,
success_flag AS event_status
FROM audit_log
WHERE audit_timestamp >= SYSDATE - 1
UNION ALL
SELECT
'INJECTION' AS event_source,
log_id AS event_id,
created_date AS event_time,
detected_by AS event_user,
attack_type AS event_type,
request_url AS event_object,
source_ip AS source_ip,
blocked AS event_status
FROM sql_injection_logs
WHERE created_date >= SYSDATE - 1
ORDER BY event_time DESC;
-- 安全监控视图2: 账户健康监控
CREATE OR REPLACE VIEW v_account_health AS
SELECT
account_id,
account_name,
account_type,
login_attempts,
is_locked,
last_login,
CASE
WHEN is_locked = 'Y' THEN 'LOCKED'
WHEN login_attempts >= 3 THEN 'WARNING'
WHEN last_login < SYSDATE - 90 THEN 'INACTIVE'
ELSE 'HEALTHY'
END AS account_health_status
FROM security_accounts;
-- 安全监控视图3: 数据访问审计
CREATE OR REPLACE VIEW v_data_access_audit AS
SELECT
audit_user,
audit_table,
audit_action,
COUNT(*) AS access_count,
MAX(audit_timestamp) AS last_access,
COUNT(DISTINCT audit_ip) AS unique_ips
FROM audit_log
WHERE audit_timestamp >= SYSDATE - 30
GROUP BY audit_user, audit_table, audit_action
ORDER BY access_count DESC;
-- ============================================================
-- 验证安全对象状态
-- ============================================================
SELECT object_name, object_type, status
FROM user_objects
WHERE owner = USER AND object_type IN ('FUNCTION', 'PROCEDURE', 'PACKAGE', 'PACKAGE BODY', 'VIEW')
ORDER BY object_type, object_name;
-- 查看RLS策略
SELECT policy_name, policy_group, policy_function, enabled
FROM user_policies;
-- ============================================================
-- 全部脚本执行完成
-- 验证所有对象状态:
-- ============================================================
SELECT object_name, object_type, status FROM user_objects
WHERE owner = USER AND object_type IN ('TABLE','VIEW','SEQUENCE','SYNONYM','TRIGGER','PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TYPE')
ORDER BY object_type, object_name;
-- 验证表数据量:
SELECT 'jewelry_categories' AS tbl, COUNT(*) AS cnt FROM jewelry_categories
UNION ALL SELECT 'jewelry_products', COUNT(*) FROM jewelry_products
UNION ALL SELECT 'jewelry_customers', COUNT(*) FROM jewelry_customers
UNION ALL SELECT 'jewelry_employees', COUNT(*) FROM jewelry_employees
UNION ALL SELECT 'security_accounts', COUNT(*) FROM security_accounts
UNION ALL SELECT 'security_roles', COUNT(*) FROM security_roles
UNION ALL SELECT 'security_permissions', COUNT(*) FROM security_permissions
UNION ALL SELECT 'jewelry_orders', COUNT(*) FROM jewelry_orders
UNION ALL SELECT 'order_items', COUNT(*) FROM order_items
UNION ALL SELECT 'inventory', COUNT(*) FROM inventory
UNION ALL SELECT 'inventory_transactions', COUNT(*) FROM inventory_transactions
UNION ALL SELECT 'product_reviews', COUNT(*) FROM product_reviews
UNION ALL SELECT 'security_config', COUNT(*) FROM security_config
UNION ALL SELECT 'sql_injection_logs', COUNT(*) FROM sql_injection_logs
UNION ALL SELECT 'audit_log', COUNT(*) FROM audit_log
UNION ALL SELECT 'ddl_audit_log', COUNT(*) FROM ddl_audit_log
UNION ALL SELECT 'inventory_alerts', COUNT(*) FROM inventory_alerts
UNION ALL SELECT 'audit_log_archive', COUNT(*) FROM audit_log_archive
UNION ALL SELECT 'encryption_keys', COUNT(*) FROM encryption_keys
UNION ALL SELECT 'password_policy', COUNT(*) FROM password_policy
UNION ALL SELECT 'injection_rules', COUNT(*) FROM injection_rules
ORDER BY tbl;
-- ============================================================
-- 执行完成
-- ============================================================
哲学管理(学)人生, 文学艺术生活, 自动(计算机学)物理(学)工作, 生物(学)化学逆境, 历史(学)测绘(学)时间, 经济(学)数学金钱(理财), 心理(学)医学情绪, 诗词美容情感, 美学建筑(学)家园, 解构建构(分析)整合学习, 智商情商(IQ、EQ)运筹(学)生存.---Geovin Du(涂聚文）
浙公网安备 33010602011771号