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.

SQL*Plus Scripting

I have been doing a lot of work for implementations with SQL scripts recently.

SQL scripts are extremely useful for automation purposes. I also like to output the contents of my scripts to a file so I have a permanent record.

When I first started looking at the scripts, they typically looked like the following:

 DROP TABLE t;  
 CREATE TABLE t  
 (  
  t_id NUMBER,  
  t_txt VARCHAR2(50)  
 );  
 CREATE UNIQUE INDEX pk_t ON t(t_id);  
 ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY t(t_id) USING INDEX;  
 exit  

Running this script through SQL*Plus produces the following output.

 top@ORCL11G> @sql_scripts.sql  
 Table dropped.  
 Table created.  
 Index created.  
 Table altered.  

Bare bones, isn't it? The problems at first sight are
- No output to a file, so no permanent record
- No record on the screen of which command is currently executing - imagine a situation with a large script containing long running updates - how can progress be assessed?

Lets amend the script to be the following:

 spool s2_log.txt  
 set echo on  
 DROP TABLE t;  
 CREATE TABLE t  
 (  
  t_id NUMBER,  
  t_txt VARCHAR2(50)  
 );  
 CREATE UNIQUE INDEX pk_t ON t(t_id);  
 ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY t(t_id) USING INDEX;  
 exit  

The "spool" command tells Oracle to direct output to a file. The "set echo on" command is SQL*Plus specific and tells Oracle to send the SQL statement it is executing to the screen.

Please see the sample output below:

 top@ORCL11G> DROP TABLE t;  
 Table dropped.  
 top@ORCL11G> CREATE TABLE t  
  2 (  
  3  t_id NUMBER,  
  4  t_txt VARCHAR2(50)  
  5 );  
 Table created.  
 top@ORCL11G>  
 top@ORCL11G> CREATE UNIQUE INDEX pk_t ON t(t_id);  
 Index created.  
 top@ORCL11G>  
 top@ORCL11G> ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (t_id) USING INDEX;  
 Table altered.  

Far more user friendly! As can be seen, SQL*Plus outputs exactly the commands and the results.

Some of the other useful commands I use are:
host - host command is also very useful - it executes and Operating System command without leaving SQL*Plus. For example, if you forget the name of the script you need to run, you can enter "host ls" at the prompt and it will list the contents of the current directory.

set timing on - prints the execution time per statement to the screen

set serveroutput on - Shows output from PL/SQL blocks when dbms_output is used

connect user/password - Allows the script to log on as a different user to execute commands in the other schema

There are far more options that I have covered here. The Oracle documentation shows all of the possibilities.

That's it for the moment - I will write another post regarding bind variables in scripts.