Links may not function; however, this content may be relevant to outdated versions of the product.
Accessing database administration operations
Access to various database tuning and maintenance tasks on the Schema Tools, Query Inspector, and Query Runner landing pages is determined by the privileges assigned to your access role and the type of database platform you are using.
The following table lists the privileges required to view each landing page.
Function/Operation | Privilege | PostgreSQL | Oracle | Other platforms |
---|---|---|---|---|
Schema Tools landing page | pxViewAndOptimizeSchema | Yes | No | No |
Query Inspector landing page | pxViewAndOptimizeQuery | Yes | Yes | No |
Query Runner landing page | pxViewAndSelectQuery | Yes | Yes | No |
The following table lists the privileges required to perform operations on the landing pages.
Function/Operation | Privilege | Postgres | Oracle | Other Platforms | Database credentials |
---|---|---|---|---|---|
Schema tools introspect | pxViewAndOptimizeSchema, pxDBIntrospect | Yes | No | No | Regular |
Increasing column lengths | pxDBAlterColumn | Yes | No | No | Regular |
Defragmenting tables | pxDBTableDefragment | Yes | No | No | Regular |
Viewing table statistics | pxDBTableGetStatistics | Yes | No | No | Regular |
Updating table statistics | pxDBTableUpdateStatistics | Yes | No | No | Regular |
Viewing history | pxDBTableUsage | Yes | No | No | Regular |
Adding an index to a table | pxDBUpdateIndex | Yes | No | No | Regular |
Removing an index from a table | pxDBDropIndex | Yes | No | No | Regular |
Query Inspector | pxViewAndOptimizeQuery pxDBExplainPlan | Yes | Yes | No | Read-only |
Query Runner | pxDBSelectQuery | Yes | Yes | No | Read-only |
Access privileges and access class
The access class, also referred to as the proxy class, is the class against which the Pega 7 Platform looks up privileges when performing database administration operations on the associated database. The privileges are added to the access class associated with an access role.
No action is required for PegaRULES and PegaDATA. The out-of-the-box Pegarules:DatabaseAdministrator role contains privileges for all of the above operations in the access class for the PegaRULES and PegaDATA databases.
If you are using an external database, you must manually create an access role and add privileges to the access class for the external database.
Configuring access to database administration operations for an external database
Configuring access to database administration operations for an external database consists of the following tasks:
- Creating an access role
- Adding the access role to your access group
- Specifying the proxy class and read-only credentials
Creating an access role
Create an access role that can be used to access the database operations on the Schema Tools, Query Inspector, and Query Runner landing pages for your external database. After you create this access role, it can be added to an operator's access group.
- Click
- To add a role, click the Add a row icon at the bottom of the table.
- Enter a name in the Role Name field.
- Select the ruleset to which this role applies in the RuleSet field.
- Select the ruleset version in the RuleSet Version field.
- Click to save the access role.
- Click .
- To add the access class to the access role that you just created, click the access role.
- Select the access class for your external database from the Class autocomplete field.
- Assign values for the privileges.
- Click the Add item icon to add a privilege.
- Add the privileges for the database operations that you want to allow this role to access by using the Name autocomplete field. Refer to the required privileges table for the privilege names.
- Click the Add item icon to add another privilege.
- Click when you finish adding privileges.
Adding the access role to your access group
Now, you can add the access role that you created to the access group of an operator.
- From the
- Click Add role in the section.
- In the autocomplete field, select the role that you configured in the Creating an access role step.
- Click .
Specifying the proxy class and read-only credentials
You configure the proxy class and read-only credentials for your external database on the Data-Admin-DB-Name page for the database if you are using the use JDBC Connection Pool or use JDBC URL connection methods. If you are using the use configuration in preferences connection method, you configure the proxy class and read-only credentials in the prconfix.xml file. The connection method is specified on the Database tab of the Data-Admin-DB-Name page for the database.
Read only-credentials are required. If read-only credentials are not specified, the following error message is displayed: "There was a problem performing a database query: There is no configuration (no Data-Admin-DB-Name instance) for database Read-only <DB Name>"
If your connection method is use JDBC Connection Pool or use JDBC URL, complete the following steps:
- In the Explorer panel, click .
- Click your database.
- On the
- If your connection method is use JDBC Connection Pool, enter the read-only credentials as set up in the application server in the Read-only JNDI name field in the JDBC DataSource settings section.
- If your connection method is use JDBC URL, enter the read-only credentials in the Readonly username and Readonly user password fields in the Authentication section.
tab, enter the read-only credentials:
- On the Advanced tab, enter the proxy class that contains the access privileges to use for database operations in the Proxyclass name field.
- Click .
If your connection method is use configuration in preferences, add the read-only credentials and the proxy class to the prconfig.xml file:
- Enter the read-only credentials, for example:
<env name="database/databases/extdb/url" value="jdbc:postgresql://00.000.000.00:5432/postgres" />
<env name="database/databases/extdb/userName" value="postgres" />
<env name="database/databases/extdb/password" value="welcome" />
<env name="database/databases/extdb/readOnlyPassword" value="welcome"/>
<env name="database/databases/extdb/readOnlyUserName" value="rouser"/>
or
<env name="database/databases/extdb/dataSourceReadOnly" value="java:comp/env/jdbc/PegaRULESReadOnly"/>
where extdb is the database name.
- Enter the proxy class, for example:
<env name="database/databases/extdb/proxyClassName" value="Data-"/>
Previous topic Pega Platform database overview Next topic All classes in a class group map to one shared database table