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.
- Oracle Multitenant DB 4 : Parameters/SGA/PGA management in CDB-PDB - July 18, 2020
- Details of running concurrent requests in Oracle EBS - May 26, 2020
- Oracle Multitenant DB 3 : Data Dictionary Architecture in CDB-PDB - March 20, 2020
Well-set DuDE.