应用上下文

一、oracle userenv和sys_context

1、 userenv(option)返回当前的会话信息
select userenv('language') from dual;
-----------------------------------------------------------
option='isdba'  若当前是dba角色,则为true,否则false.
option='language' 返回数据库的字符集.
option='sessionid' 为当前会话标识符.
option='entryid' 返回可审计的会话标识符.
option='lang'  返回会话语言名称的iso简记.
option='instance' 返回当前的实例.
option='terminal' 返回当前计算机名
-----------------------------------------------------------

2、sys_context
----------------------------------------------------------------
select
sys_context('userenv','terminal') terminal,
sys_context('userenv','language') language,
sys_context('userenv','sessionid') sessionid,
sys_context('userenv','instance') instance,
sys_context('userenv','entryid') entryid,
sys_context('userenv','isdba') isdba,
sys_context('userenv','nls_territory') nls_territory,
sys_context('userenv','nls_currency') nls_currency,
sys_context('userenv','nls_calendar') nls_calendar,
sys_context('userenv','nls_date_format') nls_date_format,
sys_context('userenv','nls_date_language') nls_date_language,
sys_context('userenv','nls_sort') nls_sort,
sys_context('userenv','current_user') current_user,
sys_context('userenv','current_userid') current_userid,
sys_context('userenv','session_user') session_user,
sys_context('userenv','session_userid') session_userid,
sys_context('userenv','proxy_user') proxy_user,
sys_context('userenv','proxy_userid') proxy_userid,
sys_context('userenv','db_domain') db_domain,
sys_context('userenv','db_name') db_name,
sys_context('userenv','host') host,
sys_context('userenv','os_user') os_user,
sys_context('userenv','external_name') external_name,
sys_context('userenv','ip_address') ip_address,
sys_context('userenv','network_protocol') network_protocol,
sys_context('userenv','bg_job_id') bg_job_id,
sys_context('userenv','fg_job_id') fg_job_id,
sys_context('userenv','authentication_type') authentication_type,
sys_context('userenv','authentication_data') authentication_data
from dual
----------------------------------------------------------------

二、本地上下文实例

set serveroutput on;
declare
 id number;
begin
 if sys_context('userenv','session_user')='SCOTT' then
  dbms_output.put_line('SCOTT，你好！！！');
 end if;
 if sys_context('userenv','session_user')='SYS' then
  dbms_output.put_line('SYS，你好！！！');
 end if;
end;

1、创建用户

用户：sys
create user huang identified by password
default tablespace users
temporary tablespace temp;

grant connect to huang;
grant resource to huang;
grant create any context to huang;
grant select on scott.emp to huang;

2、创建应用上下文 (用户需要create any context系统权限)

用户：huang
create table emp as select * from scott.emp;
create table lookup_user as select ename username,deptno from emp;

create or replace context ctx_huang using huang.ctx_huang_mgr;
--drop context ctx_huang;

3、设置上下文属性和相应的值

用户：huang
create or replace package ctx_huang_mgr
as
 procedure set_deptno;
 procedure clear_deptno;
end;
/

用户：huang
create or replace package body ctx_huang_mgr
as
--------------------------------------------------------------
  procedure set_deptno
  as
 dno number;
  begin
 select deptno into dno from lookup_user
  where username = sys_context('userenv','session_user');

 dbms_session.set_context
  (namespace => 'ctx_huang',
   attribute => 'deptno',
   value => dno);
  end set_deptno;
--------------------------------------------------------------
  procedure clear_deptno
  as
  begin
 dbms_session.clear_context
  (namespace => 'ctx_huang',
   attribute => 'deptno');
  end clear_deptno;
--------------------------------------------------------------
end ctx_huang_mgr;
/

4、创建登录触发器

用户：sys
create or replace trigger set_user_deptno
after logon on database
begin
 huang.ctx_huang_mgr.set_deptno;
exception
 when no_data_found then
  null;
end;
/

5、查询sys_context值
select sys_context('ctx_huang','deptno') from dual;

select * from scott.emp
where deptno=sys_context('ctx_huang','deptno');

select * from dba_context;

6、其它使用实例

创建细粒度访问视图
create or replace view ctx_emp
as
select * from emp
where deptno=sys_context('ctx_huang','deptno');

创建安全保护触发器
create or replace trigger restrict_updates
before delete or update on emp
for each row
begin
 if (:old.deptno != sys_context('ctx_huang','deptno')) then
  raise_application_error
   (-20001,'The records is not your department');
 end if;
end;
/

三、全局上下文实例

1、所有用户都共享的上下文值

用户：huang
create or replace context global_huang_ctx using huang.global_ctx_mgr accessed globally;

create or replace package global_ctx_mgr
as
 procedure set_level(p_level in varchar2);
 procedure clear_level;
end;
/

create or replace package body global_ctx_mgr
as
 procedure set_level(p_level in varchar2)
 as
 begin
  dbms_session.set_context
   (namespace => 'global_huang_ctx',
    attribute => 'huang_level',
    value => p_level);
 end;
 
 procedure clear_level
 as
 begin
  dbms_session.clear_all_context('global_huang_ctx');
 end;
end;
/

exec global_ctx_mgr.set_level('normal');
select sys_context('global_huang_ctx','huang_level') from dual;

exec global_ctx_mgr.clear_level;
exec dbms_session.clear_identifier;

create or replace view gbl_test
as
select * from table_name
where 1 = decode(sys_context('global_huang_ctx','huang_level'),'normal',1,'elevated',-1,0);

2、相同模式下所有会话共享的值

用户：huang
create or replace context global_huang_ctx using huang.global_ctx_mgr accessed globally;

create or replace package global_ctx_mgr
as
 procedure set_level(
  p_level in varchar2,
  p_user in varchar2);
 procedure clear_level;
end;
/

create or replace package body global_ctx_mgr
as
 procedure set_level(
  p_level in varchar2,
  p_user in varchar2)
 as
 begin
  dbms_session.set_context
   (namespace => 'global_huang_ctx',
    attribute => 'huang_level',
    value => p_level,
    username => p_user);
 end;
 
 procedure clear_level
 as
 begin
  dbms_session.clear_all_context('global_huang_ctx');
 end;
end;
/

用户：huang
exec global_ctx_mgr.set_level('normal','huang');
select sys_context('global_huang_ctx','huang_level') from dual;
用户：scott
select sys_context('global_huang_ctx','huang_level') from dual;

用户：huang
exec global_ctx_mgr.set_level('normal','scott');
select sys_context('global_huang_ctx','huang_level') from dual;
用户：scott
select sys_context('global_huang_ctx','huang_level') from dual;

注：给set_context过程传递越多的信息，就会带来更多的访问限制。

3、使用客户身份识别信息

用户：huang
create or replace context global_huang_ctx using huang.global_ctx_mgr accessed globally;

create or replace package global_ctx_mgr
as
 procedure set_level(
  p_level in varchar2,
  p_client_id in varchar2);
 procedure clear_level;
end;
/

create or replace package body global_ctx_mgr
as
 procedure set_level(
  p_level in varchar2,
  p_client_id in varchar2)
 as
 begin
  dbms_session.set_context
   (namespace => 'global_huang_ctx',
    attribute => 'huang_level',
    value => p_level,
    client_id => p_client_id);
 end;
 
 procedure clear_level
 as
 begin
  dbms_session.clear_all_context('global_huang_ctx');
 end;
end;
/

begin
 global_ctx_mgr.set_level('App A Value','Application Alpha');
 global_ctx_mgr.set_level('App B Value','Application Beta');
end;
/

用户：huang
exec dbms_session.set_identifier('Application Alpha');
select sys_context('global_huang_ctx','huang_level') from dual;

exec dbms_session.set_identifier('Application Beta');
select sys_context('global_huang_ctx','huang_level') from dual;

用户：scott(没有设置client id，无记录)
select sys_context('global_huang_ctx','huang_level') from dual;

4、保护属性值时共享属性值

用户：huang
create or replace context global_huang_ctx using huang.global_ctx_mgr accessed globally;

create or replace package global_ctx_mgr
as
 procedure set_level(
  p_level in varchar2,
  p_user in varchar2,
  p_client_id in varchar2);
 procedure clear_level;
end;
/

create or replace package body global_ctx_mgr
as
 procedure set_level(
  p_level in varchar2,
  p_client_id in varchar2)
 as
 begin
  dbms_session.set_context
   (namespace => 'global_huang_ctx',
    attribute => 'huang_level',
    value => p_level,
    username => p_user,
    client_id => p_client_id);
 end;
 
 procedure clear_level
 as
 begin
  dbms_session.clear_all_context('global_huang_ctx');
 end;
end;
/

begin
 global_ctx_mgr.set_level
  (p_level => 'Client id alpha:HUANG value',
   p_user => 'HUANG',
   p_client_id => 'Application Alpha');
 global_ctx_mgr.set_level
  (p_level => 'Client id Beta:HUANG value',
   p_user => 'HUANG',
   p_client_id => 'Application Beta');
end;
/

用户：huang
exec dbms_session.set_identifier('Application Alpha');
select sys_context('global_huang_ctx','huang_level') from dual;

exec dbms_session.set_identifier('Application Beta');
select sys_context('global_huang_ctx','huang_level') from dual;

用户：scott(无记录)
exec dbms_session.set_identifier('Application Alpha');
select sys_context('global_huang_ctx','huang_level') from dual;

用户：huang
begin
 global_ctx_mgr.set_level
  (p_level => 'Client id alpha:HUANG value',
   p_user => 'HUANG',
   p_client_id => 'Application Alpha');
 global_ctx_mgr.set_level
  (p_level => 'Client id Beta:HUANG value',
   p_user => 'SCOTT',
   p_client_id => 'Application Beta');
end;
/

用户：huang
exec dbms_session.set_identifier('Application Alpha');
select sys_context('global_huang_ctx','huang_level') from dual;

exec dbms_session.set_identifier('Application Beta');
select sys_context('global_huang_ctx','huang_level') from dual;

用户：scott
exec dbms_session.set_identifier('Application Alpha');
select sys_context('global_huang_ctx','huang_level') from dual;

exec dbms_session.set_identifier('Application Beta');
select sys_context('global_huang_ctx','huang_level') from dual;

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17012874/viewspace-693811/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17012874/viewspace-693811/