根据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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27143/viewspace-672626/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/27143/viewspace-672626/