Search code examples
oracle-databasesessionprivilegesalter

Alter session set current_schema privilege

Basicly i have an java application that uses oracle as a data source. Now my application connects with a user and uses connected user's schema for table creation and etc...

Now i have a requirment that my application also should work under another schema.

So i have to alternatives.

1 - Change my table names with a prefix like

select * from other_schema.table

2 - Altering the session before running any query like

alter session set current_schema=other_schema

I am curios about is there any possibilty that alter session privilege can be revoked by dba's.

Oracle documentation says that

You do not need any privileges to perform the other operations of this statement unless otherwise indicated.

https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_2012.htm

Please note that my app user will have all privileges for other_schema.

My application will be running many envoriments.

So going with first appoach, it looks like more safe but it can take long time.

Second one is faster but seems like tricky.

Solution

  • Yes they do. There is a role called CONNECT. This role usually (depending on version) gives you two system privileges:

    • create session
    • alter session

    when your user has only create session privilege granted directly then you can not use any alter session ... statement.

    Se Oracle docs: Addressing The CONNECT Role Change

    Note that the ALTER SESSION privilege is required for setting events. Very few database users should require the alter session privilege.

    SQL> ALTER SESSION SET EVENTS ........

    The alter session privilege is not required for other alter session commands.

    SQL> ALTER SESSION SET NLS_TERRITORY = FRANCE;

    So it really depends on Oracle version.