Security on Replacament of Derived Table

[leon_berman]

Hello ,

There is permission in the universe so table A is replaced by the table X for a some users.

Now I have built a new universe :new table  C is MERGE between Table A and Table B.

When I want to set permission for a new universe so that Switching between New Table C Table X for a particular user.

Defining the general authorization Table C dosen't presents.

WHAT CAN I DO ?

THANKS

[mhmohammed]

Hi Leon,

It's simple, if you are using Information Design Tool (IDT), launch the Security Editor, navigate to the universe name in the folder structure and right click on it and select Insert Data Security Profile, specify a name as Replacement Table C for User XYZ, go to the Tables tab, and specify the Original and Replacement tables and click OK. After that, select a User (from right hand side where yous ee the list of Users and groups) that you want to apply this security to and voila, you're done.

Thanks,

Mahboob Mohammed

[leon_berman]

Hi Mahboob ,

Thanks a lot for the answer ,

You explained very well how to give permissions,

but the problem is that table C is not on the list of original table, because it is not original ones -  MERGE between Table A and Table B .

[mhmohammed]

Hi Leon,

Table C (merge of tables A,C) is a Derived table created in the Universe? Correct?

If yes, then, instead of creating that Derived table in the Universe, why don't you create a View in the Database (with the SQL used to create that Derived table), and then update the universe to use that View instead of the Derived table.

After that, when you try to apply security to replace Table C with Table X, you should see Table C in the list of Original tables.

Make sense?

Thanks,

Mahboob Mohammed

[leon_berman]

Hi Mahboob ,

Thanks a lot for the answer ,

Your solution Excellent, know existed, I thought I could find something more efficient, so as not to build a new table/ view.

Best Regards

Sincerely Yours

Leon

[leon_berman]

Hi Mahboob ,

I've actions as you explain :

I built a new  View on a database,

Then I replaced the table C with a new View, and when I wanted to set permissions, yet I don't see a new view on permisions window

.

[mhmohammed]

Hi Leon,

That's a tough one, please confirm the below question(s):

1. In the Universe (IDT), you replaced table C with a new View (created in Database). I assume, how you did that is, refreshed the structure in Data Foundation Layer, then deleted table C, pulled in new View and created necessary joins. Then, you tried to create the Security profile. Please confirm.
2. Did you publish the updated Universe with new View, to the repository, before trying to create the Security Profile? If not, I think that is the issue, because, the Security Profile is directly created on the Universe in the Repository and not on your local Universe file. What I mean by that is, the local Universe file you've on your client machine doesn't have security attached to it. Makes sense? Because of that, as the updated Universe (including new View) is not in repository, its not showing that new View when you're trying to create Security Profile.

Updated as of 07/18/2016  12:44 PM (EST):

I just tried and confirmed that the reason why you don't see the new View in Original Table list when trying to create Data Security Profile, the reason is, you'll have to publish the Universe before you try to create the Data Security Profile, as I said, security is attached to the Universe in Repository and not to the local universe.

Let me know how it goes.

Thanks,
Mahboob Mohammed

[leon_berman]

Hi Mahboob ,

I published  the updated Universe with new View, to the repository, and  created  the Security Profile.

Permission is working ,but now  there are two new problems:

1. I must "go through the world" and update new allocation view to each object on the univers  that based on the view (no automatic change of objects as there is for joins on the univers).

2. All reports that based on objects from the view does not work - objects run away from the reports and Ineed to build everything from scratch.

What can you said about this issue ?

Best Regards,

Leon

[mhmohammed]

Hi Leon,

I understand your pain, DON'T WORRY, there is a solution to your problem. I don't know if you've deleted the objects (created based on Table C which is now replaced with a View created in Database), if yes, please get a version of the Universe in which you didn't delete the existing objects.

Steps to replace Table C (Derived Table) with View (created in Database) without messing up the Objects and reports:

(Very Important: pre-requisites - the column names in the View should be exactly same as they were in the Table C (which was a Derived table), or else you'll have to manually update the Select clauses for the objects whose column names have been modified):

1. Don't delete the Table C (Derived table) from the Data Foundation Layer, until the whole process is complete.
2. Add View (created in Database) to the Data Foundation Layer,
3. Update the existing joins (from Table C) to join with this View. So you don't have to create necessary joins and contexts. Save the Data Foundation Layer and go to the Business Layer.
4. When you're in the Business Layer, from the Menu on top left where we have File, Edit,Actions.., go to Edit -> and click on Find/Replace. It will open up the Find/Replace window, similar to a feature we have in MS Office Word/Notepad
5. In the Find What field, enter the name of the Derived Table (say derived_table_c) and click Find
6. You'll get a list populated with the objects that use columns from that Derived Table C, don't be shocked if the Replace with field is greyed out, its ok
7. Check the object names whose Derived table name in Select statement should be updated with the name of the View (created in database)
8. After you check the object names, you'll see that the Replace with field is activated
9. In the Replace with filed, Enter the View name now (save database_view_v) and click replace
10. Save & Close, and Publish the Universe. Voila, you got it.
11. No objects deleted, no incompatible issues.

Hope that helps. Let us know.

Thanks,

Mahboob Mohammed

[mhmohammed]

Hi Leon,

Any update on this issue?

Thanks,

Mahboob Mohammed