Forum Discussion

Barry_at_GU's avatar
Barry_at_GU
Level 2
6 years ago

(DQL) many to many joins without "path_row" (navigating perms-path-perm_users objects)

Hi All. I'm getting some good results on using DQL for custom reports... ... but I've hit a problem with a DQL report returning many-to-many joins. 1 [PATH ("directory")] has Many [USERS] 1 [...
Rod_p1's avatar
Rod_p1
Level 6
6 years ago

Barry,

have you thought of returning all paths to get the entire list then building the desired database in the Advanced Sequel portion of the report to just add in the report (end result table) the ones that match your unique identifiers?

Rod

  • Barry_at_GU's avatar
    Barry_at_GU
    Level 2
    6 years ago

    Thanks Rod_p1 

    I've been having a re-think on what I'm trying to achieve.

    What to achieve

    1) FOR THIS [path]: a list of available permissions ("readable_permission", "type", "appliesto" ("this folder, all subfolders, only immediate files") and permission ihneritance ( including "isinherited", "inheriting_path")

    ... this tells me the "what" and "how"

    path.absname /* (String) Absolute name of the path: device and share names */
    , path.id AS DIR_ID /* (Integer) Unique ID for this DIRECTORY relative to the msu */
    , path.name /* (String) Name of this DIRECTORY. */
    , permissions.object_type /* (String) Type of object on which this permission is set (msu, DIR) */
    , permissions.type /* [Permission] (String) Type of permission (GRANT, DENY). */
    , permissions.readable_permission /* [Permission] (String) List of readable permissions – read, write, full control etc*/
    , permissions.isinherited /* [Permission] (Integer) 1 if the permission is inherited from parent */
    , permissions.inheriting_type /* [Permission] (String) permission inherited from (msu, DIR). */
    , permissions.inheriting_path.id AS Parent_ID /* [Permission].[Path] where this permission has been inherited */
    , permissions.inheriting_path.name AS Parent_Name /* [Permission].[Path] where this permission has been inherited */
    , permissions.appliesto /* [Permission] (String) Inheritance settings for this permission (e.g. "this folder", "all subfolders", "only immediate files") */

     

    2) FOR THIS [path]: a ist of all people who have access to a path, and what their access is.

     

    path.absname		 /* (String)	Absolute name of the path: device and share names */
, path.id AS DIR_ID	 /* (Integer)	Unique ID for this DIRECTORY relative to the msu */
, path.name		 /* (String)	Name of this DIRECTORY.				 */
, permitted_users.sid	 /* [User] (String) UserID who has permissions to access this path */
, permitted_users.name	 /* [User] (String) fullname with this permission to access this path */
, permitted_users.principal_name /* [User] (String) login with this permission to access this path  */

    ... this tells me the "Who" but I'm missing their matching "how" (ie: some people only have read-only access to a path)

     

    QUESTIONS:

    How do I find out what each person's permissions are for a specific path?

    How do I structure the DQL (and how to go about additional SQL processing) to give me "path-user-perms" and "path-perms" (two separate CSV files with "path.id" the join between both) from the three DQL objects of [path], [permitted_user] and [permissions]?

     

    Thanks for your help.

    Cheers
    Barry

     

     

     

     

    • Rod_p1's avatar
      Rod_p1
      Level 6
      6 years ago
      Barry allow me to be very generic for support purposes as I do not currently have access to test the scenario nor the time to dedicate to the research of validation of the queries.

      I apologize for the lack of graphics as I am doing this reply via a phone.

      QUESTIONS:

      How do I find out what each person's permissions are for a specific path?

      Rod> Were that simply your desire there is a canned report for entitlements where you can specify a list of paths and the level + depth of permissions desired. In DQL you do need to gather the path and request the permissions associated. As your desire is a single query, I had suggested returning all of them associated with a much smaller dataset per query, else you would return far too much data creating very large databases.

      How do I structure the DQL (and how to go about additional SQL processing) to give me "path-user-perms" and "path-perms" (two separate CSV files with "path.id" the join between both) from the three DQL objects of [path], [permitted_user] and [permissions]?

      Rod> This question is slightly more complicated in that you need to specify just what you are requesting to get from where. I believe you have used viewname.column to pull the info which is how your results are being segregated into multiple databases. To create the report you desire in the mode of export you choose you will have to combine the resulting databases from the DQL query into a single source that you can use to extract SQL query matching results for inclusion in your output. There is a button at the top of the query tab when creating or editing a DQL report that uses direct SQL commands (Advanced query).

      Ultimately your task would be to combine the resultant DQL databases (versus the CSV files) into a single database for querying or into a single database post SQL query that contains only the results for export to your preferred output format like say CSV.

      Veritas does offer professional services who could direct you in terms of narrowing the query to the actual commands required if you choose to involve them.

      Hopefully you find this somewhat helpful. I apologize for any formatting or spelling errors as the mobile version appears somewhat more limited than when access is from a laptop.

      Rod