Skip to Main Content
  • Questions
  • why does "select user from dba_users" work?

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Elke.

Asked: July 06, 2016 - 9:49 am UTC

Last updated: July 07, 2016 - 2:21 pm UTC

Version: 11.2.0.4

Viewed 10K+ times! This question is

You Asked

Hi all,
I accidently typed today "select user from dba_users" in SQLPlus instead of "select username from dba_users" and it worked. Not like the correct SQL would have, but i got 21 rows (which is the correct number of users in the database), each with my login user.

SQL>select user from dba_users;

USER
------------------------------
STAHLE
STAHLE
STAHLE
STAHLE
STAHLE
...

21 rows selected.

I have no idea why this statement worked. Could you please explain it to me, because it's driving me mad :-)

Thanks in advance.
Regards
Elke

and Chris said...

Because USER is a predefined function in Oracle Database. It returns the name of the session user:

http://docs.oracle.com/database/121/SQLRF/functions243.htm#SQLRF06156

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Oh

Ghassan, July 06, 2016 - 4:46 pm UTC

If so this is a bug. And must be fixed.
"Because USER is a predefined function in Oracle"
And so? This means if table T has one billion rows select user from T returns One billion times my user! !
Well and what about the select stmt syntax? ?
I believe that the engine must evaluate the table all the way within its dico columns.

Such process is not secure and it's not good thing for oracle reputation.
Chris Saxon
July 06, 2016 - 11:33 pm UTC

So

select 'x' from any_table
select sysdate from any_table
select 1 from any_table
select dbms_random.value from any_table
select to_char(systimestamp) from any_table

are all bugs as well ?


Mark, July 06, 2016 - 7:21 pm UTC

A bug?? If you call a function in the select list, the function's result will be returned per row. There's no other sensible behavior, and if this were "fixed" then queries everywhere would break. Every other major RDBMS behaves the same way, by the way.
Chris Saxon
July 06, 2016 - 11:33 pm UTC

indeed

Term

Ghassan, July 07, 2016 - 9:12 am UTC

Tkx for replying
Saying bug. Does not means as the commonly used here.. I meant as explained that it's not secure and oracle must find solution to differentiate a select constant front a select defined function.
When I do a select user from an application table mean more to get column from this owned table and not willing get the use connection .
I think such discussion won't never lead to agreement. .since it concerns the way of interpretation.
But I think that at least oracle make better to change the name of such functions in order to avoid confusion. User is a common term .. for example replacing it's call by say env.user or get User () is securing despite that select getUser () from any_table gives same results but at least it proves intentional issuing. .
If oracle no recommend but no forbid the use of reserved terms as User, it allow a cretin of column named user?
Does this work?
Create table T ( user varchar2 (5))


Chris Saxon
July 07, 2016 - 9:38 am UTC

The names of supplied functions aren't changing anytime soon. Think of all the code that would break!

"Does this work?"

Well that easy for you to test!

SQL> create table T ( user varchar2 (5)) ;
create table T ( user varchar2 (5))
                 *
ERROR at line 1:
ORA-00904: : invalid identifier


So that's a no.

Avoid reserved words

Mark, July 07, 2016 - 1:34 pm UTC

You should avoid reserved words in the names of your tables, columns, etc. That's all. In my experience, if you need a table that represents your application's users, it's usually called a slightly more descriptive name like account_user or app_user. (Currently I work on a MySQL database with an app_user table. "user" is reserved in MySQL as well, by the way.)
Chris Saxon
July 07, 2016 - 2:21 pm UTC

Yep, good advice.

Agreed

Ghassan, July 08, 2016 - 8:04 am UTC

Avoiding reserved words is the first recommend point in all my documents normalization since 15 years.
What I deplore is that database editors make of commonly words as Reserved ones!
When I would *reserve * something I do choose the *confuseless* one.
That's what I meant in this discussion.