On @! thingy

Long time back there was a discussion about @! thingy (USER@! or SYSDATE@!). Below is an example of how that can be used.

Here is a task: there is a PL/SQL unit being called over a database link. The unit has to resolve the caller i.e. a remote user.

I'm using 11.2.0.0.0 and 10.2.0.3.0 versions of Oracle RDBMS. The 11g database is a local database to a user caller. Let's create a user and create a database link to the remote database.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.0.0 - Development
With the Partitioning, Data Mining and Real Application Testing options

SQL> GRANT CREATE SESSION,CREATE DATABASE LINK TO caller IDENTIFIED BY caller;

Grant succeeded.

SQL> CONNECT caller/caller
Connected.
SQL> CREATE DATABASE LINK l CONNECT TO callee IDENTIFIED BY callee USING 'localhost:1521/orcl';

Database link created.

Let's take care of the remote part:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> GRANT CREATE SESSION,CREATE PROCEDURE TO callee IDENTIFIED BY callee;

Grant succeeded.

SQL> CONNECT callee/callee
Connected.
SQL> CREATE OR REPLACE FUNCTION resolve_users
  2  RETURN VARCHAR2
  3  AS
  4    l_remote_user                    user_users.username%TYPE;
  5  BEGIN
  6    EXECUTE IMMEDIATE 'SELECT USER@! FROM dual' INTO l_remote_user;
  7    RETURN l_remote_user || '->' || USER;
  8  END;
  9  /

Function created.

Now we are going to call a PL/SQL unit resolve_users() over a database link being connected as user caller.

SQL> VAR v VARCHAR2(40)
SQL> EXEC :v := resolve_users@l;

PL/SQL procedure successfully completed.

SQL> PRINT v

V
---------------------------------------
CALLER->CALLEE

The local call as of callee gives this as a result:

SQL> VAR v VARCHAR2(40)
SQL> EXEC :v := resolve_users;

PL/SQL procedure successfully completed.

SQL> PRINT v

V
---------------------------------------
CALLEE->CALLEE

Which seems to be pretty obvious.

And of course if you don't know the details about the database link (user_db_links) and want to get more information about the local and remote user, you can try to use this statement to get it:

SQL> SELECT user local_user
  2       , username remote_user
  3    FROM v$session@l
  4   WHERE sid = (
  5           SELECT sid
  6             FROM v$mystat@l
  7            WHERE ROWNUM = 1
  8         )
  9  /

LOCAL_USER                     REMOTE_USER
------------------------------ ------------------------------
CALLER                         CALLEE

It presumes that the grants on v_$session and v_$mystat are given to callee.

As you probably noticed I did not use CREATE USER command. I'm too lazy for that :-). Instead I used a very ancient way of creating a user — I directly granted the privileges to him.

05/11/2011:
There was a question asked about the detection of the callee's database sid. I'm not sure when exactly it would be required, however, one can attempt using dbms_system.get_env() API call to figure that out. It has to be available on almost all modern versions of the ORACLE RDBMS. One has to be aware that dbms_system has a set of undocumented API calls, therefore, direct grant on execution of that package may pose security issues. Therefore one either has to re-think and re-state the task or create a wrapper to ensure only particular API calls of dbms_system are exposed. The example below is just to illustrate the point and should not be used in any production environments w/o careful considerations.

CREATE OR REPLACE FUNCTION get_sid
RETURN VARCHAR2
IS
 l_sid                            VARCHAR2(30);
BEGIN
  sys.dbms_system.get_env('ORACLE_SID', l_sid);
  RETURN l_sid;
END;

One can use SYS_CONTEXT('USERENV', 'SERVICE_NAME') to detect the service name.