'TITLE: SELECTIVELY ADD DB2 PERMISSIONS TO POSTSQL.BAS 'DESCRIPTION: This macro will add permissions to the PostSQL of ' any selected table in an DB2 physical model. 'AUTHOR: Jason Tiret 'DATE: 2/2/2000 Sub Main Dim MyDiagram As Diagram Dim MyEntity As Entity Dim MyModel As Model Dim MySelObj As SelectedObject Dim MySubModel As SubModel Dim grant As String Dim ID As Integer Dim MyView As View Dim ObjectName As String 'Set ERStudio variable Set MyDiagram = DiagramManager.ActiveDiagram Set MyModel = MyDiagram.ActiveModel Set MySubModel = MyModel.ActiveSubModel 'create dialog Begin Dialog UserDialog 400,392,"GRANT Statement Options" ' %GRID:10,7,1,1 Text 30,28,130,14,"Specify User/Role:",.Text1 TextBox 190,21,160,21,.usertxt Text 20,84,140,14,"Choose Permissions:",.Text2 CheckBox 60,112,120,14,"INSERT",.InsertChbx CheckBox 60,140,110,14,"UPDATE",.UpdateChbx CheckBox 60,196,110,14,"ALTER",.AlterChbx CheckBox 60,224,110,14,"DELETE",.DeleteChbx CheckBox 60,168,120,14,"SELECT",.SelectChbx CheckBox 200,112,160,14,"With Grant Option",.InsWithGrantChbx CheckBox 200,140,150,14,"With Grant Option",.UpdWithGrantChbx CheckBox 200,168,150,14,"With Grant Option",.SelWithGrantChbx CheckBox 200,196,150,14,"With Grant Option",.AltWithGrantChbx CheckBox 200,224,150,14,"With Grant Option",.DelWithGrantChbx OKButton 40,350,140,21 CancelButton 210,350,140,21 CheckBox 60,252,120,14,"CONTROL",.ControlChbx CheckBox 60,280,120,14,"REFERENCES",.referencesChbx CheckBox 60,308,110,14,"INDEX",.IndexChbx CheckBox 200,252,150,14,"With Grant Option",.ConWithGrantChbx CheckBox 200,280,150,14,"With Grant Option",.RefWithGrantChbx CheckBox 200,308,160,14,"With Grant Option",.IndWithGrantChbx OptionGroup .UserChoice OptionButton 30,56,120,14,"Not Specified",.OptionButton1 OptionButton 170,56,80,14,"USER",.OptionButton2 OptionButton 270,56,90,14,"GROUP",.OptionButton3 End Dialog Dim dlg As UserDialog If Dialog(dlg) = -1 Then For Each MySelObj In MySubModel.SelectedObjects 'make sure selected object is an Entity If MySelObj.Type = 1 Or MySelObj.Type = 16 Then ID = MySelObj.ID If MySelObj.Type = 1 Then Set MyEntity = MyModel.Entities.Item(ID) ObjectName = MyEntity.TableName ElseIf MySelObj.Type = 16 Then Set MyView = MyModel.Views.Item(ID) ObjectName = MyView.Name End If 'Add insert grant If dlg.insertchbx = 1 Then Select Case dlg.userchoice Case 0 grant = "GRANT INSERT ON " & ObjectName & " TO " & dlg.usertxt Case 1 grant = "GRANT INSERT ON " & ObjectName & " TO USER " & dlg.usertxt Case 2 grant = "GRANT INSERT ON " & ObjectName & " TO GROUP " & dlg.usertxt End Select If dlg.Inswithgrantchbx = 1 Then grant = grant & " WITH GRANT OPTION" & vbCrLf & ";" & vbCrLf Else grant = grant & vbCrLf & ";" & vbCrLf End If End If 'Add Alter grant If dlg.alterchbx = 1 Then Select Case dlg.userchoice Case 0 grant = grant & "GRANT ALTER ON " & ObjectName & " TO " & dlg.usertxt Case 1 grant = grant & "GRANT ALTER ON " & ObjectName & " TO USER " & dlg.usertxt Case 2 grant = grant & "GRANT ALTER ON " & ObjectName & " TO GROUP " & dlg.usertxt End Select If dlg.altwithgrantchbx = 1 Then grant = grant & " WITH GRANT OPTION" & vbCrLf & ";" & vbCrLf Else grant = grant & vbCrLf & ";" & vbCrLf End If End If 'Add update grant If dlg.updatechbx = 1 Then Select Case dlg.userchoice Case 0 grant = grant & "GRANT UPDATE ON " & ObjectName & " TO " & dlg.usertxt Case 1 grant = grant & "GRANT UPDATE ON " & ObjectName & " TO USER " & dlg.usertxt Case 2 grant = grant & "GRANT UPDATE ON " & ObjectName & " TO GROUP " & dlg.usertxt End Select If dlg.updwithgrantchbx = 1 Then grant = grant & " WITH GRANT OPTION" & vbCrLf & ";" & vbCrLf Else grant = grant & vbCrLf & ";" & vbCrLf End If End If 'add select grant If dlg.selectchbx = 1 Then Select Case dlg.userchoice Case 0 grant = grant & "GRANT SELECT ON " & ObjectName & " TO " & dlg.usertxt Case 1 grant = grant & "GRANT SELECT ON " & ObjectName & " TO USER " & dlg.usertxt Case 2 grant = grant & "GRANT SELECT ON " & ObjectName & " TO GROUP " & dlg.usertxt End Select If dlg.selwithgrantchbx = 1 Then grant = grant & " WITH GRANT OPTION" & vbCrLf & ";" & vbCrLf Else grant = grant & vbCrLf & ";" & vbCrLf End If End If 'add delete grant If dlg.deletechbx = 1 Then Select Case dlg.userchoice Case 0 grant = grant & "GRANT DELETE ON " & ObjectName & " TO " & dlg.usertxt Case 1 grant = grant & "GRANT DELETE ON " & ObjectName & " TO USER " & dlg.usertxt Case 2 grant = grant & "GRANT DELETE ON " & ObjectName & " TO GROUP " & dlg.usertxt End Select If dlg.delwithgrantchbx = 1 Then grant = grant & " WITH GRANT OPTION" & vbCrLf & ";" & vbCrLf Else grant = grant & vbCrLf & ";" & vbCrLf End If End If 'add control grant If dlg.controlchbx = 1 Then Select Case dlg.userchoice Case 0 grant = grant & "GRANT CONTROL ON " & ObjectName & " TO " & dlg.usertxt Case 1 grant = grant & "GRANT CONTROL ON " & ObjectName & " TO USER " & dlg.usertxt Case 2 grant = grant & "GRANT CONTROL ON " & ObjectName & " TO GROUP " & dlg.usertxt End Select If dlg.conwithgrantchbx = 1 Then grant = grant & " WITH GRANT OPTION" & vbCrLf & ";" & vbCrLf Else grant = grant & vbCrLf & ";" & vbCrLf End If End If 'add references grant If dlg.referenceschbx = 1 Then Select Case dlg.userchoice Case 0 grant = grant & "GRANT REFERENCES ON " & ObjectName & " TO " & dlg.usertxt Case 1 grant = grant & "GRANT REFERENCES ON " & ObjectName & " TO USER " & dlg.usertxt Case 2 grant = grant & "GRANT REFERENCES ON " & ObjectName & " TO GROUP " & dlg.usertxt End Select If dlg.Refwithgrantchbx = 1 Then grant = grant & " WITH GRANT OPTION" & vbCrLf & ";" & vbCrLf Else grant = grant & vbCrLf & ";" & vbCrLf End If End If 'add index grant If dlg.indexchbx = 1 Then Select Case dlg.userchoice Case 0 grant = grant & "GRANT INDEX ON " & ObjectName & " TO " & dlg.usertxt Case 1 grant = grant & "GRANT INDEX ON " & ObjectName & " TO USER " & dlg.usertxt Case 2 grant = grant & "GRANT INDEX ON " & ObjectName & " TO GROUP " & dlg.usertxt End Select If dlg.indwithgrantchbx = 1 Then grant = grant & " WITH GRANT OPTION" & vbCrLf & ";" & vbCrLf Else grant = grant & vbCrLf & ";" & vbCrLf End If End If End If 'Selected Objects If MySelObj.Type = 1 Then MyEntity.PostSQL = MyEntity.PostSQL & grant ElseIf MySelObj.Type = 16 Then MyView.PostSQL = MyView.PostSQL & grant End If grant = "" Next End If 'Dialog End Sub