/****************************************************************************
** (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.cs
**
** SAMPLE: How to grant, display and revoke privileges on a table
** with the DB2 .Net Data Provider
**
** SQL Statements USED:
** GRANT (Table, View, or Nickname Privileges)
** SELECT
** REVOKE (Table, View, or Nickname Privileges)
**
** DB2 .NET Data Provider Classes USED:
** DB2Connection
** DB2Command
** DB2Transaction
**
**
*****************************************************************************
**
** Building and Running the sample program
**
** 1. Compile the TbPriv.cs file with bldapp.bat by entering the following
** at the command prompt:
**
** bldapp TbPriv
**
** or compile TbPriv.cs with the makefile by entering the following at
** the command prompt:
**
** nmake TbPriv
**
** 2. Run the TbPriv program by entering the program name at the command
** prompt:
**
** TbPriv
**
*****************************************************************************
**
** For more information on the sample programs, see the README file.
**
** For information on developing applications, see the Application
** Development Guide.
**
** For information on using SQL statements, see the SQL Reference.
**
** For the latest information on programming, compiling, and running DB2
** applications, visit the DB2 Information Center at
** http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp
**
****************************************************************************/
using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;
class TbPriv
{
public static void Main(String[] args)
{
// Declare a DB2Connection and a DB2Transaction
DB2Connection conn = null;
DB2Transaction trans = null;
try
{
Console.WriteLine();
Console.WriteLine(
" THIS SAMPLE SHOWS HOW TO GRANT, DISPLAY AND REVOKE \n" +
" PRIVILEGES ON A TABLE.");
// Connect to a database
Console.WriteLine("\n Connecting to a database ...");
conn = ConnectDb(args);
// Demonstrate how to grant privileges on a table
trans = conn.BeginTransaction();
Grant(conn, trans);
// Demonstrate how to display privileges on a table
trans = conn.BeginTransaction();
Display(conn, trans);
// Demonstrate how to revoke privileges on a table
trans = conn.BeginTransaction();
Revoke(conn, trans);
// Disconnect from the database
Console.WriteLine("\n Disconnect from the database.");
conn.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
conn.Close();
}
} // Main
public static DB2Connection ConnectDb(String[] argv)
{
String server = "";
String alias = "";
String userId = "";
String password = "";
Int32 portNumber = -1;
String connectString;
if( argv.Length > 5 ||
( argv.Length == 1 &&
( String.Compare(argv[0],"?") == 0 ||
String.Compare(argv[0],"-?") == 0 ||
String.Compare(argv[0],"/?") == 0 ||
String.Compare(argv[0],"-h",true) == 0 ||
String.Compare(argv[0],"/h",true) == 0 ||
String.Compare(argv[0],"-help",true) == 0 ||
String.Compare(argv[0],"/help",true) == 0 ) ) )
{
throw new Exception(
"Usage: prog_name [dbAlias] [userId passwd] \n" +
" prog_name [dbAlias] server portNum userId passwd");
}
switch (argv.Length)
{
case 0: // Use all defaults
alias = "sample";
userId = "";
password = "";
break;
case 1: // dbAlias specified
alias = argv[0];
userId = "";
password = "";
break;
case 2: // userId & passwd specified
alias = "sample";
userId = argv[0];
password = argv[1];
break;
case 3: // dbAlias, userId & passwd specified
alias = argv[0];
userId = argv[1];
password = argv[2];
break;
case 4: // use default dbAlias
alias = "sample";
server = argv[0];
portNumber = Convert.ToInt32(argv[1]);
userId = argv[2];
password = argv[3];
break;
case 5: // everything specified
alias = argv[0];
server = argv[1];
portNumber = Convert.ToInt32(argv[2]);
userId = argv[3];
password = argv[4];
break;
}
if(portNumber==-1)
{
connectString = "Database=" + alias;
}
else
{
connectString = "Server=" + server + ":" + portNumber +
";Database=" + alias;
}
if(userId != "")
{
connectString += ";UID=" + userId + ";PWD=" + password;
}
DB2Connection conn = new DB2Connection(connectString);
conn.Open();
Console.WriteLine(" Connected to the " + alias + " database");
return conn;
} // ConnectDb
// This method demonstrates how to grant privileges on a table
public static void Grant(DB2Connection conn, DB2Transaction trans)
{
Console.WriteLine();
Console.WriteLine(
" ----------------------------------------------------------\n" +
" USE THE SQL STATEMENTS:\n" +
" GRANT (Table, View, or Nickname Privileges)\n" +
" COMMIT\n" +
" TO GRANT PRIVILEGES ON A TABLE.");
Console.WriteLine();
Console.WriteLine(
" GRANT SELECT, INSERT, UPDATE(salary, comm)\n" +
" ON TABLE staff\n" +
" TO USER user1");
try
{
// Create a DB2Command to execute the SQL statement
DB2Command cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = "GRANT SELECT, INSERT, UPDATE(salary, comm) " +
" ON TABLE staff" +
" TO USER user1";
cmd.ExecuteNonQuery();
Console.WriteLine();
// Commit the transaction
Console.WriteLine(" COMMIT");
trans.Commit();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} // Grant
// This method demonstrates how to display privileges on a table
public static void Display(DB2Connection conn, DB2Transaction trans)
{
Console.WriteLine();
Console.WriteLine(
" ----------------------------------------------------------\n" +
" USE THE SQL STATEMENT:\n" +
" SELECT\n" +
" TO DISPLAY PRIVILEGES ON A TABLE.");
Console.WriteLine();
Console.WriteLine(
" SELECT granteetype, controlauth, alterauth,\n" +
" deleteauth, indexauth, insertauth,\n" +
" selectauth, refauth, updateauth\n" +
" FROM syscat.tabauth\n" +
" WHERE grantee = 'USER1' AND\n" +
" tabname = 'STAFF'");
try
{
// Create a DB2Command to execute the query
DB2Command cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText =
"SELECT granteetype, controlauth, alterauth, " +
" deleteauth, indexauth, insertauth, " +
" selectauth, refauth, updateauth " +
" FROM syscat.tabauth " +
" WHERE grantee = 'USER1' AND " +
" tabname = 'STAFF' ";
DB2DataReader reader = cmd.ExecuteReader();
// Retrieve the privileges from the DB2DataReader
reader.Read();
String granteetype = reader.GetString(0);
String controlauth = reader.GetString(1);
String alterauth = reader.GetString(2);
String deleteauth = reader.GetString(3);
String indexauth = reader.GetString(4);
String insertauth = reader.GetString(5);
String selectauth = reader.GetString(6);
String refauth = reader.GetString(7);
String updateauth = reader.GetString(8);
reader.Close();
// Display the privileges
Console.WriteLine();
Console.WriteLine(
" Grantee Type = " + granteetype + "\n" +
" CONTROL priv. = " + controlauth + "\n" +
" ALTER priv. = " + alterauth + "\n" +
" DELETE priv. = " + deleteauth + "\n" +
" INDEX priv. = " + indexauth + "\n" +
" INSERT priv. = " + insertauth + "\n" +
" SELECT priv. = " + selectauth + "\n" +
" REFERENCES priv. = " + refauth + "\n" +
" UPDATE priv. = " + updateauth);
trans.Commit();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} // Display
// This method demonstrates how to revoke privileges on a table
public static void Revoke(DB2Connection conn, DB2Transaction trans)
{
Console.WriteLine();
Console.WriteLine(
" ----------------------------------------------------------\n" +
" USE THE SQL STATEMENTS:\n" +
" REVOKE (Table, View, or Nickname Privileges)\n" +
" COMMIT\n" +
" TO REVOKE PRIVILEGES ON A TABLE.");
Console.WriteLine();
Console.WriteLine(" REVOKE SELECT, INSERT, UPDATE\n" +
" ON TABLE staff\n" +
" FROM USER user1");
try
{
// Create a DB2Command to execute the SQL statement
DB2Command cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = "REVOKE SELECT, INSERT, UPDATE " +
" ON TABLE staff" +
" FROM USER user1";
cmd.ExecuteNonQuery();
// Commit the transaction
Console.WriteLine();
Console.WriteLine(" COMMIT");
trans.Commit();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} // Revoke
} // TbPriv