Import Permissions into PowerDesigner
Have you ever had to create permissions for users to many objects? And did you want to maintain these permissions in your PowerDesigner Physical Data Model (PDM)?
I recently had to add permissions to an Oracle database that I had reverse-engineered into PowerDesigner, but PD did not import the permissions! I haven't figured out why not yet.
But with the help of and who responded to my post on LinkedIn's
Sybase PowerDesigner Users group, I was able to identify all objects that needed permissions added and then create an Excel file to import them using the Excel Imports extension.
By the way, permissions are also known as "grants" since that is the DDL command to give permissions to users on objects:
GRANT action ON object TO user;
Excel Imports
Before you can import into PD from an Excel file, you must add the "Excel Import" extension to your model. The Comment section for this extension says:
"This extension allows you to create PowerDesigner model objects from tables of data imported from an Microsoft Excel file."
I won't go into the details of how to attach this extension to your model in this post. But if you need help doing that, just email me.
Excel File Template
Before you can create an Excel Imports object, you must create the Excel source file. This is where I ran into problems because PD documentation does not provide detailed guidance for this. There is a sample Excel import file located at C:\Program Files\Sybase\PowerDesigner 16\Examples (assuming you use the default installation folders) named excel_import.xls. It contains very brief examples for Table, Table.Column, Reference, Reference.Reference Join, Table.Key, and Reference2.
So, creating an Excel file to import for permissions requires a lot of guesswork. I started by creating a list report for permissions to find the column headings to use. You could use any heading you want, but then you have to know which attribute to map that column heading to. If you use column headings that PD already knows about, it will provide suggestions when you manually map the columns. (I don't use the "Auto-map columns to properties" import option.)
My permissions list report included the following columns:
- Parent
- In the List Report, this column is populated like this:
Table 'RPTUSERN'.'ACCOUNT_CODE_CATEGORIES'
- When populating your Excel Import file, only put the table's Name or Code value. So the above would become:
ACCOUNT_CODE_CATEGORIES
- Required column in Excel file
- Object
- This is the name or code (depending on how you have your model configured) of the table, view, or procedure. Since the object could be a table, view, or procedure, I added the "Object.Object Type" column to let me know. If you only want tables, you could filter the report for Table types and not display the column.
- Required column in Excel file
- User
- The User shown is not the owner of the object, but the user the permission is to be granted to. Make sure you have this user created in the PDM even if it doesn't own any objects.
- Required column in Excel file
- Grant
- This is the permission(s) you want to give to the user. I only granted SELECT, but if you have granted multiple permissions to a user for the object, they would be displayed in the list report as a comma-separated list. I don't know if a comma-separated list of permissions can be imported this way; someone should try that and let me know (or I'll have to try that later myself).
- Required column in Excel file
- Grant With Option
- If you want the user to be able to grant other users this permission, enter the permission in this column.
- Optional column in Excel file
- Revoke
- While PD provides this column for the list report and they do show up, but grayed out, on the Permissions tab of an object, I haven't figured out how they would be used since you can't grant a permission and revoke it at the same time. And PD doesn't seem to provide an option to mark an existing permission with a "to be revoked" option.
- You don't have to include this column.
- Revoke With Cascade
- While PD provides this column for the list report and they do show up, but grayed out, on the Permissions tab of an object, I haven't figured out how they would be used since you can't grant a permission and revoke it at the same time. And PD doesn't seem to provide an option to mark an existing permission with a "to be revoked" option.
- You don't have to include this column.
- Object.Object Type
- I included this column in the list report only to show me what type object was being reported. I could have filtered the rows by this column to only show table, view, or procedure objects.
- You don't have to include this column.
From this list report, I created an Excel file with same column names; only the first four are required. The fifth is optional and only needed if you want to allow the user to be able to grant the permission to other users.
Determine Objects Without Permissions
To reduce the number of entries needed in the Excel file, I needed to know which objects had missing permissions. The list report I created above only showed those objects that had permissions.
suggested I run the Check Model function to determine which objects would fail the 'Existence of Permissions' check. I did this and got an ugly list I could retreive from the Output window, but it requires a lot of editing to get just a list of the name/code value for the tables, views, or procedures. These name/code values would be the ones you would paste into the Parent and Object (or any other name you wanted for this column) of the Excel import file.
suggest I try a dependency matrix to get this list. I hadn't used a dependency matrix before, so this was (is) a learning experience for me. The defininition settings I used for my dependency matrix are:
- Object types
- Rows = Table
- Columns = User
- Matrix cells
- Dependency = Permissions
- Object type = Permission
- Object attribute = Grant
- While "Grant With Option" is also available, I couldn't find any way to have both attributes selected. So I'll have to create a second dependency matrix for "Grant With Option" attribute.
A separate dependency matrix will need to be created for View and Procedure object.
This dependency matrix is displayed in the browser the same way as a diagram. There is an icon at the top that will allow you to "Display only empty rows" that can be copied and pasted into the Excel import file. The objects in the left colum will be placed in both the Parent and Object columns of the import file.
Import Excel File
Now that you have 1) added the Excel Import extension, 2) determined which objects and users need permissions added, and 3) populated your Excel import file, you are ready to actually import the file into your physical data model.
I won't detail how to do that since that is fairly intuitive. Start the import by right-clicking in an open diagram for the model and selecting "Import Excel File...".
) If someone knows how to do that, please let all of us know with your added comment.
Posts
