Replace grants and schema owner/user on all entities including sequences

[gwa_357]

Hello All,

I’m wondering if there is a way in TDM 6.1 to replace grants and schema owners on all entities including sequences.

I have 3 roles on each entity with corresponding permissions and need to replace them with different roles as well as change schema owner/user to a new value on each entity.

Another question is to how to properly assign grants on sequences. Right now i’m adding an “After SQL” with grants for each role. Is there a “better” way? Also, is there a way to mass replace roles in sequences?

Thank you!

gw

[Daril]

Hello,

if you want assign new schema to all entities in model you can use “Schema/Owner/Database Assignment” tool. You can find it in “Main Menu - Tools - Schema/Owner Assignment”. But if you need assign new schema only for some entities(for example all entities that has schemaX assigned) you need create macro for this operation.

For multiple assign grants TDM has not tool. It will be necessarily write some macro.

Please let me know your database type (Oracle 10, SQL Serve 2016 …) and I can write some example macro for you.

Daril

[gwa_357]

Hi Daril,

Thank you for the reply! The database is Oracle 11g R2. I have tried “Schema Assignment” item, however, it did not make any replacements as, it seems, it would only work on entities whose schema is not already assigned, which is not the case here.

I figured out the solution, it’s quiet easy. I just changed (renamed) my roles/users and schema owner, and that change was applied to all objects in the data model. Initially, i create a set of new users and wanted to replace existing with new ones. For sequences and views i changed the SQL manually as there are not that many of those objects in the model.

Thanks!

[Daril]

Hi gwa,

I attached example package. Bellow are some instruction how to work with it.

1. Switch On Expert Mode

Go to "Main Menu - Settings- Options - General" there is checkbox "Expert Mode". Check it if not. Now you have available some additional functionality of TDM.

2. Import Package

Unpack package somewhere on disk.

Go to "Main Menu - Expert Mode - Customization - Import Custom Package". Select this package and confirm it.

3. Open Package Explorer

Go to "Main Menu - Expert Mode - Customization - Package Explorer". Now you have in right side of TDM docked package explorer. You can filter system packages by click on ikon "Hide System Package". In packages should be package Assignment.

4. Edit Macros

In package explorer you can see two macros. One is called ReplaceUser and second ChangeRole. You can see its source code by double click on each macro. Source Code is wrote in JScript. You can modify it how you need.

When you click by right-click on macro you can select form popupmenu item "Properties". In this dialog you can modify caption, position on main menu and popup menu and other.

5. Call Macro ReplaceUser

Open any Oracle model and go to main menu - Macros. There should be new item "Replace User". Select it, you get dialog where you can select Current User and New User. This macro set User to New User in all entities and sequences, where is Current User.

6. Call Macro Change Role

This macro is only as draft, because I don't know exact rules for replace roles (Source Code is commented). You can right click on Entity. And from popup menu choose "Macros - Change Roles".

This Macro change user of permissions to user that is assigned to Entity.

I hope it will help you.

Unfortunately permission in sequences are only on After script now. There are no better way. If you want improve this feature please enter record to Idea pond on this pages.

If you will have any next questions don't hesitate contatc me

Daril
Assignment.zip (2.14 KB)

[adolfoalta38]

Hi, the attachment is not available, where can I download it?

[Michal.Vasut]

Hello adolfoalta38,

yes, you are right, the link is broken. Sorry for inconvenience. Here is the working one:

Assignment.txg