Forum Stats

  • 3,817,249 Users
  • 2,259,295 Discussions
  • 7,893,712 Comments

Discussions

Oracle 21c: select permission on v_$session

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:

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,335 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.

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,335 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.

  • cormaco
    cormaco Member Posts: 1,909 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.
    
  • 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;