根据IP来赋予用户的脚色role

创建应用脚色
drop role admin_role;
create role admin_role IDENTIFIED USING rman.hr_admin;

创建用户
create user scott identified by scott;

将应用脚色赋给用户
grant connect,admin_role to scott;

创建procedure, note：要用authid current_user

CREATE OR REPLACE procedure hr_admin
authid current_user
as
    v_ip          varchar2(50);
begin  
    v_ip := (sys_context('userenv','IP_ADDRESS'));  
    if v_ip = '10.0.0.9'
    then    
        dbms_session.set_role('admin_role');
    else
        null;   
    end if;  
end hr_admin;
/

将查看v$instance的权限赋给admin_role
grant select on v_$instance to admin_role;

测试应用脚色
SQL> conn scott/scott@oradb42
Connected.
SQL> select * from session_roles;

ROLE
------------------------------
CONNECT

SQL> select * from v$instance;
select * from v$instance
              *
ERROR at line 1:
ORA-00942: table or view does not exist
没有执行procedure，所以应用脚色没有起作用

执行procedure，查看当前session的脚色，改变到admin_role,
SQL> conn scott/scott@oradb42
Connected.
SQL> exec rman.hr_admin;

PL/SQL procedure successfully completed.

SQL> select * from session_roles;

ROLE
------------------------------
ADMIN_ROLE

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION           STARTUP_T STATUS       PAR    THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
              1 oradb
test.test.com
10.2.0.4.0        04-SEP-10 OPEN         NO           1 STARTED
ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL    NO