Forum Stats

  • 3,827,324 Users
  • 2,260,761 Discussions
  • 7,897,203 Comments

Discussions

Categories

Oracle 21c: select permission on v_$session

User_KC6Y3
User_KC6Y3 Member Posts: 72 Green Ribbon
in General Database Discussions

I am migrating an Oracle 11g database to Oracle 21c.

In Oracle 11g, I was able to issue this sql:

GRANT select FROM v$session to <user>.

I use this in my Triggers since I commonly do this in Oracle 11g:

   SELECT machine,

      program,

      USER

    FROM sys.v_$session

   WHERE audsid = USERENV('SESSIONID');


When I try this SQL in Oracle 21c connected as SYS user, I get this error:

GRANT SELECT ON v$session TO C##Agsoft2021

Error report -

ORA-02030: can only select from fixed tables/views

02030. 00000 - "can only select from fixed tables/views"

*Cause:  An attempt is being made to perform an operation other than

      a retrieval from a fixed table/view.

*Action:  You may only select rows from fixed tables/views.

I was trying to understand the error "can only select from fixed tables/views" but don't understand this.

Did something change in Oracle 21c that I missed??

Thanks

Tagged:
· Share on TwitterShare on Facebook

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,482 Red Diamond
    edited Jan 10, 2022 7:42PM Answer ✓ 
    SQL> grant select on v$session to scott;
grant select on v$session to scott
                *
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

SQL> grant select on v_$session to scott;

Grant succeeded.

SQL>

    Now scott can query v$session.

    SY.

    · Share on TwitterShare on Facebook

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,482 Red Diamond
    edited Jan 10, 2022 7:42PM Answer ✓ 
    SQL> grant select on v$session to scott;
grant select on v$session to scott
                *
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

SQL> grant select on v_$session to scott;

Grant succeeded.

SQL>

    Now scott can query v$session.

    SY.

    · Share on TwitterShare on Facebook
  • cormaco
    cormaco Member Posts: 1,943 Silver Crown

    The error message is misleading. The error is to grant on the v$session view. Use v_$session instead:

    grant select on v$session to TEST;

ORA-02030: can only select from fixed tables/views
02030. 00000 -  "can only select from fixed tables/views"
*Cause:    An attempt is being made to perform an operation other than
           a retrieval from a fixed table/view.
*Action:   You may only select rows from fixed tables/views.

grant select on v_$session to TEST;

Grant succeeded.
    · Share on TwitterShare on Facebook
  • User_H3J7U
    User_H3J7U Member Posts: 1,062 Gold Trophy 
    select sys_context('userenv', 'host') machine,
       sys_context('userenv', 'client_program_name') program
from dual;
    · Share on TwitterShare on Facebook