'****************************************************************************
' (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.vb
'
' 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
'
'
'****************************************************************************
'
' Building and Running the sample program
'
' 1. Compile the TbPriv.vb file with bldapp.bat by entering the following
' at the command prompt:
'
' bldapp TbPriv
'
' or compile Tbpriv.vb 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
'
'****************************************************************************
Imports System
Imports System.Data
Imports Microsoft.VisualBasic
Imports IBM.Data.DB2
Public Class TbPriv
Public Shared Sub Main(args() As String)
' Declare a DB2Connection and a DB2Transaction
Dim conn As DB2Connection
Dim trans As DB2Transaction
Try
Console.WriteLine()
Console.WriteLine( _
" THIS SAMPLE SHOWS HOW TO GRANT, DISPLAY AND REVOKE " & _
vbNewLine & " PRIVILEGES ON A TABLE.")
Console.WriteLine()
' Connect to a database
Console.WriteLine(" 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(vbNewline & _
" Disconnect from the database.")
conn.Close()
Catch e As Exception
Console.WriteLine(e.Message)
If Not (conn is System.DBNull.value) Then
conn.Close()
End If
End Try
End Sub ' Main
' This method establishes a connection to a database
Public Shared Function ConnectDb(argv() As String) As DB2Connection
Dim server As String
Dim dbalias As String
Dim userId As String
Dim password As String
Dim portNumber As Int32 = -1
Dim connectString As String
If (argv.Length > 5) Then
Throw new Exception( _
"Usage: prog_name [dbAlias] [userId passwd]" & vbNewLine & _
" prog_name [dbAlias] server portNum userId passwd")
Else
If (argv.Length = 1) Then
If( String.Compare(argv(0),"?") = 0 Or _
String.Compare(argv(0),"-?") = 0 Or _
String.Compare(argv(0),"/?") = 0 Or _
String.Compare(argv(0),"-h",true) = 0 Or _
String.Compare(argv(0),"/h",true) = 0 Or _
String.Compare(argv(0),"-help",true) = 0 Or _
String.Compare(argv(0),"/help",true) = 0 ) Then
Throw new Exception( _
"Usage: prog_name [dbAlias] [userId passwd]" & vbNewLine & _
" prog_name [dbAlias] server portNum userId passwd")
End If
End If
End If
Select Case (argv.Length)
Case 0 ' Use all defaults
dbalias = "sample"
userId = ""
password = ""
Case 1 ' dbAlias specified
dbalias = argv(0)
userId = ""
password = ""
Case 2 ' userId & passwd specified
dbalias = "sample"
userId = argv(0)
password = argv(1)
Case 3 ' dbAlias, userId & passwd specified
dbalias = argv(0)
userId = argv(1)
password = argv(2)
Case 4 ' use default dbAlias
dbalias = "sample"
server = argv(0)
portNumber = Convert.ToInt32(argv(1))
userId = argv(2)
password = argv(3)
Case 5 ' everything specified
dbalias = argv(0)
server = argv(1)
portNumber = Convert.ToInt32(argv(2))
userId = argv(3)
password = argv(4)
End Select
If(portNumber = -1) Then
connectString = "Database=" & dbalias
Else
connectString = "Server=" & server & ":" & portNumber & _
";Database=" & dbalias
End If
If (userId <> "")
connectString += ";UID=" & userId & ";PWD=" & password
End If
Dim conn As DB2Connection = new DB2Connection(connectString)
conn.Open()
Console.WriteLine(" Connected to the " & dbalias & " database")
Return conn
End Function ' ConnectDb
' This method demonstrates how to grant privileges on a table
Public Shared Sub Grant(conn As DB2Connection, trans As DB2Transaction)
Console.WriteLine()
Console.WriteLine( _
" ----------------------------------------------------------" & _
vbNewLine & _
" USE THE SQL STATEMENTS:" & vbNewLine & _
" GRANT (Table, View, or Nickname Privileges)" & vbNewLine & _
" COMMIT" & vbNewLine & _
" TO GRANT PRIVILEGES ON A TABLE.")
Console.WriteLine()
Console.WriteLine( _
" GRANT SELECT, INSERT, UPDATE(salary, comm)" & vbNewLine & _
" ON TABLE staff" & vbNewLine & _
" TO USER user1")
Try
' Create a DB2Command to execute the SQL statement
Dim cmd As DB2Command = 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 e As Exception
Console.WriteLine(e.Message)
End Try
End Sub ' Grant
' This method demonstrates how to display privileges on a table
Public Shared Sub Display(conn As DB2Connection, trans As DB2Transaction)
Console.WriteLine()
Console.WriteLine( _
" ----------------------------------------------------------" & _
vbNewLine & _
" USE THE SQL STATEMENT:" & vbNewLine & _
" SELECT" & vbNewLine & _
" TO DISPLAY PRIVILEGES ON A TABLE.")
Console.WriteLine()
Console.WriteLine( _
" SELECT granteetype, controlauth, alterauth," & vbNewLine & _
" deleteauth, indexauth, insertauth," & vbNewLine & _
" selectauth, refauth, updateauth" & vbNewLine & _
" FROM syscat.tabauth" & vbNewLine & _
" WHERE grantee = 'USER1' AND" & vbNewLine & _
" tabname = 'STAFF'")
Try
' Create a DB2Command to execute the query
Dim cmd As DB2Command = 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' "
Dim reader As DB2DataReader = cmd.ExecuteReader()
' Retrieve the privileges from the DB2DataReader
reader.Read()
Dim granteetype As String = reader.GetString(0)
Dim controlauth As String = reader.GetString(1)
Dim alterauth As String = reader.GetString(2)
Dim deleteauth As String = reader.GetString(3)
Dim indexauth As String = reader.GetString(4)
Dim insertauth As String = reader.GetString(5)
Dim selectauth As String = reader.GetString(6)
Dim refauth As String = reader.GetString(7)
Dim updateauth As String= reader.GetString(8)
reader.Close()
' Display the privileges
Console.WriteLine()
Console.WriteLine( _
" Grantee Type = " & granteetype & "" & vbNewLine & _
" CONTROL priv. = " & controlauth & "" & vbNewLine & _
" ALTER priv. = " & alterauth & "" & vbNewLine & _
" DELETE priv. = " & deleteauth & "" & vbNewLine & _
" INDEX priv. = " & indexauth & "" & vbNewLine & _
" INSERT priv. = " & insertauth & "" & vbNewLine & _
" SELECT priv. = " & selectauth & "" & vbNewLine & _
" REFERENCES priv. = " & refauth & "" & vbNewLine & _
" UPDATE priv. = " & updateauth)
trans.Commit()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub ' Display
' This method demonstrates how to revoke privileges on a table
Public Shared Sub Revoke(conn As DB2Connection, trans As DB2Transaction)
Console.WriteLine()
Console.WriteLine( _
" ----------------------------------------------------------" & _
vbNewLine & _
" USE THE SQL STATEMENTS:" & vbNewLine & _
" REVOKE (Table, View, or Nickname Privileges)" & vbNewLine & _
" COMMIT" & vbNewLine & _
" TO REVOKE PRIVILEGES ON A TABLE.")
Console.WriteLine()
Console.WriteLine(" REVOKE SELECT, INSERT, UPDATE" & vbNewLine & _
" ON TABLE staff" & vbNewLine & _
" FROM USER user1")
Try
' Create a DB2Command to execute the SQL statement
Dim cmd As DB2Command = 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 e As Exception
Console.WriteLine(e.Message)
End Try
End Sub ' Revoke
End Class ' TbPriv