Worldwide [change] |Account |Register |About Cisco
Guest

Hierarchical Navigation

Cisco Policy Enforcement Point

CEPM SQL Agent Guide

Downloads

Table Of Contents

CEPM SQL Agent Guide

Contents

About This Document

Objective

Audience

SQL Agent Overview

Installing CEPM SQL Agent

Prerequisites

Installing CEPM SQL Agent

Executing CEPM SQL Agent

Row and Column Level Filtering of Data using SQL Agent

Without Implementing Row Level Filtering and Column Masking

Implementing Row Level Filtering

Implementing General Column Masking

Implementing Selective Column Masking

Documentation Updates

Related Documentation

Obtaining Documentation and Submitting a Service Request


CEPM SQL Agent Guide

Revised: Aug 8, 2009, Doc Part No: OL-19571-01

Contents

•About This Document

•SQL Agent Overview

•Installing CEPM SQL Agent

•Row and Column Level Filtering of Data using SQL Agent

•Documentation Updates

•Related Documentation

•Obtaining Documentation and Submitting a Service Request

About This Document

Objective

This document gives the overview about the SQL Agent and explains the steps for installing the SQL Agent.

Audience

This guide is for administrators who use CEPM and are responsible for resource modelling and entitlement management.

SQL Agent Overview

The SQL Agent is a database routine function that is compiled and stored in the database instance of the Cisco Entitlement Policy Manager (CEPM) application.

The SQL Agent takes the requests from a database function, or a database procedure, or a direct SQL query, executes the policy algorithms, and returns back the appropriate response as per the authorization policy decisions.

The SQL Agent will return the obligations that include the decisions for the requested resources and attributes such as user, group, role, resource, application attribute source, policy and scoped specified on a resource from the Policy Administration Point (PAP). Apart from executing the role-based policies, the SQL Agent is also capable of applying the policy combining algorithm based on the time constraints on a policy.

Installing CEPM SQL Agent

This section describes the installation process of CEPM SQL Agent.

Prerequisites

CEPM entitlement repository is installed and the database instance is running.

The application database should have the following additional privileges for the column-masking feature (see Implementing Row Level Filtering):

•EXECUTE ANY PROCEDURE

•EXECUTE ANY TYPE

Installing CEPM SQL Agent

The SQL Agent installation consists of executing a database script in the Policy Decision Point (PDP) database schema. To install the SQL Agent:

Step 1 Log in to CEPM schema (SQL prompt) using CEPM user credentials.

Step 2 Compile the database script file, pdp.sql, in the following location in the PDP installation directory.

For Oracle database: /CEPM-v33/db/scripts/oracle/

For SQL Server database: /CEPM-v33/db/scripts/mssql/

Step 3 Login to the application database.

Step 4 In the application database, compile the database script file getlist.sql in the following location.

For Oracle database: /CEPM-v33/db/scripts/oracle/SqlAgent/

Note Before compiling this file, open it in a text editor and replace `CEPM' with the database user name of CEPM database.

For SQL Server database: /CEPM-v33/db/scripts/mssql/SqlAgent/

Note Before executing this file, open it in a text editor and replace 'CEPM' with the database user name of CEPM database.

Executing CEPM SQL Agent

Run the following SQL query in the SQL Query Analyzer connection to the schema where CEPM database is running.

For Oracle: 

select *  from table(cast(decisionpoint_sqlagent(subject, resource, action, rolebundle, 
context, attributesToBeReturned, cloneUsersCheck) as NT_POLICYS_MAIN))

For SQL Server: 

Select * from decisionpoint_sqlagent(subject, resource, action, rolebundle, context, 
attributesToBeReturned, cloneUsersCheck);

Table 1 shows the parameters used in the SQL query

Table 1 SQL Query Parameters 

Parameter
Description

Subject

The subject to be authorized or entitles.

Resource

The fully qualified name of the resource.

Action

The action name, defaults to any.

Rolebundle

Comma-separated list of role bundle names. If Rolebundle information does not exist, then send database null.

Context

The fully qualified context name. If Context informationdoes not exist, then send database null.

AttributesToBeReturned

Specifies whether the attributes need to be returned. For example, user type, group type, role type, and policy attributes that are configured on a resource to be returned as obligations from the PAP. Possible values are 0 or 1, where 0 means do not return the attributes and 1 means return the attributes.

cloneUsersCheck

Specifies whether the cloned users decision needs to be evaluated. Possible values are 0 or 1, where 0 means do not check for cloning and 1 means check for cloning.

Result

The result set returned from this query contains the following fields.

•ResourceFQN—The resource's Fully Qualified Name (FQN) of the requested resource along with the action if not any.

•Decision—The decision for the resource. This can be permit, deny, or NA.

•UserAttributes—If any user attributes are being returned, this field will contain a delimited list of name-value pairs. For example: Manager^Tom~EmpId^123. The characters ^ and ~ are being used as delimiters.

•Policy Attribute—The policy attributes.


Note For Oracle, if one or more rules are configured for a policy, then the SQL Agent evaluates only those rules for which only one condition is specified. For SQL Server, SQL will not evaluate the rules configured on the policy.

Row and Column Level Filtering of Data using SQL Agent

The SQL Agent provides the additional functionality of row-level filtering and column masking for the database tables present in the protected application.This section describes the configuration using an example.

Step 1 Database table EMP_DETAILS in the protected application database has the information shown in Table 2.

Table 2 EMP_DETAILS Table

EMP_ID
NAME
SCN
DEPT_NO

1

Tom

1122334455

10

2

Amith

2233445566

10

3

Krish

3344556677

20

4

Mark

4455667788

20

5

Bruce

5566778899

20


Step 2 Log in to the PAP console as administrator and perform the operations described inWithout Implementing Row Level Filtering and Column Masking.

Without Implementing Row Level Filtering and Column Masking

To implement row-level filtering and column masking:

Step 1 Choose Home > Manage Entities > Resources. The Resource Management page is displayed.

Step 2 Select the Prime Portal application.

Step 3 Using the Create Resource from External Sources functionality create the EMP_DETAILS resource with the name of the table name and create the child resources for this resource that matches its column names.

Figure 1 Create Resource from External Source

Step 4 Create a user named Tom and map him to the role Internal Dev.

Step 5 Create an allow policy for Internal Dev to the EMP_DETAILS resource.

Figure 2 Allow Policy

Step 6 Go to the SQL Prompt (Oracle) or Query Analyzer (SQL Server) in the application database and type the following query: 

Oracle: select get_list('Tom','EMP_DETAILS','any') from dual;

SQL Server: exec Get_List 'Tom','EMP_DETAILS','any'

Result: All the records in the EMP_DETAILS table are displayed.

Implementing Row Level Filtering

To implement row level filtering:

Step 1 Click the Policy Attributes icon of the Allow:Internal Dev policy present on the EMP_DETAILS resource. The Policy Attributes page is displayed. Enter the values for Policy Attributes.

Figure 3 Row Level Filtering.

Step 2 Go to the SQL Prompt (Oracle) or Query Analyzer (SQL Server) and enter the following query: 

Oracle:select get_list('Tom','EMP_DETAILS','any') from dual;

SQL Server:exec Get_List 'Tom','EMP_DETAILS','any'

Result: Last three records of the EMP_DETAILS table are displayed. The where Attribute value contains the filter criteria emp_id > 2.

Example:

a. Enter the Attribute Name as where and Attribute Value as emp_name=$subject.

b. Go to the SQL Prompt (Oracle) or Query Analyzer (SQL Server) in the application database and enter the following query: 

Oracle:select get_list('Tom','EMP_DETAILS','any') from dual;

SQL Server:exec Get_List 'Tom','EMP_DETAILS','any'

Result: All records of the EMP_DETAILS table are displayed. The where Attribute value contains the filter criteria emp_name=Tom.

Similarly, any column name can be specified as the Value in the Policy Attributes page.

Implementing General Column Masking

To implement column masking in a general way:

Step 1 Configure a deny policy on the Resource SCN (created as child resource under the EMP_DETAILS resource) for the Internal Dev role.

Step 2 Go to the SQL Prompt (Oracle) or Query Analyzer (SQL Server) in the application database and type the following query: 

Oracle:select get_list('Tom','EMP_DETAILS','any') from dual;

SQL Server:exec Get_List 'Tom','EMP_DETAILS','any'

Result: Values of the SCN column are not displayed.

Implementing Selective Column Masking

To implement column masking in a selective way:

Step 1 Configure an allow policy on the SCN resource for the Internal Dev role.

Step 2 Click the Policy Attributes icon of the Allow:Internal Dev policy present on the SCN resource. The Policy Attributes window is displayed. Enter the values for Policy Attributes.

Figure 4 Selective Column Masking

If you specify MASK(2), on the SCN column, the first two digits on the SCN column get masked, the value of the first two digits will not be displayed.

Note Do not select the values for the Attribute Name and Attribute Value from the drop down list while creating a rule. Enter the fresh values for Attribute Name and Attribute Value. The fresh values should not belong to any of the entity types.

Step 3 Go to the SQL Prompt (Oracle) or Query Analyzer (SQL Server) in the application database and enter the following query: 

Oracle: select get_list('Tom','EMP_DETAILS','any') from dual;

SQL Server: exec Get_List 'Tom','EMP_DETAILS','any'

Result: The values for SCN column are shown as below. 

SCN 
---------------

**22334455 

**33445566 

**44556677 

**55667788 

**66778899

The first two digits of the SCN column values are masked.

Similarly, the last two digits for the column can be masked by using the Attribute value of MASK(-2) in the Policy Attributes page.

The values for SCN column will be displayed as: 

SCN 
---------------

11223344** 

22334455** 

33445566** 

44556677** 

55667788**

UNMASK behaves opposite to MASK, in case of UNMASK(x), only the `x' digits are displayed and the rest are masked.

Example 1:

Configuring UNMASK(2) as the Attribute Value in the Policy Attributes page shows the following result: 

SCN 
---------------

11******** 

22******** 

33******** 

44******** 

55********

Example 2:

Configuring UNMASK(-2) as the Attribute Value in the Policy Attributes page shows the following result: 

SCN 
---------------

********55 

********66 

********77 

********88 

********99

Documentation Updates

Table 3 Updates to CEPM SQL Agent Guide

Date
Description

July 7, 2009

Minor edits and template/boilerplate updates for publication to Cisco.com

April 3, 2009

Cisco Enterprise Policy Manager (EPM) Release 3.3.0.0


Related Documentation

CEPM_User_Guide_V3.3.0.0.pdf

Obtaining Documentation and Submitting a Service Request

For information on obtaining documentation, submitting a service request, and gathering additional information, see the monthly What's New in Cisco Product Documentation, which also lists all new and revised Cisco technical documentation, at:

http://www.cisco.com/en/US/docs/general/whatsnew/whatsnew.html

Subscribe to the What's New in Cisco Product Documentation as a Really Simple Syndication (RSS) feed and set content to be delivered directly to your desktop using a reader application. The RSS feeds are a free service and Cisco currently supports RSS Version 2.0.

CCDE, CCENT, CCSI, Cisco Eos, Cisco HealthPresence, Cisco IronPort, the Cisco logo, Cisco Lumin, Cisco Nexus, Cisco Nurse Connect, Cisco StackPower, Cisco StadiumVision, Cisco TelePresence, Cisco Unified Computing System, Cisco WebEx, DCE, Flip Channels, Flip for Good, Flip Mino, Flip Video, Flip Video (Design), Flipshare (Design), Flip Ultra, and Welcome to the Human Network are trademarks; Changing the Way We Work, Live, Play, and Learn, Cisco Store, and Flip Gift Card are service marks; and Access Registrar, Aironet, AsyncOS, Bringing the Meeting To You, Catalyst, CCDA, CCDP, CCIE, CCIP, CCNA, CCNP, CCSP, CCVP, Cisco, the Cisco Certified Internetwork Expert logo, Cisco IOS, Cisco Press, Cisco Systems, Cisco Systems Capital, the Cisco Systems logo, Cisco Unity, Collaboration Without Limitation, EtherFast, EtherSwitch, Event Center, Fast Step, Follow Me Browsing, FormShare, GigaDrive, HomeLink, Internet Quotient, IOS, iPhone, iQuick Study, IronPort, the IronPort logo, LightStream, Linksys, MediaTone, MeetingPlace, MeetingPlace Chime Sound, MGX, Networkers, Networking Academy, Network Registrar, PCNow, PIX, PowerPanels, ProConnect, ScriptShare, SenderBase, SMARTnet, Spectrum Expert, StackWise, The Fastest Way to Increase Your Internet Quotient, TransPath, WebEx, and the WebEx logo are registered trademarks of Cisco Systems, Inc. and/or its affiliates in the United States and certain other countries.

All other trademarks mentioned in this document or website are the property of their respective owners. The use of the word partner does not imply a partnership relationship between Cisco and any other company. (0907R)

Any Internet Protocol (IP) addresses and phone numbers used in this document are not intended to be actual addresses and phone numbers. Any examples, command display output, network topology diagrams, and other figures included in the document are shown for illustrative purposes only. Any use of actual IP addresses or phone numbers in illustrative content is unintentional and coincidental.

© 2009 Cisco Systems, Inc. All rights reserved.