none
Security trimming on Database connector. RRS feed

  • Question

  • Can I configure Security trimming on database connector.?

    Hello Everyone,

    Suppose I have one table 'EmployeeSalartStructure' and i crawled it using jdbcconnector. This table contains salary of all employees, employees may be the Manager or normal person. If Manager login to fast search center and he hit the query in search box they he can see all generated result but when the normal user login then i want to restrict him by seeing the information from crawled table.

     

    Friday, January 28, 2011 12:49 PM

Answers

  • Check this thread about BCS against a database with security. It involves creating a custom security trimmer. But I guess the method would work for the jdbc connector as well.

    Regards,
    Mikael Svenson


    Search Enthusiast - MCTS SharePoint/WCF4/ASP.Net4
    http://techmikael.blogspot.com/ - http://www.comperiosearch.com/
    Sunday, January 30, 2011 7:09 PM
  • Hi

    If you cannot for some reason replicate your user-groups in AD, the alternative is to specify the "spacl" attribute while feeding. It is document as part of the SharePoint Protocol documentation and can be found here: http://msdn.microsoft.com/en-us/library/ee626130(office.12).aspx

    As far as I know, this can only be specified when feeding via BCS, not when done using the jdbcconnector.

    Regards


    Thomas Svensen | Microsoft Enterprise Search Practice
    • Marked as answer by Uday G Tuesday, April 19, 2011 9:30 AM
    Tuesday, April 19, 2011 8:56 AM
    Moderator

All replies

  • Check this thread about BCS against a database with security. It involves creating a custom security trimmer. But I guess the method would work for the jdbc connector as well.

    Regards,
    Mikael Svenson


    Search Enthusiast - MCTS SharePoint/WCF4/ASP.Net4
    http://techmikael.blogspot.com/ - http://www.comperiosearch.com/
    Sunday, January 30, 2011 7:09 PM
  • Hi Mikael,

    Thanks for your valualbe suggestion

    I gone through your blog and trying for implementation of custom security trimmer, along with the link which you had provided the link I am referring is here http://msdn.microsoft.com/en-us/library/ee819923.aspx#step2register

    I have created custom security trimmer with my access logic, but i am failed to debug it. 

    Once it will done i will let you know by this blog.

    Here are some observation of mine as

    When i looked into the crawled urls then i am not getting any database crawled url like : http://localhost/sites/fastsearch/60 - where 60 is primary key of database item which is I am getting on search result page.

    Thanks and Regards,

    Uday

    Tuesday, February 1, 2011 11:26 AM
  • Did you get this to work Uday? And it's expected to get the primary key in the url. You have to provide your own viewing of the results and customize the xslt to display them like you want.

    -m


    Search Enthusiast - MCTS SharePoint/WCF4/ASP.Net4
    http://techmikael.blogspot.com/ - http://www.comperiosearch.com/
    Saturday, April 16, 2011 7:24 PM
  • Hi Mikael ,

    This has done...but we can called it as filtering on datatable and providing our own viewing on results.

    But I was looking for security trimming on external database using jdbc connector.

    ACL should get attached while indexing.

    For this can we require our own indexing connector.?

     

    Thanks and Regards,

    Uday

    Monday, April 18, 2011 9:24 AM
  • You could do pretty much like in the BCS example, but you would have to create your security module as a custom pipeline stage instead, as the jdbc connector only serves up columns from a database table or view.

    I haven't tried this myself, but should be doable. Do you have both users and groups? And the easiest way is to replicate them in AD, and then man the db users/groups to the AD ones.

    -m


    Search Enthusiast - MCTS SharePoint/WCF4/ASP.Net4
    http://techmikael.blogspot.com/ - http://www.comperiosearch.com/
    Monday, April 18, 2011 7:15 PM
  • Hi

    If you cannot for some reason replicate your user-groups in AD, the alternative is to specify the "spacl" attribute while feeding. It is document as part of the SharePoint Protocol documentation and can be found here: http://msdn.microsoft.com/en-us/library/ee626130(office.12).aspx

    As far as I know, this can only be specified when feeding via BCS, not when done using the jdbcconnector.

    Regards


    Thomas Svensen | Microsoft Enterprise Search Practice
    • Marked as answer by Uday G Tuesday, April 19, 2011 9:30 AM
    Tuesday, April 19, 2011 8:56 AM
    Moderator
  • Hi Mikael,

    Security trimming for fast search:

    In database table i have three different rows with security descriptor  and in the XML i have something like

    <AccessControlEntry Principal="corp0\administrator">
          <Right BdcRight="Edit" />
          <Right BdcRight="Execute" />
          <Right BdcRight="SetPermissions" />
          <Right BdcRight="SelectableInClients" />
        </AccessControlEntry>

    But after crawling am getting the error as follows....

    Error while crawling LOB contents. ( Cannot open database "SecTest" requested by the login. The login failed. Login failed for user 'CORP0\administrator'. )

    Regards,

    Ananth.R.


    Ananth

    Monday, December 17, 2012 3:22 AM
  • Hi,

    Does the user CORP0\administrator have read access on your database?

    Thanks,
    Mikael Svenson


    Search Enthusiast - SharePoint MVP/MCT/MCPD - If you find an answer useful, please up-vote it.
    http://techmikael.blogspot.com/
    Author of Working with FAST Search Server 2010 for SharePoint

    Monday, December 17, 2012 7:19 PM
  • Hi Mikael

    I have the security descriptor for corp0\administrator for all the documents in the sql table.

    My XML code is below..  If i fast search for the above user am not getting the results...Also if i want to access/deny where i need to add the users.. Please help

    <?xml version="1.0" encoding="utf-16" standalone="yes"?>
    <Model xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://schemas.microsoft.com/windows/2007/BusinessDataCatalog BDCMetadata.xsd" Name="FinalST" xmlns="http://schemas.microsoft.com/windows/2007/BusinessDataCatalog">
      <AccessControlList>
        <AccessControlEntry Principal="corp0\administrator">
          <Right BdcRight="Edit" />
          <Right BdcRight="Execute" />
          <Right BdcRight="SetPermissions" />
          <Right BdcRight="SelectableInClients" />
        </AccessControlEntry>
      </AccessControlList>
      <LobSystems>
        <LobSystem Type="Database" Name="SecTest">
          <Properties>
            <Property Name="WildcardCharacter" Type="System.String">%</Property>
          </Properties>
          <AccessControlList>
            <AccessControlEntry Principal="corp0\administrator">
              <Right BdcRight="Edit" />
              <Right BdcRight="Execute" />
              <Right BdcRight="SetPermissions" />
              <Right BdcRight="SelectableInClients" />
            </AccessControlEntry>
          </AccessControlList>
          <Proxy />
          <LobSystemInstances>
            <LobSystemInstance Name="SecTest">
              <Properties>
                <Property Name="AuthenticationMode" Type="System.String">PassThrough</Property>
                <Property Name="DatabaseAccessProvider" Type="System.String">SqlServer</Property>
                <Property Name="RdbConnection Data Source" Type="System.String">WIN-H1RNQQ98SOM</Property>
                <Property Name="RdbConnection Initial Catalog" Type="System.String">AdventureWorks2012</Property>
                <Property Name="RdbConnection Integrated Security" Type="System.String">SSPI</Property>
                <Property Name="RdbConnection Pooling" Type="System.String">True</Property>
                <Property Name="ShowInSearchUI" Type="System.String"></Property>
              </Properties>
            </LobSystemInstance>
          </LobSystemInstances>
          <Entities>
            <Entity Namespace="http://win-h1rnqq98som:8141" Version="1.0.0.0" EstimatedInstanceCount="10000" Name="FinalST" DefaultDisplayName="FinalST">
              <Properties>
                <Property Name="Title" Type="System.String">DisplayName</Property>
                <Property Name="DefaultAction" Type="System.String">View Profile</Property>
              </Properties>
              <AccessControlList>
                <AccessControlEntry Principal="corp0\administrator">
                  <Right BdcRight="Edit" />
                  <Right BdcRight="Execute" />
                  <Right BdcRight="SetPermissions" />
                  <Right BdcRight="SelectableInClients" />
                </AccessControlEntry>
              </AccessControlList>
              <Identifiers>
                <Identifier TypeName="System.Int32" Name="Id" />
              </Identifiers>
              <Methods>
                <Method IsStatic="false" Name="TestRead Item">
                  <Properties>
                    <Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">Text</Property>
                    <Property Name="RdbCommandText" Type="System.String">SELECT [Id] , [DisplayName] , [Extension] , [Data] , [ContentType],[SecurityDescriptor]  FROM [dbo].[Test] WHERE [Id] = @Id</Property>
                    <Property Name="BackEndObjectType" Type="System.String">SqlServerTable</Property>
                    <Property Name="BackEndObject" Type="System.String">Test</Property>
                    <Property Name="Schema" Type="System.String">dbo</Property>
                  </Properties>
                  <AccessControlList>
                    <AccessControlEntry Principal="corp0\administrator">
                      <Right BdcRight="Edit" />
                      <Right BdcRight="Execute" />
                      <Right BdcRight="SetPermissions" />
                      <Right BdcRight="SelectableInClients" />
                    </AccessControlEntry>
                  </AccessControlList>
                  <Parameters>
                    <Parameter Direction="In" Name="@Id">
                      <TypeDescriptor TypeName="System.Int32" IdentifierName="Id" Name="Id" />
                    </Parameter>
                    <Parameter Direction="Return" Name="TestRead Item">
                      <TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="TestRead Item">
                        <TypeDescriptors>
                          <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="TestRead ItemElement">
                            <TypeDescriptors>
                              <TypeDescriptor TypeName="System.Int32" ReadOnly="true" IdentifierName="Id" Name="Id">
                                <Properties>
                                  <Property Name="RequiredInForms" Type="System.Boolean">true</Property>
                                </Properties>
                              </TypeDescriptor>
                              <TypeDescriptor TypeName="System.String" Name="DisplayName">
                                <Properties>
                                  <Property Name="Size" Type="System.Int32">50</Property>
                                  <Property Name="RequiredInForms" Type="System.Boolean">true</Property>
                                </Properties>
                                <Interpretation>
                                  <NormalizeString FromLOB="NormalizeToNull" ToLOB="NormalizeToEmptyString" />
                                </Interpretation>
                              </TypeDescriptor>
                              <TypeDescriptor TypeName="System.String" Name="Extension">
                                <Properties>
                                  <Property Name="Size" Type="System.Int32">50</Property>
                                  <Property Name="RequiredInForms" Type="System.Boolean">true</Property>
                                </Properties>
                                <Interpretation>
                                  <NormalizeString FromLOB="NormalizeToNull" ToLOB="NormalizeToEmptyString" />
                                </Interpretation>
                              </TypeDescriptor>
                              <TypeDescriptor TypeName="System.Byte[]" Name="Data">
                                <Properties>
                                  <Property Name="RequiredInForms" Type="System.Boolean">true</Property>
                                </Properties>
                              </TypeDescriptor>
                              <TypeDescriptor TypeName="System.String" Name="ContentType">
                                <Properties>
                                  <Property Name="Size" Type="System.Int32">100</Property>
                                  <Property Name="RequiredInForms" Type="System.Boolean">true</Property>
                                </Properties>
                                <Interpretation>
                                  <NormalizeString FromLOB="NormalizeToNull" ToLOB="NormalizeToEmptyString" />
                                </Interpretation>
                              </TypeDescriptor>
                              <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>
                            </TypeDescriptors>
                          </TypeDescriptor>
                        </TypeDescriptors>
                      </TypeDescriptor>
                    </Parameter>
                  </Parameters>
                  <MethodInstances>
                    <MethodInstance Type="SpecificFinder" ReturnParameterName="TestRead Item" ReturnTypeDescriptorPath="TestRead Item[0]" Default="true" Name="TestRead Item" DefaultDisplayName="Read Item Final">
                      <Properties>
                        <Property Name="WindowsSecurityDescriptorField" Type="System.String">SecurityDescriptor</Property>
                      </Properties>
                      <AccessControlList>
                        <AccessControlEntry Principal="corp0\administrator">
                          <Right BdcRight="Edit" />
                          <Right BdcRight="Execute" />
                          <Right BdcRight="SetPermissions" />
                          <Right BdcRight="SelectableInClients" />
                        </AccessControlEntry>
                      </AccessControlList>
                    </MethodInstance>
                  </MethodInstances>
                </Method>
                <Method IsStatic="false" Name="TestRead List">
                  <Properties>
                    <Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">Text</Property>
                    <Property Name="RdbCommandText" Type="System.String">SELECT [Id] , [DisplayName] , [Extension] , [Data] , [ContentType] FROM [dbo].[Test]</Property>
                    <Property Name="BackEndObjectType" Type="System.String">SqlServerTable</Property>
                    <Property Name="BackEndObject" Type="System.String">Test</Property>
                    <Property Name="Schema" Type="System.String">dbo</Property>
                  </Properties>
                  <AccessControlList>
                    <AccessControlEntry Principal="corp0\administrator">
                      <Right BdcRight="Edit" />
                      <Right BdcRight="Execute" />
                      <Right BdcRight="SetPermissions" />
                      <Right BdcRight="SelectableInClients" />
                    </AccessControlEntry>
                  </AccessControlList>
                  <Parameters>
                    <Parameter Direction="Return" Name="TestRead List">
                      <TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="TestRead List">
                        <TypeDescriptors>
                          <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="TestRead ListElement">
                            <TypeDescriptors>
                              <TypeDescriptor TypeName="System.Int32" ReadOnly="true" IdentifierName="Id" Name="Id">
                                <Properties>
                                  <Property Name="RequiredInForms" Type="System.Boolean">true</Property>
                                </Properties>
                              </TypeDescriptor>
                              <TypeDescriptor TypeName="System.String" Name="DisplayName">
                                <Properties>
                                  <Property Name="Size" Type="System.Int32">50</Property>
                                  <Property Name="RequiredInForms" Type="System.Boolean">true</Property>
                                </Properties>
                                <Interpretation>
                                  <NormalizeString FromLOB="NormalizeToNull" ToLOB="NormalizeToEmptyString" />
                                </Interpretation>
                              </TypeDescriptor>
                              <TypeDescriptor TypeName="System.String" Name="Extension">
                                <Properties>
                                  <Property Name="Size" Type="System.Int32">50</Property>
                                  <Property Name="RequiredInForms" Type="System.Boolean">true</Property>
                                </Properties>
                                <Interpretation>
                                  <NormalizeString FromLOB="NormalizeToNull" ToLOB="NormalizeToEmptyString" />
                                </Interpretation>
                              </TypeDescriptor>
                              <TypeDescriptor TypeName="System.Byte[]" Name="Data">
                                <Properties>
                                  <Property Name="RequiredInForms" Type="System.Boolean">true</Property>
                                </Properties>
                              </TypeDescriptor>
                              <TypeDescriptor TypeName="System.String" Name="ContentType">
                                <Properties>
                                  <Property Name="Size" Type="System.Int32">100</Property>
                                  <Property Name="RequiredInForms" Type="System.Boolean">true</Property>
                                </Properties>
                                <Interpretation>
                                  <NormalizeString FromLOB="NormalizeToNull" ToLOB="NormalizeToEmptyString" />
                                </Interpretation>
                              </TypeDescriptor>
                            </TypeDescriptors>
                          </TypeDescriptor>
                        </TypeDescriptors>
                      </TypeDescriptor>
                    </Parameter>
                  </Parameters>
                  <MethodInstances>
                    <MethodInstance Type="Finder" ReturnParameterName="TestRead List" Default="true" Name="TestRead List" DefaultDisplayName="Final Read List">
                      <Properties>
                          <Property Name="RootFinder" Type="System.String"></Property>
                      </Properties>
                      <AccessControlList>
                        <AccessControlEntry Principal="corp0\administrator">
                          <Right BdcRight="Edit" />
                          <Right BdcRight="Execute" />
                          <Right BdcRight="SetPermissions" />
                          <Right BdcRight="SelectableInClients" />
                        </AccessControlEntry>
                      </AccessControlList>
                    </MethodInstance>
                  </MethodInstances>
                </Method>
                <Method Name="GetData">
                  <Properties>
                    <Property Name="RdbCommandText" Type="System.String">SELECT Data FROM [dbo].[Test] WHERE [Id] = @Id </Property>
                    <Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data, Version=2.0.0.0, Culture=neutral, 
                                                      PublicKeyToken=b77a5c561934e089">Text</Property>
                  </Properties>
                  <Parameters>
                    <Parameter Direction="In" Name="@Id">
                      <TypeDescriptor TypeName="System.Int32" IdentifierName="Id" Name="Id" />
                    </Parameter>
                    <Parameter Name="StreamData" Direction="Return">
                      <TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
                         IsCollection="true" Name="DataReaderTypeDescriptorName">
                        <TypeDescriptors>
                          <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
                                Name="DataRecordTypeDescriptorName">
                            <TypeDescriptors>
                              <TypeDescriptor TypeName="System.Data.SqlTypes.SqlBytes, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="Data" />
                            </TypeDescriptors>
                          </TypeDescriptor>
                        </TypeDescriptors>
                      </TypeDescriptor>
                    </Parameter>
                  </Parameters>
                  <MethodInstances>
                    <MethodInstance Name="DataAccessor" Type="StreamAccessor" ReturnParameterName="StreamData" ReturnTypeDescriptorName="Data">
                      <Properties>
                        <!-- If extension field is available-->
                        <Property Name="Extension" Type="System.String">Extension</Property>
                        <!--If MimeType is available-->
                        <Property Name="ContentType" Type="System.String">ContentType</Property>
                        <!--If attachments is to be displayed in profile pages, add the following property-->
                        <Property Name="MimeTypeField" Type="System.String">ContentType</Property>
                      </Properties>
                    </MethodInstance>
                  </MethodInstances>
                </Method>
              </Methods>
              <Actions>
                <Action Position="1" IsOpenedInNewWindow="false" Url="http://win-h1rnqq98som:8141/FastSearchResultsPage/_bdc/http___win-h1rnqq98som_8141/Final_1.aspx?Id={0}" ImageUrl="/_layouts/1033/images/viewprof.gif" Name="View Profile">
                  <LocalizedDisplayNames>
                    <LocalizedDisplayName LCID="1033">View Profile</LocalizedDisplayName>
                  </LocalizedDisplayNames>
                  <Properties>
                    <Property Name="IsTaskpaneAction" Type="System.Boolean">true</Property>
                    <Property Name="Office Version" Type="System.String">14</Property>
                  </Properties>
                  <ActionParameters>
                    <ActionParameter Index="0" Name="Id[0]">
                      <Properties>
                        <Property Name="IdOrdinal" Type="System.Byte">0</Property>
                      </Properties>
                    </ActionParameter>
                  </ActionParameters>
                </Action>
              </Actions>
            </Entity>
          </Entities>
        </LobSystem>
      </LobSystems>
    </Model>


    Ananth

    Friday, December 21, 2012 4:52 AM
  • Am having the user details with permissions in the AD

    Security Descriptor code:

     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);

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

                return secDes;

            }


    Ananth

    Friday, December 21, 2012 5:01 AM
  • The above is fine  if you use windows accounts to secure the content. However if you secure the content with custom claims, you are out of luck. anybody have idea on how to populate a custom claim as the security descriptor?
    Wednesday, July 10, 2013 7:51 PM