'TITLE: SELECTIVELY ADD SYBASE PERMISSIONS TO POSTSQL.BAS
'DESCRIPTION: This macro will add permissions to the PostSQL of
' any selected table in an Sybase or MS SQL Server 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 ObjectName As String
Dim MyView As View
'Set ERStudio variable
Set MyDiagram = DiagramManager.ActiveDiagram
Set MyModel = MyDiagram.ActiveModel
Set MySubModel = MyModel.ActiveSubModel
'create dialog
Begin Dialog UserDialog 450,287,"GRANT Options" ' %GRID:10,7,1,1
Text 30,28,130,14,"Specify User/Role:",.Text1
TextBox 190,21,160,21,.usertxt
Text 30,56,140,14,"Choose Permissions:",.Text2
CheckBox 60,77,120,14,"INSERT",.InsertChbx
CheckBox 60,112,110,14,"UPDATE",.UpdateChbx
CheckBox 60,182,120,14,"REFERENCES",.ReferenceChbx
CheckBox 60,217,110,14,"DELETE",.DeleteChbx
CheckBox 60,147,120,14,"SELECT",.SelectChbx
CheckBox 200,77,160,14,"With Grant Option",.InsWithGrantChbx
CheckBox 200,112,150,14,"With Grant Option",.UpdWithGrantChbx
CheckBox 200,147,150,14,"With Grant Option",.SelWithGrantChbx
CheckBox 200,182,150,14,"With Grant Option",.RefWithGrantChbx
CheckBox 200,217,150,14,"With Grant Option",.DelWithGrantChbx
OKButton 40,252,140,21
CancelButton 260,252,140,21
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
grant = "GRANT INSERT ON " & ObjectName & " TO " & dlg.usertxt
If dlg.Inswithgrantchbx = 1 Then
grant = grant & " WITH GRANT OPTION" & vbCrLf & "go" & vbCrLf
Else
grant = grant & vbCrLf & "go" & vbCrLf
End If
End If
'Add References grant
If dlg.referencechbx = 1 Then
grant = grant & "GRANT REFERENCES ON " & ObjectName & " TO " & dlg.usertxt
If dlg.refwithgrantchbx = 1 Then
grant = grant & " WITH GRANT OPTION" & vbCrLf & "go" & vbCrLf
Else
grant = grant & vbCrLf & "go" & vbCrLf
End If
End If
'Add update grant
If dlg.updatechbx = 1 Then
grant = grant & "GRANT UPDATE ON " & ObjectName & " TO " & dlg.usertxt
If dlg.updwithgrantchbx = 1 Then
grant = grant & " WITH GRANT OPTION" & vbCrLf & "go" & vbCrLf
Else
grant = grant & vbCrLf & "go" & vbCrLf
End If
End If
'add select grant
If dlg.selectchbx = 1 Then
grant = grant & "GRANT SELECT ON " & ObjectName & " TO " & dlg.usertxt
If dlg.selwithgrantchbx = 1 Then
grant = grant & " WITH GRANT OPTION" & vbCrLf & "go" & vbCrLf
Else
grant = grant & vbCrLf & "go" & vbCrLf
End If
End If
'add delete grant
If dlg.deletechbx = 1 Then
grant = grant & "GRANT DELETE ON " & ObjectName & " TO " & dlg.usertxt
If dlg.delwithgrantchbx = 1 Then
grant = grant & " WITH GRANT OPTION" & vbCrLf & "go" & vbCrLf
Else
grant = grant & vbCrLf & "go" & vbCrLf
End If
End If
End If
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