locked
Users site permission? RRS feed

  • Question

  • Hi everyone, I'm trying to do a custom search webparts because sharepoint doesn't have a good cross site/wildcard search.  I'm using C# & connecting straight to SQL table to do the search.  However, I'm stuck at users permission issue.
    If user at top site level or subsite, they can search & pull the whole result from other subsites
    http://company/site1  http://company/site2 .......

    For example:  user1 only has permission on Site1 but with my search. user1 can still search & pull every document from top site (ok here), site1 (ok here), & results from site2 (not ok here).

    How can I change my SQL syntax to include user permission so that user1 only pull up the search result from sites that he/she has permission to?

    here is my button click SQL search:

    string strSearch;

    strSearch = "SELECT AllDocs.DirName + '/' + AllDocs.LeafName AS LinkFile, AllDocs.Id, AllDocs.DirName, AllDocs.LeafName, U.nvarchar7 AS Title, "
                    + "U.nvarchar10 AS Keywords, AllDocStreams.[Content], DATEADD(hh, - 5, U.tp_Modified) AS Modified, Editor.tp_Title, Author.tp_Title AS SubmitBy, "
                    + "Webs.FullUrl "
                    + "FROM AllUserData AS U INNER JOIN AllDocs ON AllDocs.ListId = U.tp_ListId AND AllDocs.DoclibRowId = U.tp_ID AND AllDocs.Type = 0 "
                    + "INNER JOIN AllDocStreams ON AllDocs.SiteId = AllDocStreams.SiteId AND AllDocs.Id = AllDocStreams.Id "
                    + "LEFT OUTER JOIN Webs ON AllDocs.WebId = Webs.Id AND AllDocs.SiteId = Webs.SiteId LEFT OUTER JOIN UserInfo AS Editor ON Editor.tp_ID = U.tp_Editor AND Editor.tp_SiteID = U.tp_SiteId "
                    + "FULL OUTER JOIN UserInfo AS Author ON Author.tp_ID = U.tp_Author AND Author.tp_SiteID = U.tp_SiteId "
                    + "WHERE (U.nvarchar10 LIKE '%" + searchtxt.Text + "%' OR AllDocs.Leafname LIKE '%" + searchtxt.Text + "%' OR U.nvarchar7 LIKE '%" + searchtxt.Text + "%' OR Author.tp_Title LIKE '%" + searchtxt.Text + "%' OR Editor.tp_Title LIKE '%" + searchtxt.Text + "%') AND AllDocs.Extension IN ('pdf', 'doc', 'dwg', 'xls', 'docx', 'par', 'dft', 'asm', 'cfg', 'txt')";

    • Edited by KevinLe Wednesday, June 3, 2009 1:50 PM
    Monday, June 1, 2009 3:49 PM

Answers

  • Hi,

    You shouldn't be directly accessing the database.  What are your business requirements?  It's likely this could be achieved by writing a query against the search api.

    Dave
    My SharePoint Blog - http://www.davehunter.co.uk/blog
    Monday, June 1, 2009 4:03 PM
  • You can try running the following code which executes a FullTextSQLQuery using the wildcard syntax and the CONTAINS clause. Make sure to install the infrastructure update on your development wss box. Run a full crawl and execute the query.

    http://www.microsoft.com/enterprisesearch/en/us/technical-resources.aspx

      
    public static DataTable GetFTSQueryWildCardSearch()
    {
    
               DataTable results = new DataTable();
    
               using (SPSite site = new SPSite("http://basesmcdev2/sites/tester1"))
               {
                   FullTextSqlQuery fts = new FullTextSqlQuery(site);
                   fts.QueryText = "SELECT Title,FileExtension,ContentType,Created,LastModifiedTime,Path FROM SCOPE() WHERE (CONTAINS(Path,'\"/sites/tester1/Lists/* \"'))";
                   fts.ResultTypes = ResultType.RelevantResults;
                   fts.RowLimit = 300;
    
                   ResultTableCollection rtc = fts.Execute();
                   if (rtc.Count > 0)
                   {
    
                       using (ResultTable relevantResults = rtc[ResultType.RelevantResults])
                           results.Load(relevantResults, LoadOption.OverwriteChanges);
    
                   }
    
                   return results;
    
               }
    
    }

    certdev.com
    Thursday, June 4, 2009 6:55 PM

All replies

  • Hi,

    You shouldn't be directly accessing the database.  What are your business requirements?  It's likely this could be achieved by writing a query against the search api.

    Dave
    My SharePoint Blog - http://www.davehunter.co.uk/blog
    Monday, June 1, 2009 4:03 PM
  • I forgot to mention that this is WSS 3.  WSS 3 has a bad search function.  My boss asked me to do a search webparts.  Since I'm not a Guru in Sharepoint programming, I connect straight to SQL table & do a search there.  It's much easier for me to do.

    Does Sharepoint has any kind of Session variables or something?  What is the User permission table?  I just need to modify my SQL statement in my button click event

    Thanks

    Monday, June 1, 2009 4:06 PM
  • Instead of trying to do anything yourself, just install Search Server 2008 Express on top of WSS 3.0 (use the create a new site option in the installation).

    This gives you the same level of searches as are available with MOSS 2007 (and it's free).
    WSS FAQ sites: http://wssv2faq.mindsharp.com and http://wssv3faq.mindsharp.com
    Total list of WSS 3.0 / MOSS 2007 Books (including foreign language) http://wssv3faq.mindsharp.com/Lists/v3%20WSS%20FAQ/V%20Books.aspx
    Monday, June 1, 2009 4:25 PM
  • Yes, I already tried the Search Server 2008.  Search server doesn't search cross site & it doesn't support Wild card search.  That's why I have to do my search web part
    Monday, June 1, 2009 4:58 PM
  • Can someone help me with this?

    Thanks
    Monday, June 1, 2009 7:15 PM
  • Search server 2008 supports cross farm searching. It also supports wild card searching using the Contains clause. For more information regarding doing searches using the FullTextSqlQuery class see the links below. Extremly fast and very scalable, and microsoft supported. Automatically security trimmed.


    http://msdn.microsoft.com/en-us/library/ms493660.aspx

    http://msdn.microsoft.com/en-us/library/ms513993.aspx

    http://msdn.microsoft.com/en-us/library/microsoft.office.server.search.query.fulltextsqlquery.aspx
    http://www.certdev.com
    • Proposed as answer by Steve.Curran Monday, June 1, 2009 10:12 PM
    Monday, June 1, 2009 10:12 PM
  • We're a small business company.  We don't have the budget to buy Full version of Search Server.  That's why I have to do my custom search webparts in the first place.  I know that Full version Search Server 2008 can do this but we can't buy it.

    Can someone help me with this?  how can I check for user site permission & filter throught my SQL statement?  I found a small routine that check for user subsite permission but I don't know how to apply to my SQL statement.  Would someone please help me with this?  I'm very appreciated.

    Thanks

    public bool UserHasRigtsToSite(string webUrl)
            {
                SPUser currentUser = SPContext.Current.Web.CurrentUser;
                bool result = false;
                SPSecurity.RunWithElevatedPrivileges(delegate()
                {
                    using (SPSite site = new SPSite(SPContext.Current.Site.ID))
                    {
                        using (SPWeb web = site.OpenWeb(webUrl))
                        {
                            result = web.DoesUserHavePermissions(currentUser.LoginName, SPBasePermissions.ViewPages | SPBasePermissions.Open);
                        }
                    }
                }
                );

                return result;
            }

    Wednesday, June 3, 2009 1:49 PM
  • Search Server 2008 is free with the infrastructure update for WSS.
    http://www.certdev.com
    Wednesday, June 3, 2009 3:23 PM
  • Are you sure search server 2008 will perform cross-site search (search topsite & every subsite) with wildcard search?  the last time that I tried, it was not working with cross-site search & with wildcard
    Wednesday, June 3, 2009 5:11 PM
  • Please read the link I provided before regarding using the CONTAINS clause and its ability to do wild card searching.

    http://msdn.microsoft.com/en-us/library/ms513993.aspx
    http://www.certdev.com
    Wednesday, June 3, 2009 5:15 PM
  • I read the link about the contain clause.  How do I set this? or how do I perform wildcard search?  I still can't do wildcard search

    Thanks
    Thursday, June 4, 2009 6:22 PM
  • You can try running the following code which executes a FullTextSQLQuery using the wildcard syntax and the CONTAINS clause. Make sure to install the infrastructure update on your development wss box. Run a full crawl and execute the query.

    http://www.microsoft.com/enterprisesearch/en/us/technical-resources.aspx

      
    public static DataTable GetFTSQueryWildCardSearch()
    {
    
               DataTable results = new DataTable();
    
               using (SPSite site = new SPSite("http://basesmcdev2/sites/tester1"))
               {
                   FullTextSqlQuery fts = new FullTextSqlQuery(site);
                   fts.QueryText = "SELECT Title,FileExtension,ContentType,Created,LastModifiedTime,Path FROM SCOPE() WHERE (CONTAINS(Path,'\"/sites/tester1/Lists/* \"'))";
                   fts.ResultTypes = ResultType.RelevantResults;
                   fts.RowLimit = 300;
    
                   ResultTableCollection rtc = fts.Execute();
                   if (rtc.Count > 0)
                   {
    
                       using (ResultTable relevantResults = rtc[ResultType.RelevantResults])
                           results.Load(relevantResults, LoadOption.OverwriteChanges);
    
                   }
    
                   return results;
    
               }
    
    }

    certdev.com
    Thursday, June 4, 2009 6:55 PM
  • Thank you very much for your codes.

    Sorry I'm a still a newbie in Sharepoint development.  Could you show me how to apply this codes? 

    On my development machine, I have to create a new webpart & add a new class.  Then, I can paste this codes in?  how do I use this codes?

    Can you show me?  thank you so much
    Thursday, June 4, 2009 8:26 PM
  • You can look at this link which gives step by step instructions on how to create your own custom search web part.

    http://msdn.microsoft.com/en-us/library/bb871647.aspx
    certdev.com
    Friday, June 5, 2009 4:12 PM