Introduction
In the first part of this article we covered implications of external system throttling thresholds for users of Business Connectivity Services (BCS). That blog post displayed the error messages a user may see when the items returned from the external system exceed the throttling thresholds. In the second part of this article the default threshold settings were described and how to change them when using on-premises installations of either Microsoft SharePoint® Foundation 2013 or Microsoft SharePoint® Server 2013.
In this third part we will cover the implications, for users, of the permission settings on the External Content Type (ECT). The other parts of this series are:
- Why this blog series
- Threshold Limit Errors
- Changing External System Throttling
- External Content Type Authorization Errors
- External System Authorization Errors
- Summary
Business Data Connectivity Authorisation Background
Each object stored in the Business Data Connectivity (BDC) service application metadata store has an access control list (ACL) that specifies which users and groups have permissions on the objects. Out of the thirteen BDC metadata objects, only permissions on the external system, external content type (ECT), and the BDC model can be set using the SharePoint 2013 Central Administration website.
Note: A professional developer can set the permissions using code for ECT operations (methods) and instances of those operations that specify how to use the operation by using a specific set of default values.
BDC metadata object permissions
The five BDC metadata objects that can have their own controllable ACLs:
- External system*
- BDC model*
- ECTs*
- Operations (also known as methods)
- Operation instances
* You can configure the permissions for the BDC objects identified by the * using the SharePoint 2013 Central Administration website, Windows PowerShell® or programmatically.
These objects are referred to as individually securable metadata objects. All other objects obtain their permissions from their parent object. For example, associations, actions, and identifiers cannot be assigned permissions directly but take their permissions from their parent ECT. However, by default, permissions do not propagate from one individually securable metadata object to another, so an ECT operation does not inherit its permissions from its ECT, unless the propagate check box is selected.
When permissions are propagated from an ECT, all operations and operation instances for that ECT receive the new permissions, replacing the permissions originally set on those individually securable metadata objects.
Note: Do not select the Propagate Permissions To All check box on the BDC metadata store as every external system, BDC model, or ECT will inherit this configuration when added to the metadata store. This also prevents users from unnecessary access to any external system, BDC model, or ECT that they should not have.
You can set the following four permissions on the BDC metadata store and BDC metadata objects:
- · Edit
- · Execute
- · Selectable In Clients
- · Set Permissions
Not all permissions are applicable to all objects. For example, enabling the Execute and Selectable In Clients with the BDC metadata store or the external system has no effect. However, setting these permissions at the BDC metadata store or external system level and selecting the propagation check box can be useful when you do not want to individually configure ECTs or operations for these two permissions. There isn’t an Edit permission on the ECT, however selecting the Edit permission on the ECT is useful as objects below the ECT, which do have an Edit permission inherits this permission settings, so users of a BCS tool, such as, SharePoint Designer, can create and modify objects that the ECT defines, such as, operations, actions, and associations.
What this blog post details
This blog post details the implications to the end user of the four permission on external content types. The scenarios detailed propagated the permission from the external content type to all the child individually securable metadata objects. At the end of this post, there are details on how to find ECT permission settings.
This blog post does not detail how to complete the security tasks of applying permissions to the external content type and assumes users are mapped to at least the Contribute permission level on the lists used in these scenarios, and have the permission to add Web Parts to pages.
Note: Information on the BCS related security tasks that can be completed by IT Professionals, SharePoint Online administrators, site collection administrators, site owners, external content type creators and developers can be found in the TechNet article; “Overview of Business Connectivity Services security tasks in SharePoint 2013”, which can be found at: http://technet.microsoft.com/en-us/library/jj683116.aspx.
The rest of this post describes errors an end-user may see in the browser, such as when using:
This post also details error messages a user may see in Office applications when accessing external data using BCS, for example, when using Quick Parts in a Microsoft Word document.
Edit permission on BDC Metadata Store
Before you can modify an external content type (ECT), it has to be created. To create an external content type (ECT), you must first have Edit permissions to the BDC metadata store. The BDC metadata store is the SQL Server database associated with the BDC service application that stores the BDC Models, external content types and external system information. This is true whether you are creating an ECT by uploading a .bdcm file using the SharePoint 2013 Central Administration web site, or deploying the BDC model or external content type, using a tool, such as SharePoint Designer or LightningTools’ BCS Meta Man.
SharePoint farm administrators, SharePoint Windows PowerShell®, and application pool accounts have full permissions to a BDC service application and Edit permissions on the Metadata store. Farm administrators can then maintain or repair the BDC service application if necessary and deploy solutions packages that use BCS. However, these accounts do not have Execute permissions on any metadata store objects. Therefore, such accounts can upload or create a BDC model with its associated external system definition and ECT, and can even create an external list from those ECTs. However, these accounts would not be able to execute any of the operations on the external content, and therefore, when the external list is displayed in the browser, an authentication error would be displayed.
Also, when you configured a user as an administrator of a BDC service application, using the Administrators button on the Service Applications Ribbon, this does not give the user any permissions to manage the BDC metadata store.
As an administrator of the BDC service application you will be able to display the page that allows you to view External Systems, External Content Types and BDC models, however when you click the any of the commands on the Edit Ribbon, for example, Import to upload a BDC model, the error message: “Sorry, this site hasn’t been shared with you.” is displayed.
As a BDC service application administrator, in SharePoint Designer, no external content types will be displayed in the External Content Types gallery, and when you attempt to add a connection to an external system, such as an SQL Server database, then the message “Access denied by Business Data Connectivity” is displayed, as shown in the following screenshot.
Edit permission on External Content Type
To modify an existing ECT, you need to have Edit permissions on the ECT. Without Edit permissions the ECT will not be visible in the External Content Types gallery in SharePoint Designer.
No Execute permission on the External Content Type
Once you have created an ECT, next you need to test it – the easiest method is to create an external list based on the ECT. Just because you have the Edit permissions on the ECT, it does not follow that you have Execute Permissions on the ECT. With only Edit permission on the ECT you will be able to create an external list assuming you have the Manage Lists and the Add and Customize Pages rights and you are using SharePoint Designer, however when you click on the external list, you may see no data. To display data in the external list you need permission to execute the Read List operation (Finder method) which is associated with the view.
The following sections details the error messages you received with eternal lists, external columns, Business Data Web Parts and Quick Parts, when you do not have the Execute permission on the ECT.
External List
When you do not have the Execute permissions for the Read List operation, then no data is displayed in the external list and the following error message is displayed:
“Access denied by Business Data Connectivity.”
As shown in the following screenshot:
External Column
When a user displays in the browser a view of a list or a library that contains an external column, the data in the external, as well as data in any associated columns can be seen. This user may be mapped to any permission level that allows them to see the items in the list, such as View Only, Edit or Contribute. This user may not have the Execute permission on the ECT. This happens, as when the user who created or modified the items, selected the values in the external columns, then the data from the external system is copied to the list or library at that time, and is stored within a SharePoint content database. This is unlike external lists, where external data is not copied from the external system.
However, if a user is allowed to modify an item in the list or library, but does not have the Execute permission on the ECT, then when they try to edit the properties of a list item or file, the Edit properties page tries to resolve the previously selected value in the external column and as the user does not have the rights to execute the Read List operation (Finder method) the following message is displayed:
“No exact match was found. Click the item(s) that did not resolve for more options. You can also use Select button to choose External Data.”
As shown in the following screenshot:
If the user then displays the External Item Picker for the external column, the following error message is displayed:
“Access denied. You do not have permission to access this content.”
As shown in the following screenshot:
This user who has no Execute permission on the ECT associated with the external column will be able to:
- Create new items, upload and create new documents providing that they do not attempt to add any content into the external column.
- Delete items or documents in the list or library.
When such a user wants to modify the properties of an existing list item or document, then they must delete the contents in the external column to save their modifications. If the external column is a required column, then the user will be unable to make any modifications to items in the list or create new items.
Word Quick Parts
When a user has inserted Quick Parts into a document to display or modify values in an external column, then when the document first opens then any embedded external data is displayed for exactly the same reasons why a user can see data in the external column when viewing the library in the browser. However, if the user tries to amend the values in the Quick Part control, then when the External Item Picker is displayed, the following error message is displayed:
“An error occurred while obtaining business data by using the Picker web service. Contact your system administrator.”
As shown in the following screenshot:
Business Data Web Parts
When a user displays a page where a Business Data Web Part is displayed, and does not have Execute permissions to the ECT that the Business Data Web Part uses, then no external data is displayed and the following error message is displayed:
“Access denied. You do not have permission to access this content.
Correlation ID:1d90a99c-28d3-0034-3fb4-8a8221d868dc”
As shown in the following screenshot.
User does not have Selectable in Client Permissions on the External Content Type
The external content type (ECT) is not displayed in the External Content Type Picker when you try to create an External List or use Business Data Web Parts. However all ECTs are displayed when you create an external column, even if you have no BDC object permission to any ECTs.
Tip: To create an external list you must have both the SharePoint Manage Lists and the Add and Customize Pages rights, as well as the Selectable in Client object permission to the ECT. When you have the Manage Lists rights and not the Add and Customize Pages rights, then you are only allowed to add SharePoint internal list apps, such as, SharePoint libraries – Document, Form, Wiki Page, Picture and SharePoint lists – Links, Announcements, Contacts, and so on; however to display the option to create an external list under Apps you can add on the Your Apps page you must have the Add and Customize Pages rights. By default this right is included in the Full Control permission level and the Design permission level, and therefore, usually only Site Owners can create external lists.
Methods for finding the permissions for an external content type
You can use the SharePoint Central Administration web site, SharePoint Designer or Windows PowerShell to find the permissions settings for an ECT, as described below:
· SharePoint Central Administration web site:
- Open the SharePoint Central Administration website in the browser.
- Under Application Management, click Manage service applications.
- On the Service Applications page, click the name of the BDC service for which you want to manage permissions.
- Select the check box to the left of the ECT and then click Set Object Permissions on the Edit Ribbon tab.
· SharePoint Designer:
- Using SharePoint Designer, open any site where you have permissions to use SharePoint Designer.
- In the Navigation pane, click External Content Types.
The External Content Types gallery page is displayed in the workspace. The workspace might be empty if no ECTs have been created or you have do not have Edit permissions on any ECTs. - Click the ECT to display the Summary View.
You can view, but not modify the permissions for the ECT which are displayed in the Permissions area.
· Windows PowerShell:
To display the permissions for one ECT, type commands similar to the following:
$ect = Get-SPBusinessDataCatalogMetadataObject –BdcObjectType Entity `
-Name Suppliers `
-Namespace Northwind_MetaMan `
-ServiceContext http://intranet
$ect.GetAccessControlList()
Where Suppliers is the name of your ECT, Northwind_MetaMan is the namespace, and http://intranet is the URL of a web application that is associated with your BDC service application.
To export the output to a file that you can then open in Microsoft Excel use the following command:
$ect.GetAccessControlList() | Export-Csv c:\Tools\ECT.csv
To display all active ECTs and their permissions, type commands similar to the following:
$metadatastore = Get-SPBusinessDataCatalogMetadataObject `
–BdcObjectType Catalog –ServiceContext http://intranet
$ects = $metadatastore.GetEntities(“*”,”*”,$true)
$ects | foreach {
$_.Namespace, $_.Name,
$metadatastore.GetEntity($_.Namespace,$_.Name).GetAccessControlList()
}
The ECT output can be formatted so that it is easier to read by using a command similar to the following:
$ects | foreach {
(“`r`n ECT: ” + $_.Name + “`t” + $_.Namespace),
$metadatastore.GetEntity($_.Namespace,$_.Name).GetAccessControlList()
}
Warning: Windows PowerShell scripts can affect the performance of SharePoint and therefore can impact your users. You should consider running scripts outside of business hours.
Summary
In this third part of this blog series, we detailed errors that are displayed when users do not have the necessary BDC object permissions on external content types. Briefly:
- When you do not have Edit permissions on the BDC Metadata Store, then you cannot create an ECT.
- When you do not have Edit permissions on the ECT, you cannot modify or delete the ECT.
- To create external lists, you must have the SharePoint Manage Lists and the Add and Customize Pages rights, as well as the Selectable in Client object permission to the ECT.
- To create, read (view), update or delete external data using an external list you must have the Execute permission on the relevant operation.
- To select an ECT in a Business Data Web Part you must have the Selectable in Client object permission to the ECT. For users to see the external data in the Web Part they must have the Execute permission.
- To select external data stored in external columns, including selecting external data using Quick Part controls in Word, you must have Execute permissions on the Read List operation.
Reference
- Overview of Business Connectivity Services security tasks in SharePoint 2013.
- Set permissions on the BCS Metadata Store for a Business Connectivity Services on-premises solution in SharePoint 2013.
- Use Windows PowerShell cmdlets to manage Business Connectivity Services in SharePoint Server 2013.
