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"
/>
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***