cancel
Showing results for 
Search instead for 
Did you mean: 

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

Barry_at_GU
Level 2

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 [PATH ("directory")] has Many [PERMISSIONS]

The DQL I'm using returns three CSV files:

  • path.csv
  • permitted_users.csv
  • permissions.csv

no problem there

But I'm trying to use - as much as possible - repeatable unique identifiers and NOT "path_row".

"path_row" is a runtime unique identifyer but it cannot be used as a repeatable primary/foreign key

A unique value for [PATH] can be either [PATH].id or the [PATH].absname

A unique value for [PERMITTED_USERS] can be [PERMITTED_USERS].sid or [PERMITTED_USERS].principal_name using the [USERS] object.

but data returned from the [PERMISSIONS] object - when retrieved via [PATH] doesn't have a reference back to the [PATH] object (it *does* but only if you say "give me all the paths that have this permission", which is the opposite of what I want to do)

Any suggestions on how I can avoid using "path_row" to join across perms | path | perm_users objects?

Thanks
Barry

/* 
   Examples of target:
    \\NA-TNT-CIFS\groups\INS\KLM\EIS
*/ 

FROM path GET
	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.					*/
	, path.depth				/* (Integer)	Depth of the path from the root of the share		*/
	, path.isdeleted			/* (Integer) 	1 if the path is deleted, 0 otherwise.			*/
	, path.isarchive			/* (Integer)	Returns 1 if directory is archived, 0 otherwise.	*/
	, path.isoffline			/* (Integer)	If directory physically moved to offline storage (=1)	*/
/************************************************************************************************************************/	
	, permitted_users.sid			/* [User] (String) UserID who has permissions to access this path		*/
	, permitted_users.name			/* [User] (String) fullname of user with this permission to access this path	*/
	, permitted_users.principal_name	/* [User] (String) login name of user with this permission to access this path 	*/
/************************************************************************************************************************/	
	, 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")	*/

WHERE 
        path.type = 'DIR'
        /*********** general migration areas ************/
        AND path.device.name = "NA-TNT-CIFS" OR path.device.name = "NB-TNT-GRPS"
        AND path.msu.name = "groups"

        /*********** specific migration target ************/
        AND matchi(path.name, "*KLM/EIS*") = 1

 

 

3 REPLIES 3

Rod_p1
Level 6
Employee Accredited Certified

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

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
Level 6
Employee Accredited Certified
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