Question
Capgemini
IN
Last activity: 23 Mar 2018 16:30 EDT
Need query to grant privileges to 'Base User' per object_type in a Dual User setup
Hi,
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 ?
For example,
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.
- Should it be granted through roles ?
Thanks!
Pegasystems Inc.
US
For 7.3.1, the supported base user is the same as data user. PRPC automatically assign the rule schema object privilege to data user. The only required privilege for data/base user is 'create session'
Capgemini
IN
As I understand, the Oracle data schema owner can be used as the Base runtime user and we may not need to create a separate oracle user to act as ‘Base runtime user’.
I could see the following statement 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.
-
UNLIMITED TABLESPACE
-
CREATE SESSION"
Are you saying that the base user do not require separate grants for the objects in rule schema (including rules tables used in rules resolution ) ?
In our setup, we have created four oracle users as mentioned below -
1.rule schema owner
2.data schema owner
3.base user
4.admin user
We are interested to know -
The grant queries that would be needed for the Base user for different object_types and if it could be granted through roles ?
Thanks!
Pegasystems Inc.
US
I am curious why you need to do this at all. When install/upgrade (and I believe import) are run the grants are issued as part of the DDL. Do you find that the grants are missing?
Are you modifying a single user setup to be dual user?
Capgemini
IN
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.
-
UNLIMITED TABLESPACE
-
CREATE SESSION"
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 ?
Thanks.
Pegasystems Inc.
US
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.
Capgemini
IN
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.
Thanks.
Pegasystems Inc.
US
Yes, follow the Guide to give permissions to the base (data schema) user, the install/upgrade will grant permissions to the rules schema user.
Pegasystems Inc.
US
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.