I'm trying to write an eql to filter out all the documents which have a particular role in them thus making it visible to all the users who are included in that role but there are no such fields for 'security' tab in the designer. Is this query possible?
Yes, you can query the Roles to accomplish this:
START WITH Role SELECT Id, Name, SecurityRefs.Object.Id, SecurityRefs.Object.Label WHERE SecurityRefs.Object.Type = 3 AND Name = '[Name of Role]' ORDER BY SecurityRefs.Object.Label
* * * * *
Unfortunately, all that you can return from a Role query is the document id and the object label (title). However, you can use the id to generate a hyperlink to the document.
If you want to see all the permissions you can output those, too. You can also add a parameter to select the role you want to view at runtime;
START WITH Role SELECT Id, Name, SecurityRefs.Object.Id, SecurityRefs.Object.Label, CASE BITAND( SecurityRefs.Permission, 1 ) WHEN 1 THEN 'Yes' ELSE 'No' END pView, CASE BITAND( SecurityRefs.Permission, 2 ) WHEN 2 THEN 'Yes' ELSE 'No' END pModify, CASE BITAND( SecurityRefs.Permission, 4 ) WHEN 4 THEN 'Yes' ELSE 'No' END pApprove, CASE BITAND( SecurityRefs.Permission, 16 ) WHEN 16 THEN 'Yes' ELSE 'No' END pDelete WHERE SecurityRefs.Object.Type = 3 AND Id = @Id ORDER BYSecurityRefs.Object.Label
Use this search to populate the @Id parameter:
START WITH Role SELECT Id col1, Name col2 WHERE Name LIKE @StringValue ORDER BY Name
Note that returning permission "1" (view) is not really necessary, because if the role doesn't have that, the document wont appear in the report anyway. It will never be "No". Also, the designer will want you to alias the names of the CASE statement columns, but if you use the word "View" as an alias, that will cause an error, so I prefixed it with "p". You can change it back on the format layout tab.
Eric Rajala | Consultant
Answer Verified By: Devang Singh
Thankyou Eric, that was really helpful.
You are welcome. Two caveats to finish this topic. If you have any documents with no security applied, they are completely open (all permissions) to every user, but they will not appear on this report.
If a specific view permission is not applied, but others are, you WILL see 'No' in the view column. In this case, it usually means that all users can view the document. It could also mean that the permission settings are inconsistent with each other, in which case you will see a warning about security in Director.
Also, the report doesn't include document templates, if you want to look at those, templates are object type 188, not 3.
Thanks Eric, I also have a doubt regarding search. Is it possible to search in different elements then merge the columns in result in the same eql query? eg here Im trying to search under 'Roles' and another one in 'Documents', can I merge different select columns (exclusive) in a single eql say in 'Roles' and get a result with columns from 'Documents' as well?
No. You'd have to export the reports to an Excel workbook and merge them there. What "merging" is possible you can see with the intellisense.in the report designer. For example, if a report begins START WITH Document you can, of course, return the document Id, as with every object. But you can also get the Ids for related objects such as Tags, Physical items, Change Requests and etc.: PhysicalItems.PhysicalItem.Id, Tags.Tag.Id, Projects.Project.Id, . . . Similarly, you can retrieve related document Ids from a report that START[s] WITH one of these objects - Documents.Document.Id.. Then you can merge on the Ids in Excel. You could also merge on other values that are available in both reports, but Ids are always present at a minimum. So on a START WITH Role report, "SecurityRefs.Object.Id" is the document id WHERE SecurityRefs.Object.Type = 3. On a Role object report, all you can see from the related objects are the Ids and the Labels (aka the title in the case of a document),
If you want to do it all on the server side, then you will need SSRS (Microsoft SQL Server Reporting Services).