CREST Practitioner Security Analyst (CPSA) Practice

Which command in Oracle retrieves the current user?

1. SELECT user FROM dual

2. SELECT CURRENT_USER

3. SELECT username FROM all_users

4. SELECT instance_name FROM v$instance

The correct answer is: SELECT user FROM dual

The command that retrieves the current user in Oracle is indeed the one that uses the dual table. The dual table is a special one-row, one-column table present by default in all Oracle databases. It is often used for selecting a single value or for performing calculations without needing an actual table. When you execute "SELECT user FROM dual", Oracle returns the username of the connected user. This is because the 'user' function, when queried, provides the current schema for the session. While other options contain valid SQL queries, they do not specifically return the current user in the same context. "SELECT CURRENT_USER" would also return the current user, but it is not the most common or idiomatic way to achieve this in Oracle, making it less favorable in certain contexts. The commands querying from all_users and v$instance are suited for different purposes, such as listing all users and retrieving instance information, respectively, and do not focus on returning the current user.