2

See the example bellow. User is created with dba (but not sysdba) privs. He's allows to access v$session (which is public synonym to view in schema SYS, having different name).

When executing alter session set current_schema=SYS;, the public synonym does not resolve anymore.

SQL> create user xy identified by xy;
User created.

SQL> grant connect, resource, dba to xy;    
Grant succeeded.

SQL> connect xy/xy;
Connected.
SQL> select count(1) from v$session;

  COUNT(1)
----------
        96

SQL> alter session set current_schema=SYS;

Session altered.

SQL> select count(1) from v$session;
select count(1) from v$session
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

Update: I understand, that this is not a problem for most of the applications. In GUI application I work on a user can execute any SQL in a worksheet, he also can switch into any schema (using combo box). But then when the connection is returned into connection pool this connection can not work anymore. When current schema is switched to SYS embedded application queries raise an error.

I just do not understand rationale for this behavior.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.