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:
The following illustration puts all things together from technology perspective:
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.
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:
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.
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/
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.
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”.
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.
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.
It is important to grant your users “Execute” permission.
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
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.
If we login with Vendor’s account, then only one record is displayed.
If user clicks on item, then PDF form will open with External List details.
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.