Saturday, May 31, 2014

The USER Keyword

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>

No comments: