Secure Application Roles | Effective Oracle Database 10g Security by Design

Secure application roles were introduced in Oracle9i Database. They were designed to meet the same requirements of password-protected roles, but they can actually do more.

You need to prevent users or rogue applications from enabling certain roles. To do this, Oracle ensures that the secure application role can only be enabled from within a PL/SQL program. There are two security aspects to this. First, the user or application has to have EXECUTE rights on the PL/SQL program if it ever stands a chance of enabling the role. Second, the PL/SQL program itself will perform a series of verifications and validations to ensure that everything is as it should be before setting the role(s). In this way, the PL/SQL program acts as a sentry guarding the role.

This is invaluable, and it solves the problem not resolved in the previous example—the database now has a say in whether or not the roles should be enabled. The application will execute the procedure. In essence, this can be considered a request to turn on the privileges. It’s ultimately up to the database and the code you write to determine whether or not the role will be enabled.

For example, if a request to enable a role occurs at 3 A.M. on Sunday morning, the database may decide that this isn’t a normal time to be working and that the request might therefore be from a hacker. The database can then elect to not enable the role.

Secure Application Role Example

To create a secure application role, specify the PL/SQL program you want to be used to act as the sentry for your role. The following creates a role appropriately named sec_app_role that will be enabled by the PRIV_MGR program in the SEC_MGR schema:

sec_mgr@KNOX10g> CREATE ROLE sec_app_role   2    IDENTIFIED USING sec_mgr.priv_mgr; Role created. sec_mgr@KNOX10g> -- Grant role to scott; sec_mgr@KNOX10g> GRANT sec_app_role TO scott; Grant succeeded. sec_mgr@KNOX10g> -- Disable role. sec_mgr@KNOX10g> -- User has to enable to get the privileges. sec_mgr@KNOX10g> ALTER USER scott DEFAULT ROLE CONNECT, RESOURCE; User altered.

There are a couple of things worth noting. First, notice the similar yet one-word different syntax from the password-protected roles—IDENTIFIED BY for passwords and IDENTIFIED USING for secure application roles. Also, the secure application role program (PRIV_MGR in the preceding example) doesn’t have to exist before the role is created. In fact, the schema doesn’t even have to exist. The syntax doesn’t specify whether the program is a PL/SQL package, procedure, or function. That’s because it can be any one of the three. After you create the role, you have to grant the role to appropriate users.

Tip

As mentioned before, if you don’t disable the role, it defeats the purpose of having it secured.

In creating the program that enables the secure application role, you have to use invoker rights. In the section discussing definer and invoker rights, you noticed that roles were disabled in definer rights and enabled in invoker. For consistency reasons among others, roles can only be enabled from within invoker rights procedures. This includes standard roles and password protected roles.

To illustrate the effectiveness of secure application roles, remove the previous grant on the DATA_OWNER.T table from the standard APP_USER_ROLE and grant the privilege to the secure application role:

sec_mgr@KNOX10g> -- Revoke privilege from standard role sec_mgr@KNOX10g> REVOKE ALL ON data_owner.t FROM app_user_role; Revoke succeeded. sec_mgr@KNOX10g> -- Grant select privilege on T sec_mgr@KNOX10g> -- to secure application role sec_mgr@KNOX10g> GRANT SELECT ON data_owner.t   2    TO sec_app_role; Grant succeeded.

To protect access to this table, you want to check that the user is accessing the database from the database server machine. Do this by checking for the localhost IP address of 127.0.0.1; this simulates allowing user’s privileges only when they have successfully logged into the database server. Note that IP addresses can be spoofed, but it’s difficult to spoof the localhost IP address from a foreign machine.

The first security check actually happens before the code executes. The database will verify the user has rights to execute your PL/SQL program. Therefore, you have to grant EXECUTE on the procedure that will enable the secure application role. This procedure, named PRIV_MGR, will need to be accessible to the SCOTT user:

 sec_mgr@KNOX10g> -- Create secure application role procedure. sec_mgr@KNOX10g> -- Name of procedure has to match name defined sec_mgr@KNOX10g> -- to enable the role as given sec_mgr@KNOX10g> -- in the CREATE ROLE DDL sec_mgr@KNOX10g> CREATE OR REPLACE PROCEDURE priv_mgr   2  AUTHID CURRENT_USER   3  AS   4  BEGIN   5    IF (SYS_CONTEXT ('userenv', 'ip_address') =   6                                         '127.0.0.1')   7    THEN   8      DBMS_SESSION.set_role ('sec_app_role');   9    END IF;  10  END;  11  / Procedure created. sec_mgr@KNOX10g> -- Grant execute privileges sec_mgr@KNOX10g> GRANT EXECUTE ON priv_mgr TO scott; Grant succeeded.

Logging in as SCOTT using SQL*Plus from a remote server, you’ll see that the role will not be enabled, even though the procedure executes successfully. First, view your IP address; then check to see which default roles have been enabled.

scott@KNOX10g> -- Show current IP Address   scott@KNOX10g> SELECT SYS_CONTEXT('userenv', 'ip_address') FROM DUAL; SYS_CONTEXT('USERENV','IP_ADDRESS') ---------------------------------------------------------------------   192.168.0.100     scott@KNOX10g> -- Show enabled roles   scott@KNOX10g> SELECT ROLE FROM session_roles; ROLE   ------------------------------   CONNECT   RESOURCE

Even if SCOTT knows he has to enable the SEC_APP_ROLE, he can’t:

scott@KNOX10g> -- Show role cannot be enabled   scott@KNOX10g> EXEC dbms_session.set_role('sec_app_role'); BEGIN dbms_session.set_role('sec_app_role'); END; * ERROR at line 1: ORA-28201: Not enough privileges to enable application role    'SEC_APP_ROLE' ORA-06512: at "SYS.DBMS_SESSION", line 124  ORA-06512: at line 1

SCOTT then executes the procedure to enable the role. The procedure will execute successfully. He tries to query the table with no success. Querying the SESSION_ROLES indicates that the role wasn’t set by the procedure:

scott@KNOX10g> -- Try to enable the secure application role   scott@KNOX10g> EXEC sec_mgr.priv_mgr     PL/SQL procedure successfully completed. scott@KNOX10g> -- Access object   scott@KNOX10g> SELECT * FROM data_owner.t; SELECT * FROM data_owner.t                          * ERROR at line 1: ORA-00942: table or view does not exist       scott@KNOX10g> -- Show enabled roles   scott@KNOX10g> SELECT ROLE FROM session_roles; ROLE   ------------------------------   CONNECT   RESOURCE

Performing the same set of operations when directly logged in to the server, you’ll see the expected results. The role is set as illustrated in the following code:

scott@KNOX10g> SELECT SYS_CONTEXT ('userenv', 'ip_address') FROM DUAL; SYS_CONTEXT('USERENV','IP_ADDRESS') ---------------------------------------------------------------------   127.0.0.1     scott@KNOX10g> EXEC sec_mgr.priv_mgr     PL/SQL procedure successfully completed. scott@KNOX10g> SELECT * FROM data_owner.t; D   -  X     scott@KNOX10g> SELECT ROLE FROM session_roles; ROLE   ------------------------------   SEC_APP_ROLE

The benefit of a secure application role is that the database ultimately decides whether the role is enabled or not. This is advantageous because it will allow you to modify your security policy without needing to change your deployed applications. For example, if your policy now says the user can only enable the privileges during business hours, you simply modify your PL/SQL code that guards your role:

sec_mgr@KNOX10g> -- Modify security policy to only allow privs   sec_mgr@KNOX10g> -- during 8 a.m. to 5 p.m. sec_mgr@KNOX10g> CREATE OR REPLACE PROCEDURE priv_mgr     2  AUTHID CURRENT_USER     3  AS    4  BEGIN    5    IF (    SYS_CONTEXT ('userenv', 'ip_address') =  6                                             '127.0.0.1'  7        AND TO_CHAR (SYSDATE, 'HH24') BETWEEN 8 AND 17)  8    THEN     9      DBMS_SESSION.set_role ('sec_app_role'); 10    END IF; 11  END; 12  /    Procedure created. sec_mgr@KNOX10g> CONN scott/tiger   Connected. scott@KNOX10g> COL ip format a16   scott@KNOX10g> COL hour format a5   scott@KNOX10g> SELECT SYS_CONTEXT ('userenv', 'ip_address') ip,   2         TO_CHAR (SYSDATE, 'HH24') HOUR      3    FROM DUAL; IP               HOUR   ---------------- -----   127.0.0.1        21     scott@KNOX10g> EXEC sec_mgr.priv_mgr     PL/SQL procedure successfully completed. scott@KNOX10g> SELECT * FROM data_owner.t; SELECT * FROM data_owner.t                                                  * ERROR at line 1: ORA-00942: table or view does not exist

Restrictions

As with password-protected roles, secure application roles cannot be constrained by the proxy authentication DDL. Recall the DDL allows you to restrict roles that can be enabled or can’t be enabled via a proxy connection. Unfortunately, secure application roles are also not supported.

sec_mgr@KNOX10g> ALTER USER scott     2      GRANT CONNECT THROUGH app_public     3      WITH ROLE sec_app_role;     WITH ROLE sec_app_role                 * ERROR at line 3: ORA-28168: attempted to grant password-protected role

Practical Uses

Secure application roles are a great solution when your architecture depends on the selective nature of enabling roles and privileges. For example, if your application connects to the same schema and you want to maintain separate privileges for separate end users, you can use secure application roles to decide when to enable the privileges.

Secure application roles are the preferred way to meet the requirements listed previously in which privileges are to be granted only after some validation and verification process occurs. They are an excellent tool because the code can not only check environmental attributes, such as the IP address, but the code also can take parameters, which it can use to help make its security decisions.

The best method for implementation is to use a combination of parameters and nonparameter checks. This is because the parameters can be faked or manipulated; the nonparameter information (time, IP address, authentication mode, and so on) is much more controlled and harder for the user or application to alter.

Also, note that this security model is only as good as the code that is written. In the example, you check the IP address. While this is certainly better than nothing, it still represents a single check. Secure application roles are most effective when they check a combination of things. Each check in itself may not be too secure, but generally the combination of all checks provides the necessary security desired and provides defense in depth.