Currently our development team is finishing up a cumulative update 1 for PDF SharePoint V2. One of new features we added there is support of External Content Types and External Lists. We received many requests from PDF SharePoint customer’s to add support for BCS and so about 2 months ago we started digging into Business Connectivity Services.

Even back in March 2010, we reviewed a blog by Michal Pisarek “BCS External List Limitations” (link: http://www.sharepointanalysthq.com/2010/07/bcs-external-list-limitations/). He provided a decent list of BCS limitations in context of External Lists.

One of the limitations “Row Level Security” caused one of customers to ask us for assistance. Their business case was simple enough:

Company has an external list of with information about vendor’s insurance. Each record represents insurance information for specific vendor. The business requirement was to display a PDF form with insurance information and allow vendor to update it. It is imperative that each vendor can access only their insurance record;

In the same time members of the procurement department must be able to access all records.

The following illustration puts all things together from technology perspective:

Architecture Diagram

Displaying external list item as PDF is easy if you use our PDF SharePoint V2 with CU1. Unfortunately our customer got lost how to implement Item Level Security.

Our engineering team decided right away not to use SharePoint Views to implement security. While it is arguable if SharePoint views should be used for securing data at all, our team believes that security should be applied as close to the original data as possible; in this case it would be in the SQL server.

As we know External Content Type can have one of three external data types: .NET Type, SQL Server, and WCF Service.

Data Source Type Selection for BCS Data Connection
Data Source Type Selection for BCS Data Connection

Solution with WCF Service would be the easiest one. Since we can use User’s Identity and Claims authentication, we just add logic to WCF service to trim items based on Claims’ information. There is a good article by Travis Nielson explaining how to do it with WCF: http://blogs.pointbridge.com/Blogs/nielsen_travis/Pages/Post.aspx?_ID=37

However our customer uses SQL server to store their vendor’s insurance information. So we decide to implement row-level security and ensure that External List and PDF forms still work. The concept of row-level security is not new and there are several articles, which explain how to enable row-level security in SQL Server.

To summarize we successfully implemented a row-level security in SQL database and integrated all pieces together. The point is that while SharePoint 2010 Business Connectivity Service has limitations, it does not mean your implementation should be limited. By understanding surrounding technologies like SQL Server, PDF SharePoint you can achieve item level security and display external data in the PDF document.

You can read further this post if you are interested in technical details how our team implemented External List with Item Level Security.

We started with a simple database table with few relevant fields:

Database Table 'Vendor Insurance' Design
Database Table 'Vendor Insurance' Design
VendorInsurance table sample data
VendorInsurance table sample data

The logic for row level trimming would be if user does not have a database role “ProcurementDeptMember: then user can access only records associated with his AD account (column “ADVendorAccount”).

Now, we need to implement new database view that would contain our trimming logic.

CREATE VIEW [dbo].[tvfInsuranceInfoSecured]
AS
SELECT
VendorID, VendorName, Insurance_Carrier,
PolicyNumber, ExpirationDate, ProofOfInsuranceProvided,
ADVendorAccount
FROM
dbo.InsuranceInfo
WHERE
(IS_MEMBER(‘ProcurementDeptMember’) = 1) OR
(ADVendorAccount = SUSER_NAME())
GO

As you can see we use 2 Transact-SQL functions “SUSER_NAME()” and “IS_MEMBER()”:

  • SUSER_NAME() returns a login identification name of the user who accessing this view. For example: DOMAINVendorMSFT.
  • IS_MEMBER() returns 1 if current users is a member of specific database role. In our case, vendors will not have such role but users of Procurement Department will. This will ensure that procurement department can see all records.

Once we are done with database, it is time to create External Content Type. In this example, we will not create “Create” or “Update” methods. We will focus on “Read Item” and “Read List” methods of External Content Type.

We start with SharePoint Designer and create Vendor External Content Type.

Note: if you want a more detailed article on creation of External Content Type, please take a look at this article: http://blog.libinuko.com/2010/04/10/sharepoint-2010-how-to-create-external-content-type/

SharePoint Designer - Create External Content Type
SharePoint Designer - Create External Content Type

First we put some basic information about External Content Type like Name, Display Name. Now it is time to configure external data source, which will point to SQL database.

SharePoint Designer - Create new data connection
SharePoint Designer - Create new data connection

Make sure you select “User’s Identity” when configuring SQL Server Connection.

Select created database view in Data Source Explorer and select operations “New Read Item” and “New Read List”.

SharePoint Designer - Create Item Read methods
SharePoint Designer - Create Item Read methods

You can select default options of the wizards. Of course, there are many specific setting that can improve BCS operations. For example: you can setup a row number limit for “Read List” operation. We will omit these options as topic of this article is Item Level Security in External List.

Once both methods are created, you should save your External Content Type.

Now we are ready to create External List based on this External Content Type. We will use SharePoint Designer button “Create Lists & Forms”, enter list name and click Ok.

SharePoint Designer - Create External List
SharePoint Designer - Create External List

Once list is created, it is time to check permissions on newly created External Content Type. For that we will have to open SharePoint 2010 Central Administration, select “Manager Service Applications”, and click on “Business Connectivity Services”. Of course, this article assumes that you already installed and configured BCS on your farm.

Make sure you select “External Content Types” view, then open context menu for your External Content Type.  Select “Set Permissions” option.

BCS - Set Permissions for External Content Type
BCS - Set Permissions for External Content Type

It is important to grant your users “Execute” permission.

BCS - Giving "Execute" permission to BCS users
BCS - Giving "Execute" permission to BCS users

It is important to note that every other time we had to do IISRESET command at this step.

Now, it is time to create a PDF template that would display data element from external item. I will skip few steps and just show the most interesting ones. Once we have a template opened in PDF SharePoint designer we can map PDF data fields to External Content Type columns.

Note: We are using PDF SharePoint V2 with Cumulative Update 1. If you want to obtain CU1, please contact us at support[at]spzap.com

Designing PDF Template for External Content Type
Designing PDF Template for External Content Type

Once PDF template is deployed to External Type, we can go to SharePoint site and check on our External List. First we will login with user who has “ProcurementDeptMember” role. This user is able to see all 3 records.

External List displays all records
External List displays all records

If we login with Vendor’s account, then only one record is displayed.

External List displays 1 record (security trimmed)
External List displays 1 record (security trimmed)

If user clicks on item, then PDF form will open with External List details.

Adobe Reader displays PDF document with data from external list
Adobe Reader displays PDF document with data from external list

We hope this post was helpful for you to understand that not all BCS limitations are absolute. As you can see we were able to provide item-level security, nice presentation of the data using PDF SharePoint and use SharePoint 2010 Business Connectivity Services.

2 thoughts on “Business Connectivity Service with “Item Level” security and PDF forms”

  1. Does this solution require Claims based authentication and Kerberos working in order to forward the user’s identity from the SharePoint server to the DB server?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.