9 Replies Latest reply on Jan 22, 2019 3:38 PM by 2773669

    Session Monitor - Privileges Needed to Use

    user10212135

      Hello,

      I have Developers who want to use the Monitor Session features in SQL Developer.  I have created a role named browse_session_role and assigned these privs:

      grant select on SYS.V_$PROCESS to browse_session_role;

      grant select on SYS.V_$SESSION to browse_session_role;

      grant select on SYS.V_$TRANSACTION to browse_session_role;

      grant select on SYS.V_$SESSION_LONGOPS to browse_session_role;

      grant select on SYS.V_$LOCK to browse_session_role;

      grant select on SYS.V_$SESSTAT to browse_session_role;

      grant select on SYS.V_$STATNAME to browse_session_role;

      grant select on SYS.V_$ACCESS to browse_session_role;

      grant select on SYS.V_$ROLLNAME to browse_session_role;

      grant select on SYS.V_$SQLTEXT_WITH_NEWLINES to browse_session_role;

      grant select on SYS.V_$SQL to browse_session_role;

      grant select on SYS.V_$OPEN_CURSOR to browse_session_role;

      grant select on SYS.V_$SESSION_WAIT to browse_session_role;

      grant select on SYS.V_$SESSION_EVENT to browse_session_role;

      grant select on SYS.V_$SESSION_CONNECT_INFO to browse_session_role;

      grant select on SYS.V_$EVENT_NAME to browse_session_role;

      grant select on SYS.V_$SESS_IO to browse_session_role;

      grant select on SYS.V_$EVENTMETRIC to browse_session_role;

      grant select on SYS.DBA_ROLLBACK_SEGS to browse_session_role;

       

      Still getting the ORA-00942 error when trying to run the Monitor Sessions. 

       

      I have read that the solution is to grant the user the select_any_dictionary privilege and be done with it. I have concerns about the wisdom of that ... Comments anyone ???

       

      Questions:

      1. Will Monitor Sessions work with a  homegrown role ?

      2. if so, what additional grants am I missing?

      3. if not, is granting select_any_dictionary the only and best solution ?

       

      Much thanks

      catharine

        • 1. Re: Session Monitor - Privileges Needed to Use
          user10212135

          UPDATE: I found an archived conversation from 2014 detailing how to copy and edit the Sessions Report... It's doable but seems painful... Any newer solutions to this issue ?? 

          • 2. Re: Session Monitor - Privileges Needed to Use

            Still getting the ORA-00942 error when trying to run the Monitor Sessions.

            . . .

            I found an archived conversation

            I know you meant well but just telling us an exception number or that you 'found' something isn't going to do most people much good in understanding what you are talking about.

             

            When you post you need to SHOW US:

             

            1. WHAT you do

            2. HOW you do it

            3. WHAT results you get

             

            That includes showing the actual exception message you are getting. And when you 'find' something then post a link to it so people can read it for themselves.

             

            If you open the log pane you can see the SQL that sql developer uses  to query the session information.

             

            For some operations privileges need to be granted DIRECTLY to the user - for others you can use grant them to a role and then grant the role to the user.

             

            The role has to be ACTIVE for the user or it won't be used - just assigning it isn't enough.

             

            How do you know the list of privileges you granted is enough? If you miss even one you will get the exception you got. If the privileges need to be granted directly you will get the message you got.

             

            There are also differences between SELECT ANY DICTIONARY (which is a System Privilege) and SELECT_CATALOG_ROLE which is a role.

            https://hemantoracledba.blogspot.com/2014/02/the-difference-between-select-any.html

             

            Now - back to your issue

            I have created a role named browse_session_role and assigned these privs:

            Why?

             

            Why did you do that when there is already (see link) both a system privilege and an Oracle role that will do the job?

             

            Notwithstanding 'good intentions' you should ALWAYS use available Oracle supplied functionality unless you have a GOOD reason not to AND you know what you how to do it better yourself without opening some GAPING security holes.

             

            I suggest you rethink your strategy and use Oracle's solutions.

            • 3. Re: Session Monitor - Privileges Needed to Use
              user10212135

              Mr/Ms RP0428,

              I hope perhaps you have misunderstood my post and that you did not intend our remarks to come across quite as offensively as they did.

               

              Firstly, I agree that screen shots are useful and perhaps would have been helpful if I was asking about an obscure functionality, but the Monitor Sessions tool is a well known tool and I [wrongly] assumed that anyone interested in addressing my issue would be familiar with what it is.

               

              Secondly, I am somewhat aware of the differences between system privileges and roles, having been an Oracle DBA since the early 1990s. 

               

              Thirdly, I have searched the web somewhat thoroughly on the issue of granting a system privilege vs creating or granting a role and have read many of the posts, one of which you reference.  The issue as to whether the select any dictionary privilege should be freely granted is up for debate.  It is a powerful privilege which up until recently included the ability to see table with password data in it...Additionally, and please feel free to correct me, I believe its precise underlying privileges are not documented.

               

              I adhere to the principle of giving developers the least privileges possible and the most explicit ones at that.  I believe that many experienced DBAs would agree.

               

              The post about modifying the Sessions report which I alluded to is this:

              http://www.thatjeffsmith.com/archive/2012/07/customizing-monitor-sessions-in-oracle-sql-developer/

               

              I am in the business of helping people to do their jobs without giving them tools that allow them to [accidentally] shoot themselves in the foot.  I'd like to see more of my developer team use SQL Developer -- I've wasted too much time trying to get 7 different versions of TOAD up and running and kicking the orphaned sessions out of the system.

               

              If you have something concrete and useful about what object privileges besides what I am currently granting are needed, I would like to hear about it. 

               

              Much thanks

              catharine

              • 4. Re: Session Monitor - Privileges Needed to Use

                There was absolutely NOTHING 'offensive' about my reply. Perhaps you are just projecting your own inner demons onto it?

                If you have something concrete and useful about what object privileges besides what I am currently granting are needed, I would like to hear about it.

                I already provided the best advice you are going to get and you didn't refute it or even comment on it.

                Secondly, I am somewhat aware of the differences between system privileges and roles, having been an Oracle DBA since the early 1990s.

                We can only respond based on what you post. And what you posted seemed to indicate you don't know how to conduct basic testing about privileges and roles.

                 

                All I can do is repeat what I said earlier and maybe after a cup of coffee you will see the value in it:

                 

                1. open the log window

                2. review the query sql developer is using for the monitor sessions.

                3. identify the tables/views the query is using

                4. execute the query manually with a new/dumb user to test what privileges are needed and how they should be granted

                I'd like to see more of my developer team use SQL Developer

                Most developer teams have NO BUSINESS monitoring sessions. You should either have a team lead responsible for that or set up a single VM workstation that the developers can use to monitor them. That way you don't have to give the developers any grants at all except to view the screen doing the monitoring.

                • 5. Re: Session Monitor - Privileges Needed to Use
                  thatJeffSmith-Oracle

                  "Most developer teams have NO BUSINESS monitoring sessions.'

                   

                  Interesting opinion. But that's not the question. It's how, not should.

                   

                  Your advice is technically correct

                  • Open the log panel
                  • Use the screen
                  • Capture the views referenced in the selects, as shown in the statements page
                  • Grant selects on said views to the role

                   

                  I would say, yes, do this.

                   

                  There's no built-in functionality to grant privs to use this screen. It's a reasonable question. And instead of answering it directly, I also like showing how to get the info so you can answer it for yourself.

                   

                  The general tone on our forums is not a welcoming one. I'd like to see folks try being more open and receptive to helping others without the judgement of skills and experience.

                   

                  As Dalton said in Roadhouse, just be nice.

                  • 6. Re: Session Monitor - Privileges Needed to Use
                    user10212135

                    Thanks Jeff,

                     

                    Your clarification is appreciated.

                     

                    -catharine

                    • 7. Re: Session Monitor - Privileges Needed to Use
                      user10212135

                      Hello Again,

                      thanks for pointing out how to use the functionality in SQL Dev to chase down the queries and tables being used. 

                       

                      --catharine

                      • 8. Re: Session Monitor - Privileges Needed to Use
                        2773669

                        Thanks for this info.  I did not know about the Log panel until reading this thread.  I can see how it will be a really useful general tool to figure out access issues with SQL Developer itself.

                         

                        That being said, I don't understand why certain database tools (and I'm including TOAD here) don't provide canned scripts that grant the privileges needed to use functions that are on their tool's menus.  At the very least, the privileges should be listed in the Help documentation.  Instead, it is necessary to spend a half an hour or more querying the internet for answers.  Multiply that by the number of users out there and the lost time is substantial.

                         

                        Worse, most users are under schedule pressure and cannot afford to get side-tracked on a tool issue.  Further, in the case of "Monitor Sessions", many novice-to-intermediate developers who get a privilege error after clicking "Monitor Sessions" are going to assume that substantial DBA privileges are required and give up right there.  Like me, they are not going to know about the Log pane either.  Maybe months or years later, when they have more experience, they might get around to digging further.  In the meantime, this tool, which could help resolve many the issues they encounter every day, goes unused.

                         

                        Just my opinion, but I think it should be easier to find this stuff out.  I think the Help topic for every menu item that requires anything other than the default developer privileges should list what those privileges are.  Getting a privilege error should just be a speed bump rather than a dead end or time sink.

                        • 9. Re: Session Monitor - Privileges Needed to Use
                          2773669

                          For what it's worth, here is what I ended up with, building on Catharine's list.

                           

                          This comes with a couple of caveats:

                          1. This has not been tested on a brand new user account.  The privileges were added to a schema owner account, which may have already had certain required privileges.
                          2. Privileges for "kill session" are not included.

                           

                          -- Set username here.

                          define vs_user_nm=XYZ

                           

                          -------------------------------------------------------------------------------

                          -- For "Monitor Sessions".

                          -- NOTE: The views are "fixed", which means that privileges cannot be granted

                          -- on them.  Privileges must be granted on the underlying tables.

                          -------------------------------------------------------------------------------

                          grant select on V_$ACCESS                        to &vs_user_nm.;

                          grant select on V_$EVENTMETRIC                   to &vs_user_nm.;

                          grant select on V_$EVENT_NAME                    to &vs_user_nm.;

                          grant select on V_$LOCKED_OBJECT                 to &vs_user_nm.;

                          grant select on V_$LOCK                          to &vs_user_nm.;

                          grant select on V_$OPEN_CURSOR                   to &vs_user_nm.;

                          grant select on V_$PROCESS                       to &vs_user_nm.;

                          grant select on V_$ROLLNAME                      to &vs_user_nm.;

                          grant select on V_$SESSION                       to &vs_user_nm.;

                          grant select on V_$SESSION_CONNECT_INFO          to &vs_user_nm.;

                          grant select on V_$SESSION_EVENT                 to &vs_user_nm.;

                          grant select on V_$SESSION_LONGOPS               to &vs_user_nm.;

                          grant select on V_$SESSION_WAIT                  to &vs_user_nm.;

                          grant select on V_$SESSTAT                       to &vs_user_nm.;

                          grant select on V_$SESS_IO                       to &vs_user_nm.;

                          grant select on V_$SQL                           to &vs_user_nm.;

                          grant select on V_$SQL_PLAN                      to &vs_user_nm.;

                          grant select on V_$SQLCOMMAND                    to &vs_user_nm.;

                          grant select on V_$SQLTEXT_WITH_NEWLINES         to &vs_user_nm.;

                          grant select on V_$STATNAME                      to &vs_user_nm.;

                          grant select on V_$TRANSACTION                   to &vs_user_nm.;

                           

                           

                          -- For RAC environments.

                          grant select on GV_$ACCESS                       to &vs_user_nm.;

                          grant select on GV_$EVENTMETRIC                  to &vs_user_nm.;

                          grant select on GV_$EVENT_NAME                   to &vs_user_nm.;

                          grant select on GV_$LOCK                         to &vs_user_nm.;

                          grant select on GV_$LOCKED_OBJECT                to &vs_user_nm.;

                          grant select on GV_$OPEN_CURSOR                  to &vs_user_nm.;

                          grant select on GV_$PROCESS                      to &vs_user_nm.;

                          -- No "GV" version.

                          -- grant select on GV_$ROLLNAME                     to &vs_user_nm.;

                          grant select on GV_$SESSION                      to &vs_user_nm.;

                          grant select on GV_$SESSION_CONNECT_INFO         to &vs_user_nm.;

                          grant select on GV_$SESSION_EVENT                to &vs_user_nm.;

                          grant select on GV_$SESSION_LONGOPS              to &vs_user_nm.;

                          grant select on GV_$SESSION_WAIT                 to &vs_user_nm.;

                          grant select on GV_$SESSTAT                      to &vs_user_nm.;

                          grant select on GV_$SESS_IO                      to &vs_user_nm.;

                          grant select on GV_$SQL                          to &vs_user_nm.;

                          grant select on GV_$SQL_PLAN                     to &vs_user_nm.;

                          grant select on GV_$SQLCOMMAND                   to &vs_user_nm.;

                          grant select on GV_$SQLTEXT_WITH_NEWLINES        to &vs_user_nm.;

                          grant select on GV_$STATNAME                     to &vs_user_nm.;

                          grant select on GV_$TRANSACTION                  to &vs_user_nm.;

                           

                           

                          grant select on SYS.DBA_LOCK                     to &vs_user_nm.;

                          grant select on SYS.DBA_ROLLBACK_SEGS            to &vs_user_nm.;

                           

                           

                          -------------------------------------------------------------------------------

                          -- For other queries re system configuration and performance.

                          -------------------------------------------------------------------------------

                          grant select on SYS.V_$SQLTEXT                       to &vs_user_nm.;

                           

                          grant select on SYS.GV_$SQLTEXT                      to &vs_user_nm.;