Question

5
Replies
465
Views
Close popover
Loic Mitton (LoicM950)
Louis Dreyfus Company

Louis Dreyfus Company
FR
LoicM950 Member since 2018 29 posts
Louis Dreyfus Company
Posted: September 18, 2018
Last activity: November 30, 2018
Closed
Solved

Pega 7.4 - dual user configuration - optimize schema - create indexes not working

Hello,

We are using Pega 7.4 with SQL Server 2016 and tomcat.

During my review on the compliance score I discovered some report that may need indexes.

I went to the Optimize schema page and tried to add some indexes.

I got the following message :

 Cannot find the object "DATA.PR_XXX_YYY_ZZZ_DATA_LOCAT" because it does not exist or you do not have permissions.
Last SQL: CREATE  INDEX EXCHANGEIndex ON DATA.PR_XXX_YYY_ZZZ_DATA_LOCAT ("EXCHANGE") 

I checked with database profiler and found that the base user is used, not the admin user.

Context.xml is set like this :

<Resource name="jdbc/PegaRULES"
auth="Container"
factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
type="javax.sql.DataSource"
driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://database;selectMethod=cursor;sendStringParametersAsUnicode=false"
username="base_user"
password="password"
maxActive="100"
maxIdle="30"
testWhileIdle="true"
maxWait="10000"
testOnBorrow="true"
validationQuery="SELECT 1"
jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"
/>

<Resource name="jdbc/AdminPegaRULES"
auth="Container"
factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
type="javax.sql.DataSource"
driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://database;selectMethod=cursor;sendStringParametersAsUnicode=false"
username="admin_user"
password="password"
maxActive="10"
initialSize="5"
minIdle="5"
maxIdle="5"
testWhileIdle="true"
maxWait="5000"
testOnBorrow="true"
validationQuery="SELECT 1"
jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"
/>

<Environment
name="prconfig/database/databases/PegaRULES/dataSourceAdmin"
value="java:comp/env/jdbc/AdminPegaRULES"
type="java.lang.String"
/>

<Environment
name="prconfig/database/databases/PegaDATA/dataSourceAdmin"
value="java:comp/env/jdbc/AdminPegaRULES"
type="java.lang.String"
/>

<Environment
name="prconfig/database/databases/PegaRULES/defaultSchema"
value="rules"
type="java.lang.String"
/>

<Environment
name="prconfig/database/databases/PegaDATA/defaultSchema"
value="data"
type="java.lang.String"
/>

As you can see admin user is set for both rules and data schema for datasourceadmin.

I don't understand why to optimizeSchema doesn't use the admin profile, is it normal?

Also, SQL rights are set as follow :

Admin :

ALTER AUTHORIZATION ON SCHEMA::[rules] TO admin_user

ALTER AUTHORIZATION ON SCHEMA::[data] TO admin_user

GRANT CREATE TABLE, CREATE VIEW, CREATE PROCEDURE , CREATE FUNCTION,CREATE ASSEMBLY, EXECUTE TO admin_user;

GRANT ALTER ON SCHEMA :: data to admin_user;

GRANT ALTER ON SCHEMA :: rules to admin_user;

Base:

GRANT SELECT , INSERT, UPDATE, DELETE, EXECUTE ON SCHEMA :: data TO base_user;

Could you help to find what is the issue?

Thank you

***Edited by Moderator Marissa to update SR Details***
Pega Platform Data Integration Support Case Created
Moderation Team has archived post,
Close popover This thread is closed to future replies. Content and links will no longer be updated. If you have the same/similar Question, please write a new Question.