A script-enabled browser is required for this page to function properly.

Setting and Checking Database Roles at Runtime

To set a database role:

DBMS_SESSION.SET_ROLE('payroll'); 

To set a role with a password:

DBMS_SESSION.SET_ROLE('payroll IDENTIFIED BY PASSWORD xyz001'); 

For security reasons, the password could be provided as a variable.

If the user did not have the role defined as a default role (which would automatically enable the related privileges at logon), then the only time the user would have access privileges to the database object would be while running the form, which is the preferred behavior. Since menu security is based on roles which have been granted, not only the ones currently enabled (or SET), your application must programmatically enable the roles as necessary.

To check and set database roles dynamically at runtime:

The DBMS_SESSION package includes a procedure (SET_ROLE) and a corresponding function (IS_ROLE_SET) to test the result of the procedure.

Setting and checking database roles at runtime Examples

/* Use the DBMS_SESSION package (including the SET_ROLE
** procedure and corresponding function IS_ROLE_SET) to set
** and check roles from a Oracle Forms application.
** SET_THE_ROLE: Attempts to set the role passed as a
** parameter. If the role requires a password, then pass
** the string 'ROLENAME IDENTIFIED BY PASSWORD' rather than
** simply passing 'ROLENAME' as the argument.
** Returns: TRUE if successful at setting the role
** FALSE otherwise.*/ 

FUNCTION SET_THE_ROLE(fp_rname VARCHAR2)
RETURN BOOLEAN IS

/* ORA-1979: Missing or invalid password... */ 

wrong_password exception;
pragma exception_init(wrong_password, -1979); 

/* ORA-1919: Role does not exist */ 

role_does_not_exist exception;
pragma exception_init(role_does_not_exist, -1919); 

BEGIN

/* Call the Stored Procedure to perform the SET ROLE */ 

DBMS_SESSION.SET_ROLE(fp_rname);
RETURN(TRUE); 
EXCEPTION

/* Handle the two probable errors */ 

WHEN role_does_not_exist
OR wrong_password THEN
RETURN(FALSE); 

END;

/* Using this procedure, you could implement a simple
** PRE-FORM trigger: */

IF NOT(DBMS_SESSION.IS_ROLE_SET('payroll') OR
SET_THE_ROLE('payroll')) THEN
MESSAGE('You are not authorized to run Payroll.');
PAUSE;
RAISE FORM_TRIGGER_FAILURE;
END IF;
END IF;