脚本目标：列出所有非系统用户的系统、对象权限以及所拥有的角色.

1、脚本内容
[oracle@hisdb1 scripts]$ vi user_priv.sh
#!/bin/bash
MT_HOME="/home/oracle/monitor"
DB_SID=`ps -ef | grep ora_pmon | grep -v grep | awk -F'_' '{print $3}'`
export ORACLE_SID=$DB_SID
pdbs="$MT_HOME/tmp/pdbs.txt"
sin_non_sysuser="$MT_HOME/tmp/sin_non_sysuser.txt"
pdb_non_sysuser="$MT_HOME/tmp/pdb_non_sysuser.txt"
sys_privs="$MT_HOME/tmp/sys_privs.txt"
tab_privs="$MT_HOME/tmp/tab_privs.txt"
user_role="$MT_HOME/tmp/user_role.txt"
db_11g_user="$MT_HOME/tmp/db_11g_user.txt"
total_user="$MT_HOME/tmp/total_user.txt"
total_user_privs="$MT_HOME/tmp/total_user_privs.txt"
tmp_pdb_non_sysuser="$MT_HOME/tmp/tmp_pdb_non_sysuser.txt"
test -d /home/oralce/monitor/tmp || mkdir -p /home/oracle/monitor/tmp

sqlplus -S / as sysdba <<EOF > $pdbs
set linesize 190 pagesize 0;
set feedback off;
set verify off;
show pdbs
EOF
pdbname=`cat $pdbs | awk '{print $2}'`

function sin_19c_db_user() {
sqlplus -S / as sysdba <<EOF > $sin_non_sysuser
set linesize 190 pagesize 0;
set feedback off;
set verify off;
col username for a30;
select username,account_status from dba_users;
EOF
}

function pdb_19c_db_user() {
test -f $pdb_non_sysuser && > $pdb_non_sysuser
for i in `echo $pdbname`
do
echo "PDB $i's non-sysusers:" >> $pdb_non_sysuser
echo "USERNAME                       ACCOUNT_STATUS" >> $pdb_non_sysuser
sqlplus -S / as sysdba <<EOF >> $pdb_non_sysuser
set linesize 190 pagesize 0;
set feedback off;
set verify off;
col username for a30;
alter session set container=$i;
select username,account_status from dba_users;
EOF
echo "" >> $pdb_non_sysuser
done
}

function sys_privs() {
if [[ $# -eq 2 ]]; then
  user_name=$1
  pdb_name=$2
else
  user_name=$1
  pdb_name=''
fi
if [[ "x$pdb_name" = "x" ]]; then
echo "$user_name sys privileges." > $sys_privs
echo "GRANTEE                   PRIVILEGE" >> $sys_privs
sqlplus -S / as sysdba <<EOF >> $sys_privs
set linesize 190 pagesize 0;
set feedback off;
set verify off;
col privilege for a35;
col grantee for a25;
select grantee,privilege from dba_sys_privs where grantee='${user_name}';
EOF
echo "" >> $sys_privs
else
echo "$user_name sys privileges." > $sys_privs
echo "GRANTEE                   PRIVILEGE" >> $sys_privs
sqlplus -S / as sysdba <<EOF >> $sys_privs
set linesize 190 pagesize 0;
set feedback off;
set verify off;
col privilege for a35;
col grantee for a25;
alter session set container=$pdb_name;
select grantee,privilege from dba_sys_privs where grantee='${user_name}';
EOF
echo "" >> $sys_privs
fi
}

function tab_privs() {
if [[ $# -eq 2 ]]; then
  user_name=$1
  pdb_name=$2
else
  user_name=$1
  pdb_name=''
fi
if [[ "x$pdb_name" = "x" ]]; then
echo "$user_name tab privileges." > $tab_privs
echo "GRANTEE                   OWNER           TABLE_NAME                          PRIVILEGE" >> $tab_privs
sqlplus -S / as sysdba <<EOF >> $tab_privs
set linesize 190 pagesize 0;
set feedback off;
set verify off;
col privilege for a35;
col table_name for a35;
col grantee for a25;
col owner for a15;
select grantee,owner,table_name,privilege from dba_tab_privs where grantee='${user_name}';
EOF
echo "" >> $tab_privs
else
echo "$user_name tab privileges." > $tab_privs
echo "GRANTEE                   OWNER           TABLE_NAME                          PRIVILEGE" >> $tab_privs
sqlplus -S / as sysdba <<EOF >> $tab_privs
set linesize 190 pagesize 0;
set feedback off;
set verify off;
col privilege for a35;
col table_name for a35;
col grantee for a25;
col owner for a15;
alter session set container=$pdb_name;
select grantee,owner,table_name,privilege from dba_tab_privs where grantee='${user_name}';
EOF
echo "" >> $tab_privs
fi
}

function user_role() {
if [[ $# -eq 2 ]]; then
  user_name=$1
  pdb_name=$2
else
  user_name=$1
  pdb_name=''
fi
if [[ "x$pdb_name" = "x" ]]; then
echo "$user_name role privileges." > $user_role
echo "GRANTEE                   GRANTED_ROLE" >> $user_role
sqlplus -S / as sysdba <<EOF >> $user_role
set linesize 190 pagesize 0;
set feedback off;
set verify off;
col grantee for a25;
col granted_role for a25;
select grantee,granted_role from dba_role_privs where grantee='${user_name}';
EOF
echo "" >> $user_role
else
echo "$user_name role privileges." > $user_role
echo "GRANTEE                   GRANTED_ROLE" >> $user_role
sqlplus -S / as sysdba <<EOF >> $user_role
set linesize 190 pagesize 0;
set feedback off;
set verify off;
col grantee for a25;
col granted_role for a25;
alter session set container=$pdb_name;
select grantee,granted_role from dba_role_privs where grantee='${user_name}';
EOF
echo "" >> $user_role
fi
}

function single_11g_db_user() {
sqlplus -S / as sysdba <<EOF >$db_11g_user
set linesize 190 pagesize 0;
set feedback off;
set verify off;
col username for a30;
select username,account_status from dba_users;
EOF
}

jud_pdb=`cat $pdbs | grep -iw "PDB$SEED" | wc -l`
if [[ $jud_pdb -eq 1 ]];then
sin_19c_db_user 
pdb_19c_db_user
echo -e "\033[31mThe following is the status of all non-sysusers.\033[0m" > $total_user
echo "CDB's non-sysusers:" >> $total_user
echo "USERNAME                       ACCOUNT_STATUS" >> $total_user
cat $sin_non_sysuser >> $total_user
echo "" >> $total_user 
cat $pdb_non_sysuser | grep -viE "C##" >> $total_user
cat $total_user

#List total privileges detail of cdb non-sysusers.
if cat $sin_non_sysuser | awk '{print $1}' | wc -l >/dev/null; then
cdb_user=`cat $sin_non_sysuser | awk '{print $1}'`
test -f $total_user_privs && >$total_user_privs
for i in `echo $cdb_user`
do
echo -e "\033[31mThe following is the CDB's non-sysusers $i total privileges.\033[0m" >> $total_user_privs
   sys_privs "$i"
   cat $sys_privs >> $total_user_privs
   tab_privs "$i"
   cat $tab_privs >> $total_user_privs
   user_role "$i" 
   cat $user_role >> $total_user_privs
done
fi

#List total privileges detail of pdb non-sysusers.
for j in `echo $pdbname`
do
echo -e "\033[31mThe following is the PDB $j's non-sysusers total privileges.\033[0m" >> $total_user_privs
sqlplus -S / as sysdba <<EOF > $tmp_pdb_non_sysuser
set linesize 190 pagesize 0;
set feedback off;
set verify off;
col username for a20;
alter session set container=$j;
select username from dba_users where username not like 'C##%';
EOF
   for m in `cat $tmp_pdb_non_sysuser`
   do
     sys_privs "$m" "$j"
	 cat $sys_privs >> $total_user_privs
     tab_privs "$m" "$j"
	 cat $tab_privs >> $total_user_privs
     user_role "$m" "$j"   
     cat $user_role >> $total_user_privs 
   done
done
cat $total_user_privs
fi

jud_11g_db=`cat $pdbs | grep -iw "SP2-0158" | wc -l`
if [[ $jud_11g_db -eq 1 ]];then
single_11g_db_user
echo -e "\033[31mThe following is the status of 11g all non-sysusers.\033[0m" > $total_user
echo "USERNAME                       ACCOUNT_STATUS" >> $total_user
cat $db_11g_user >> $total_user
echo "" >> $total_user 
cat $total_user

    #List total privileges detail of 11g non-sysusers.
    if cat $db_11g_user | awk '{print $1}' | wc -l >/dev/null; then
    db_user=`cat $db_11g_user | awk '{print $1}'`
    test -f $total_user_privs && >$total_user_privs
    for k in `echo $db_user`
    do
    echo -e "\033[31mThe following is non-sysusers $k total privileges.\033[0m" >> $total_user_privs
       sys_privs "$k"
       cat $sys_privs >> $total_user_privs
       tab_privs "$k"
       cat $tab_privs >> $total_user_privs
       user_role "$k" 
       cat $user_role >> $total_user_privs
    done
    fi
cat $total_user_privs
fi

if [[ $jud_11g_db -eq 0 ]] && [[ $jud_pdb -eq 0 ]];then
sin_19c_db_user
echo -e "\033[31mThe following is the status of all non-sysusers in non-cdb circumstances.\033[0m" > $total_user
echo "USERNAME                       ACCOUNT_STATUS" >> $total_user
cat $sin_non_sysuser >> $total_user
echo "" >> $total_user 
cat $total_user

    #List total privileges detail of non-sysusers in non-cdb circumstances.
    if cat $sin_non_sysuser | awk '{print $1}' | wc -l >/dev/null; then
    db_user=`cat $sin_non_sysuser | awk '{print $1}'`
    test -f $total_user_privs && >$total_user_privs
    for n in `echo $db_user`
    do
    echo -e "\033[31mThe following is non-sysusers $n total privileges.\033[0m" >> $total_user_privs
       sys_privs "$n"
       cat $sys_privs >> $total_user_privs
       tab_privs "$n"
       cat $tab_privs >> $total_user_privs
       user_role "$n" 
       cat $user_role >> $total_user_privs
    done
    fi
cat $total_user_privs
fi

exit 0

2、脚本测试
2.1、多租户环境测试
The following is the status of all non-sysusers.
CDB's non-sysusers:
USERNAME             ACCOUNT_STATUS
C##LEO               OPEN

PDB PDB$SEED's non-sysusers:
USERNAME             ACCOUNT_STATUS

PDB TMISPDB's non-sysusers:
USERNAME             ACCOUNT_STATUS
PDBADMIN             OPEN
LEO                  OPEN

PDB ORCLPDB's non-sysusers:
USERNAME             ACCOUNT_STATUS
LEO                  OPEN
ALINA                OPEN

PDB HEALPDB's non-sysusers:
USERNAME             ACCOUNT_STATUS
LEO                  OPEN

The following is the CDB's non-sysusers C##LEO total privileges.
C##LEO sys privileges.
GRANTEE                   PRIVILEGE

C##LEO tab privileges.
GRANTEE                   OWNER           TABLE_NAME                PRIVILEGE

C##LEO role privileges.
GRANTEE                   GRANTED_ROLE

The following is the PDB PDB$SEED's non-sysusers total privileges.
The following is the PDB TMISPDB's non-sysusers total privileges.
PDBADMIN sys privileges.
GRANTEE                   PRIVILEGE

PDBADMIN tab privileges.
GRANTEE                   OWNER           TABLE_NAME                PRIVILEGE

PDBADMIN role privileges.
GRANTEE                   GRANTED_ROLE
PDBADMIN                  PDB_DBA

LEO sys privileges.
GRANTEE                   PRIVILEGE
LEO                       UNLIMITED TABLESPACE

LEO tab privileges.
GRANTEE                   OWNER           TABLE_NAME                PRIVILEGE

LEO role privileges.
GRANTEE                   GRANTED_ROLE
LEO                       DBA

The following is the PDB ORCLPDB's non-sysusers total privileges.
LEO sys privileges.
GRANTEE                   PRIVILEGE

LEO tab privileges.
GRANTEE                   OWNER           TABLE_NAME                PRIVILEGE
LEO                       ALINA           TEST                      SELECT

LEO role privileges.
GRANTEE                   GRANTED_ROLE
LEO                       PDB_DBA
LEO                       RESOURCE
LEO                       CONNECT

ALINA sys privileges.
GRANTEE                   PRIVILEGE
ALINA                     CREATE TABLE
ALINA                     UNLIMITED TABLESPACE

ALINA tab privileges.
GRANTEE                   OWNER           TABLE_NAME                PRIVILEGE

ALINA role privileges.
GRANTEE                   GRANTED_ROLE
ALINA                     RESOURCE
ALINA                     CONNECT
ALINA                     DBA

The following is the PDB HEALPDB's non-sysusers total privileges.
LEO sys privileges.
GRANTEE                   PRIVILEGE

LEO tab privileges.
GRANTEE                   OWNER           TABLE_NAME                PRIVILEGE

LEO role privileges.
GRANTEE                   GRANTED_ROLE
LEO                       PDB_DBA

2.2、非多租户测试
The following is the status of 11g all non-sysusers.
USERNAME             ACCOUNT_STATUS
LEO                  OPEN
GOLDENGATE           OPEN
TEST01               OPEN

The following is non-sysusers LEO total privileges.
LEO sys privileges.
GRANTEE                   PRIVILEGE
LEO                       UNLIMITED TABLESPACE

LEO tab privileges.
GRANTEE                   OWNER           TABLE_NAME                PRIVILEGE

LEO role privileges.
GRANTEE                   GRANTED_ROLE
LEO                       DBA

The following is non-sysusers GOLDENGATE total privileges.
GOLDENGATE sys privileges.
GRANTEE                   PRIVILEGE
GOLDENGATE                UNLIMITED TABLESPACE

GOLDENGATE tab privileges.
GRANTEE                   OWNER           TABLE_NAME                PRIVILEGE

GOLDENGATE role privileges.
GRANTEE                   GRANTED_ROLE
GOLDENGATE                DBA

The following is non-sysusers TEST01 total privileges.
TEST01 sys privileges.
GRANTEE                   PRIVILEGE
TEST01                    UNLIMITED TABLESPACE

TEST01 tab privileges.
GRANTEE                   OWNER           TABLE_NAME                PRIVILEGE

TEST01 role privileges.
GRANTEE                   GRANTED_ROLE
TEST01                    DBA