No Code secure search with FAST Search for SharePoint 2010

  • Article

Introduction

Implementing secure database search on the item level with FastSearch for SharePoint 2010 (FS4SP) has been a somewhat confusing journey for me.

At the start I had some questions:

  • Can I use BCS?
  • Must I write .NET assembly connector?
  • Can I use FAST’s jdbcconnector?

The short answer is yes, no and yes. The long answer will follow below.

Secure search

To support secure search at the item level each document will have to be associated with an ACL, describing who has access to the document. At query time the user's group memberships will be looked up against Active Directory and added to the query to match against the ACLs stored in the index. This way only documents the user has access to will be shown in the search result. Out the box, FS4SP will only support Active Directory as the user directory.

Enabling secure search on database content is therefore only a matter of adding a security descriptor to each row, describing who should be able to read this row. This sounds easy enough, but the question is still; what should this ACL look like, and how do I put it on the correct place in my index.

Using BCS

As an example I've used the AdventureWorksDW database and the table DimProduct. The table has been altered to include a column named SecurityDescriptor of type varbinary(MAX). I started with SharePoint Designer (SPD) and created my simple model with ReadList and ReadItem methods. This model was exported from SPD to a BDCM (XML) file. The model doesn’t include any information about where to fetch the security descriptor, it is however described on various places on the internet how to enable security on the item level in the model. MSDN has one example. Note that you only need to update the SpecificFinder method, no BinarySecurityDescriptorAccessor is needed.

I'll recap the steps below, but please check MSDN for the correct placement in your bdcm (or see the attached example):

1. Add a new TypeDescriptor in the SpecificFinder, this will read a bytearray from the column named SecurityDescriptor:

 <TypeDescriptor TypeName="System.Byte[], mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="SecurityDescriptor">
 <TypeDescriptors>
 <TypeDescriptor TypeName="System.Byte" Name="SecurityDescriptorElement" />
 </TypeDescriptors>
</TypeDescriptor>

2. Add a reference to the the SecurityDescriptor in the property WindowsSecurityDescriptorField (still on SpecificFinder)

 <MethodInstance Type="SpecificFinder" ReturnParameterName="DimProductRead Item" ReturnTypeDescriptorPath="DimProductRead Item[0]" Default="true" Name="DimProductRead Item" DefaultDisplayName="Read Item AdventureWorksDW">
  <Properties>
 <Property Name="WindowsSecurityDescriptorField" Type="System.String">SecurityDescriptor</Property>
 </Properties>

3. Make sure to remove the UseClientCachingForSearch

 <Property Name="UseClientCachingForSearch" Type="System.String"></Property>

4. Also make sure you have "ShowInSearchUI" (should already be there)

Now, import your model using Central Admin, and once you have your SecurityDescriptors in place you should be ready to crawl.

Creating the ACL

Finding the correct format of the Security Descriptor (ACL) and how to store it in SQL server was the most difficult part digging out. The Security Descriptor should be stored in binary format in the column referenced in the BDC model. On MSDN there is a C# code snippet that retrieves a sample windows Security Descriptor for a specific user/group in binary as a byte array. I've extended the snippet to update my SecurityDescriptor column (I’m using the same ACL for all rows):

 static void Main(string[] args)
{
    Program sdl = new Program();
    byte[] b = sdl.GetSecurityDescriptor("Contoso", "SearchUsers");

    SqlConnection cn = new SqlConnection();
    cn.ConnectionString = "Data Source=demo2010a;Integrated Security=True";
    cn.Open();

    SqlCommand cmd = new SqlCommand("UPDATE [AdventureWorksDW].[dbo].[DimProduct] SET [SecurityDescriptor] = @SecurityDescriptor", cn);
    cmd.Parameters.Add("@SecurityDescriptor", System.Data.SqlDbType.VarBinary);
    cmd.Parameters["@SecurityDescriptor"].Value = b;

    cmd.ExecuteNonQuery();

    cn.Close();

    Console.WriteLine(b.ToString());
}

private Byte[] GetSecurityDescriptor(string domain, string username)
{
    NTAccount acc = new NTAccount(domain, username);
    SecurityIdentifier sid = (SecurityIdentifier)acc.Translate(typeof(SecurityIdentifier));
    CommonSecurityDescriptor sd = new CommonSecurityDescriptor(false, false, ControlFlags.None, sid, null, null, null);
    sd.SetDiscretionaryAclProtection(true, false);    //Deny access to all users.   
    SecurityIdentifier everyone = new SecurityIdentifier(WellKnownSidType.WorldSid, null);
    sd.DiscretionaryAcl.RemoveAccess(AccessControlType.Allow, everyone, unchecked((int)0xffffffffL), InheritanceFlags.None, PropagationFlags.None);
    //Grant full access to a specified user.   
    sd.DiscretionaryAcl.AddAccess(AccessControlType.Allow, sid, unchecked((int)0xffffffffL), InheritanceFlags.None, PropagationFlags.None);

    Console.WriteLine(sd.GetSddlForm(AccessControlSections.All));

    byte[] secDes = new Byte[sd.BinaryLength];
    sd.GetBinaryForm(secDes, 0); 
    return secDes;
}

Once you've run the code snippet above you should be ready crawl, and any users in the SearchUsers groups should be able to see the secured rows in the search results.

JDBC format

The BCS crawler will submit the SecurityDescriptor in string format (SDDL) into the docaclms property. During content processing the docaclms property will be converted to an internal format which consists of the SID's of each user/group which allowed or denied access to the document. Each SID is base32 encoded and put into the property docacl. The docacl property is the one which is actually filtered against at query time. When using the jdbc connector one has the possibility to write directly to the docacl property. To create a correct string use the Get-FASTSearchSecurityEncodedSid:

PS C:\FASTSearch\bin> Get-FASTSearchSecurityEncodedSid -User Contoso\danj

S-1-5-21-331390976-2650875657-2422772959-1705

aecqaaaaaaaakfiaaaaabigacmesoam525kgrefjayaaa

PS C:\FASTSearch\bin> Get-FASTSearchSecurityEncodedSid -User Contoso\SearchUsers

S-1-5-21-331390976-2650875657-2422772959-2004

aecqaaaaaaaakfiaaaaabigacmesoam525kgregua3aaa

The cmdlet will output both the SID and the encoded SID. To create a proper acl prefix each SID with "win" (allow) or "9win"  (deny). To allow CONTOSO\danj and CONTOSO\SearchUsers, the docacl would be

winaecqaaaaaaaakfiaaaaabigacmesoam525kgrefjayaaa; winaecqaaaaaaaakfiaaaaabigacmesoam525kgregua3aaa

The ACL above could either be put into the SQL table as showed below, or it can it can be hardcoded in the SQL query of the jdbc connector configuration:

SELECT [ProductKey], 'winaecqaaaaaaaakfiaaaaabigacmesoam525kgrefjayaaa; winaecqaaaaaaaakfiaaaaabigacmesoam525kgregua3aaa' as docacl

See the attached jdbc connector configuration for a complete example.

Database sample

The screenshot below shows a couple of sample rows. docaclwin contains the base32 encoded SID values, and SecurityDescriptor contains the binary Security Descriptor.

image

SampleFiles.zip