How can I create a Livereport on permissions?

How can I create a Livereport that will prompt me for a user and then give me a list of folders for which that user has the "Add Item" permission? If this is possible, I can use the solution to expand it to other Livereports that detail folders for which a user has "Delete" permission, "Modify" permission, etc. This would be very useful for cleaning up permissions that may have been loosely defined in the first place.- Bhupinder Singh (bsingh@opentext.com)

Find more posts tagged with

Comments

BSandUser4_(Delete)_567006

You can search from DTreeACL table.

Bhupinder_Singh

I am familiar with the DTREEACL table, but how does one interpret the permission numbers themselves to extract which permissions each number translates into?

BSandUser4_(Delete)_567006

Column ACLType defines the premission type: 0 for ACL, 1-4 for the mode level permissions.Column See defines the permission level: 1 for See, 2 for See Contents, 3 for Modify (includes Add Items) and 4 for Delete...You can also try to join this table with KUAF table to get user info.Hope this helps.

Laurent_Scaon_(MotulAdmin_(Delete)_2225025)

You will find attached a livereport which gives you the list of folders for which a user has the "Add Item" permission?If you have a question or a problem, you can reach me at : lscaon@motul.fr.

Laurent_Scaon_(MotulAdmin_(Delete)_2225025)

You will find attached a document which lists the code "permissions" in the DTreeAcl Table.

Michael_Pate_(aflacadmin_-_(deleted))

Thanks for the spreadsheet!I studied this table for a bit and found that each column in your table adds a value to the total for the permissions. For example, Adding the "Edit Permissions" permission will increase the value of the ACL Code by 16. I posted these values and added them to your spreadsheet.I haven't written one report yet, but thought someone else might find this information useful.Tom Russ

Michael_Pate_(aflacadmin_-_(deleted))

Here's the attachment

Alex_Kowalenko_(akowalen_(Delete)_2285456)

The attached explains the DTreeACL.Permissions bitmap structure and provides a tutotial on how to build a query for a complete list of users who have a specific type of permission on an object.

Sander_van_der_Moolen_(verkeeruser1_-_(deleted))

Hi,Just wondering wether there is someone ou there who has been able to "translate" the report posted by Alex to suit MSSQL. Of course, I could do it myself (which will probably take me some time, if not a lot), but why reinvent the wheel?I'd be most grateful.Greetz,Sander

Robert_Davies_(unlondonadmin_-_(deleted))

Dear SanderThis is an extremely useful website. It is a Microsoft site that helps with migration from Oracle to MS SQL. We work with Oracle. I have used it to go from MS SQL to Oracle even though it is designed as a site to go from Oracle to MS SQL:http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/deploy/upgrdmigrate/oracle.aspHope this is useful to youAnne

Gordon_Maxwell

I have taken the spreadsheet and (hopefully) improved it with the following changes and addition. 1. re-ordered the columns to show which permission is reflected by which bit.2. added a binary numbers column3. Changed the number in the last row to 258,207. (Previously it was 262,143, which presumed "all bits on", i.e. binary 11 1111 1111 1111 1111, but as far as I can tell, bits 6-7 and 9-12 are never on, so the binary number should actually be 11 1111 0000 1001 1111, which is decimal 258,207.)It would probably be more intuitive if the columns for the atomic perms ran from right to left (like the binary numbers), rather than left to right, but that's for another day...

Atiya_Sultana

Is there a LiveReport that can display the results for the folders as in the attached folder hierarchy/structure and against each folder the permissions in terms of read only (if it is see, see contents), write-no delete (if it is see, see contents, edit attributes, add items, reserve, modify), write (if it is see, see contents, edit attributes, add items, reserve, modify, delete permissions) and full access (if it is see, see contents, edit attributes, add items, reserve, modify, delete and edit permissions) are displayed.If there is no Livereport, is it possible to pull this information in the above mentioned/attached format from SQL? Please help me know asap.

Yves_Lieutenant

Hello,are you sure about the permission value for the See Content ?For my point of view, it must be 32768 instead of 36865.by example : in case of permission value 102531 I don't have the right result.regards.

Tim_Hunter

36865 is the proper valuefor 102531 it should be S/SC/M

Yvonne_Knauthe

SAW AG added permissions values that are common for object types other than 'simple documents'.Customer Support verified these. Might come in handy if querying the database.

Appu_Nair1

Where did the attachments go? another case of a good document ad input completely lost in discussions? As a matter of fact I have Alex's document from this discussion with me or in my case I can actually look in builder to figure it out...
@Karen Weir will the original discussions albeit converted comes with its artifacts at some time? or is it similar to the original communities getting retired and all that content was lost that kind of situation or is the algorithm something like the discussion is very old(it happened in 2000) so lets axe things that are old

Karen Weir

Thanks for notifying me @Appu_Nair1 , I'll bring this to the developers to review.

Karen

Appu Nair

Where did the attachments go? another case of a good document and input completely lost in discussions? For what it is worth Alex Kowalenko's post was basically what each BitMap was. All programming languages know how to check for Bits. Here s a simple report in SQLserver that shows how to print out a value Oracle has the same things it is called BitAnd and since you know the Bits to check you can find it using Oracle as well. I hope Programmers reading this probably needs to understand that is the reason why we do this is because OT in Oscript has defined $PCreate as 4 which is 2^2 Alex's Document was a treatise on itself so I cannot quite match that.

select
FLOOR(DTreeACL.Permissions/POWER(2,1))%2 'SEE',
FLOOR(DTreeACL.Permissions/POWER(2,15))%2 'SEE CONTENTS',
FLOOR(DTreeACL.Permissions/POWER(2,16))%2 'Modify',
FLOOR(DTreeACL.Permissions/POWER(2,17))%2 'Edit Attributes',
FLOOR(DTreeACL.Permissions/POWER(2,2))%2 'Add Items',
FLOOR(DTreeACL.Permissions/POWER(2,13))%2 'Reserve',
FLOOR(DTreeACL.Permissions/POWER(2,14))%2 'Delete Versions',
FLOOR(DTreeACL.Permissions/POWER(2,3))%2 'Delete',

from DTreeACL where DataID in (20462573,2118877) /Two DataIds I was interested/
and ACLType=2 /* 1-Owner,2 -Group,3 -PA 4-System,0-Below the ACL*/

In Oscript one would see $PSee as 2

Apparently @Karen Weir posted a message saying that some discussions got the attachments back I do not see this Discussion with the original attachments so in case it helps a future using some of the ways

I leave this for any budding Oscripters out there...It is asimple script that pulls the values
Dynamic unMask=PermMaskToAssoc(241795)
scheduler.debugbreak()

function Assoc PermMaskToAssoc(\
Integer permissions )
Assoc permAssoc
permAssoc.See = ( ( permissions & $PSee ) == $PSee )
permAssoc.SeeContent = ( ( permissions & $PSeeContents ) == $PSeeContents )
permAssoc.Modify = ( ( permissions & $PModify ) == $PModify )
permAssoc.EditAttr = ( ( permissions & $PEditAtts ) == $PEditAtts )
permAssoc.EditPerm = ( ( permissions & $PEditPerms ) == $PEditPerms )
permAssoc.DeleteVer = ( ( permissions & $PDeleteVersions ) == $PDeleteVersions )
permAssoc.Delete = ( ( permissions & $PDelete ) == $PDelete )
permAssoc.Reserve = ( ( permissions & $PCheckout ) == $PCheckout )
permAssoc.Create = ( ( permissions & $PCreateNode ) == $PCreateNode )
permAssoc.AddMajorVersion = ( ( permissions & $PAddMajorVersion ) == $PAddMajorVersion )
return( permAssoc )
end

if you go to LLIAPI Ospace and look at the StartUp script you will find how OT Programmers have done the Enumerations.
or search for this String in Builder $LLIAPI.NodeUtil.fPermToString

Colin_Schmidt

Would this in SQL Server do as well?

case when CAST((dta.permissions & 2) as varchar) = 0 then 'None' else '' end +

case when CAST((dta.permissions & 2) as varchar) = 2 then 'See | ' else '' end +

case when CAST((dta.permissions & 36865) as varchar) = 36865 then 'See Contents | ' else '' end +

case when CAST((dta.permissions & 65536) as varchar) = 65536 then 'Modify | ' else '' end +

case when CAST((dta.permissions & 16) as varchar) = 16 then 'Edit Perms | ' else '' end +

case when CAST((dta.permissions & 131072) as varchar) = 131072 then 'Edit Attr | ' else '' end +

case when CAST((dta.permissions & 4) as varchar) = 4 then 'Add Items | ' else '' end +

case when CAST((dta.permissions & 16384) as varchar) = 16384 then 'Delete Vers | ' else '' end +

case when CAST((dta.permissions & 8) as varchar) = 8 then 'Delete | ' else '' end +

case when CAST((dta.permissions & 8192) as varchar) = 8192 then 'Reserve' else '' end ACL10