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.
This was first published in July 2003