RMAN Virtual Private Catalog

In Oracle 11g, we can grant restricted access to the RMAN catalog to some users so that they can only access a limited set of databases that are registered in the RMAN catalog.

This is done by creating a Virtual Private Catalog which in turn will grant a particular user read/write access to only that user’s RMAN metadata. We can in this way create a number of multiple recovery catalog users each seeing only having access to a limited set of databases while the base recovery catalog owner has access to the entire metadata.

For example, in the RMAN catalog owned by user RMAN11D, there a a number of databases registered, but we would like to restrict access to the APEX database to a single user – RMAN_APEX.

So we need to first create a user in the database which houses the base RMAN catalog, grant that user the RECOVERY_CATALOG_OWNER role and then the ‘catalog for database …..’ privilege.

That user will then create a virtual catalog and when he connects to that catalog, we will see that he can only access the one database which he has been granted access for which is the APEX database.

The original RMAN catalog owner is RMAN11D – note the databases which are currently registered:

SQL> select name from rc_database;

NAME
--------
SID1D
SID1S
SID1A
APEX
SID1T

Create the Virtual Catalog User – RMAN_APEX

SQL> create user rman_apex identified by rman_apex
  2   default tablespace RMAN11D
  3   temporary tablespace temp
  4  quota unlimited on rman11d;

User created.

SQL>  grant recovery_catalog_owner to rman_apex;

Grant succeeded.

Connect to catalog as catalog owner and grant permissions on the one database – APEX

[PROD] emrep:/u01/oracle > rman target / catalog rman11d/d11rman@rcatd

Recovery Manager: Release 11.1.0.6.0 - Production on Tue Dec 1 10:34:33 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: EMREP (DBID=3892233981)
connected to recovery catalog database

RMAN> grant catalog for database apex to rman_apex;

Grant succeeded.

Connect now as the user RMAN_APEX and create the Virtual Private Catalog

[PROD] emrep:/u01/oracle > rman target / catalog rman_apex/rman_apex@rcatd

Recovery Manager: Release 11.1.0.6.0 - Production on Tue Dec 1 10:39:56 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: EMREP (DBID=3892233981)
connected to recovery catalog database

RMAN> create virtual catalog;

found eligible base catalog owned by RMAN11D
created virtual catalog against base catalog owned by RMAN11D

If we connect as the original RMAN catalog owner we can see all the registered databases

[PROD] emrep:/u01/oracle > rman target / catalog rman11d/d11rman@rcatd

RMAN> list incarnation;

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
72209   72226   APEX     1312143933       PARENT  1          07-NOV-07
72209   72210   APEX     1312143933       CURRENT 15653492933 04-AUG-09
17258   17259   SID1T    2951173163       CURRENT 1          08-OCT-09
761     762     SID1S    3097605603       CURRENT 1          08-SEP-09
2139    2140    SID1A    3639578917       CURRENT 1          16-SEP-09
1       2       SID1D    3743031640       CURRENT 1          27-AUG-09

Note that only one database is registered in this catalog when we connect as RMAN_APEX

rman target / catalog rman_apex/rman_apex@rcatd

RMAN> list incarnation;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
72209   72226   APEX     1312143933       PARENT  1          07-NOV-07
72209   72210   APEX     1312143933       CURRENT 15653492933 04-AUG-09