'****************************************************************************
' (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.vb
'
' SAMPLE: How to Grant, display and revoke privileges on database
' with the DB2 .NET Data Provider
'
' SQL Statements USED:
' COMMIT
' GRANT
' REVOKE
' SELECT
'
' DB2 .NET Data Provider Classes USED:
' DB2Connection
' DB2Command
' DB2Transaction
'
'
'****************************************************************************
'
' Building and Running the sample program
'
' 1. Compile the DbAuth.vb file with bldapp.bat by entering the following
' at the command prompt:
'
' bldapp DbAuth
'
' or compile DbAuth.vb with the makefile by entering the following at
' the command prompt:
'
' nmake DbAuth
'
' 2. Run the DbAuth program by entering the program name at the command
' prompt:
'
' DbAuth
'
'****************************************************************************
'
' 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 DbAuth
Public Shared Sub Main(args() As String)
Dim conn As DB2Connection
Dim trans As DB2Transaction
Try
Console.WriteLine()
Console.Write(" THIS SAMPLE SHOWS HOW TO GRANT/DISPLAY/REVOKE")
Console.WriteLine(" AUTHORITIES ON DATABASE")
Console.WriteLine()
' Connect to a database
Console.WriteLine(" Connecting to a database ...")
conn = ConnectDb(args)
Console.WriteLine()
' Grant user authorities on a database
trans = conn.BeginTransaction()
Grant(conn,trans)
' Display user authorities on a database
Display(conn)
' Revoke user authorities on a database
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 shows how to grant user authorities on a database
Public Shared Sub Grant(conn As DB2Connection, trans As DB2Transaction)
Console.WriteLine()
Console.WriteLine( _
" ----------------------------------------------------------" & _
vbNewLine & _
" USE THE SQL STATEMENTS:" & vbNewLine & _
" GRANT (Database Authorities)" & vbNewLine & _
" COMMIT" & vbNewLine & _
" TO GRANT AUTHORITIES AT DATABASE LEVEL" & vbNewLine)
Try
Console.WriteLine( _
" GRANT CONNECT, CREATETAB, BINDADD" & vbNewLine & _
" ON DATABASE" & vbNewLine & _
" TO USER user1")
' grant authorities to 'user1'
Dim cmd As DB2Command = conn.CreateCommand()
cmd.CommandText = "GRANT CONNECT, CREATETAB, BINDADD " & _
" ON DATABASE" & _
" TO USER user1"
cmd.Transaction = trans
cmd.ExecuteNonQuery()
' Commit the transaction
Console.WriteLine()
Console.WriteLine(" COMMIT")
trans.Commit()
Catch e As Exception
Console.WriteLine(e.Message)
trans.Rollback()
End Try
End Sub ' Grant
' This method displays the authorities for a user on a database
Public Shared Sub Display(conn As DB2Connection)
Console.WriteLine()
Console.WriteLine( _
" ----------------------------------------------------------" & _
vbNewLine & _
" USE THE SQL STATEMENT:" & vbNewLine & _
" SELECT" & vbNewLine & _
" TO DISPLAY AUTHORITIES FOR ANY USER AT DATABASE LEVEL" & vbNewLine)
Console.WriteLine( _
" SELECT granteetype, dbadmauth, createtabauth," & vbNewLine & _
" bindaddauth, connectauth, nofenceauth," & vbNewLine & _
" implschemaauth, loadauth" & vbNewLine & _
" FROM syscat.dbauth" & vbNewLine & _
" WHERE grantee = 'USER1'" & vbNewLine)
' Retrieve and display the results from the SELECT statement
Try
Dim cmd As DB2Command = conn.CreateCommand()
cmd.CommandText = "SELECT granteetype, dbadmauth, createtabauth, "& _
" bindaddauth, connectauth, nofenceauth, "& _
" implschemaauth, loadauth "& _
" FROM syscat.dbauth " & _
" WHERE grantee = 'USER1'"
Dim reader As DB2DataReader = cmd.ExecuteReader()
reader.Read()
' Retrieve the authorities from the DB2DataReader
Dim granteetype As String = reader.GetString(0)
Dim dbadmauth As String = reader.GetString(1)
Dim createtabauth As String = reader.GetString(2)
Dim bindaddauth As String = reader.GetString(3)
Dim connectauth As String = reader.GetString(4)
Dim nofenceauth As String = reader.GetString(5)
Dim implschemaauth As String = reader.GetString(6)
Dim loadauth As String = reader.GetString(7)
reader.Close()
' Display the authorities
Console.WriteLine( _
" Grantee Type = " & granteetype & vbNewLine & _
" DBADM auth. = " & dbadmauth & vbNewLine & _
" CREATETAB auth. = " & createtabauth & vbNewLine & _
" BINDADD auth. = " & bindaddauth & vbNewLine & _
" CONNECT auth. = " & connectauth & vbNewLine & _
" NO_FENCE auth. = " & nofenceauth & vbNewLine & _
" IMPL_SCHEMA auth. = " & implschemaauth & vbNewLine & _
" LOAD auth. = " & loadauth)
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub 'Display
' This method shows how to revoke user authorities on a database
Public Shared Sub Revoke(conn As DB2Connection, trans As DB2Transaction)
Console.WriteLine()
Console.WriteLine( _
" ----------------------------------------------------------" & _
vbNewLine & _
" USE THE SQL STATEMENTS:" & vbNewLine & _
" REVOKE (Database Authorities)" & vbNewLine & _
" COMMIT" & vbNewLine & _
" TO REVOKE AUTHORITIES AT DATABASE LEVEL.")
Try
' Revoke authorities of 'user1'
Console.WriteLine()
Console.WriteLine( _
" REVOKE CONNECT, CREATETAB, BINDADD" & vbNewLine & _
" ON DATABASE" & vbNewLine & _
" FROM USER user1")
Dim cmd As DB2Command = conn.CreateCommand()
cmd.CommandText = "REVOKE CONNECT, CREATETAB, BINDADD " & _
" ON DATABASE " & _
" FROM USER user1"
cmd.Transaction = trans
cmd.ExecuteNonQuery()
' Commit the transaction
Console.WriteLine()
Console.WriteLine(" COMMIT")
trans.Commit()
Catch e As Exception
Console.WriteLine(e.Message)
trans.Rollback()
End Try
End Sub ' Revoke
End Class ' DbAuth