05-06-2019 08:55 PM
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:
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
05-08-2019 06:59 PM
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
05-14-2019 07:14 PM
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
05-20-2019 11:18 AM