Here are a couple of examples with the USER keyword, which I tested on Oracle 11.2. You can use it after the SHOW command or in a SELECT statement as follows. It returns the name of the user running the current session:
SQL> conn fred/bloggs
Connected.
SQL> show user
USER is "FRED"
SQL> select user from dual
2 /
USER
------------------------------
FRED
SQL>
You can also include it in a WHERE clause to return rows which match the current username. To illustrate this, I created an employee table:
SQL> create table emp_table
2 (emp_name varchar2(10),
3 dept varchar2(10),
4 salary number)
5 /
Table created.
SQL>
... then I added 2 rows for the IT department and 2 for Sales:
SQL> insert into emp_table
2 values('ANDREW','IT',10000)
3 /
1 row created.
SQL> insert into emp_table
2 values('BRIAN','SALES',20000)
3 /
1 row created.
SQL> insert into emp_table
2 values('COLIN','IT',30000)
3 /
1 row created.
SQL> insert into emp_table
2 values('DAVID','SALES',40000)
3 /
1 row created.
SQL>
I created a view to return rows from the employee table in the same department as the user running the current session:
SQL> create view emp_view
2 as select * from emp_table
3 where dept =
4 (select dept from emp_table
5 where emp_name = user)
6 /
View created.
SQL> grant select on emp_view to andrew, brian
2 /
Grant succeeded.
SQL>
... so when Andrew used it, he only saw rows from the IT department where he worked:
SQL> conn andrew/andrew
Connected.
SQL> select * from fred.emp_view
2 /
EMP_NAME DEPT SALARY
---------- ---------- ----------
ANDREW IT 10000
COLIN IT 30000
SQL>
... and when Brian used it, he only saw data for Sales:
SQL> conn brian/brian
Connected.
SQL> select * from fred.emp_view
2 /
EMP_NAME DEPT SALARY
---------- ---------- ----------
BRIAN SALES 20000
DAVID SALES 40000
SQL>
