Access to V$RESTORE_POINT

I have recently been working in an environment that uses guaranteed restore points (GRPs) more than anywhere else I’ve ever worked and therefore I’ve found myself querying V$RESTORE_POINT a lot. Or at least I’ve been trying to. The client also takes onboard the wisdom that granting SELECT_CATALOG_ROLE to those that don’t need all it has to offer is not a good idea. Therefore they have created a new role to provide the level of access that is deemed appropriate for users such as me in their production and UAT environments. When I first tried to access V$RESTORE_POINT in such an environment I didn’t properly absorb the error in front of me:

select name from v$restore_point
                 *
ERROR at line 1:
ORA-01031: insufficient privileges

Initially I incorrectly assumed that my user did not have access to V_$RESTORE_POINT, but of course that would have resulted in “ORA-00942: table or view does not exist”. When I checked and found that I did have SELECT on V_$RESTORE_POINT through the custom role I realised that I was going to have to look a little deeper.

The weeks went by and those weeks turned into months…

Last week I finally found the time to take a proper look. What I discovered surprised me and I’m still looking to understand why Oracle would choose to implement access to [G]V$RESTORE_POINT in this way…

The output below and accompanying comments demonstrate how access to V$RESTORE_POINT is controlled

SYS can SELECT from V$RESTORE_POINT (no surprises):

SYS@orcl> select name from v$restore_point;

NAME
--------------------------------------------------------------------------------
PRE_SELECT_CATALOG_ROLE_TEST

SYS@orcl> 

A user with only CREATE SESSION and SELECT_CATALOG_ROLE can SELECT from V$RESTORE_POINT (no big surprises)

SYS@orcl> create user scr_test identified by scr_test01;

User created.

SYS@orcl> grant create session to scr_test;

Grant succeeded.

SYS@orcl> grant select_catalog_role to scr_test;

Grant succeeded.

SYS@orcl> conn scr_test/scr_test01
Connected.
SCR_TEST@orcl> select name from v$restore_point;

NAME
--------------------------------------------------------------------------------
PRE_SELECT_CATALOG_ROLE_TEST

SCR_TEST@orcl> 

So now for something a little radical…

Drop the SELECT_CATALOG_ROLE and recreate it with no privileges, then grant the empty role to SCR_TEST

SCR_TEST@orcl> conn / as sysdba
Connected.
SYS@orcl> drop role select_catalog_role;

Role dropped.

SYS@orcl> create role select_catalog_role;

Role created.

SYS@orcl> grant select_catalog_role to scr_test;

Grant succeeded.

SYS@orcl>

… and then test SELECT from V$RESTORE_POINT as SCR_TEST

SYS@orcl> conn scr_test/scr_test01
Connected.
SCR_TEST@orcl> select name from v$restore_point;

NAME
--------------------------------------------------------------------------------
PRE_SELECT_CATALOG_ROLE_TEST

SCR_TEST@orcl> 

What you read above isn’t the first thing I tried when attempting to work out what privileges I would need in order to access V$RESTORE_POINT. My earlier activities involved SQL Trace, but that didn’t get me too far as I found myself in numerous recursive queries on tables like OBJAUTH$. Deciphering that will have to wait for another day. Until then it seems sufficient to know that you need to at least be granted a role named SELECT_CATALOG_ROLE in order to select from v$restore_point, even if that role has no privileges itself!

This has similarities to a post from Neil on SELECT_CATALOG_ROLE being hardcoded into the definition of KU$_HTABLE_VIEW.

SELECT_CATALOG_ROLE isn’t hardcoded into the definition of GV$RESTORE_POINT, which is based on X$KCCRSP and X$KCCNRS, so I can only assume that it is hardcoded into these “fixed tables”.

According to this un(officially)published Oracle support note X$KCC is Kernel Cache Control file management and maybe RSP is ReStore Point and NRS is Named ReStore point?