'MACRO TITLE: SELECTIVELY INSERTADD ORACLESYBASE SYNONYMPERMISSIONS INTO POSTSQL.BAS
'DESCRIPTION: This Macromacro will add apermissions Synonymto tothe aPostSQL specifiedof table/view.
'The macro will prompt the user with a dialog to chooseany the table/viewselected table in an Sybase or MS SQL Server physical model.
'TheAUTHOR: synonymJason codeTiret
will be inserted into the PostSQL'DATE: of the specified 2/2/2000
'table/view.
Sub Main
Dim MyDiagram As Diagram
Dim MyEntity As Entity
Dim MyModel As Model
Dim MyDiagramMySelObj As DiagramSelectedObject
Dim MyEntityMySubModel As EntitySubModel
Dim MyViewgrant As ViewString
Dim SynonymID As StringInteger
Dim ObjectName As String
'Current Model has to be physicalDim MyView As View
'setSet ERERStudio variablesvariable
Set MyDiagram = DiagramManager.ActiveDiagram
Set MyModel = MyDiagram.ActiveModel
Set MySubModel = MyModel.ActiveSubModel
If MyModel.Logical = True Then
'create dialog
Begin Dialog UserDialog 450,287,"GRANT Options" ' %GRID:10,7,1,1
MsgBox("Current Model has to be Physical.")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
ElseIf MyModel.MajorPlatform <> "Oracle 7/8" Then 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
MsgBox ("Current model has to be Oracle 7/8 platform.")
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
Else CheckBox 200,182,150,14,"With Grant Option",.RefWithGrantChbx
CheckBox 200,217,150,14,"With Grant Option",.DelWithGrantChbx
'initialize ObjectArray that holds tables and views OKButton 40,252,140,21
CancelButton 260,252,140,21
'forEnd the Dialog list
Dim ObjectCountdlg As Integer UserDialog
ObjectCountIf Dialog(dlg) = MyModel.Entities.Count + MyModel.Views.Count-1 Then
ReDim ObjectArray( 0 To ObjectCount ) As String
For Each MySelObj In MySubModel.SelectedObjects
'make sure selected object is an Entity
Dim count As Integer If MySelObj.Type = 1 Or MySelObj.Type = 16 Then
count ID = 0MySelObj.ID
'insert tables and views into the ObjectArray for the dialog list If MySelObj.Type = 1 Then
For Each Set MyEntity In= MyModel.Entities .Item(ID)
ObjectArray(count)ObjectName = MyEntity.EntityNameTableName
count
= count + 1
ElseIf MySelObj.Type = 16 NextThen
For Each Set MyView In= MyModel.Views.Item(ID)
ObjectArray(count)ObjectName = MyView.Name
count = count + 1End If
Next
'Add insert grant
If dlg.insertchbx = 1 Then
Begin Dialog UserDialog 510,224,"Synonym Editor" ' %GRID:10,7,1,1 grant = "GRANT INSERT ON " & ObjectName & " TO " & dlg.usertxt
If dlg.Inswithgrantchbx = 1 Then
GroupBox 20,7,470,161,"Choose the Table or View that you would like to create a synonym for:",.GroupBox1 grant = grant & " WITH GRANT OPTION" & vbCrLf & "go" & vbCrLf
Else
grant = grant & vbCrLf & "go" & vbCrLf
CancelButton 350,182,130,28 End If
DropListBox 40,28,220,168,ObjectArray(),.ObjectList End If
OKButton 200,182,130,28 'Add References grant
TextBox 40,140,220,21,.SynName If dlg.referencechbx = 1 Then
OptionGroup .Group1 grant = grant & "GRANT REFERENCES ON " & ObjectName & " TO " & dlg.usertxt
OptionButton 40,91,60,14,"Yes",.yesIf dlg.refwithgrantchbx = 1 Then
OptionButton 110,91,60,14,"No",.nogrant = grant & " WITH GRANT OPTION" & vbCrLf & "go" & vbCrLf
Text 30,70,430,14,"Do you want the Synonym accessible to all users (ie, PUBLIC)?",.Text2
Else
Text 30,119,370,14,"Please give a name for your synonym.",.Text1 grant = grant & vbCrLf & "go" & vbCrLf
End DialogIf
Dim dlg As UserDialog
End If
'Add update grant
If Dialog(dlg).updatechbx = -1 Then
grant = grant & "GRANT UPDATE ON " & ObjectName & " TO " & dlg.usertxt
If dlg.group1updwithgrantchbx = 01 Then
Synonym grant = "CREATE PUBLIC SYNONYM " & dlg.SynName & vbCrLf 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
Synonym grant = Synonym grant & "GRANT SELECT ON " & vbTabObjectName & "FOR TO " & ObjectArray(dlg.objectlist) dlg.usertxt
If dlg.selwithgrantchbx = 1 Then
grant = grant & " WITH GRANT OPTION" & vbCrLf & ";go" & vbCrLf
Else
Synonym grant = "CREATE SYNONYM " & dlg.SynName & vbCrLf grant & vbCrLf & "go" & vbCrLf
End If
End If
'add delete grant
If dlg.deletechbx = 1 Then
Synonym grant = Synonym grant & "GRANT DELETE ON " & vbTabObjectName & "FOR TO " & ObjectArray(dlg.objectlist)dlg.usertxt
If dlg.delwithgrantchbx = 1 Then
grant = grant & " WITH GRANT OPTION" & vbCrLf & ";go" & vbCrLf
End If Else
Dim ObjName As String grant = grant & vbCrLf & "go" & vbCrLf
ObjName = ObjectArray(dlg.objectlist) End If
Set MyEntity = MyModel.Entities.Item(ObjName) End If
Set MyView = MyModel.Views.Item(ObjName) End If
If MyViewMySelObj.Type Is= Nothing1 Then
MyEntity.PostSQL = MyEntity.PostSQL & Synonymgrant
Else ElseIf MySelObj.Type = 16 Then
MyView.PostSQL = MyView.PostSQL & Synonym grant
End If
End If grant = ""
End If 'dialog
Next
End If 'Physical checkDialog
End Sub