CODEWITHSUNDEEP
sqloracle-database
President Camacho
President Camacho

Reputation: 1900

Change user during session through a query

I'm currently logged in as admin on an oracle-database and I want to change the user of my session with a query. Is that possible?

Upvotes: 0

Views: 13135

Answers (2)

Bahaa
Bahaa

Reputation: 1757

Having encountered the same problem, I found out you can use the connect command to switch users. Note that, for changing in a script, without interactive shell, you need to provide the password in the command. Here is an example

-- Some arbitrary scripts executing with an admin role
-- Following is an example of a privileged command
CREATE USER ANOTHER_USER
   IDENTIFIED BY a_weak_password
GRANT CREATE TABLE TO ANOTHER_USER;

-- Now switch to newly created user
CONNECT ANOTHER_USER/a_weak_password
-- Check which user is currently set
SHOW USER

Upvotes: 0

Yasir Arsanukayev
Yasir Arsanukayev

Reputation: 9676

Unfortunately, Switching to a Different Schema with ALTER SESSION SET CURRENT_SCHEMA = <schema name> (as suggested in the comments) does not also switch the user, it just lets you avoid specifying the schema in your SQL statements during the session. In Oracle Database, the user and the schema are separate, but every user has the schema.

However you can satisfy your needs implementing roles. You provide privileges to the role and than can use SET ROLE statement during your session to switch between the roles with appropriate set of privileges.

Upvotes: 6

Related Questions