-- 連接到數(shù)據(jù)庫(kù)
sqlplus sys as sysdba

-- 切換到HR模式
ALTER SESSION SET CURRENT_SCHEMA = hr;

-- 創(chuàng)建策略函數(shù)
CREATE OR REPLACE FUNCTION emp_dept_policy (schema_name IN VARCHAR2, object_name IN VARCHAR2)
RETURN VARCHAR2
AS
  v_predicate VARCHAR2(4000);
BEGIN
  -- 獲取當(dāng)前用戶(hù)的部門(mén)ID
  v_predicate := 'department_id = (SELECT department_id FROM users WHERE username = USER)';
  RETURN v_predicate;
END;
/

-- 使用DBMS_RLS包創(chuàng)建并應(yīng)用策略
BEGIN
  DBMS_RLS.ADD_POLICY (
    object_schema => 'hr',
    object_name   => 'employees',
    policy_name   => 'emp_dept_policy',
    function_schema => 'hr',
    policy_function => 'emp_dept_policy',
    statement_types => 'SELECT, INSERT, UPDATE, DELETE'
  );
END;
/

-- 創(chuàng)建示例用戶(hù)并授予權(quán)限
CREATE USER user1 IDENTIFIED BY password;
CREATE USER user2 IDENTIFIED BY password;

GRANT CONNECT TO user1;
GRANT CONNECT TO user2;

GRANT SELECT, INSERT, UPDATE, DELETE ON hr.employees TO user1;
GRANT SELECT, INSERT, UPDATE, DELETE ON hr.employees TO user2;

-- 插入一些示例數(shù)據(jù)
INSERT INTO hr.employees (employee_id, name, department_id, salary) VALUES (1, 'Alice', 10, 50000);
INSERT INTO hr.employees (employee_id, name, department_id, salary) VALUES (2, 'Bob', 20, 60000);
INSERT INTO hr.employees (employee_id, name, department_id, salary) VALUES (3, 'Charlie', 10, 55000);

INSERT INTO hr.users (username, department_id) VALUES ('USER1', 10);
INSERT INTO hr.users (username, department_id) VALUES ('USER2', 20);

COMMIT;

-- 以u(píng)ser1身份連接數(shù)據(jù)庫(kù)并查詢(xún)employees表
sqlplus user1/password@database
SELECT * FROM hr.employees;

-- 以u(píng)ser2身份連接數(shù)據(jù)庫(kù)并查詢(xún)employees表
sqlplus user2/password@database
SELECT * FROM hr.employees;

-- 連接到數(shù)據(jù)庫(kù)
sqlplus sys as sysdba

-- 切換到HR模式
ALTER SESSION SET CURRENT_SCHEMA = hr;

-- 創(chuàng)建示例表和用戶(hù)表
CREATE TABLE hr.employees (
  employee_id NUMBER PRIMARY KEY,
  name VARCHAR2(100),
  department_id NUMBER,
  salary NUMBER
);

CREATE TABLE hr.users (
  username VARCHAR2(30) PRIMARY KEY,
  department_id NUMBER
);

-- 插入一些示例數(shù)據(jù)
INSERT INTO hr.employees (employee_id, name, department_id, salary) VALUES (1, 'Alice', 10, 50000);
INSERT INTO hr.employees (employee_id, name, department_id, salary) VALUES (2, 'Bob', 20, 60000);
INSERT INTO hr.employees (employee_id, name, department_id, salary) VALUES (3, 'Charlie', 10, 55000);

INSERT INTO hr.users (username, department_id) VALUES ('USER1', 10);
INSERT INTO hr.users (username, department_id) VALUES ('USER2', 20);

COMMIT;

-- 創(chuàng)建策略函數(shù)
CREATE OR REPLACE FUNCTION hr.emp_dept_policy (schema_name IN VARCHAR2, object_name IN VARCHAR2)
RETURN VARCHAR2
AS
  v_predicate VARCHAR2(4000);
BEGIN
  -- 獲取當(dāng)前用戶(hù)的部門(mén)ID
  v_predicate := 'department_id = (SELECT department_id FROM hr.users WHERE username = USER)';
  RETURN v_predicate;
END;
/

-- 使用DBMS_RLS包創(chuàng)建并應(yīng)用策略
BEGIN
  DBMS_RLS.ADD_POLICY (
    object_schema => 'hr',
    object_name   => 'employees',
    policy_name   => 'emp_dept_policy',
    function_schema => 'hr',
    policy_function => 'emp_dept_policy',
    statement_types => 'SELECT, INSERT, UPDATE, DELETE'
  );
END;
/

-- 創(chuàng)建用戶(hù)并授予權(quán)限
CREATE USER user1 IDENTIFIED BY password;
CREATE USER user2 IDENTIFIED BY password;

GRANT CONNECT TO user1;
GRANT CONNECT TO user2;

GRANT SELECT, INSERT, UPDATE, DELETE ON hr.employees TO user1;
GRANT SELECT, INSERT, UPDATE, DELETE ON hr.employees TO user2;

-- 以u(píng)ser1身份連接數(shù)據(jù)庫(kù)并查詢(xún)employees表
sqlplus user1/password@database
SELECT * FROM hr.employees;

-- 以u(píng)ser2身份連接數(shù)據(jù)庫(kù)并查詢(xún)employees表
sqlplus user2/password@database
SELECT * FROM hr.employees;