前段时间做一个系统优化时，发现系统中查询语句中添加了很多对会话的限制，系统的名称是xxxxVPD。
当时以为是开发商的什么安全策略，直到看到了oracle的dbms_rls包，才恍然大悟。原来这也是oracle的安全策略啊。
知识点1：dbms_rls包的使用
rls即row level security，是对语句的额外限制。
该特性从10g开始出现。
脚本：
create or replace function my_name
(
obj_schema varchar2,
obj_name varchar2
)
return varchar2
is
d_my_name varchar2(2000);
BEGIN
d_my_name := 'owner_name = sys_context (''USERENV'', ''SESSION_USER'')';
-- this is the restriction you wish to enforce
RETURN d_my_name;
END my_name;
/
exec dbms_rls.add_policy('SCOTT','ASD','POLICY1','SCOTT','MY_NAME');
exec dbms_rls.enable_policy('SCOTT','ASD','POLICY1',true);
exec dbms_rls.refresh_policy('SCOTT','ASD','POLICY1');
exec dbms_rls.drop_policy('SCOTT','ASD','POLICY1');
exec dbms_rls.enable_policy('SCOTT','ASD','POLICY1',false);
实验：
(1)准备
SQL> conn scott/scott@testdb
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott
SQL> create table scott.asd as select owner,table_name from dba_tables;
Table created
SQL> select count(1) from scott.asd;
COUNT(1)
----------
2780
SQL> select count(1) from scott.asd where owner='SCOTT';
COUNT(1)
----------
42
SQL> select count(1) from scott.asd where owner='TERRY';
COUNT(1)
----------
0
(2)创建函数
SQL> create or replace function my_name
2 (
3 obj_schema varchar2,
4 obj_name varchar2
5 )
6 return varchar2
7 is
8 d_my_name varchar2(2000);
9 BEGIN
10 d_my_name := 'owner = sys_context (''USERENV'', ''SESSION_USER'')';
11 -- this is the restriction you wish to enforce
12 RETURN d_my_name;
13 END my_name;
14 /
Function created
(3)创建策略并开启
SQL> exec dbms_rls.add_policy('SCOTT','ASD','POLICY1','SCOTT','MY_NAME');
PL/SQL procedure successfully completed
SQL> exec dbms_rls.enable_policy('SCOTT','ASD','POLICY1',true);
PL/SQL procedure successfully completed
(4)查询和验证
SQL> select count(1) from asd;
COUNT(1)
----------
42
SQL> explain plan for select count(1) from scott.asd;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1818302109
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 6 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
|* 2 | TABLE ACCESS FULL| ASD | 42 | 714 | 6 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"=SYS_CONTEXT('USERENV','SESSION_USER')) --可以看到，此处加上了policy的约束
Note
-----
- dynamic sampling used for this statement (level=2)
18 rows selected
(5)对特定操作添加policy
SQL> exec dbms_rls.drop_policy('SCOTT','ASD','POLICY1');
PL/SQL procedure successfully completed
SQL> exec dbms_rls.add_policy('SCOTT','ASD','POLICY1','SCOTT','MY_NAME','INSERT,INDEX,UPDATE,DELETE');
PL/SQL procedure successfully completed
SQL> select count(1) from asd;
COUNT(1)
----------
2780
注：select语句的policy已经解除了。
下面是update语句使用policy的实例：
SQL> update asd set table_name=table_name||'123';
42 rows updated
SQL> rollback;
Rollback complete
下面是delete语句使用policy的实例：
SQL> delete from asd ;
42 rows deleted
SQL> rollback;
Rollback complete
当我执行insert语句时，出现了问题：
SQL> insert into asd select * from asd;
2780 rows inserted
SQL> rollback;
Rollback complete
SQL> create table asd2 as select * from asd;
Table created
SQL> select count(1) from asd2;
COUNT(1)
----------
2780
不太明白为什么insert语句没有使用policy
INDEX语句也不知道哪里可以使用policy：
SQL> create index idx_asd_tab on asd(table_name);
Index created
SQL> select dbms_metadata.get_ddl('INDEX','IDX_ASD_TAB','SCOTT') from dual;
DBMS_METADATA.GET_DDL('INDEX',
--------------------------------------------------------------------------------
CREATE INDEX "SCOTT"."IDX_ASD_TAB" ON "SCOTT"."ASD" ("TABLE_NAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
TABLESPACE "USERS"
(6)经过实验，sys用户下不会受到policy的影响。
在sys用户下创建policy，其他schema会受到此影响，但sys是超级用户，可以看到所有信息。
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as SYS
SQL> define
DEFINE _SQLPLUS_RELEASE = "000000000" (CHAR)
DEFINE _EDITOR = "PLSQLDev" (CHAR)
DEFINE _DATE = "2014/3/8" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 " (CHAR)
DEFINE _O_RELEASE = "000000000" (CHAR)
DEFINE _USER = "SYS" (CHAR) --当前用户环境变量中 _USER是SYS
DEFINE _CONNECT_IDENTIFIER = "TESTDB" (CHAR)
SQL> select sys_context('USERENV','SESSION_USER') from dual;
SYS_CONTEXT('USERENV','SESSION
--------------------------------------------------------------------------------
SYS
SQL> create or replace function my_name
2 (
3 obj_schema varchar2,
4 obj_name varchar2
5 )
6 return varchar2
7 is
8 d_my_name varchar2(2000);
9 BEGIN
10 d_my_name := 'owner = sys_context (''USERENV'', ''SESSION_USER'')';
11 -- this is the restriction you wish to enforce
12 RETURN d_my_name;
13 END my_name;
14 /
Function created
SQL> execute dbms_rls.add_policy('SCOTT','ASD','POLICY1','SYS','MY_NAME');
PL/SQL procedure successfully completed
SQL> select count(1) from scott.asd;
COUNT(1)
----------
2780
SQL> exec dbms_rls.enable_policy('SCOTT','ASD','POLICY1',true);
PL/SQL procedure successfully completed
SQL> select count(1) from scott.asd;
COUNT(1)
----------
2780
SQL> exec dbms_rls.refresh_policy('SCOTT','ASD','POLICY1');
PL/SQL procedure successfully completed
SQL> select count(1) from scott.asd;
COUNT(1)
----------
2780
SQL> select count(1) from scott.asd where owner= sys_context('USERENV','SESSION_USER');
COUNT(1)
----------
968
以上pl/sql package的用法：
DBMS_RLS.ADD_POLICY (
object_schema IN VARCHAR2 NULL,
object_name IN VARCHAR2,
policy_name IN VARCHAR2,
function_schema IN VARCHAR2 NULL,
policy_function IN VARCHAR2,
statement_types IN VARCHAR2 NULL,
update_check IN BOOLEAN FALSE,
enable IN BOOLEAN TRUE,
static_policy IN BOOLEAN FALSE,
policy_type IN BINARY_INTEGER NULL,
long_predicate IN BOOLEAN FALSE,
sec_relevant_cols IN VARCHAR2,
sec_relevant_cols_opt IN BINARY_INTEGER NULL);
DBMS_RLS.DROP_POLICY (
object_schema IN VARCHAR2 NULL,
object_name IN VARCHAR2,
policy_name IN VARCHAR2);
DBMS_RLS.REFRESH_POLICY (
object_schema IN VARCHAR2 NULL,
object_name IN VARCHAR2 NULL,
policy_name IN VARCHAR2 NULL);
DBMS_RLS.ENABLE_POLICY (
object_schema IN VARCHAR2 NULL,
object_name IN VARCHAR2,
policy_name IN VARCHAR2,
enable IN BOOLEAN);
上面讲到的是对所有语句的policy，还可以增加策略组（policy group）：
DBMS_RLS.ADD_GROUPED_POLICY(
object_schema VARCHAR2,
object_name VARCHAR2,
policy_group VARCHAR2,
policy_name VARCHAR2,
function_schema VARCHAR2,
policy_function VARCHAR2,
statement_types VARCHAR2,
update_check BOOLEAN,
enabled BOOLEAN,
static_policy IN BOOLEAN FALSE,
policy_type IN BINARY_INTEGER NULL,
long_predicate IN BOOLEAN FALSE,
sec_relevant_cols IN VARCHAR2);
当时以为是开发商的什么安全策略，直到看到了oracle的dbms_rls包，才恍然大悟。原来这也是oracle的安全策略啊。
知识点1：dbms_rls包的使用
rls即row level security，是对语句的额外限制。
该特性从10g开始出现。
脚本：
create or replace function my_name
(
obj_schema varchar2,
obj_name varchar2
)
return varchar2
is
d_my_name varchar2(2000);
BEGIN
d_my_name := 'owner_name = sys_context (''USERENV'', ''SESSION_USER'')';
-- this is the restriction you wish to enforce
RETURN d_my_name;
END my_name;
/
exec dbms_rls.add_policy('SCOTT','ASD','POLICY1','SCOTT','MY_NAME');
exec dbms_rls.enable_policy('SCOTT','ASD','POLICY1',true);
exec dbms_rls.refresh_policy('SCOTT','ASD','POLICY1');
exec dbms_rls.drop_policy('SCOTT','ASD','POLICY1');
exec dbms_rls.enable_policy('SCOTT','ASD','POLICY1',false);
实验：
(1)准备
SQL> conn scott/scott@testdb
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott
SQL> create table scott.asd as select owner,table_name from dba_tables;
Table created
SQL> select count(1) from scott.asd;
COUNT(1)
----------
2780
SQL> select count(1) from scott.asd where owner='SCOTT';
COUNT(1)
----------
42
SQL> select count(1) from scott.asd where owner='TERRY';
COUNT(1)
----------
0
(2)创建函数
SQL> create or replace function my_name
2 (
3 obj_schema varchar2,
4 obj_name varchar2
5 )
6 return varchar2
7 is
8 d_my_name varchar2(2000);
9 BEGIN
10 d_my_name := 'owner = sys_context (''USERENV'', ''SESSION_USER'')';
11 -- this is the restriction you wish to enforce
12 RETURN d_my_name;
13 END my_name;
14 /
Function created
(3)创建策略并开启
SQL> exec dbms_rls.add_policy('SCOTT','ASD','POLICY1','SCOTT','MY_NAME');
PL/SQL procedure successfully completed
SQL> exec dbms_rls.enable_policy('SCOTT','ASD','POLICY1',true);
PL/SQL procedure successfully completed
(4)查询和验证
SQL> select count(1) from asd;
COUNT(1)
----------
42
SQL> explain plan for select count(1) from scott.asd;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1818302109
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 6 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
|* 2 | TABLE ACCESS FULL| ASD | 42 | 714 | 6 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"=SYS_CONTEXT('USERENV','SESSION_USER')) --可以看到，此处加上了policy的约束
Note
-----
- dynamic sampling used for this statement (level=2)
18 rows selected
(5)对特定操作添加policy
SQL> exec dbms_rls.drop_policy('SCOTT','ASD','POLICY1');
PL/SQL procedure successfully completed
SQL> exec dbms_rls.add_policy('SCOTT','ASD','POLICY1','SCOTT','MY_NAME','INSERT,INDEX,UPDATE,DELETE');
PL/SQL procedure successfully completed
SQL> select count(1) from asd;
COUNT(1)
----------
2780
注：select语句的policy已经解除了。
下面是update语句使用policy的实例：
SQL> update asd set table_name=table_name||'123';
42 rows updated
SQL> rollback;
Rollback complete
下面是delete语句使用policy的实例：
SQL> delete from asd ;
42 rows deleted
SQL> rollback;
Rollback complete
当我执行insert语句时，出现了问题：
SQL> insert into asd select * from asd;
2780 rows inserted
SQL> rollback;
Rollback complete
SQL> create table asd2 as select * from asd;
Table created
SQL> select count(1) from asd2;
COUNT(1)
----------
2780
不太明白为什么insert语句没有使用policy
INDEX语句也不知道哪里可以使用policy：
SQL> create index idx_asd_tab on asd(table_name);
Index created
SQL> select dbms_metadata.get_ddl('INDEX','IDX_ASD_TAB','SCOTT') from dual;
DBMS_METADATA.GET_DDL('INDEX',
--------------------------------------------------------------------------------
CREATE INDEX "SCOTT"."IDX_ASD_TAB" ON "SCOTT"."ASD" ("TABLE_NAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
TABLESPACE "USERS"
(6)经过实验，sys用户下不会受到policy的影响。
在sys用户下创建policy，其他schema会受到此影响，但sys是超级用户，可以看到所有信息。
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as SYS
SQL> define
DEFINE _SQLPLUS_RELEASE = "000000000" (CHAR)
DEFINE _EDITOR = "PLSQLDev" (CHAR)
DEFINE _DATE = "2014/3/8" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 " (CHAR)
DEFINE _O_RELEASE = "000000000" (CHAR)
DEFINE _USER = "SYS" (CHAR) --当前用户环境变量中 _USER是SYS
DEFINE _CONNECT_IDENTIFIER = "TESTDB" (CHAR)
SQL> select sys_context('USERENV','SESSION_USER') from dual;
SYS_CONTEXT('USERENV','SESSION
--------------------------------------------------------------------------------
SYS
SQL> create or replace function my_name
2 (
3 obj_schema varchar2,
4 obj_name varchar2
5 )
6 return varchar2
7 is
8 d_my_name varchar2(2000);
9 BEGIN
10 d_my_name := 'owner = sys_context (''USERENV'', ''SESSION_USER'')';
11 -- this is the restriction you wish to enforce
12 RETURN d_my_name;
13 END my_name;
14 /
Function created
SQL> execute dbms_rls.add_policy('SCOTT','ASD','POLICY1','SYS','MY_NAME');
PL/SQL procedure successfully completed
SQL> select count(1) from scott.asd;
COUNT(1)
----------
2780
SQL> exec dbms_rls.enable_policy('SCOTT','ASD','POLICY1',true);
PL/SQL procedure successfully completed
SQL> select count(1) from scott.asd;
COUNT(1)
----------
2780
SQL> exec dbms_rls.refresh_policy('SCOTT','ASD','POLICY1');
PL/SQL procedure successfully completed
SQL> select count(1) from scott.asd;
COUNT(1)
----------
2780
SQL> select count(1) from scott.asd where owner= sys_context('USERENV','SESSION_USER');
COUNT(1)
----------
968
以上pl/sql package的用法：
DBMS_RLS.ADD_POLICY (
object_schema IN VARCHAR2 NULL,
object_name IN VARCHAR2,
policy_name IN VARCHAR2,
function_schema IN VARCHAR2 NULL,
policy_function IN VARCHAR2,
statement_types IN VARCHAR2 NULL,
update_check IN BOOLEAN FALSE,
enable IN BOOLEAN TRUE,
static_policy IN BOOLEAN FALSE,
policy_type IN BINARY_INTEGER NULL,
long_predicate IN BOOLEAN FALSE,
sec_relevant_cols IN VARCHAR2,
sec_relevant_cols_opt IN BINARY_INTEGER NULL);
DBMS_RLS.DROP_POLICY (
object_schema IN VARCHAR2 NULL,
object_name IN VARCHAR2,
policy_name IN VARCHAR2);
DBMS_RLS.REFRESH_POLICY (
object_schema IN VARCHAR2 NULL,
object_name IN VARCHAR2 NULL,
policy_name IN VARCHAR2 NULL);
DBMS_RLS.ENABLE_POLICY (
object_schema IN VARCHAR2 NULL,
object_name IN VARCHAR2,
policy_name IN VARCHAR2,
enable IN BOOLEAN);
上面讲到的是对所有语句的policy，还可以增加策略组（policy group）：
DBMS_RLS.ADD_GROUPED_POLICY(
object_schema VARCHAR2,
object_name VARCHAR2,
policy_group VARCHAR2,
policy_name VARCHAR2,
function_schema VARCHAR2,
policy_function VARCHAR2,
statement_types VARCHAR2,
update_check BOOLEAN,
enabled BOOLEAN,
static_policy IN BOOLEAN FALSE,
policy_type IN BINARY_INTEGER NULL,
long_predicate IN BOOLEAN FALSE,
sec_relevant_cols IN VARCHAR2);
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26451536/viewspace-1103962/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/26451536/viewspace-1103962/
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
