Request help for a report on permissions.

General_Dynamics_C4_Systems

Hello,Does anyone know how or if I can generate a report showing me the users that have edit permissions authority?THANK YOU!

Alex_Kowalenko_(akowalen_(Delete)_2285456)

Here is the answer I gave in earlier email correspondence to one of my students. I am posting it here for the benefit of others.The question whether users have certain permissions is relative to the object that these permissions apply to. So, to ask for a list of all users with "Edit Permissions" (EP) authority, you have to complete the question by asking "EP on what object?".Assuming that you know the object in question, then, to answer your question, you have to filter the query on the Permissions column of the access control list of this object. Now, the DTreeACL.Permissions column is a bitmask code. According to the bit code, bit 4 is "on" if EP is set. However, there is no function in Oracle or MS SQL to directly access the value of a bit so we have to use some binary arithmetic. If you divide a bitmask by 2 to the power of the bit to be tested, then the last bit is the one you want. If it's "on", the result is an odd number, otherwise it is even. In our case, 2 to the power of 4 is 16.Let me illustrate a solution in the following examples (which assume an Oracle RDMS). The following query will find RightID's of those with EP on the object with DataID = 2000 (usually the Enterprise Workspace volume).Query 1:--------Select RightIDFrom DTreeACLWhere Mod( Floor( Permissions / 16 ), 2 ) = 1 /* Bit 4 on */ And DataID = 2000 /* Enterprise Workspace */The RightID list from this query includes all user and group ID's in the access control list that have EP on Enterprise Workspace. But you want users, whether they are in this list or are descendents of the groups in this list. By "descendents" I mean children, grandchildren, etc.For descendents we need a hierarchical query on KUAFChildren:Query 2: (Oracle Only)----------------------Select ChildIDFrom KUAFChildrenStart With ID in (Query 1) Connect By Prior ChildID = IDThe union of Query 1 and Query 2 includes all users and groups with EP on Enterprise Workspace:Query 3:--------(Query 1)Union(Query 2)To get your answer we have to filter this list by user type and those users that are not deleted. The following query also adds user names: Query 4:--------Select Name, IDFrom KUAFWhere ID in (Query 3) And Deleted = 0 /* Not Deleted */ And Type = 0 /* User type */To be complete, you should also test for the special condition where Public Access may have EP. A query for this, with the same selection columns as Query 4 is:Query 5:--------Select '*Public*' Name, RightID IDFrom DTreeACLWhere Mod( Floor( Permissions / 16 ), 2 ) = 1 /* Bit 4 on */ And DataID = 2000 /* Enterprise Workspace */ And RightID = -1 /* Public Access */The full query, sorted by the first column, then is:Query 6:--------(Query 4)Union(Query 5)Order By 1So, to expand Query 6, the full query to answer your question on which users have EP for the Enterprise Workspace is:SQL: (Oracle Only)------------------(Select Name, ID From KUAF Where ID in ( (Select RightID From DTreeACL Where Mod( Floor( Permissions / 16 ), 2 ) = 1 /* Bit 4 on */ And DataID = 2000 /* Enterprise Workspace */ ) Union (Select ChildID From KUAFChildren Start With ID in (Select RightID From DTreeACL Where Mod( Floor( Permissions / 16 ), 2 ) = 1 /* Bit 4 on */ And DataID = 2000 /* Enterprise Workspace */ ) Connect By Prior ChildID = ID ) ) And Deleted = 0 /* Not Deleted */ And Type = 0 /* User type */)Union(Select '*Public*' Name, RightID ID From DTreeACL Where Mod( Floor( Permissions / 16 ), 2 ) = 1 /* Bit 4 on */ And DataID = 2000 /* Enterprise Workspace */ And RightID = -1 /* Public Access */)Order By 1I created a LiveReport with this query and have attached it as an export to this reply. Note that this LiveReport asks to select a container which is used as the object of EP.