List privileges granted to a user

This script will list all the privileges granted (directly and indirectly) to the user of your DB2 database.

# Author: Alkesh Vipani (alkeshvipani@yahoo.com)
#!/bin/ksh

if [[ $# -ne 2 ]]; then
        echo
        echo 'Usage: usrauth <DatabaseName> <UserName>'
        echo "The script will list all the privileges granted (directly & indirectly) to the user on the database."
        echo
else
db2 connect to $1 >/dev/null

db2 -td";"  <<! |tail +22| grep -Ev "db2 =>| selected" >/tmp/dbauth_$$.out
select  substr(GRANTOR,1,8) Grantor, substr(GRANTEE,1,8) Grantee,
        GRANTEETYPE T, DBADMAUTH DBA, CREATETABAUTH crTab,
        BINDADDAUTH bind, CONNECTAUTH conn, NOFENCEAUTH  noFnc,
        IMPLSCHEMAAUTH implSch, LOADAUTH  load,
        EXTERNALROUTINEAUTH ExtRoutine, QUIESCECONNECTAUTH QscConn
from syscat.dbauth;
!

if [[ $? = 0 ]]
then
   a=`printf " %-8s %c " $2 'U'`
   grep -i "$a" /tmp/dbauth_$$.out >/dev/null
   if [[ $? = 0 ]]; then
      echo
      echo "Database Privileges - Granted directly to the user:"
      echo "--------------------------------------------------"
      echo
      grep -Ei "$a|------|GRANTOR  GRANTEE  T " /tmp/dbauth_$$.out
   else
      echo
      echo "Database Privileges - Granted directly to the user: NONE"
      echo "--------------------------------------------------"
   fi

   echo
   grp_cnt=1
   for grp in `lsgroup ALL | grep $2 | cut -f1 -d" "`
   do
        a=`printf " %-8s %c " $grp 'G'`
        grep -i "$a" /tmp/dbauth_$$.out >/dev/null
        if [[ $? = 0 ]]; then
           if [[ $grp_cnt = 1 ]];then
              echo "Database Privileges - Granted indirectly through groups:"
              echo "-------------------------------------------------------"
              grp_cnt=0
           fi
           echo
           echo "User $2 is member of $grp group."
           echo
           grep -Ei "$a|------|GRANTOR  GRANTEE  T " /tmp/dbauth_$$.out
        fi
   done
   if [[ $grp_cnt = 1 ]];then
      echo "Database Privileges - Granted indirectly through groups: NONE"
      echo "-------------------------------------------------------"
   fi
fi

db2 -td";"  <<! |tail +22| grep -Ev "db2 =>| selected" >/tmp/tabauth_$$.out
select  substr(GRANTOR,1,8) Grantor,substr(GRANTEE,1,8) Grantee,
        GRANTEETYPE T, substr(tabschema,1,8) Schema,
        substr(TABNAME,1,30) Table,
        SELECTAUTH S, INSERTAUTH INS, UPDATEAUTH U, DELETEAUTH D,
        INDEXAUTH IND, REFAUTH R, ALTERAUTH A, CONTROLAUTH C
from syscat.tabauth;
!

if [[ $? = 0 ]]
then
   a=`printf " %-8s %c " $2 'U'`
   grep -i "$a" /tmp/tabauth_$$.out >/dev/null
   if [[ $? = 0 ]]; then
      echo
      echo "Table Privileges - Granted directly to the user:"
      echo "-----------------------------------------------"
      echo
      grep -Ei "$a|------|GRANTOR  GRANTEE  T SCHEMA" /tmp/tabauth_$$.out
   else
      echo
      echo "Table Privileges - Granted directly to the user: NONE"
      echo "-----------------------------------------------"
      echo
   fi

   grp_cnt=1
   echo
   for grp in `lsgroup ALL | grep $2 | cut -f1 -d" "`
   do
        a=`printf " %-8s %c " $grp 'G'`
        grep -i "$a" /tmp/tabauth_$$.out >/dev/null
        if [[ $? = 0 ]]; then
           if [[ $grp_cnt = 1 ]];then
              echo "Table Privileges - Granted indirectly through groups:"
              echo "----------------------------------------------------"
              grp_cnt=0
           fi
           echo "User $2 is member of $grp group."
           echo
           grep -Ei "$a|------|GRANTOR  GRANTEE  T SCHEMA" /tmp/tabauth_$$.out
        fi
   done
   if [[ $grp_cnt = 1 ]];then
      echo "Table Privileges - Granted indirectly through groups: NONE"
      echo "----------------------------------------------------"
   fi
fi
echo
db2 connect reset >/dev/null
db2 terminate >/dev/null
rm /tmp/tabauth_$$.out /tmp/dbauth_$$.out
echo "
Column headings for table privileges:
        S       -->     Select
        INS     -->     Insert
        U       -->     Update
        D       -->     Delete
        IND     -->     Index
        R       -->     Reference
        A       -->     Alter
        C       -->     Control
"
fi                
 

For More Information

• Feedback: E-mail the editor with your thoughts about this tip.
• More tips: Hundreds of free DB2 tips and scripts.
• Tip contest: Have a DB2 tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
• Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
• Forums: Ask your technical DB2 questions--or help out your peers by answering them--in our active forums.
• Best Web Links: DB2 tips, tutorials, and scripts from around the Web.