wordpress显示网站在线人数bt磁力猪
本实验包含了：
- 简单的Oracle Redaction演示
- 针对指定用户的Redaction
实验环境
假设有一个19c多租户数据库，PDB名为orclpdb1。
我们将在orclpdb1中建立2个用户：
- redact_user: redact管理员
- schema_user: schema用户
基础实验
首先进入数据库orclpdb1，创建用户redact_user：
alter session set container=orclpdb1;
create user redact_user identified by oracle;
grant connect, resource, unlimited tablespace to redact_user;
grant select on Sys.redaction_policies to redact_user;
grant select on Sys.redaction_columns to redact_user;
grant execute on dbms_redact to redact_user;
然后再创建一个普通用户schema_user：
alter session set container=orclpdb1;
create user schema_user identified by oracle;
grant connect, resource, unlimited tablespace to schema_user;
以schema_user用户登录，并创建表和插入数据：
connect schema_user/oracle@orclpdb1
CREATE TABLE "EMPLOYEES" ("EMPLOYEE_ID" NUMBER(6,0), "FIRST_NAME" VARCHAR2(20), "LAST_NAME" VARCHAR2(25), "SOCIAL_SECURITY" VARCHAR2(11), "SALARY" NUMBER(4,0));
insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SOCIAL_SECURITY,SALARY) values (100,'Steven','King','247-85-9056',7000);
insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SOCIAL_SECURITY,SALARY) values (101,'Neena','Kochhar','334-08-6578',5000);
commit;
以redact_user登入，定义redact策略：
connect redact_user/oracle@orclpdb1BEGIN
DBMS_REDACT.ADD_POLICY (object_schema => 'SCHEMA_USER',object_name => 'EMPLOYEES',policy_name => 'redact_policy',column_name => 'SOCIAL_SECURITY',function_type => DBMS_REDACT.RANDOM,expression => '1=1',enable => TRUE);
END;
/
注意，此时redact_user对于schema_user中的表是没有读取权限的：
SQL> show user
USER is "REDACT_USER"
SQL> select * from schema_user.employees;
select * from schema_user.employees*
ERROR at line 1:
ORA-00942: table or view does not exist
此时schema_user查看SOCIAL_SECURITY列，策略生效：
SQL> connect schema_user/oracle@orclpdb1
Connected.
SQL> select social_security from employees;SOCIAL_SECU
-----------
z8e.SQ<Y#@m
qP/uDj(&yX7
赋予redact_user对表的读取权限：
grant select on employees to redact_user;
connect redact_user/oracle@orclpdb1SQL> select social_security from schema_user.employees;SOCIAL_SECU
-----------
Q*NCEmtLY2V
E,8FG0#gM4@
可以看到，目前redact策略对redact_user也是生效的。
扩展实验
在此实验中，我们将实现选择性的redaction。即redact policy仅对schema_user生效。
这时通过redact expresion实现的。
查看DBMS_REDACT的帮助。其语法为：
DBMS_REDACT.ADD_POLICY (object_schema IN VARCHAR2 := NULL,object_name IN VARCHAR2,policy_name IN VARCHAR2,column_name IN VARCHAR2 := NULL,function_type IN BINARY_INTEGER := DBMS_REDACT.FULL,function_parameters IN VARCHAR2 := NULL,expression IN VARCHAR2,enable IN BOOLEAN := TRUE,regexp_pattern IN VARCHAR2 := NULL,regexp_replace_string IN VARCHAR2 := NULL,regexp_position IN BINARY_INTEGER := 1,regexp_occurrence IN BINARY_INTEGER := 0,regexp_match_parameter IN VARCHAR2 := NULL,policy_description IN VARCHAR2 := NULL,column_description IN VARCHAR2 := NULL);
其expression参数的作用为：
Default boolean expression for the table or view. If this expression is used, then redaction takes place only if this policy expression evaluates to TRUE.
现在要做的就是修改策略：
connect redact_user/oracle@orclpdb1BEGIN
DBMS_REDACT.ALTER_POLICY (object_schema => 'SCHEMA_USER',object_name => 'EMPLOYEES',policy_name => 'redact_policy',column_name => 'SOCIAL_SECURITY',action => DBMS_REDACT.MODIFY_EXPRESSION,expression => 'SYS_CONTEXT ( ''USERENV'',''SESSION_USER'' ) =''SCHEMA_USER'''
);
END;
/
现在不同的用户查看的结果就不一样了：
SQL> connect redact_user/oracle@orclpdb1
Connected.
SQL> select social_security from schema_user.employees;SOCIAL_SECU
-----------
247-85-9056
334-08-6578SQL> connect schema_user/oracle@orclpdb1
Connected.
SQL> select social_security from schema_user.employees;SOCIAL_SECU
-----------
9NbODS\?AVj
PAOj4FtYXIW
清理
connect redact_user/oracle@orclpdb1BEGIN
DBMS_REDACT.DROP_POLICY (object_schema => 'SCHEMA_USER',object_name => 'EMPLOYEES',policy_name => 'redact_policy');
END;
/alter session set container=orclpdb1;
drop user schema_user cascade;
drop user redact_user cascade;
Redaction 策略是否可以复用
再来回顾下ADD_POLICY过程的参数：
```sql
DBMS_REDACT.ADD_POLICY (object_schema IN VARCHAR2 := NULL,object_name IN VARCHAR2,policy_name IN VARCHAR2,column_name IN VARCHAR2 := NULL,function_type IN BINARY_INTEGER := DBMS_REDACT.FULL,function_parameters IN VARCHAR2 := NULL,expression IN VARCHAR2,enable IN BOOLEAN := TRUE,regexp_pattern IN VARCHAR2 := NULL,regexp_replace_string IN VARCHAR2 := NULL,regexp_position IN BINARY_INTEGER := 1,regexp_occurrence IN BINARY_INTEGER := 0,regexp_match_parameter IN VARCHAR2 := NULL,policy_description IN VARCHAR2 := NULL,column_description IN VARCHAR2 := NULL);
可以看到，由于参数的粒度比较细，唯一可以的是expression，其作用为：
表或视图的默认布尔表达式。 如果使用此表达式，则仅当此策略表达式的计算结果为 TRUE 时才会发生编辑。
如果真的需要有2列，其策略完全一样，基于以上过程再封装也比较简单。
参考
- Redaction Management in Oracle SQL Developer