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:
- A transaction cannot span multiple containers. If the set of actions you must perform in the target container requires a transaction, consider using an autonomous transaction and perform a commit or rollback as the last action.
- SET ROLE statements are not allowed.
- All operations are isolated within a PDB.
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.
