PPM_DWH user access

[CMCN1982]

Hi all.

 

When configuring the ppm_dwh user for the data warehouse the documentation states that you need to grant this access:

GRANT ALTER ANY LOGIN TO PPM_DWH

 

Our DBA has informed me that:

A SQL account (ppm_dwh) was granted ALTER ANY LOGIN permission

This is an extraordinary access equivalent to a security administrator on the server. A user with this access can grant or remove any login to the server.

 

So I have to file an exception with our security office. They will require a valid business case and I was just wondering if someone could elaborate a bit more on why this access is essential.

In the notes it just says that The database user must be able to alter the Clarity-schema and otherwise own the database.

[Nika Hadzhikidi]

Hi Colin,

 

Both privileges below are required as per the Installation Guide:

GRANT ALTER ANY LOGIN TO PPM_DWH
GRANT ALTER ANY LINKED SERVER TO PPM_DWH

 

I understand you have concerns about the first right as per your DBA. Please let them know that this access is currently required, and to make sure the password is strong for PPM_DWH user.

 

There is no supported way to modify the access right now, please feel free to raise an idea here in Communities for this to be reviewed by our Product Management.

 

Hope this helps.

 

Kind Regards

 

Nika Hadzhikidi
CA Technologies
Principal Support Engineer

[CMCN1982]

Thank you Nika!

 

I will just have to file security exceptions for this permission.

[julma03]

Hold on...These two rights are only needed to initially create the dblink. Once it's created by the CSA you can check the box just above the "custom Database Link" attribute in the CSA to stop the CSA from attempting to remove/recreate the link again.

So ask the DBA to grant the two rights temporally.

Then go into the CSA and create the link by saving the DWH page without the box being check.

Now check that box and click save again.

Then ask the DBA to revoke the two rights.

After the DBA does this, you can click save on the DWH page over and over and the created link will not be affected.

 

Optionally the DBA could create the link themselves.  This was the whole purpose of that check box.

 

I'm sure the DBA will have not issues granting these right temporary.

 

Thanks

Mark

[Nika Hadzhikidi]

Hi Colin, Mark,

 

Thank you so much for bringing this up and your comments. Based on this I asked our Sustaining Engineering to review and test the product functionality without the ALTER ANY LOGIN right (granting it only for the Database link creation). After testing with Clarity PPM, they confirmed the permission on MSSQL:

ALTER ANY LOGIN
ALTER ANY LINKED SERVER

 

and Oracle:
CREATE DATABASE LINK

 

may be safely revoked. This will be added to our Support documentation to correct the existing statements.

 

Note : If the right is revoked and you try creating a new database link from CSA, an error will be displayed. You have to readd the permissions back if you need to recreate the database link.

 

Kind Regards

 

Nika Hadzhikidi
CA Technologies

[CMCN1982]

Thank you Nika.

That is very useful to know. Our lead DBA and Security office had some concerns about the access remaining in place indefinitely.

So this news certainly alleviates those concerns.

Thanks again.