Saturday, January 29, 2011

Check that your SQL is Being Run by the Correct User


This example is adapted from catalog.sql, one of Oracle's required scripts. It shows how you can check that your SQL is being run by the correct user. The SQL used is as follows:

CONN / AS SYSDBA
SHOW USER
WHENEVER SQLERROR EXIT;
SELECT TO_NUMBER('MUST_BE_RUN_AS_SYS')
FROM DUAL
WHERE USER != 'SYS';
WHENEVER SQLERROR CONTINUE;
CONN ANDREW/REID
SHOW USER
WHENEVER SQLERROR EXIT;
SELECT TO_NUMBER('MUST_BE_RUN_AS_SYS')
FROM DUAL
WHERE USER != 'SYS';
WHENEVER SQLERROR CONTINUE;

It runs like this:

SQL> CONN / AS SYSDBA
Connected.
SQL> SHOW USER
USER is "SYS"
SQL> WHENEVER SQLERROR EXIT;
SQL> SELECT TO_NUMBER('MUST_BE_RUN_AS_SYS')
  2  FROM DUAL
  3  WHERE USER != 'SYS';

no rows selected

SQL> WHENEVER SQLERROR CONTINUE;
SQL> CONN ANDREW/REID
Connected.
SQL> SHOW USER
USER is "ANDREW"
SQL> WHENEVER SQLERROR EXIT;
SQL> SELECT TO_NUMBER('MUST_BE_RUN_AS_SYS')
  2  FROM DUAL
  3  WHERE USER != 'SYS';
SELECT TO_NUMBER('MUST_BE_RUN_AS_SYS')
                 *
ERROR at line 1:
ORA-01722: invalid number

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

C:\Documents and Settings\Andrew\Desktop>

It should be fairly obvious how it works but if you do not understand, let me know and I will add some explanation.

No comments: