# This is a shell archive. Remove anything before this line, # then unpack it by saving it in a file and typing "sh file". # # Wrapped by Jacob L. Salomon on Wed Jul 17 14:51:43 2002 # # This archive contains: # dup-auth.sh dup-auth.txt # featureRequests.txt # # Modification/access file times will be preserved. LANG=""; export LANG PATH=/bin:/usr/bin:/usr/sbin:/usr/ccs/bin:$PATH; export PATH EXIT_STATUS=0 echo x - dup-auth.sh cat >dup-auth.sh <<'@EOF' #!/usr/bin/ksh # dup-auth.sh - Replicate table authorizations in the specified # database. # Author: Jacob Salomon # JakeSalomon@netscape.net # Release: 1.1 # Date: 2002-07-16 #==================================================================== # Release history: # 1.0 2000-04-03 Initial Release # 1.1 2002-07-16 Finally added database level permissions to the # output and -a option # 1.11 2002-07-17 Minor refinement to the integration of database and # table/column level permissions. It now presents # database level permissions first, followed by the # integrtated table and column level permissions. #==================================================================== # This program produces the GRANT commands that will replicate the # current permissions on the database tables. This includes both table- # level and column-level permissions. # # Required Parameters: # - Database name #==================================================================== # Start with some basic setup & functions # program=$(basename $0) # For debugging purposes, change DB_FILE as necesssary # #DB_FILE=/tmp/$0.$$.err DB_FILE=/dev/null errmsg() # For debugging - set DB_FILE if needed { echo $(date)::$* >>$DB_FILE } # # make_in_list() Shell function to transform a comma-separated # list - arguments to dup-auth.sh - into the format # of and IN-list for SQL. # e.g. mylist=moe,larry,curley,shemp,joe # make_in_list($mylist) -> ("moe","larry","curley","shemp","joe") # make_in_list() { comma_list=$1 # Copy into another variable, for clean code set $(echo $comma_list | tr , " ") # Separate components into # shell parameters, with count in_list="(" # In list starts with open parentheses while [ $# -gt 0 ] # Loop counts down my list do in_list=${in_list}\"${1}\" # Append quoted first item to list if [ $# -gt 1 ] # If this is not last item passed then in_list=${in_list}, # put comma separator before next item fi shift # Make next item the current done # At loop end, list is complete, but.. in_list=${in_list}")" # need to close the IN list for SQL echo $in_list # Return this list to caller } # usage() { cat <<%% $program [-h] [-r] -d database [-t table[,table..]] [-u user[,user..]] [-a perm[,perm..]] [-T table[,table..]] [-U user[,user..]] [-A perm[,perm..]] -h : This help text -r : Generate "revoke" commands instead of "grant" commands. -d : REQUIRED Specify the database for which you are replicating permissions. -t : Replicate permissions for only the table[s] nameed in the list -u : Replicate permissions for only the user[s] named in the list -a : Replicate only the named permissions ("a" is for authorization): select, update, insert, delete, index, alter, references, connect, resource, dba -T : Generate commands for all tables except those listed (Not in list) -U : Generate commands for all users except those listed (Not in list) -A : Generate commands for all permissions except those listed (Not in list) Note that the format of the list for the -t and -u options are in the form of item,item,item - A comma-separated list with no embedded spaces. EOF %% } cleanup() { rm $DB_TEMPLATE rm $TAB_TEMPLATE rm $COL_TEMPLATE rm $DB_SQL rm $TAB_SQL rm $COL_SQL rm $DB_UNL rm $TAB_UNL rm $COL_UNL } # init_program() # Initial setup of global variables and default options { YES=0 NO=1 help_flag=$NO grant_flag=$YES basfname=$(basename $program .sh) # Set up lists and arrays to help generate SQL in a loop. # # Database Level Permission: # ------------------------- db_index_list="1 2 3" # List of indices for arrays below max_db_num=3 # Highest subscript in array below # English names ; systuser pattern matches # --------------------- ; --------------------------------- db_perm[1]=\"dba\" ; db_pattern[1]=\"D\" db_perm[2]=\"resource\" ; db_pattern[2]=\"R\" db_perm[3]=\"connect\" ; db_pattern[3]=\"C\" # # Table Level Permissions: # ----------------------- tp_index_list="1 2 4 5 6 7 8" # List of indices for arrays below max_tp_num=8 # Highest subscript in array below # English names ; systabauth.tabauth pattern matches # --------------------- ; --------------------------------- tab_perm[1]=\"select\" ; tab_pattern[1]=\"[sS]\" tab_perm[2]=\"update\" ; tab_pattern[2]=\"[uU]\" tab_perm[4]=\"insert\" ; tab_pattern[4]=\"[iI]\" tab_perm[5]=\"delete\" ; tab_pattern[5]=\"[dD]\" tab_perm[6]=\"index\" ; tab_pattern[6]=\"[iI]\" tab_perm[7]=\"alter\" ; tab_pattern[7]=\"[aA]\" tab_perm[8]=\"references\" ; tab_pattern[8]=\"[rR]\" # Column Level Permissions: # ------------------------ cp_index_list="1 2 3" # List of indices for arrays below max_cp_num=3 # Highest subscript in array below # English names ; syscolauth.colauth pattern matches # --------------------- ; --------------------------------- col_perm[1]=\"select\" ; col_pattern[1]=\"[sS]\" col_perm[2]=\"update\" ; col_pattern[2]=\"[uU]\" col_perm[3]=\"references\" ; col_pattern[3]=\"[rR]\" # # Define temp file names for SQL, including templates to be edited # DB_SQL=/tmp/${basfname}-$$.sql # File for db-level auths DB_TEMPLATE=/tmp/${basfname}-dtempl.$$.sql # & db-level union TAB_SQL=/tmp/${basfname}-tabs.$$.sql # File for SQL for table auths TAB_TEMPLATE=/tmp/${basfname}-ttempl.$$.sql # & table-union COL_SQL=/tmp/${basfname}-cols.$$.sql # File for SQL for column auths COL_TEMPLATE=/tmp/${basfname}-ctempl.$$.sql # & column-union >$DB_SQL # Make sure those files exist and >$TAB_SQL # that they are empty >$COL_SQL DB_UNL=/tmp/${basfname}-db.$$.unl # File to unload db auths TAB_UNL=/tmp/${basfname}-tabs.$$.unl # File to unload table auths COL_UNL=/tmp/${basfname}-cols.$$.unl # File to unload column auths } # End init_program() # parse_params() { # No parameters have been looked at yet so database="" # No database, table_list="" # no table name user_list="" # or user name perm_list="" # No permissions specified either n_table_list="" # no negated table names n_user_list="" # or negated user name n_perm_list="" # No negated permissions specified either opt_string="hrd:t:u:a:T:U:A:" while getopts $opt_string cparm do errmsg $cparm with param: "$OPTARG" and OPTIND: $OPTIND case $cparm in h) errmsg Received -h option help_flag=$YES ;; d) errmsg Received -d option with param: "$OPTARG" database="$OPTARG" ;; t) errmsg Received -t option with param: "$OPTARG" if [ "$table_list" = "" ] # If first encounter of this option then # then make the argument the list table_list="$OPTARG" else # Secondary encounter: Append the arg- table_list=${table_list},"$OPTARG" # ument to existing list fi ;; u) errmsg Received -u option with param: "$OPTARG" if [ "$user_list" = "" ] # If first encounter of this option then # then make the argument the list user_list="$OPTARG" else # Secondary encounter: Append the arg- user_list=${user_list},"$OPTARG" # ument to existing list fi ;; a) errmsg Received -a option with param: "$OPTARG" if [ "$perm_list" = "" ] # If first encounter of this option then # then make the argument the list perm_list="$OPTARG" else # Secondary encounter: Append the arg- perm_list=${perm_list},"$OPTARG" # ument to existing list fi ;; # T) if [ "$n_table_list" = "" ] # If first encounter of this option then # then make the argument the list n_table_list="$OPTARG" else # Secondary encounter: Append the arg- n_table_list=${n_table_list},"$OPTARG" # ument to existing list fi ;; U) errmsg Received -U option with param: "$OPTARG" if [ "$n_user_list" = "" ] # If first encounter of this option then # then make the argument the list n_user_list="$OPTARG" else # Secondary encounter: Append the arg- n_user_list=${n_user_list},"$OPTARG" # ument to existing list fi ;; A) errmsg Received -A option with param: "$OPTARG" if [ "$n_perm_list" = "" ] # If first encounter of this option then # then make the argument the list n_perm_list="$OPTARG" else # Secondary encounter: Append the arg- n_perm_list=${n_perm_list},"$OPTARG" # ument to existing list fi ;; r) errmsg Received -r option grant_flag=$NO ;; *) echo Unrecognized option: $cparm 1>&2 usage exit 1 ;; esac done } # process_params() # Perform additional validation and setups. { if [ $help_flag -eq $YES ] then # Just wanted a help page? usage # Well, you got it. exit 0 # Goodbye fi # Option: -d (Specify database. Required) # Validate the database parameter - make sure it was given on the # command line # if [ -z "$database" ] then echo Database not specified usage exit 2 fi # Option: -t (Specify a list of tables) # TABLE_CLAUSE="--" # Assume not supplied, then check it if [ -n "$table_list" ] # Was a list of tables given? then # Build clause of table names TABLE_CLAUSE="and tabname in $(make_in_list $table_list)" fi # End -t Option # Option: -u (Specify a list of user names) # USER_CLAUSE="--" # Start as useless clause if [ -n "$user_list" ] # Was a list of users given? then # Build clause of user names USER_CLAUSE="and grantee in $(make_in_list $user_list)" fi # End -u Option # Option: -a (Specify a list of permisions) # PERM_CLAUSE="--" # Start as useless clause if [ -n "$perm_list" ] # Was a list of permissions given? then # Build clause of permission names PERM_CLAUSE="and permission in $(make_in_list $perm_list)" fi # End -a Option # Option: -T (Specify a negated list of tables) # N_TABLE_CLAUSE="--" # Assume not supplied, then check it if [ -n "$n_table_list" ] # Was a list of tables given? then # Build clause of table names N_TABLE_CLAUSE="and tabname not in $(make_in_list $n_table_list)" fi # End -T Option # # Option: -U (Specify a negated list of user names) # N_USER_CLAUSE="--" # Start as useless clause if [ -n "$n_user_list" ] # Was a list of users given? then # Build clause of user names N_USER_CLAUSE="and grantee not in $(make_in_list $n_user_list)" fi # End -U Option # Option: -A (Specify a negated list of permisions) # N_PERM_CLAUSE="--" # Start as useless clause if [ -n "$n_perm_list" ] # Was a list of permissions given? then # Build clause of permission names N_PERM_CLAUSE="and permission not in $(make_in_list $n_perm_list)" fi # End -A Option } # # Program execution begins here # init_program # Initialize variables, lists and arrays parse_params $* # Flag whatever options the user entered process_params # Setup strings based on the user's options # Start setting up the SQL. Database authorizations first. # Start with the template with place-holders for parts of the query # cat >$DB_TEMPLATE <<%% select _PERM_ permission, username grantee, -- Use grantee to re-use clauses usertype auth_flag from sysusers where usertype = _UTYPE_ %% SEDFILE="/tmp/$program-t.$$.sed" # Sed script file to overwrite alot # Loop to generate correct SQL to get all database-level permissions: # for db_num in $db_index_list # Turn above template file into do # useful SQL cat >$SEDFILE <<%% s/_PERM_/${db_perm[$db_num]}/ s/_UTYPE_/${db_pattern[$db_num]}/ %% sed -f $SEDFILE $DB_TEMPLATE >>$DB_SQL # Filter/replace patterns if [ $db_num -lt $max_db_num ] # If not yet on last array entry then # combine most recent SQL with echo UNION >>$DB_SQL # next SQL I will generate fi done # Now the last clause on the above-generated, unioned SQL: # cat <<%% >>$DB_SQL into temp temp_dbauths with no log ; %% # OK, I have done the database level privileges; now for table level # cat >$TAB_TEMPLATE <<%% select _PERM_ permission, a.grantee, t.tabname, a.grantor, a.tabauth[_POS_,_POS_] auth_flag from systabauth a, systables t where a.tabid = t.tabid and a.tabauth[_POS_,_POS_] matches _PATT_ and t.tabid >= 100 %% # # Loop to generate the correct SQL for all table authorizations # Plan: # o Generate a SED command file for each permission type # o Run the template through the sed script, append to the SQL # for tp_num in $tp_index_list do cat >$SEDFILE <<%% # Refresh the sed script s/_PERM_/${tab_perm[$tp_num]}/g s/_POS_/$tp_num/g s/_PATT_/${tab_pattern[$tp_num]}/g %% sed -f $SEDFILE $TAB_TEMPLATE >>$TAB_SQL # Filter/replace the patterns if [ $tp_num -lt $max_tp_num ] # Not yet on last entry of array then echo UNION >>$TAB_SQL # combine it with next query fi done cat <<%% >>$TAB_SQL # I still have something to add to the SQL into temp temp_tabauths with no log ; %% # OK, I have generated SQL to get table-level permissions from catalog # systabauth. Now to do the same for column level permissions from # catalog syscolauth. # cat >$COL_TEMPLATE <<%% select _PERM_ permission, grantee, colname, tabname, grantor, colauth[_POS_,_POS_] auth_flag from systables t, syscolumns c, syscolauth ca where t.tabid = c.tabid and t.tabid = ca.tabid and c.colno = ca.colno and ca.colauth[_POS_,_POS_] matches _PATT_ and t.tabid >= 100 %% # for cp_num in $cp_index_list # Loop up arrays of column info do cat >$SEDFILE <<%% # Refresh the sed script (again) s/_PERM_/${col_perm[$cp_num]}/g s/_POS_/$cp_num/g s/_PATT_/${col_pattern[$cp_num]}/g %% sed -f $SEDFILE $COL_TEMPLATE >>$COL_SQL # Filter/replace the patterns if [ $cp_num -lt $max_cp_num ] # If this is not last entry of array then echo UNION >>$COL_SQL # combine it with next query fi done cat <<%% >>$COL_SQL # I still have something to add to the SQL into temp temp_colauths with no log ; %% rm $SEDFILE # Remove last incarnation of SED script file # Note: All SQL scripts above will be augmented in the following step. # # Now, the SQL scripts only create temp tables. We need to use those # temp tables to produce the desired permissions info to the users. # Note that I have not yet made use of the TABLE_CLAUSE and USER_CLAUSE # that I went to such trouble to set up. Now's my chance. # Also, if the user specified the -r option, I should generate REVOKE # commands instead of GRANT commands. I make that decision here. # if [ $grant_flag -eq $YES ] # User requested the default GRANT then cat >>$DB_SQL <<%% # Generate DB-level grant commands unload to $DB_UNL delimiter "-" select "grant " || trim(permission) || " to " || trim(grantee) || " ;" from temp_dbauths where auth_flag matches "[DRC]" $USER_CLAUSE $N_USER_CLAUSE $PERM_CLAUSE $N_PERM_CLAUSE ; %% cat >>$TAB_SQL <<%% # Generate table-level grant commands unload to $TAB_UNL delimiter "-" select "grant " || trim(permission) || " on " || trim(tabname) || " to " || trim(grantee) || " as " || trim(grantor) || " ;" from temp_tabauths where auth_flag matches "[suidxar]" $TABLE_CLAUSE $N_TABLE_CLAUSE $USER_CLAUSE $N_USER_CLAUSE $PERM_CLAUSE $N_PERM_CLAUSE union select "grant " || trim(permission) || " on " || trim(tabname) || " to " || trim(grantee) || " as " || trim(grantor) || " with grant option ;" from temp_tabauths where auth_flag matches "[SUIDXAR]" $TABLE_CLAUSE $N_TABLE_CLAUSE $USER_CLAUSE $N_USER_CLAUSE $PERM_CLAUSE $N_PERM_CLAUSE ; %% cat >>$COL_SQL <<%% # Generate column-level grant commands unload to $COL_UNL delimiter "-" select "grant " || trim(permission) || "(" || trim(colname) || ")" || " on " || trim(tabname) || " to " || trim(grantee) || " as " || trim(grantor) || " ;" from temp_colauths where auth_flag matches "[sur]" $TABLE_CLAUSE $N_TABLE_CLAUSE $USER_CLAUSE $N_USER_CLAUSE $PERM_CLAUSE $N_PERM_CLAUSE union select "grant " || (permission) || "(" || trim(colname) || ")" || " on " || trim(tabname) || " to " || trim(grantee) || " as " || trim(grantor) || " with grant option ;" from temp_colauths where auth_flag matches "[SUR]" $TABLE_CLAUSE $N_TABLE_CLAUSE $USER_CLAUSE $N_USER_CLAUSE $PERM_CLAUSE $N_PERM_CLAUSE ; %% # else # User requested REVOKE commands cat >>$DB_SQL <<%% # Generate DB-level revoke commands unload to $DB_UNL delimiter "-" select "revoke " || trim(permission) || " from " || trim(grantee) || " ;" from temp_dbauths where auth_flag matches "[DRC]" $USER_CLAUSE $N_USER_CLAUSE $PERM_CLAUSE $N_PERM_CLAUSE ; %% # cat >>$TAB_SQL <<%% # Generate revoke on table commands unload to $TAB_UNL delimiter "-" select "revoke " || trim(permission) || " on " || trim(tabname) || " from " || trim(grantee) || " ;" from temp_tabauths where auth_flag matches "[suidxarSUIDXAR]" $TABLE_CLAUSE $N_TABLE_CLAUSE $USER_CLAUSE $N_USER_CLAUSE $PERM_CLAUSE $N_PERM_CLAUSE ; %% # Note - There is currently no syntax in SQL for revoking a column- # level privelege. Hence, for tables where column privs were detected # we need only one comnmand - to revoke the privelege on the table # from that user. Hence, the "unique" qualifier and the commented-out # column name. # cat >>$COL_SQL <<%% # Generate revoke on column commands unload to $COL_UNL delimiter "-" select unique "revoke " || trim(permission) --|| "(" || trim(colname) || ")" || " on " || trim(tabname) || " from " || trim(grantee) || " ;" from temp_colauths where auth_flag matches "[surSUR]" $TABLE_CLAUSE $N_TABLE_CLAUSE $USER_CLAUSE $N_USER_CLAUSE $PERM_CLAUSE $N_PERM_CLAUSE ; %% fi # if [ $grant_flag -eq $YES ] # # Moment of truth: Send the generated SQL to the engine # (Kinda anti-climactic, isn't it? ;-) # cat $DB_SQL $TAB_SQL $COL_SQL | dbaccess $database - # Data has been unloaded. It needs a bit of processing: # - Get rid of the - at the end of each line # - Sort the data as needed. # SED_DASH="sed s/-//" # sed command to drop '-' at end of lines SED_BLANK="sed /^$/d" # sed command to delete blank lines SORT_DBP="sort +3 -4 +1 -2" # Sort command for db-level permissions SORT_TAB="sort +3 -4 +5 -6 +1 -2" # Sort for table-level perms # First output db-level permissions: # cat $DB_UNL | \ $SED_DASH | $SORT_DBP | $SED_BLANK | beautify-unl.sh -db # Next, output table & column permissions # cat $TAB_UNL $COL_UNL | \ $SED_DASH | $SORT_TAB | $SED_BLANK | beautify-unl.sh -db # Finished. Get rid of the temp files I created. # cleanup # ==================================================================== @EOF touch -m 0717120202 dup-auth.sh touch -a 0717120502 dup-auth.sh chmod 755 dup-auth.sh echo x - dup-auth.txt cat >dup-auth.txt <<'@EOF' Program: dup-auth.sh Short Description: Generate SQL to replicate or negate permissions on tables and columns in a database. Author: Jacob Salomon JakeSalomon@netscape.net Program: dup-auth.sh Short Description: Generate SQL to replicate or negate permissions on tables and columns in a database. Author: Jacob Salomon JakeSalomon@netscape.net Initial Release Date: 2000-04-04 Latest Release Date: 2002-07-16 Requirements: The most recent update (1.10) of beautify-unl.sh, available as a free download from iiug, with URLs: http://www.iiug.org/members/memb_software/archive/beautify_unl ftp://ftp.iiug.org/pub/informix/pub/beautify_unl.gz Any previous version of beautify-unl.sh will result in blank output. (Since you have the shell script, you can comment out the pipe to beautify-unl.sh. The result will still be valid, only a bit less readable.) Motivation: ---------- Some operations people at my employer computer center realized that database permissions had "run amuck"; there were were people with table and columns permissions they had no business having. The request to correct the situation looked something like: For the following tables: {inventory,bulls,sheep,creep,rock,roll,shake,bake,roast,toast} only these users: {moe,larry,shemp,curley,joe,stanley,ollie} may have insert, update, delete permissions. All others may have select. The actual number of tables was over 20 but about this number of users. When looking at existing permissions on these tables (no trivial query either, if you want something readable) I knew I had to write a script to do this. It was simply not possible to revoke each inappropriate authorization by hand - there were simply too many to manage. And this was only one request. There were several similar requests for different sets of tables and users. (In retrospect, after writing the new script and running it, there were well over 200 revocations for that first request alone.) The Solution: dup-auth.sh ------------------------- I initially had written a query to generate GRANT commands to replicate all table and column authorizations from the current database in order to FTP the result to another system with an identical database. Pack- rat that I am with any SQL or shell script, I was often asked to narrow the query to a specific table or user. The query itself was a long union that captures some literal values and table names into a temp table, followed by a query from the temp table to generate the GRANT commands in SQL to fed into the engine at the target site. The Program: ----------- When I began to apply the above query to the current problem, I realized the shell script must have more options than user and table. For a list of options, here is the output of the -h option: ---------------------------------------------------------------------- $ dup-auth.sh -h dup-auth.sh [-h] [-r] -d database [-t table[,table..]] [-u user[,user..]] [-a perm[,perm..]] [-T table[,table..]] [-U user[,user..]] [-A perm[,perm..]] -h : This help text -r : Generate "revoke" commands instead of "grant" commands. -d : REQUIRED Specify the database for which you are replicating permissions. -t : Replicate permissions for only the table[s] nameed in the list -u : Replicate permissions for only the user[s] named in the list -a : Replicate only the named permissions ("a" is for authorization): select, update, insert, delete, index, alter, references, connect, resource, dba -T : Generate commands for all tables except those listed (Not in list) -U : Generate commands for all users except those listed (Not in list) -A : Generate commands for all permissions except those listed (Not in list) Note that the format of the list for the -t and -u options are in the form of item,item,item - A comma-separated list with no embedded spaces. EOF ---------------------------------------------------------------------- Explanation of parameters: ------------------------- The options t, u, and a, plus their upper-case negations, T, U and A, can all be specified multiple times and their parameters will be concatenated. For all of these options, the parameter list consists of comma-separated names with no embedded white spaces. No other item- separator is recognized. An example of these is given with the -t option but applies to all of these options. -h : The help text, which was easier to program than setting up a MAN page. -d database : This is the only required parameter. You must name a database because permissions are a database-specific attribute. -r : By default, dup-auth.sh generates GRANT commands in order to replicate permissions on the specified (or all) tables. If your intent is to revoke permissions, use the -r option and it will generate REVOKE commands. -t : Specify a list of tables. dup-auth.sh will replicate grant (or revoke) commands for only the specified tables, ignoring all other tables in the database. Default: Generate the command for all tables. The table list must be in the form: table1,table2,table3.. with no embedded spaces. However, if your list of tables is very long (like 20 or so) and would make for an unreadable command line, you can use as many -t options as you like. The lists will be concatenated internally. For example: dup-auth.sh -d stores -t customer,orders -t items,stock is functionally equivalent to: dup-auth.sh -d stores -t customer,orders,items,stock -T : Specify a negated list of tables. dup-auth.sh will replicate grant (or revoke) commands only for tables NOT in the list. This is most useful if I wish to revoke permissions on most tables from a user (or the public) but 30 or so need to remain generally accessible. -u : Specify a list of users. dup-auth.sh will replicate grant (or revoke) commands only for those tables where the listed users have some permissions. By default, all users are listed. -U : Specify a negated list of users. dup-auth.sh will generate grant (or revoke) commands for users not on the list. This is most useful with the -r option when I wish to revoke permissions from all but a group of users. -a : Specify a list of authorizations. dup-auth.sh will replicate grant (or revoke) commands only the permissions listed in the parameter. Default is to list all existing permissions. Note that the permissions must be spelled out in full, as in the help text. This program does not [yet] recognize abbreviated permission names. Why did I use the letter "a" for authorizations, rather than the arguably more intuitive "p" for permissions? Because somewhere down the road I intend to add a -p option for procedures. -A : Specify a negated list of authorizations. Replicate commands to grant (or revoke) all existing permissions except those listed in the parameter. Examples: (1) Replicate all table/column permissions for user dajls $ dup-auth.sh -d bndb -u dajls Database selected. 14959 row(s) retrieved into temp table. 6 row(s) unloaded. 37 row(s) retrieved into temp table. 0 row(s) unloaded. Database closed. grant select on audit_detail1 to dajls as informix; grant select on audit_detail2 to dajls as informix; grant select on audit_detail3 to dajls as informix; grant select on audit_header1 to dajls as informix; grant select on audit_header2 to dajls as informix; grant select on audit_header3 to dajls as informix; The dbaccess informative messages can be avoided by redirecting standard error to /dev/null. This will be done in the remainder of the examples. (2) Revoke whatever permissions dajls has in the database: $ dup-auth.sh -r -d bndb -u dajls 2>/dev/null revoke select on audit_detail1 from dajls; revoke select on audit_detail2 from dajls; revoke select on audit_detail3 from dajls; revoke select on audit_header1 from dajls; revoke select on audit_header2 from dajls; revoke select on audit_header3 from dajls; (3) Revoke whatever permissions dajls has on tables audit_header1 and audit_detail1: $ dup-auth.sh -r -d bndb -u dajls -taudit_header1,audit_detail1 \ 2>/dev/null revoke select on audit_detail1 from dajls; revoke select on audit_header1 from dajls; Note that in this example, I happened to enter the list of tables with no space between the the -t option and its parameter list. This is standard shell convention for parsing command-line options with the shell getopts command. (4) The same command as (3) but with multiple invocations of the -t option: $ dup-auth.sh -r -d bndb -u dajls -taudit_header1 -t audit_detail1 \ 2>/dev/null revoke select on audit_detail1 from dajls; revoke select on audit_header1 from dajls; (5) Instead of the above revocation, I will try the opposite: revoke all permissions possessed by dajls on all tables EXCEPT audit_header1 and audit_detail1: $ dup-auth.sh -r -dbndb -udajls -Taudit_header1,audit_detail1 \ 2>/dev/null revoke select on audit_detail2 from dajls; revoke select on audit_detail3 from dajls; revoke select on audit_header2 from dajls; revoke select on audit_header3 from dajls; (6) Let's see who has update permissions on the "store" table and who granted said permissions. $ dup-auth.sh -d bndb -t store -a update 2>/dev/null grant update(frp_code) on store to autofrp as informix; grant update(frp_invntry_date) on store to autofrp as informix; grant update(on_hand_upd_date) on store to autofrp as informix; grant update on store to autostor as informix; grant update on store to bndb as informix; grant update on store to cpmfk as informix; grant update(frp_code) on store to frp as informix; grant update(frp_invntry_date) on store to frp as informix; grant update(lta_day_qty) on store to frp as informix; grant update(on_hand_upd_date) on store to frp as informix; grant update(upd_dtime) on store to frp as informix; grant update(upd_user_id) on store to frp as informix; grant update(frp_code) on store to merch as informix; grant update(frp_invntry_date) on store to merch as informix; grant update(lta_day_qty) on store to merch as informix; grant update(on_hand_upd_date) on store to merch as informix; grant update(sales_qty) on store to spdxs as informix; grant update(upd_dtime) on store to spdxs as informix; grant update(upd_user_id) on store to spdxs as informix; .... (7) In example (6) I cut the list off. But I did see user names there that did not belong. Suppose I now wish to generate all permissions but select from all improperly authorized users. The only users who should be able to effect any change on store are ftp, autofrp and autostor. Let's break this down a bit: - I wish to revoke all permissions except SELECT - from users not members of this elite group: frp and autofrp. - Revoke: -r - On table: store - Negated permissions list: -A select - Negated user list: -U frp,autofrp,autostor There is one little kink in this: SQL does not seem to have a syntax for the revocation of column-level privileges. I cannot simply reverse the grant command. For example, if I try: revoke update(frp_code) on store from merch; I will get error -379: Cannot revoke privilege on columns. Instead, I must revoke the privilege on the table in full from the user. $ dup-auth.sh -r -d bndb -t store -U frp,autofrp,autostor \ -A select 2>/dev/null revoke update on store from merch; revoke update on store from spfjs; revoke update on store from spjxr; revoke delete on store from splbb; revoke insert on store from splbb; revoke update on store from splbb; revoke delete on store from store; revoke insert on store from store; revoke update on store from store; revoke update on store from txdxj; (8) Just to illustrate that the "with grant option" is taken into consideration, here is an example that illustrates it: $ dup-auth.sh -d bndb -t source_control 2>/dev/null grant delete on source_control to dvtdd as informix with grant option; grant insert on source_control to dvtdd as informix with grant option; grant select on source_control to dvtdd as informix with grant option; grant update on source_control to dvtdd as informix with grant option; grant delete on source_control to mtsat as informix; grant insert on source_control to mtsat as informix; .... I have tried to use examples that describe all possible features and uses. I hope you find it useful. -- Jacob Salomon 04/04/2000 ======================================================================== @EOF touch -m 0716174002 dup-auth.txt touch -a 0717120402 dup-auth.txt chmod 666 dup-auth.txt echo x - featureRequests.txt cat >featureRequests.txt <<'@EOF' Speculations on ideas for additional feature of dup-auth.sh. 1. Permissions on stored procedures. This entails a new option and permission name: -p -a execute Question: Is there a "WITH GRANT OPTION" clause for procedures? Question: Is there an "AS user> clause for procedures? 2. Generate code to give one user all permissions already possesed by another user. This would require the user to unter the -u option while constraining the -u parameter list to one user name, plus new command options: -g Grant to all users in the parameter list. -G Grant to all users in the parameter list, with GRANT option. -s When granting, a single name for the AS clause. Default would be whoever granted it to the original user whose permissions I am replicating. Suppose user YUTZ has select and update on tables inventory and stock. I wish to grant yutz'z permissions to moe and curley. So I would use the command: dup-auth.sh -d stores \ -u yutz \ -t inventory,stock \ -g moe,curley -s larry which should generate something like: grant select on inventory to moe as larry; grant update on inventory to moe as larry; grant select on inventory to curley as larry; grant update on inventory to curley as larry; grant select on stock to moe as larry; grant update on stock to moe as larry; grant select on stock to curley as larry; grant update on stock to curley as larry; The new -[gG] and -s options require me to code new validations: - The the -u option has been used and that it has exactly one parameter: the user name whose permissions I am replicating. - The -U parameter has NOT been used (or I can ignore it) because it is the equivalent of spcifying all other users in the system. - If -s is used, make sure the -g or -G option was used. - The -r option - does it make sense to say: Whatever privs userA has, revoke them from userB 3. Duplicate/revoke database level permissions. I guess I would use -b as an option to specify dataBase level privileges. It would be mutually exclusive with -[gGtTpP] or any other option related to table and procedure level privileges. Note that this was implemented in release 1.1 but not as a separate option. It was easier to integrate it into the resto of the code. However, this has produced a minor quirk regarding the sorting of the output: All the database-level permissions are presented first, sorted by the user name. Since the user name is field 4 in the database-level permissions output but field 6 in table and column- level permissions there was no way to sort the combined outputs in a single command. ======================================================================== @EOF touch -m 0717115402 featureRequests.txt touch -a 0717120402 featureRequests.txt chmod 666 featureRequests.txt if [ $EXIT_STATUS -eq 1 ];then exit 1 fi exit 0