SQL> CONNECT sys/oracle AS SYSDBA
Connected.
SQL> CREATE USER sec IDENTIFIED BY sec;
GRANT create session  TO sec  WITH ADMIN OPTION;
GRANT select any dictionary, select any table,
create any context, drop any context,
create user, alter user, drop user,
create role, alter any role, drop any role,
create table, create procedure,create view,
create any trigger, administer database trigger
TO sec;

User created.

SQL> GRANT execute on DBMS_SESSION to sec;
Grant succeeded.

SQL>   2    3    4    5    6    7 
Grant succeeded.

SQL>

Grant succeeded.

SQL>
SQL>
SQL>
 

SQL> CONNECT system/oracle
Connected.
SQL> ALTER USER sec
  2    DEFAULT TABLESPACE example
  3    QUOTA UNLIMITED ON example;

User altered.

SQL>
SQL> CONNECT sec/sec
Connected.
SQL> DROP TABLE app_roles;
DROP TABLE app_roles
           *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> CREATE TABLE app_roles (
  id            NUMBER
                CONSTRAINT app_roles_pk PRIMARY KEY,
  username              VARCHAR2(30)NOT NULL,
  role                  VARCHAR2(30),
  ip_address            VARCHAR2(15),
  CONSTRAINT app_roles_uk UNIQUE (username, role, ip_address));

INSERT INTO app_roles

Table dropped.

SQL> SQL>   2    3    4    5    6    7    VALUES (1, 'PFAY', 'HR_EMP_MGR',
    sys_context('userenv','ip_address'));
COMMIT;

Table created.

SQL> SQL>   2    3 
1 row created.

SQL>
Commit complete.

SQL> SQL>
SQL> create table node ( id int ,name varchar2(20), create_time date);

Table created.                                                       
                                                                     
SQL> insert into node values(1000,'CAOLC',sysdate);                  
                                                                     
1 row created.                                                       
                                                                     
SQL> commit;  

SQL>create view v_node as select  id, upper(name) as Name1,trunc(create_time) as int_date from node;

SQL> select * from app_roles;

        ID USERNAME                       ROLE
---------- ------------------------------ ------------------------------
IP_ADDRESS
---------------
         1 PFAY                           HR_EMP_MGR
134.32.26.58

SQL> create role hr_emp_mgr identified  using sec.app_roles_pkg;

Role created.

SQL> CREATE OR REPLACE PACKAGE app_roles_pkg
  2    AUTHID CURRENT_USER
  3  IS
  4    PROCEDURE set_role (
  5      p_role_name    VARCHAR2 );
  6  END;
  7  /

CREATE OR REPLACE PACKAGE BODY app_roles_pkg IS
  PROCEDURE set_role (
    p_role_name VARCHAR2 )
  AS

Package created.

SQL> SQL>   2    3    4    5      v_id  app_roles.id%TYPE;
  6    BEGIN
  7      SELECT  id
  8        INTO  v_id
  9        FROM  sec.app_roles
 10        WHERE username = sys_context('userenv','current_user')
 11          AND role = p_role_name
 12          AND ip_address = sys_context('userenv','ip_address');
 13      dbms_session.set_role(p_role_name);
 14    END;
 15  END;
 16  /

Package body created.

SQL> SQL>
SQL>
SQL>
SQL>
SQL>
SQL> GRANT execute ON app_roles_pkg TO public;

Grant succeeded.

SQL> GRANT select ON app_roles TO public;

Grant succeeded.

SQL> grant  select on v_node  to public;

Grant succeeded.

SQL> create user pray identified by pray;

User created.

SQL> connect sys/oracle@prod as sysdba
Connected.
 
SQL> create user pfay identified by pfay;

User created.

SQL> grant connect to pfay;

Grant succeeded.

SQL> connect pfay/pfay@prod
Connected.
SQL> select * from session_roles;

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

SQL> exec sec.app_roles_pkg.set_role('HR_EMP_MGR');

PL/SQL procedure successfully completed.

SQL> select * from session_roles;

ROLE
------------------------------
HR_EMP_MGR

SQL> select count(*) from hr.employees;
select count(*) from hr.employees
                        *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> connect system/oracle@prod 
Connected.
SQL> grant select on hr.employees to pfay;

Grant succeeded.

SQL> connect pfay/pfay@prod
Connected.
SQL> connect system/oracle@prod 
Connected.
SQL> revoke select on hr.employees from pfay;

Revoke succeeded.

SQL> grant select on hr.employees to hr_emp_mgr;

Grant succeeded.

SQL> connect pfay/pfay@prod
Connected.
SQL> select  count(*) from hr.employees;
select  count(*) from hr.employees
                         *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select * from session_roles;

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

SQL>  exec sec.app_roles_pkg.set_role('HR_EMP_MGR');

PL/SQL procedure successfully completed.

SQL> select * from session_roles;

ROLE
------------------------------
HR_EMP_MGR

SQL> select  count(*) from hr.employees;

  COUNT(*)
----------
       108

1  select a.id from sec.app_roles a ,sec.v_node  b
  2  where username = sys_context('userenv','current_user')
  3  and  ip_address = sys_context('userenv','ip_address')
  4* and b.id=1000 and b.int_date=trunc(sysdate) and b.name1='CAOLC'

create or replace trigger restrict_user_machine
after logon on database
when (user='PFAY')
declare
  v_count int ;
begin  
    select count(*) into  v_count from sec.app_roles a ,sec.v_node  b 
  where username = sys_context('userenv','current_user')            
       and  ip_address = sys_context('userenv','ip_address')             
      and b.id=1000 and b.int_date=trunc(sysdate) and b.name1='CAOLC'    ;
 if v_count = 1  then
    sec.app_roles_pkg.set_role('HR_EMP_MGR');
  return;
 end if;
end;

--create synonym pfay.admin for sec.app_roles_pkg;