PowerDesigner create table grants in VB

Question

I'm trying to create a script to automatically grant select,update,delete,insert on tables to specific users.

Dim ModelTables, table, tab, newPerm
SET ModelTables = obj.Tables

For Each table in ModelTables

  If IsObject(table) Then
     Set tab = table

     'Testing just on one table
     If InStr(tab.Name, "MY_TEST_TABLE")=1 Then

         set newPerm = tab.Permissions.createNew()
         '-- this is all I managed to create

     End If
  End if
Next

This is all I managed to create. I don't know the structure of a Permission. I'm also totaly new to VB. Can somebody support me with a hint / proper code / documentation, please?

This is a side question, but depending on your target DBMS, why don't you put the users in a group, and just grant the permissions to this group? — pascal, Apr 06 '17 at 13:48
Add `option explicit` at the beginning at the script, it really helps the development... — pascal, Apr 06 '17 at 13:50

score 0 · Answer 1 · answered Apr 06 '17 at 14:27

Here is a sample to create new permissions, and list the existing ones.

option explicit
dim obj
set obj = activemodel
dim ModelTables : set ModelTables = obj.Tables
dim u : set u = Finduser("User_1")
if not u is nothing then
   dim table
   For Each table in ModelTables
      If IsObject(table) Then
         dim tab
         Set tab = table
         dim p
         if tab.Permissions.count = 0 then
            output "creating permission on " & tab.name
            set p = tab.Permissions.createNew
            set p.DBIdentifier = u
            p.grant = "SELECT,DELETE,INSERT,UPDATE"
         end if
         ' show permissions
         output "permissions on " & tab.name
         for each p in tab.Permissions
            output "   for user " & p.DBIdentifier & " : " & p.grant
         next
      End if
   Next
end if

function FindUser(name)
   dim u
   for each u in activemodel.users
      if u.name = name then
         set FindUser = u
         output "found user"
      end if
   next
end function

PowerDesigner create table grants in VB

1 Answers1