Verifying the user executing a SQL script

When writing scripts to be run via SQL*Plus it’s often a good idea to check that the script is being executed as the desired user, and to abort with an error if this is not the case. This is especially true for installation scripts or any scripts which perform dangerous or destructive actions.

One method to do this is to include the following code at the top of your scripts:

whenever sqlerror exit
BEGIN
  if (sys_context('USERENV','SESSION_USER') != ‘SCOTT') then
    RAISE_APPLICATION_ERROR(-20001, 'You must be connected as SCOTT to run this script.');
  end if;
END;
/
whenever sqlerror continue

I have abstracted this out into a standalone script which can be called with the single argument of the username to be tested for (the download link can be found at the end of the article).

If you decide to use this script instead of using the code above in your script then be aware that you should set “whenever sqlerror exit” first (see the notes within the script header for more details).

Here’s an example of the script in use…

-- ------------------------------------------------------------
-- Script to truncate all tables within the "etl_owner" schema
-- ------------------------------------------------------------
whenever sqlerror exit
@test_user.sql etl_owner

set head off feedback off
sppol _truncate.sql
select 'truncate table '||table_name||';' from user_tables;
spool off
@_truncate.sql