#!/usr/bin/perl ######################################################################## # (c) Copyright IBM Corp. 2007 All rights reserved. # # The following sample of source code ("Sample") is owned by International # Business Machines Corporation or one of its subsidiaries ("IBM") and is # copyrighted and licensed, not sold. You may use, copy, modify, and # distribute the Sample in any form without payment to IBM, for the purpose of # assisting you in the development of your applications. # # The Sample code is provided to you on an "AS IS" basis, without warranty of # any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR # IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF # MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do # not allow for the exclusion or limitation of implied warranties, so the above # limitations or exclusions may not apply to you. IBM shall not be liable for # any damages you suffer as a result of using, copying, modifying or # distributing the Sample, even if IBM has been advised of the possibility of # such damages. ######################################################################### # # SOURCE FILE NAME: dbauth.pl # # SAMPLE: How to grant, display, and revoke authorities at database level # # SQL STATEMENTS USED: # SELECT INTO # GRANT (Database Authorities) # REVOKE (Database Authorities) # # ########################################################################## # # For more information on the sample programs, see the README file. # # For information on developing Perl applications, see the Application # Development Guide. # # For information on using SQL statements, see the SQL Reference. # # For the latest information on programming, building, and running DB2 # applications, visit the DB2 Information Center: # http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp ########################################################################### select STDERR; $|=1; select STDOUT; $|=1; use strict; use warnings; use DBI; # access the module for DB2 Sample Utility functions use DB2SampUtil; # check and parse the command line arguments # call the subroutine CmdLineArgChk() from DB2SampUtil.pm my ($database, $user, $password) = CmdLineArgChk(@ARGV); # declare return code, statement handler, database handler and local variable my ($rc, $sth, $dbh, $i); sub DbAuthGrant(); sub DbAuthForAnyUserOrGroupDisplay(); sub DbAuthRevoke(); print "\nTHIS SAMPLE SHOWS "; print "\nHOW TO GRANT/DISPLAY/REVOKE AUTHORITIES AT DATABASE LEVEL.\n"; # connect to the database print "\n Connecting to database...\n"; $dbh = DBI->connect($database, $user, $password, {AutoCommit =>0}) || die "Can't connect to $database: $DBI::errstr"; print "\n Connected to database.\n"; # call the subroutine DbAuthGrant $rc = DbAuthGrant(); if ($rc != 0) { print "\nGranting Database authorities at Database level failed\n"; } # call the subroutine DbAuthForAnyUserOrGroupDisplay $rc = DbAuthForAnyUserOrGroupDisplay(); if ($rc != 0) { print "\nDisplay of Database authorities for any user at Database\n"; print "level failed\n"; } # call the subroutine DbAuthRevoke $rc = DbAuthRevoke(); if ($rc != 0) { print "\nRevoke Database authorities from user at Database\n"; print "level failed\n"; } # disconnect from the database print "\n Disconnecting from database.\n"; $dbh->disconnect || die $DBI::errstr; print " Disconnected from database.\n"; ######################################################################### # Description: How to grant authorities at database level # Input : None # Output : Returns 0 on success, exits otherwise. ######################################################################### sub DbAuthGrant() { my $sql; print "\n-----------------------------------------------------------"; print "\nUSE THE SQL STATEMENTS:\n"; print " GRANT (Database Authorities)\n"; print " COMMIT\n"; print "TO GRANT AUTHORITIES AT DATABASE LEVEL.\n"; # grant user authorities at database level print "\n GRANT CONNECT, CREATETAB, BINDADD ON DATABASE"; print " TO USER user1\n"; $sql = qq(GRANT CONNECT, CREATETAB, BINDADD ON DATABASE TO USER user1); # prepare and execute the SQL statement # call the subroutine PrepareExecuteSql() from DB2SampUtil.pm $sth = PrepareExecuteSql($dbh, $sql); print " COMMIT\n"; # commit the transaction or call TransRollback() from DB2SampUtil.pm # if it fails $dbh->commit() || TransRollback($dbh); # no more data to be fetched from statement handle $sth->finish; return 0; } # DbAuthGrant ######################################################################### # Description: How to display authorities for any user at database level # Input : None # Output : Returns 0 on success, exits otherwise. ######################################################################### sub DbAuthForAnyUserOrGroupDisplay() { my $sql; print "\n-----------------------------------------------------------"; print "\nUSE THE SQL STATEMENT:\n"; print " SELECT INTO\n"; print "TO DISPLAY AUTHORITIES FOR ANY USER AT DATABASE LEVEL.\n"; print "\n SELECT granteetype, dbadmauth, createtabauth, bindaddauth,\n"; print " connectauth, nofenceauth, implschemaauth, loadauth\n"; print " FROM syscat.dbauth\n"; print " WHERE grantee = 'USER1'\n"; $sql = qq(SELECT granteetype, dbadmauth, createtabauth, bindaddauth, connectauth, nofenceauth, implschemaauth, loadauth FROM syscat.dbauth WHERE grantee = 'USER1'); # prepare and execute the SQL statement # call the subroutine PrepareExecuteSql() from DB2SampUtil.pm $sth = PrepareExecuteSql($dbh, $sql); my ($granteetype, $dbadmauth, $createtabauth, $bindaddauth, $connectauth, $nofenceauth, $implschemaauth, $loadauth) = $sth->fetchrow_array; # check for problems which may have terminated the fetch early die $sth->errstr if $sth->err; print "\n Grantee Type = ", $granteetype, "\n"; print " DBADM auth. = ", $dbadmauth, "\n"; print " CREATETAB auth. = ", $createtabauth, "\n"; print " BINDADD auth. = ", $bindaddauth, "\n"; print " CONNECT auth. = ", $connectauth, "\n"; print " NO_FENCE auth. = ", $nofenceauth, "\n"; print " IMPL_SCHEMA auth. = ", $implschemaauth, "\n"; print " LOAD auth. = ", $loadauth, "\n"; # no more data to be fetched from statement handle $sth->finish; return 0; } # DbAuthForAnyUserOrGroupDisplay() ######################################################################### # Description: How to revoke authorities at database level # Input : None # Output : Returns 0 on success, exits otherwise. ######################################################################### sub DbAuthRevoke() { my $sql; print "\n-----------------------------------------------------------"; print "\nUSE THE SQL STATEMENTS:\n"; print " REVOKE (Database Authorities)\n"; print " COMMIT\n"; print "TO REVOKE AUTHORITIES AT DATABASE LEVEL.\n"; # revoke user authorities at database level print "\n REVOKE CONNECT, CREATETAB, BINDADD ON DATABASE "; print "FROM USER user1\n"; $sql = qq(REVOKE CONNECT, CREATETAB, BINDADD ON DATABASE FROM user1); # prepare and execute the SQL statement # call the subroutine PrepareExecuteSql() from DB2SampUtil.pm $sth = PrepareExecuteSql($dbh, $sql); print " COMMIT\n"; # commit the transaction or call TransRollback() from DB2SampUtil.pm # if it fails $dbh->commit() || TransRollback($dbh); # no more data to be fetched from statement handle $sth->finish; return 0; } # DbAuthRevoke()