Wednesday, August 29, 2012

How to See the Name of Your Database

This example was tested on Oracle 11.2. It shows how a user can see his database name with only the CREATE SESSION privilege. First I created a user:

SQL> create user fred identified by bloggs
  2  /

User created.

SQL> grant create session to fred
  2  /

Grant succeeded.

SQL>

Then I tried to create the user again. This failed. So if the user had existed, perhaps with extra privileges, before I started this example, I would have seen an error when I ran the preceding step:

SQL> create user fred identified by bloggs
  2  /
create user fred identified by bloggs
            *
ERROR at line 1:
ORA-01920: user name 'FRED' conflicts with another
user or role name

SQL>

Finally, I connected as the new user and displayed the name of the database I was connected to:

SQL> conn fred/bloggs
Connected.
SQL> select sys_context('USERENV', 'DB_NAME')
  2   DATABASE from dual
  3  /

DATABASE
----------
ANDREW01

SQL>

No comments: