SQL*Plus bind and substitution variables

Is it possible to create generic grant scripts for a release across all of the environments which are used on the path to production eg SIT, UAT?

Each of the environments in use have "read only" and "fire fighter" users created to prevent the schema owner password being too widely used. To complicate things, the read only and fire fighter user names do not remain constant. Grant scripts are created on a per environment basis which means the same scripts are not being run more than once - increasing the risk that errors are made when the scripts are amended.

The scenario is best described in the following script:

CREATE USER uat    IDENTIFIED BY uat;  
CREATE USER ro_uat IDENTIFIED BY ro_uat;
CREATE USER ff     IDENTIFIED BY ff_uat;  
-- Grant privileges to connect

connect uat/uat
CREATE TABLE x
(x_id NUMBER);

GRANT SELECT ON x TO ro_uat;
GRANT SELECT, INSERT, UPDATE, DELETE ON x to ff_uat;

I have created an application owner user and two users to be used for read only and update. The GRANT statements need to be executed once per environment - and amended on deployment to each new environment.

The solution uses a mixture of substitution and bind variables. A substitution variable is identified in a SQL*Plus script with & or && and are used to allow repeated use within a script. A bind variable in this context is a variable which is used in SQL*Plus and can be referenced within SQL or PL/SQL executed as part of the script.

My first attempt to solve the problem resulted in the following script:

VARIABLE ro VARCHAR2(30);  -- Declare bind variables to use in block below
VARIABLE ff VARCHAR2(30);  
-- Create an anonymous block to populate the variables
DECLARE  
  v_user VARCHAR2(30);  
BEGIN  
  -- Get the user
  SELECT user   
  INTO   v_user  
  FROM   dual;  
  -- Set variables depending on the user, notice the use of the ':' to signify a bind variable
  CASE v_user  
    WHEN 'PROD' THEN   
      :ro := 'ROPROD';  
      :ff := 'FFPROD';  
    WHEN 'UAT' THEN  
      :ro := 'ROUAT';  
      :ff := 'FFUAT';  
    WHEN 'DEV' THEN  
      :ro := 'RODEV';  
      :ff := 'FFDEV';  
  END CASE;  
END;  
/  
-- Grant permissions
GRANT SELECT ON x TO :ro;
GRANT SELECT ON x TO :ro
                     *
ERROR at line 1:
ORA-00987: missing or invalid username(s)

We cannot use a bind variable here.

Lets try converting the bind variable to a substitution variable.

-- This code can be run after the code above   
-- Ensure the parameters are reset 
undefine ro_param   
undefine ff_param   
-- create a user variable. new_value stores the result of the query in the variable  
-- note that the column in the query must match the column name in the command  
column rousr format a30 new_value ro_param   
column ffusr format a30 new_value ff_param   
SELECT :ro AS rousr,   
       :ff AS ffusr   
FROM dual; 

GRANT SELECT ON x TO &&first;
old   1: GRANT SELECT ON x TO &&first
new   1: GRANT SELECT ON x TO RODEV

Grant succeeded.  

Please note that "set define on" should be set in the script to ensure that the && characters are identified and used properly. "set verify on" is also useful - it displays the lines marked old and new in the script above.