Posted: 8 Mar 2018 8:16 EST Last activity: 23 Mar 2018 16:30 EDT
Need query to grant privileges to 'Base User' per object_type in a Dual User setup
We are trying to use Dual User configuration in Pega 7.3.1 with WebSphere/Oracle. When it comes to the Base User we have the following line mentioned in Installation Guide - "Basic read and write access to data and rules tables including rules resolution."
I understand it is advised to provide insert/update/delete/execute on the objects within the data and rules schemas for Base User - could someone please be specific for each object_types under the schemas ?
select count(*) , object_type, owner from dba_objects where owner like 'XXX%' group by object_type, owner order by owner ;
COUNT(*) OBJECT_TYPE OWNER
---------- ----------------------- ----------------------------
1 LOB XXX_ADMIN
6 FUNCTION XXX_APP_DATA
657 INDEX XXX_APP_DATA
20 JAVA CLASS XXX_APP_DATA
159 LOB XXX_APP_DATA
9 PROCEDURE XXX_APP_DATA
289 TABLE XXX_APP_DATA
3 VIEW XXX_APP_DATA
6 FUNCTION XXX_APP_RULES
324 INDEX XXX_APP_RULES
20 JAVA CLASS XXX_APP_RULES
40 LOB XXX_APP_RULES
1 PROCEDURE XXX_APP_RULES
66 TABLE XXX_APP_RULES
3 VIEW XXX_APP_RULES
15 rows selected.
Could someone please provide me the exact query to be run to grant privileges to 'Base User' ? Please specify on each type of object_types.
I could see the following in 7.3.1 Installation Guide -
"In a dual-user configuration, grant these privileges and roles:
Base user — The Base user is the Oracle data schema owner.
Basic read and write access to data and rules tables including rules resolution.
As I understand we need to create the Oracle run-time Base User and provide the grants accordingly before the install process starts. Does Pega automatically issue the grant statement as required during installation/upgrade process?
If not, could you please provide the grant queries (if needed) for the Base user for different object_types and if it could be granted through roles ?
You have identified that the "base user" may be the data schema owner, that is what we suggest you do. The install/upgrade process automatically issues the grant statements for the rules schema owner. Use the instructions in the installation guide and you should be all set! By all means please send suggestions for improvement if you find some aspect of the guide sparse or confusing.
Just to align my understanding, could you please confirm the below comments -
1) Assuming the data schema owner is used as the Base User, Pega would automatically issue grant statements to the Base User as needed for the objects in rule schema. The data schema owner would anyway have access to all objects in data schema.
2) Only the grant to Create Session would need to be issued to the Base User.
That's correct. PRPC will automatically grant all objects in rule schema to base user, including both OOTB objects and customers' own objects created through PRPC application later. The base user has to be the data schema user.