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>

ORA-04050

This was tested on Oracle 11.2. When you create a profile, its PASSWORD_VERIFY_FUNCTION (PVF) will be shown as DEFAULT if you do not specify it explicitly:

SQL> conn / as sysdba

Connected.

SQL> show user

USER is "SYS"

SQL> create profile andrews_profile

  2  limit password_life_time 60

  3  /

Profile created.

SQL> select limit from dba_profiles

  2  where profile = 'ANDREWS_PROFILE'

  3  and resource_name = 'PASSWORD_VERIFY_FUNCTION'

  4  /

LIMIT

----------------------------------------

DEFAULT 

SQL> 

This does not mean that it has a PVF called DEFAULT. It means that it has the same PVF as the DEFAULT profile. In this database the DEFAULT profile's PVF is NULL, which means it has no PVF:

SQL> select limit from dba_profiles

  2  where profile = 'DEFAULT'

  3  and resource_name = 'PASSWORD_VERIFY_FUNCTION'

  4  /

LIMIT

----------------------------------------

NULL 

SQL> 

You can create a function to use as a PVF.  Here is a very simple example:

SQL> create or replace function andrews_verify_function(

  2    username     varchar2,

  3    password     varchar2,

  4    old_password varchar2)

  5    return boolean as

  6  begin

  7    if length(password) < 4 then

  8      return false;

  9    else

 10      return true;

 11    end if;

 12  end andrews_verify_function;

 13  /

Function created. 

SQL> 

You can make a profile use it like this:

SQL> alter profile andrews_profile limit

  2  password_verify_function andrews_verify_function

  3  /

 

Profile altered.

 

SQL>

... and the name of the function will be stored in DBA_PROFILES:

SQL> select limit from dba_profiles

  2  where profile = 'ANDREWS_PROFILE'

  3  and resource_name = 'PASSWORD_VERIFY_FUNCTION'

  4  /

 

LIMIT

----------------------------------------

ANDREWS_VERIFY_FUNCTION

 

SQL>

Then I saw a potential problem. If I had a function called DEFAULT, and a profile with PVF set to DEFAULT, how would Oracle know if I was referring to the function called DEFAULT or the PVF used by the DEFAULT profile? Fortunately the clever people at Oracle have thought of that. If you try to create a function called DEFAULT, you get an error:

SQL> create or replace function default(

  2    username     varchar2,

  3    password     varchar2,

  4    old_password varchar2)

  5    return boolean as

  6  begin

  7    if length(password) < 4 then

  8      return false;

  9    else

 10      return true;

 11    end if;

 12  end default;

 13  /

create or replace function default(

                           *

ERROR at line 1:

ORA-04050: invalid or missing procedure, function, or

package name

SQL>

Reply to: Reply to Andrew Reid

Send

DISCONNECT

You can return from SQL*Plus to the operating system using exit or quit:

 

SQL> exit

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.7.0 - Production

UNIX>

 

SQL> quit

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.7.0 - Production

UNIX>

 

You can also use disconnect, which terminates your database session but leaves you in SQL*Plus:

 

SQL> show user

USER is "ORACLE"

SQL> disconnect

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.7.0 - Production

SQL> show user

USER is ""

SQL>

 

This lets you enter and/or modify SQL*Plus commands but does not allow you to run them:

 

SQL> select sysdate from dual

  2  /

SP2-0640: Not connected

SQL> c/sysdate/sysdate + 1/

  1* select sysdate + 1 from dual

SQL> /

SP2-0640: Not connected

SQL>

 

To do that, you have to reconnect to the database:

 

SQL> show user

USER is ""

SQL> conn /

Connected.

SQL> show user

USER is "ORACLE"

SQL> l

  1* select sysdate + 1 from dual

SQL> /

 

SYSDATE+1

---------

17-FEB-12

 

SQL>

Another Example Using Intersect

In the course of helping a colleague today, I had to find a database user who had 2 roles assigned. You can do this with a couple of subqueries but it is easier with an INTERSECT. I was logged in as user ORACLE when I did this and I was surprised to see ORACLE in the list alongside USER_2. It seems that, if you create a role, it is automatically assigned to you. I did not know this. You can see this demonstrated at the end of the example. I ran it on an Oracle 9 database but the same thing happens in Oracle 10 and 11:

 

SQL> conn /

Connected.

SQL> show user

USER is "ORACLE"

SQL> create role role_a

  2  /

 

Role created.

 

SQL> create role role_b

  2  /

 

Role created.

 

SQL> grant role_a to user_1

  2  identified by user_1

  3  /

 

Grant succeeded.

 

SQL> grant role_a, role_b to user_2

  2  identified by user_2

  3  /

 

Grant succeeded.

 

SQL> grant role_b to user_3

  2  identified by user_3

  3  /

 

Grant succeeded.

 

SQL> select distinct grantee from dba_role_privs

  2  where grantee in

  3  (select grantee from dba_role_privs

  4   where granted_role = 'ROLE_A')

  5  and grantee in

  6  (select grantee from dba_role_privs

  7   where granted_role = 'ROLE_B')

  8  /

 

GRANTEE

------------------------------

ORACLE

USER_2

 

SQL> select grantee from dba_role_privs

  2  where granted_role = 'ROLE_A'

  3  intersect

  4  select grantee from dba_role_privs

  5  where granted_role = 'ROLE_B'

  6  /

 

GRANTEE

------------------------------

ORACLE

USER_2

 

SQL> create role blah

  2  /

 

Role created.

 

SQL> select grantee from dba_role_privs

  2  where granted_role = 'BLAH'

  3  /

 

GRANTEE

------------------------------

ORACLE

 

SQL>

Password_Verify_Function (Part 1)

This example was tested on Oracle 10 on Linux. It shows you how to create a simple password verify function (PVF). First connect as the SYS user because a PVF must be owned by SYS:

SQL> conn / as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> 

Create a profile to use the PVF:

SQL> create profile andrews_profile
  2  limit password_life_time 60
  3  /

Profile created.

SQL>

Try to give my new profile a PVF called my_verify_function. This fails as it has not been created yet:

SQL> alter profile andrews_profile limit
  2  password_verify_function my_verify_function
  3  /
alter profile andrews_profile limit
*
ERROR at line 1:
ORA-07443: function MY_VERIFY_FUNCTION not found

SQL>

Create a function with the required name. A PVF must have the parameters shown. It should be obvious what this PVF is supposed to do:

SQL> create or replace function my_verify_function (
  2  username     varchar2,
  3  password     varchar2,
  4  old_password varchar2)
  5  return boolean as
  6  begin
  7  if length(password) < 4 then
  8    return false;
  9  else
 10    return true;
 11  end if;
 12  end my_verify_function;
 13  /

Function created.

SQL>

Change the new profile to use this PVF:

SQL> alter profile andrews_profile limit
  2  password_verify_function my_verify_function
  3  /

Profile altered.

SQL>

Create a user and assign it the new profile:

SQL> create user andrew identified by reid1
  2  profile andrews_profile
  3  /

User created.

SQL>

Change the user’s password. The new value is passed to the PVF and rejected:

SQL> alter user andrew identified by rei
  2  /
alter user andrew identified by rei
*
ERROR at line 1:
ORA-28003: password verification for the specified
password failed
ORA-28003: password verification for the specified
password failed

SQL>

The error message was not very helpful so change the PVF accordingly:

SQL> create or replace function my_verify_function (
  2  username     varchar2,
  3  password     varchar2,
  4  old_password varchar2)
  5  return boolean as
  6  begin
  7  if length(password) < 4 then
  8   raise_application_error
  9   (-20000, 'Password < 4 characters long');
 10  else
 11   return true;
 12  end if;
 13  end my_verify_function;
 14  /

Function created.

SQL>

Try to change the password again. This time the reason for rejection is clear:

SQL> alter user andrew identified by rei
  2  /
alter user andrew identified by rei
*
ERROR at line 1:
ORA-28003: password verification for the specified
password failed
ORA-20000: Password < 4 characters long

SQL>

Fix the problem and try again:

SQL> alter user andrew identified by reid2
  2  /

User altered.

SQL>

set esc

You can use set esc to specify an escape character to SQL*Plus. This will often be a back slash (\). Then, if you have a variable e.g. $TAX_YEAR, which has a value of 2011, SQL*Plus will interpret $TAX_YEAR as 2011 but it will interpret \$TAX_YEAR as a string of characters with a value of $TAX_YEAR. I have an example somewhere demonstrating this and, when I find it, I will put it in a blog post. In the meantime, here is an example showing how NOT to use it. A few days ago, I was given a data patch to run. It contained a set esc / statement so the escape character was a forward slash (/). After I had run it, I found I could not reconnect to the database. To show how this happened, I need a couple of users: 

SQL> conn / as sysdba

Connected.

SQL> create user andrew

  2  identified by reid

  3  /

 

User created.

 

SQL> create user andrewreid

  2  identified by andrewreid

  3  /

 

User created.

 

SQL> grant create session to

  2  andrew, andrewreid

  3  /

 

Grant succeeded.

 

SQL>
  

Then I connect as the first user, andrew. Note that esc is off at the start:

SQL> show esc

escape OFF

SQL> conn andrew/reid

Connected.

SQL> show user

USER is "ANDREW"

SQL>
  

Next I set esc /, just like the data patch did:

SQL> set esc /

SQL> show esc

escape "/" (hex 2f)

SQL>
  

Finally, I try to reconnect to the database. Oracle normally treats a forward slash as the separator between the username and password in a connect string. However, if the forward slash is the escape character, it does not do this any more. When this happened for real, I was unable to connect at all. For the example I have changed things to show exactly what happens. I try to connect as user andrew, who has a password of reid. Oracle treats the forward slash as the escape character and assumes I want to connect as andrewreid. I enter the password for andrewreid and login successfully. Then I run a show user command to verify that the username is really andrewreid, not andrew:
  

SQL> conn andrew/reid

Enter password:

Connected.

SQL> show user

USER is "ANDREWREID"

SQL>

CONN / AS SYSOPER in Windows XP

I was running Oracle on Windows XP and found I could not do CONN / AS SYSOPER:

C:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 11 23:44:21 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn / as sysoper
ERROR:
ORA-01031: insufficient privileges

SQL>

I went to the Oracle 10 client software under All Programs and found the Administration Assistant for Windows under Configuration and Migration Tools (click to enlarge it and bring it into focus):

I expanded it out as shown below and highlighted OS Database Operators - Computer:

I clicked on Action then Add/Remove:

This displayed the screen below:

I clicked the pull down arrow to the right of the Domain: box and selected the only choice displayed. This populated the Name / Description box:

I wanted to CONN / AS SYSOPER from user Andrew so I highlighted it and then I could click on the Add button:

Which added Andrew to the OS Database Operators - Computer box at the bottom:

So I clicked OK then I was able to do CONN / AS SYSOPER. When you do this, you log on as the PUBLIC user:

C:\>sqlplus / as sysoper

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Feb 12 19:34:57 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> show user
USER is "PUBLIC"
SQL>