How to get information from multiple PDBs

Not so long ago, I was faced with a request to collect information from several schemas located in multiple CDB databases. This can be done by connecting to each CDB, switching to a particular PDB, and retrieving the required information from there. However, in environments where the number of CDB databases (or PDBs in CDB databases) is noticeably big, some level of automation is required.

A simple script can be created which would contain a set of command which first finds the names of PDBs in a database, then executes “alter session set container…” and then execute the required query, like “select col1, col2 from t1…”
In this context, we immediately face the “ORA-01031: insufficient privileges” error while switching between PDBs using a PL/SQL procedure. This is well explained in the white paper “Security Concepts in Oracle Multitenant” (https://www.oracle.com/technetwork/database/multitenant/learn-more/multitenant-security-concepts-12c-2402462.pdf). One of the explanations is (from the above-mentioned document) “When the CONNECT role is granted to a local user inside a PDB the user does not automatically inherit the privilege to move to another container.” To resolve this issue, the following technique can be used based on the DBMS_SQL package (as described in https://docs.oracle.com/database/121/ADMIN/cdb_admin.htm#ADMIN14185). And, of course, several limitations/considerations exists:

In the below example, I queried several PDBs in all multitenant databases on a server to find information about some privileges for a particular table:

#!/bin/sh -x

# List databases
DB=`ps -ef | grep -v grep | grep ora_lgwr_ | awk '{print $8}' | awk '{ FS="_"; print $3 }'`;DB=`echo $DB`
NODE_NAME=`hostname`
LOGFILE=/tmp/check_privs_$NODE_NAME.log

touch $LOGFILE
if [ "$?" -ne 0 ]; then
  echo "Can't create logfile $LOGFILE" >&2
  exit 2
fi


  for INST in $DB
  do
    if [ "$INST" != " " ]; then
      . oraenv <<< $INST >/dev/null 2>&1
OUT=`echo "
  set feed off
  set lines 200
  set pages 200
  set serveroutput on size unlimited
  whenever sqlerror exit 5
  show parameter db_name
  declare
 cursor c1 is select CON_ID, NAME from v\\$pdbs where OPEN_MODE='READ WRITE';
cur integer;

dummy integer;
v_statement     	VARCHAR2(2000);
grantor 		VARCHAR2(20);
grantee 		VARCHAR2(20);
privilege       	VARCHAR2(30);

begin

v_statement:='select grantor, grantee, privilege from dba_tab_privs where table_name=''TABLE_1''';


cur:=dbms_sql.open_cursor;

for i in c1
 loop
   dbms_output.put_line('PDB:'||i.name);

    dbms_sql.parse(
     c=>cur,
     statement=>v_statement,
     Container=>i.name,
     language_flag=>dbms_sql.native);

  dbms_sql.define_column(cur,1,grantor,20);
  dbms_sql.define_column(cur,2,grantee,20);
  dbms_sql.define_column(cur,3,privilege,30);

 dummy := dbms_sql.execute(cur);
LOOP
    EXIT WHEN dbms_sql.fetch_rows(cur) = 0;

    dbms_sql.column_value(cur,1,grantor);
    dbms_sql.column_value(cur,2,grantee);
    dbms_sql.column_value(cur,3,privilege);
    dbms_output.put_line(grantor||'.TABLE_1 is granted for '||privilege||' to '||grantee);

  END LOOP;
end loop;
 dbms_sql.close_cursor(cur);
end;
/" | sqlplus -s "/ as sysdba"`;

  echo "$OUT" >> $LOGFILE
fi
  done

As a result, the log file contains the following output:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      DB1
PDB:PDB1
SCHEMA1.TABLE_1 is granted for SELECT to ROLE_1
PDB:PDB2
SCHEMA2.TABLE_1 is granted for SELECT to ROLE_1
PDB:PDB3
SCHEMA3.TABLE_1 is granted for SELECT to ROLE_4

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      DB2
PDB:PDB4
SCHEMA4.TABLE_1 is granted for SELECT to ROLE_1
PDB:PDB5
SCHEMA1.TABLE_1 is granted for SELECT to ROLE_5

Probably the script functionality can be extended. For example, by adding more flexibility and getting a more generic version, the idea is clearly seen:
dbms_sql.open_cursor => dbms_sql.parse => dbms_sql.define_column => dbms_sql.column_value => dbms_output.put_line.