Secure Roles

（一）Secure Roles 
You can use the IDENTIFIED BY clause to authorize the role with a password. This clause specifies how the user must be authorized before the role can be enabled for use by a specific user to which it has been granted. 
You cannot authenticate a password-authenticated role on logon, even if the role is a member of your list of default roles. You must explicitly enable it with the SET ROLE statement using the required password.
不能把密码角色设置为default role自动生效，而是每次登陆后使用set role来设置生效
注：有密码的role只能直接授予给user，不能授予给role
If the role is password authenticated, then you cannot grant it indirectly to the user, nor can you make it a default role. 
You only can grant this type of role directly to the user. 

CREATE ROLE c##testrole IDENTIFIED BY oracle;
Grant select on sys.t1 to c##testrole;
grant c##testrole to c##test;
conn c##test/oracle
select * from sys.t1;
ORA-00942: table or view does not exist
set role c##testrole identified by oracle;
select * from sys.t1;
ID
----------
2222

（二）Secure Application Roles
创建secure applicaiton role，然后把role授予user
在user登陆时执行procedure/package来使用role

1. 创建secure application role:
CREATE ROLE hr_admin IDENTIFIED USING sec_mgr.hr_admin_role_check;

2. 授权：
GRANT SELECT, INSERT, UPDATE, DELETE ON HR.EMPLOYEES TO hr_admin;

3. 创建上面使用的sec_mgr.hr_admin_role_check存储过程，它必须使用current_user权限
下面表示只在连接ip为192.0.2.10到192.0.2.20， 连接时间在8点到17点才会使用role
CREATE OR REPLACE PROCEDURE hr_admin_role_check
 AUTHID CURRENT_USER 
 AS 
 BEGIN 
  IF (SYS_CONTEXT ('userenv','ip_address') 
    BETWEEN '192.0.2.10' and '192.0.2.20'
     AND
    TO_CHAR (SYSDATE, 'HH24') BETWEEN 8 AND 17)
  THEN
    EXECUTE IMMEDIATE 'SET ROLE hr_admin'; 
  END IF;
 END;
/

4. 存储过程授予user1：
GRANT EXECUTE ON hr_admin_role_check TO user1;

5. 角色授予user1:
grant hr_admin to user1;

6. 使用：
conn user1@hrpdb
EXECUTE sec_admin.hr_admin_role_check;