Oracle Alter session like in postgres

If you are looking for looking for

alter session set curent_schema=ABCD; 

kind of statement in postgres we have 


Example :

dvdrental=# create user test password 'test';
CREATE ROLE
                       

If you want to inherit permissions of another role you can do :


dvdrental=# alter role test  CREATEROLE CREATEDB;
ALTER ROLE

dvdrental=# SELECT SESSION_USER, CURRENT_USER;
 session_user | current_user
--------------+--------------
 postgres     | postgres
(1 row)

If you want to change user to another user without reconnecting (like sudo in Linux) :


 dvdrental=# SET SESSION AUTHORIZATION 'test';
SET
dvdrental=> SELECT SESSION_USER, CURRENT_USER;
 session_user | current_user
--------------+--------------
 test         | test
(1 row)

dvdrental=>

Comments

Post a Comment

Popular posts from this blog

switching between users in postgres

SET SESSION AUTHORIZATION SET [ SESSION | LOCAL ] SESSION AUTHORIZATION username SET [ SESSION | LOCAL ] SESSION AUTHORIZATION DEFAULT RESET SESSION AUTHORIZATION This command sets the session user identifier and the current user identifier of the current SQL session to be  username . The user name may be written as either an identifier or a string literal. Using this command, it is possible, for example, to temporarily become an unprivileged user and later switch back to being a superuser. Example: C:\WINDOWS\system32> psql -U postgres psql (10.11) WARNING: Console code page (437) differs from Windows code page (1252)          8-bit characters might not work correctly. See psql reference          page "Notes for Windows users" for details. Type "help" for help. postgres=# select current_user,session_user;  current_user | session_user --------------+--------------  postgres     | pos...
Read more

Connecting to PostgresSQL database

After logging into postgres user, from cmdline type psql. bash-4.1$ psql could not change directory to "/var/lib/mysql/TESTDB" psql (8.4.18) Type "help" for help. Some basic cmds: postgres=# show databases;   -- This list databases postgres=# List databases      -- This list databases -- Listing database in postgres ##     \l will give you basic information of databases in instance postgres-# \l                                   List of databases    Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges  -----------+----------+----------+-------------+-------------+-----------------------  postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |  template0 | postgres | UTF8     | en_US.UTF...
Read more