Skip to content

RMAN: GRANTING RESTRICTED ACCESS USING VIRTUAL PRIVATE CATALOG

You can grant a user restricted access to the base recovery catalog by granting that user read/write access only to that user’s RMAN metadata, also known as a virtual private catalog.

FOR EXAMPLE:

Say you have two teams, one is handling Production instances and other is taking care of Non-Production Instances. However all databases are connected to single RMAN database. You don’t want Non-Production team to look at RMAN production data, so you grant the Non-production team a restricted access to the base recovery catalog.

 

Example based on Oracle Database 11gR2 (11.2.0.4)

Production Database: PROD

Non-Production Database: BRIJ

RMAN Catalog Database: RMANDB

base recovery catalog owner: rman

virtual private catalog owner: rman_non_prod (this user will be used for backup/recovey of Non-Production database BRIJ)

 

STEP 1: CREATE THE USER WHO WILL OWN THE NEW VIRTUAL PRIVATE CATALOG

 

In Catalog Database RMANDB,

SYS@rmandb > create user rman_non_prod identified by rman_non_prod

temporary tablespace temp
default tablespace CATALOGTBS
quota unlimited on CATALOGTBS;

User created.

STEP 2: GRANT THE NEW USER THE RECOVERY_CATALOG_OWNER ROLE

In Catalog Database RMANDB,

SYS@rmandb > grant recovery_catalog_owner to rman_non_prod;

Grant succeeded.

 

STEP 3: GRANT RESTRICTED ACCESS (VIRTUAL PRIVATE CATALOG ACCESS) TO NEW USER

Connect to the recovery catalog database RMANDB as the base recovery catalog owner (rman), and grant the new user rman_non_prod restricted access (virtual private catalog access) to just one database (BRIJ),from the base recovery catalog.

$ rman

Recovery Manager: Release 11.2.0.4.0 – Production on Sat Feb 15 17:41:43 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

RMAN> connect catalog rman/rman@rmandb

connected to recovery catalog database

RMAN> grant catalog for database BRIJ to rman_non_prod;

Grant succeeded.
The catalog for database privilege allows the user rman_non_prod to access the catalog metadata pertaining to the BRIJ database.

 

STEP 4: CREATE THE VIRTUAL PRIVATE CATALOG

Now that the virtual private catalog owner has the catalog for database privilege, that user can log in to the base recovery catalog and create the virtual private catalog:

$ rman

Recovery Manager: Release 11.2.0.4.0 – Production on Sat Feb 15 17:45:32 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

RMAN> connect catalog rman_non_prod/rman_non_prod@rmandb

connected to recovery catalog database

RMAN> create virtual catalog;

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

In this step, Oracle process will create a set of views and synonyms based on the central or base recovery catalog for the virtual private catalog owner. These views and synonyms are copied to the schema of the virtual catalog owner.

 

STEP 5: TESTING

 

You can confirm that the user rman_non_prod can access only the non production database BRIJ (and not the other production databases PROD in the base recovery catalog) by issuing the following command:

$ rman

Recovery Manager: Release 11.2.0.4.0 – Production on Sat Feb 15 17:45:32 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

RMAN> connect catalog rman_non_prod/rman_non_prod@rmandb

RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
——- ——- ——– —————- — ———- ———-
1 16 BRIJ 1279650270 PARENT 1 24-AUG-2013 11:37:30
1 2 BRIJ 1279650270 CURRENT 925702 03-FEB-2014 11:07:13
If you log in as the owner of the base recovery catalog owner RMAN and issue the list incarnation command, you’ll see the production database also in the base recovery catalog as well, as shown in the following output:

$ rman

Recovery Manager: Release 11.2.0.4.0 – Production on Sat Feb 15 17:55:24 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

RMAN> connect catalog rman/rman@rmandb

RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
——- ——- ——– —————- — ———- ———-
1 16 BRIJ 1279650270 PARENT 1 24-AUG-2013 11:37:30
1 2 BRIJ 1279650270 CURRENT 925702 03-FEB-2014 11:07:13
12 150 PROD 3533598612 PARENT 1 14-NOV-2013 01:35:10
12 150 PROD 3533598612 CURRENT 909437 25-JAN-2014 01:04:33

You can see that only the owner of the base recovery catalog can view the metadata for all the databases registered in that catalog, unlike the owner of the virtual private catalog, who is restricted to a specific database or databases.

 

Brijesh Gogia

One Comment

  1. ShanDBA ShanDBA

    Well-set DuDE.

Leave a Reply