158. You want to create a role to meet these requirements: 
1: The role is to be protected from unauthorized usage. 
2: The password of the role is not to be embedded in the application source code or stored in a table. 
Which method would you use to restrict enabling of such roles? 
A.Create the role with global authentication.  
B.Create the role with external authentication. 
C.Create the role as a secure application role. 
D.Create the role as a password-protected role. 
E.Create a role and use Fine-Grained Access Control (FGAC) to secure the role. 
Answer: C   

http://docs.oracle.com/cd/E11882_01/network.112/e36292/authorization.htm#DBSEG524

Securing Role Privileges by Using Secure Application Roles

A secure application role is a role that can be enabled only by an authorized PL/SQL package (or procedure). The PL/SQL package itself reflects the security policies needed to control access to the application.

This method of role creation restricts the enabling of this type of role to the invoking application. For example, the application can perform authentication and customized authorization, such as checking whether the user has connected through a proxy.

This type of role strengthens security because passwords are not embedded in application source code or stored in a table. This way, the actions the database performs are based on the implementation of your security policies, and these definitions are stored in one place, the database, rather than in your applications. If you need to modify the policy, you do so in one place without having to modify your applications. No matter how users connect to the database, the result is always the same, because the policy is bound to the role.

To enable the secure application role, you must execute its underlying package by invoking it directly from the application when the user logs in, before the user exercises the privileges granted by the secure application role. You cannot use a logon trigger to enable a secure application role, nor can you have this type of role be a default role.

When you enable the secure application role, Oracle Database verifies that the authorized PL/SQL package is on the calling stack, that is, it verifies that the authorized PL/SQL package is issuing the command to enable the role.

You can use secure application roles to ensure the existence of a database connection. Because a secure application role is a role implemented by a package, the package can validate that users can connect to the database through a middle tier or from a specific IP address. In this way, the secure application role prevents users from accessing data outside an application. They are forced to work within the framework of the application privileges that they have been granted.

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_6012.htm#SQLRF54310

IDENTIFIED Clause

Use the IDENTIFIED clause to indicate that a user must be authorized by the specified method before the role is enabled with the SET ROLE statement.

BY password The BY password clause lets you create a local role and indicates that the user must specify the password to the database when enabling the role. The password can contain only single-byte characters from your database character set regardless of whether this character set also contains multibyte characters.

USING package The USING package clause lets you create a secure application role, which is a role that can be enabled only by applications using an authorized package. If you do not specify schema, then the database assumes the package is in your own schema.

EXTERNALLY Specify EXTERNALLY to create an external role. An external user must be authorized by an external service, such as an operating system or third-party service, before enabling the role.

Depending on the operating system, the user may have to specify a password to the operating system before the role is enabled.

GLOBALLY Specify GLOBALLY to create a global role. A global user must be authorized to use the role by the enterprise directory service before the role is enabled at login.

建立角色

DROP ROLE hr_admin;
CREATE ROLE hr_admin IDENTIFIED USING sys.hr_admin_role_check;
GRANT SELECT, INSERT, UPDATE, DELETE ON HR.EMPLOYEES TO hr_admin;

CREATE OR REPLACE PROCEDURE hr_admin_role_check AUTHID CURRENT_USER AS
  v_timestamp   TIMESTAMP := systimestamp;
  v_ss          INT;
  v_employee_id INT;
BEGIN
  v_ss := mod(extract(SECOND FROM v_timestamp),10);
  IF v_ss >= 0 AND v_ss <= 4 THEN
    EXECUTE IMMEDIATE 'SET ROLE hr_admin';
  END IF;
  SELECT employee_id INTO v_employee_id FROM hr.employees WHERE ROWNUM <=1;
  dbms_output.put_line(v_employee_id);
END;
/
grant hr_admin to scott;
GRANT EXECUTE ON hr_admin_role_check TO scott;

查看权限

SQL> SELECT * FROM dba_role_privs WHERE grantee = 'SCOTT';
GRANTEE                        GRANTED_ROLE                   ADMIN_OPTION DEFAULT_ROLE
------------------------------ ------------------------------ ------------ ------------
SCOTT                          RESOURCE                       NO           YES
SCOTT                          HR_ADMIN                       NO           NO
SCOTT                          CONNECT                        NO           YES
3 rows selected

不能直接SET ROLE hr_admin

SQL> SET ROLE hr_admin;
SET ROLE hr_admin
*
ERROR at line 1:
ORA-01924: role 'HR_ADMIN' not granted or does not exist

当SET ROLE hr_admin第一次通过，后面就可以一直用了

SQL> set time on
13:13:44 SQL> exec sys.hr_admin_role_check;
BEGIN sys.hr_admin_role_check; END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.HR_ADMIN_ROLE_CHECK", line 10
ORA-06512: at line 1


13:14:00 SQL> exec sys.hr_admin_role_check;

PL/SQL procedure successfully completed.

13:14:01 SQL> exec sys.hr_admin_role_check;

PL/SQL procedure successfully completed.

13:14:26 SQL> exec sys.hr_admin_role_check;

PL/SQL procedure successfully completed.

13:14:28 SQL>