Issuing Cross Schema DDL GRANTS while Upgrading to 7.2.2
Team, can anyone take a look at SR-B52399 and offer a solution? A solution to a similar post on the forum was shared with my customer but was not viable because they are not allowing DDL permissions.
Thank you.
ING, Netherlands
NL
In context to this SR, the below post was separately initiated in PDN and response was received from Pega. Please check if this helps:
https://collaborate.pega.com/question/client-constraint-during-configuration-pega-7-post-upgrade
Pegasystems Inc.
--
Unfortunately the previous post was not enough to answer their questions and they still need help. This urgently needs an answer.
Here's the data from the SR:
Description
When Upgrading from 6.1 to 7.2.2 on split schema configuration, we are not having DDL grants from Base user to RULES and DATA schemas but we are having DML for tables, views .. and execute for PLSQL blocks.We are not having any kind of privileges between RULES and DATA schemas. Due to client restrictions, only at the time of upgrade they provided ADMIN schema/user to perform upgrade steps. After upgrade ADMIN schema/user is dropped. Also revoked all the cross schema grants between DATA and RULES schema.
Steps To Reproduce
Unfortunately the previous post was not enough to answer their questions and they still need help. This urgently needs an answer.
Here's the data from the SR:
Description
When Upgrading from 6.1 to 7.2.2 on split schema configuration, we are not having DDL grants from Base user to RULES and DATA schemas but we are having DML for tables, views .. and execute for PLSQL blocks.We are not having any kind of privileges between RULES and DATA schemas. Due to client restrictions, only at the time of upgrade they provided ADMIN schema/user to perform upgrade steps. After upgrade ADMIN schema/user is dropped. Also revoked all the cross schema grants between DATA and RULES schema.
Steps To Reproduce
2. create data schema and rules schema - PEGA_DATA, PEGA_RULES
3. Create another user which will have grants to access data and rules schemas - APP_PEGA_USER (this should not have DDL grants to perform DDL's on DATA and RULES schema)
privilege's which we have from APP_PEGA_USER to PEGA_DATA, PEGA_RULES are as follows
INSERT
SELECT
EXECUTE
MERGE VIEW
QUERY REWRITE
ON COMMIT REFRESH
ALTER
DEBUG
UPDATE
FLASHBACK
DELETE
4. upgrade and start application
Error Message
ORA-01031: insufficient privileges
- As part of upgrade script execution , one DB admin user is required which will have "ANY" privilege so that it can create and execute all necessary things in data and rules schema. We got that and executed all upgrade scripts successfully.
- Now as per policy we came to know that , no schema/user should have DDL privilege to any other schema. So to follow this we revoked all privileges from data and rules schema what was granted during upgrade step(cross schema object granting-3rd step of upgrade) by pega script.
- Now to configure the application server to communicate with pega schema we created one user which has only DML permissions on data and rules schema. This has been provided by creating some database role. Now we are using this user in PegaRULES data source.
- After this configuration we can bring up our application and able to login. But we are getting some issue in log in execution of some OOTB procedures like SPPR_DEFRAGMENT_TABLE (ORA-01031: insufficient privileges) and also for some other objects.
- We have also tried to delete unused tables from application as suggested by pega which is a post upgrade step, that also failed due to privilege issue. Also we tried create data tables from Pega7 application in sandbox and that also failed due to privilege issue.
- As per our analysis we found that for Pega 7 split schema configuration the base user which we are using in PegaRULES data source that should have admin privileges including DDL execution on both DATA and RULES schema, which we can't provide right now due to policy.
- If that above admin privilege is not there then it is possible that for framework installation and for any hotfix installation will also fail.
- Again if those admin privileges and cross schema grants are not there then it may possible that normal pega application functionality may also be impacted.
Pegasystems Inc.
IN
4.After this configuration we can bring up our application and able to login. But we are getting some issue in log in execution of some OOTB procedures like SPPR_DEFRAGMENT_TABLE (ORA-01031: insufficient privileges) and also for some other objects.
SPPR_DEFRAGMENT_TABLE procedure creates and drops the temporary table for optimizing the syslocks table.
If base user does not have the privilege then please disable it by setting the follwoing Dynamic System Setting to false.
Owning Ruleset: Pega-RULES
DSS Name:syslocks/defragEnabled
Value: false
4.We have also tried to delete unused tables from application as suggested by pega which is a post upgrade step, that also failed due to privilege issue. Also we tried create data tables from Pega7 application in sandbox and that also failed due to privilege issue.
4.After this configuration we can bring up our application and able to login. But we are getting some issue in log in execution of some OOTB procedures like SPPR_DEFRAGMENT_TABLE (ORA-01031: insufficient privileges) and also for some other objects.
SPPR_DEFRAGMENT_TABLE procedure creates and drops the temporary table for optimizing the syslocks table.
If base user does not have the privilege then please disable it by setting the follwoing Dynamic System Setting to false.
Owning Ruleset: Pega-RULES
DSS Name:syslocks/defragEnabled
Value: false
4.We have also tried to delete unused tables from application as suggested by pega which is a post upgrade step, that also failed due to privilege issue. Also we tried create data tables from Pega7 application in sandbox and that also failed due to privilege issue.
This is expected. As Base user does not have the DDL privilege you can create or delete the tables from any of the schema. For Unused tables you need to manually generate the drop table statements from the list of the table names and incase of data tables you will have the option to generate the DDL and apply it manually.
4.As per our analysis we found that for Pega 7 split schema configuration the base user which we are using in PegaRULES data source that should have admin privileges including DDL execution on both DATA and RULES schema, which we can't provide right now due to policy.
No this is not mandatory. If base user does not have the DDL privileges then you have to manually download and apply the SQL using any database tools with the user who has the DDL access.
4.If that above admin privilege is not there then it is possible that for framework installation and for any hotfix installation will also fail.
No. If Framework or hotfix contains any of the schema changes then it will provide an option to download the DDL and apply it manually through DBA.
4.Again if those admin privileges and cross schema grants are not there then it may possible that normal pega application functionality may also be impacted.
The Base user definitely required the following privileges and roles on both PegaDATA and PegaRULES schemas to Pega Application to work properly in split schema mode.
•INSERT/SELECT/UPDATE/DELTE
•EXECUTE ANY PROCEDURE