'TITLE: SELECTIVELY ADD ORACLE PERMISSIONS TO POSTSQL.BAS 'DESCRIPTION: This macro will add permissions to the PostSQL of ' any selected table in an Oracle physical model. 'REQUIREMENT: Active model must be Oracle. '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 450,336,"GRANT Statement 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,110,14,"ALTER",.AlterChbx 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",.AltWithGrantChbx CheckBox 200,217,150,14,"With Grant Option",.DelWithGrantChbx OKButton 40,294,140,21 CancelButton 260,294,140,21 CheckBox 60,252,130,14,"REFERENCES",.referenceschbx CheckBox 200,252,180,14,"With Grant Option",.refwithgrantchbx 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 & ";" & vbCrLf Else grant = grant & vbCrLf & ";" & vbCrLf End If End If 'Add Alter grant If dlg.alterchbx = 1 Then grant = grant & "GRANT ALTER ON " & ObjectName & " TO " & dlg.usertxt 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 grant = grant & "GRANT UPDATE ON " & ObjectName & " TO " & dlg.usertxt 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 grant = grant & "GRANT SELECT ON " & ObjectName & " TO " & dlg.usertxt 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 grant = grant & "GRANT DELETE ON " & ObjectName & " TO " & dlg.usertxt If dlg.delwithgrantchbx = 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 grant = grant & "GRANT REFERENCES ON " & ObjectName & " TO " & dlg.usertxt If dlg.Refwithgrantchbx = 1 Then grant = grant & " WITH GRANT OPTION" & vbCrLf & ";" & vbCrLf Else grant = grant & vbCrLf & ";" & vbCrLf 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 = "" End If Next End If 'Dialog End Sub