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?
