Friday, August 29, 2014

SQL Server Access Control & Synonyms

OK. So, say you have a SQL Server database and you want to provide varying levels access to different users groups.   This database was created for one application, and now you are being asked to provide access to a report development team.   How do you go about doing that in some rational way, say the way an admin assigns access to files on a file server. 

Well, that would be great, only there aren't any folders in SQL Server.  BUT, since SQL Server 2005 we've been given real Schema objects and Synonyms to boot.

SO, how does that help us?  Lets take a look, say you built your database and like any rational developer you built everything in DBO.  Here's a list of your tables:

dbo.Customers
dbo.PurchaseOrders
dbo.Invoices
dbo.UserLogins
dbo.ApplicationSettings

Obviously you don't want the report developers having access to the UserLogins and ApplicationSettings.  One, they just don't need it, and two, there's sensitive stuff in there.

Our approach:
  • Use an active directory group (My_Domain\Report Writers) to control access.
  • Create a Schema for the report writers to access
  • Assign access to the new schema and not the old one.
 Step 1) Create the new schema:
CREATE SCHEMA reports

Step 2) Add some table synonyms to the schema:
CREATE SYNONYM reports.PurchaseOrders FOR dbo.PurchaseOrders
CREATE SYNONYM reports.Invoices FOR dbo.Invoices
CREATE SYNONYM reports.Customers FOR dbo.Customers

 Step 3) Map the AD group to your database
CREATE LOGIN [MY_DOMAIN\Report Writers] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
CREATE USER [MY_DOMAIN\Report Writers] FOR LOGIN [MY_DOMAIN\Report Writers]

(This is the same as adding a Login at the server level, and mapping to the public role on a database catalog).

Step 4) Give the report writers access to the reports schema.
GRANT SELECT ON SCHEMA :: reports TO [MY_DOMAIN\Report Writers]

What have we accomplished?
  • Your report writer team can log into your database
  • Your report writer team can view all of the table synonyms in Management Studio
  • Your report writer team doesn't have any write permissions (INSERT/DELETE/UDPATE) to anything.
  • Your report writer team cannot query the objects in DBO directly, so they don't have access to sensitive tables like UserLogins and ApplicationSettings.
Why did this work?

Turns out that in SQL Server Synonyms are like file system hard links.  So if you had a file in one directory, and took away permissions on that directory.  Then created a hard link in another directory and give permissions, the user would have access.  The same idea works here.   Since the report writers didn't have access to the DBO schema, they can't view the tables there.  But since they have access to REPORTS they may read the synonyms and query them as well.

Turns out that you can customize access to the synonyms once they are created.  All of the GANT/DENY/REVOKE commands work the same.  You'll even be able to apply column level security!


Tuesday, August 12, 2014

Anonymous Performance Point Dashboards (SP2013)

Performance Point (PPS) became part of the Enterprise offering of SharePoint starting with Microsoft Office SharePoint Server 2007.  As a tool it was branded as "Bringing BI to the Masses."  In SharePoint 2010, it was possible to deploy PPS dashboards to BI sites with anonymous access.  SharePoint 15 (2013) broke this, either on purpose or by mistake, and here's how it happened:

Assembly: Microsoft.PerformancePoint.ScoreCard.WebControls.dll
Version: 14.0.0.0
Class: Microsoft.PerformancePoint.ScoreCard.OlapViewCache
Derived Class: System.Web.UI.Page

Assembly: Microsoft.PerformancePoint.ScoreCard.WebControls.dll
Version: 15.0.0.0
Class: Microsoft.PerformancePoint.ScoreCard.OlapViewCache
Derived Class: Microsoft.SharePoint.WebControls.LayoutsPageBase

Differences between version 14 & 15: Other than derived class, none.

Result of the change: _layouts/PPSWebParts/OlapViewCache.aspx requires user authentication with SharePoint 2013 (v15), where as SharePoint 2010 (v14) did not.  This means that while the ASPX application page generated by SharePoint designer can be placed in an anonymous access document library, elements referenced on the page via Image (<img src=""/>) tags require authentication.  Failure to provide credentials causes the chart elements to not render, causing a critical failure of the dashboard in anonymous access sites.

Here's the work around we implemented.

  1. Create an ASPX page which duplicates the operations of Microsoft.PerformancePoint.ScoreCard.OlapViewCache.
  2. Copy the ASPX page from (1) to:
    • 15\TEMPLATE\LAYOUTS\PPSWebParts
    • 14\TEMPLATE\LAYOUTS\PPSWebParts

Note: an IISRESET may be required after placing the files in the 14 & 15 hives.

The following content implements the replacement OlapViewCache.aspx which derives from Page instead of LayoutsPageBase.



<%@ Page Language="C#" %>
<%@ Assembly Name="Microsoft.PerformancePoint.ScoreCards.ServerCommon, 
        Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Import Namespace="Microsoft.PerformancePoint.Scorecards"  %>
<%@ Import Namespace="Microsoft.SharePoint.WebControls"  %>
<%@ Import Namespace="System"  %>
<%@ Import Namespace="System.Globalization"  %>
<%@ Import Namespace="System.Web"  %>
<%--
    Name:                   OlapViewCache.aspx
    Deployment Location:    15\TEMPLATE\LAYOUTS\PPSWebParts
    Description:
        Replaces the SharePoint 2013 OlapViewCache.aspx utility page.  The script
        code in this file was produced to replicate
        Microsoft.PerforamcePoint.Scorecards.WebControls which changed inheritance
        to LayoutsPageBase in SharePoint v15 (2013).  In v14, System.Web.UI.Page
        was the derived class.  The change in v15 caused the page to require 
        authentication meanwhile, other dashboard components could be used 
        anonymously.  This ASPX class derives from Page once more.
--%>
<script runat="server" type="text/C#">
    private void Page_Load(object sender, EventArgs e) {
        string externalkey = Request.QueryString["cacheID"];
        string s1 = Request.QueryString["height"];
        string s2 = Request.QueryString["width"];
        string tempFcoLocation = Request.QueryString["tempfco"];
        string str1 = Request.QueryString["cs"];
        string str2 = Request.QueryString["cc"];
        int height;
        int width;
        
        try {
            height = int.Parse(s1, (IFormatProvider)CultureInfo.InvariantCulture);
            width = int.Parse(s2, (IFormatProvider)CultureInfo.InvariantCulture);
        } catch {
            height = 480;
            width = 640;
        }
        
        int colStart = 1;
        int colCount = 100;
        try {
            if (str1.Length > 0)
                colStart = Convert.ToInt32(str1, 
                        (IFormatProvider)CultureInfo.CurrentCulture);
            if (str2.Length > 0)
                colCount = Convert.ToInt32(str2, 
                        (IFormatProvider)CultureInfo.CurrentCulture);
        } catch {
            colStart = 1;
            colCount = 100;
        }
        
        string mimeType;
        string viewHtml;
        byte[] bytesImageData;
        if (!BIMonitoringServiceApplicationProxy.Default
                .GetReportViewImageData(tempFcoLocation, externalkey, 
                    height, width, colStart, colCount, out mimeType, 
                    out viewHtml, out bytesImageData))
            return;
        
        if (mimeType.IndexOf("TEXT", StringComparison.OrdinalIgnoreCase) >= 0) {
            Response.ContentType = mimeType;
            Response.Write(viewHtml);
        } else {
            if (bytesImageData.Length <= 0)
                return;
            Response.Clear();
            Response.ContentType = mimeType;
            Response.BinaryWrite(bytesImageData);
            HttpContext.Current.ApplicationInstance.CompleteRequest();
        }
    }
</script>