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

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Chris Saxon

Thanks for the question, Elke.

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

Answered by: Chris Saxon - Last updated: July 07, 2016 - 2:21 pm UTC

Category: SQL*Plus - Version: 11.2.0.4

Viewed 1000+ times

Whilst you are here, check out some content from the AskTom team: Running external programs from the scheduler

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 we 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

and you rated our response

  (5 ratings)

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

Reviews

Oh

July 06, 2016 - 4:46 pm UTC

Reviewer: Ghassan

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

Followup  

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 ?


July 06, 2016 - 7:21 pm UTC

Reviewer: Mark from NY

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

Followup  

July 06, 2016 - 11:33 pm UTC

indeed

Term

July 07, 2016 - 9:12 am UTC

Reviewer: Ghassan

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

Followup  

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

July 07, 2016 - 1:34 pm UTC

Reviewer: Mark from NY

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

Followup  

July 07, 2016 - 2:21 pm UTC

Yep, good advice.

Agreed

July 08, 2016 - 8:04 am UTC

Reviewer: Ghassan

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.