This content has been archived and is no longer being updated. Links may not function; however, this content may be relevant to outdated versions of the product.
LinkedIn
Copied!

Table of Contents

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.

The access class for each operation is @baseclass.

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.

The access class for each operation is the Proxy class given in Data-Admin-DB-Name.

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:

  1. Creating an access role
  2. Adding the access role to your access group
  3. 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.

  1. Click Designer Studio > Org & Security > Groups and Roles > Access Roles.

    Access Roles tab

  2. To add a role, click the Add a row icon at the bottom of the table.

    Add New Role dialog box

  3. Enter a name in the Role Name field.
  4. Select the ruleset to which this role applies in the RuleSet field.
  5. Select the ruleset version in the RuleSet Version field.
  6. Click Submit to save the access role.
  7. Click Submit.
  8. To add the access class to the access role that you just created, click the access role.

    Add access role object

  9. Select the access class for your external database from the Class autocomplete field.
  10. Assign values for the privileges.
  11. Click the Add item icon to add a privilege.
  12. 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.
  13. Click the Add item icon to add another privilege.
  14. Click Save 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.

  1. From the Operator menu, click Access group.

    Edit Access Group dialog box

  2. Click Add role in the Available roles section.
  3. In the autocomplete field, select the role that you configured in the Creating an access role step.
  4. Click Save.

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:

  1. In the Explorer panel, click Records > SysAdmin > Database.
  2. Click your database.
  3. On the Database tab, enter the read-only credentials:
    • 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.

    Database tab

  4. On the Advanced tab, enter the proxy class that contains the access privileges to use for database operations in the Proxyclass name field.

    Advanced tab

  5. Click Save.

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


100% found this useful


Related Content

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.