Generating Grant Statements on Tables using PowerDesigner 7 & 8

In some instances, you may want to automate the generation of Grant statements on tables.  This functionality can be implemented via changes in the XDB file and the use of Extended Attributes.  For this document, Sybase AS Anywhere 6 is being used as the target DBMS.

For an overview of how to use PowerDesigner Extended Attributes see one of the following Technical Documents.

Tapping the power of PowerDesigner 7.x Extended Attributes

Tapping the power of PowerDesigner 8.0 Extended Attributes

1) Create a new DBMS file
    - Select Tools>Resources>DBMS
    - Select the "new" icon to create a new DBMS file and give it a name such as Sybase AS Anywhere 6 Extended
    - Select Sybase AS Anywhere 6 in the "Copy from" drop down listbox
    - Click OK

2) Add a new Extended Attribute
    - Right-click on Extended Attributes>Objects, select Add Items and select Table
    - Right-click on Extended Attributes>Objects>Table and select New>Extended Attribute
    - Expand Table and select the newly created Extended Attribute
    - Rename the Extended Attribute to GrantInsert
    - Under Data Type, select (string) from the drop down listbox.
    - Type the database group name or user as the Default Value.  For example, InsertGroup or UserTest.
NOTE: These steps should be repeated for each set of permissions that you want to define.  In this case, GrantUpdate was added as a second variable.
NOTE: The Extended Attribute name will be used as a variable in the Create Table section.  This variable name is case sensitive.

3) Modify the Create Table syntax
    The following syntax must be added to the Create Table item:
     [%GrantInsert%?;
        Grant insert %TABLE% to %GrantInsert%]
     In this example, the variable name = GrantInsert

    - Select the Create Item under Script>Objects>Table
    - At the end of the Create syntax, add
           [%GrantInsert%?;
                Grant insert %TABLE% to %GrantInsert%]
NOTE: The " ; " is the terminator for Sybase ASA 6.  This should be changed to the terminator character for the target DBMS.

Sample code for Create Item:

create table [%QUALIFIER%]%TABLE%
(
    %TABLDEFN%
)
[%OPTIONS%]

[%GrantInsert%?;
Grant insert %TABLE% to %GrantInsert%]
[%GrantUpdate%?;
Grant update %TABLE% to %GrantUpdate%]

4) Click OK
5) Save the new XDB file in the \\program files\PowerDesigner 7\DBMS directory using a name like sqlany6_ext.xdb

To implement the GrantInsert and GrantUpdate extended attribute
1) Change the current DBMS to Sybase AS Anywhere 6 Extended
2) Open the Table properties and select Preview.  By default, both extended attributes are enabled.

To disable the GrantInsert extended attribute
1) Open the Table properties and the Extended Attributes tab
2) Select the GrantInsert extended attribute and delete the current value

3) Click OK to apply the changes
4) Open the Table properties and select preview
 
 

Related Links