#!/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: tbpriv.pl
#
# SAMPLE: How to grant, display and revoke privileges on a table
#
# SQL STATEMENTS USED:
#         SELECT
#         GRANT
#         REVOKE
#
#                           
##########################################################################
#
# 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 variables
my ($rc, $sth, $dbh);
my ($granteetype, $controlauth, $alterauth,$deleteauth, $indexauth); 
my ($insertauth, $selectauth, $refauth, $updateauth, );

print "THIS SAMPLE SHOWS HOW TO GRANT, DISPLAY AND REVOKE PRIVILEGES \n";
print "ON A TABLE. \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";

# demonstrate how to grant privileges on a table
$rc = TbPrivGrant();
if($rc == 0)
{
	die "\nError: Granting privileges on a table failed\n";
}

# demonstrate how to display privileges on a table
$rc = TbPrivDisplay();
if(not defined $rc)
{
  die "\nError: Displaying privileges on a table failed\n";
}

# demonstrate how to revoke privileges on a table
$rc = TbPrivRevoke();
if(not defined $rc)
{
  die "\nError: Revoking privileges on a table failed";
}

# disconnect from the database
print "\n  Disconnecting from database.";
$dbh->disconnect
  || die "Can't disconnect from database: $DBI::errstr";
print "\n  Disconnected from database.\n";

##########################################################################
# Description: How to grant privileges on a table
# Input      : None
# Output     : Returns 0 on success, exits otherwise 
##########################################################################
sub TbPrivGrant
{
  
  my $sql;

  print "  \n----------------------------------------------------------\n"; 
  print "USE THE SQL STATEMENTS:\n"; 
  print "  GRANT (Table, View, or Nickname Privileges)\n"; 
  print "TO GRANT PRIVILEGES ON A TABLE.\n";
  print "\n  GRANT SELECT, INSERT, UPDATE(salary, comm)\n"; 
  print "    ON TABLE staff\n"; 
  print "    TO USER user1";

  $sql = qq(GRANT SELECT, INSERT, UPDATE(salary, comm)
                ON TABLE staff
                TO USER user1);
  # prepare and execute the SQL statement
  # call the subroutine PrepareExecuteSql() from DB2SampUtil.pm
  $sth = PrepareExecuteSql($dbh, $sql);
  
  # commit the transaction
  print "\n\n  COMMIT \n";
  $rc  = $dbh->commit;
 
  # no more data to be fetched from statement handle
  $rc = $sth->finish;
  
  return $rc;
} # TbPrivGrant

##########################################################################
# Description: How to display privileges on a table
# Input      : None
# Output     : Returns 0 on success, exits otherwise
##########################################################################
sub TbPrivDisplay
{
 
  my $sql;

  print "----------------------------------------------------------\n";
  print "USE THE SQL STATEMENT:\n";
  print "  SELECT\n";
  print "TO DISPLAY PRIVILEGES ON A TABLE.\n";
  print "\n  SELECT granteetype, controlauth, alterauth,";
  print "\n         deleteauth, indexauth, insertauth,";
  print "\n         selectauth, refauth, updateauth";
  print "\n    FROM syscat.tabauth";
  print "\n    WHERE grantee = 'USER1' AND";
  print "\n          tabname = 'STAFF'\n";

  $sql = qq(SELECT granteetype, controlauth, alterauth,
                      deleteauth, indexauth, insertauth,
                      selectauth, refauth, updateauth
                 FROM syscat.tabauth
                 WHERE grantee = 'USER1' AND
                       tabname = 'STAFF');
 
  # prepare and execute the SQL statement
  # call the subroutine PrepareExecuteSql() from DB2SampUtil.pm
  $sth = PrepareExecuteSql($dbh, $sql);
  
  # fetch result of the query into variables for display
  ($granteetype, $controlauth, $alterauth,$deleteauth, $indexauth, 
   $insertauth, $selectauth, $refauth, $updateauth) = $sth->fetchrow();
  
  print "\n  Grantee Type     = ",$granteetype;
  print "\n  CONTROL priv.    = ",$controlauth;
  print "\n  ALTER priv.      = ",$alterauth;
  print "\n  DELETE priv.     = ",$deleteauth;
  print "\n  INDEX priv.      = ",$indexauth;
  print "\n  INSERT priv.     = ",$insertauth;
  print "\n  SELECT priv.     = ",$selectauth;
  print "\n  REFERENCES priv. = ",$refauth;
  print "\n  UPDATE priv.     = ",$updateauth;
  print "\n";
  
  # no more data to be fetched from statement handle
  $rc = $sth->finish;
  
  return $rc;
} # TbPrivDisplay

##########################################################################
# Description: How to revoke privileges on a table
# Input      : None
# Output     : Returns 0 on success, exits otherwise
##########################################################################
sub TbPrivRevoke
{
 
  my $sql;

  print "----------------------------------------------------------\n";
  print "USE THE SQL STATEMENT:\n";
  print "  REVOKE (Table, View, or Nickname Privileges)\n";
  print "TO REVOKE PRIVILEGES ON A TABLE.\n";
  print "\n  REVOKE SELECT, INSERT, UPDATE";
  print "\n    ON TABLE staff";
  print "\n    FROM USER user1";

  $sql = qq(REVOKE SELECT, INSERT, UPDATE
                 ON TABLE staff
                 FROM USER user1);

  # prepare and execute the SQL statement
  # call the subroutine PrepareExecuteSql() from DB2SampUtil.pm
  $sth = PrepareExecuteSql($dbh, $sql); 
  
  # commit the transaction  
  print "\n\n  COMMIT\n";
  my $rc = $dbh->commit;
  
  # no more data to be fetched from statement handle
  $rc = $sth->finish;
 
  return $rc;
} # TbPrivRevoke