• Replies 10 replies
  • Subscribers 338 subscribers
  • Views 139 views
  • Users 0 members are here
Related

SECURITY SET UP - SECURITY OBJECT WORKSHEET

Charlotte Gross
$organization

Hello,

This is the first of many posts I am sure...

We are in the midst of implementing Tessitura here at Scottish Ballet and have seen the wonderful security object worksheet that was done by Pittsburgh Cultural Trust for V4. Basically, it is a list of all the objects that need to be considered for security in order to decide which user group should have access to what.

I was wondering if anyone would have an updated version of this? This would really help us going through this process quicker!

Many thanks,

Charlotte

 

Parents
  • $organization

    Hi Charlotte!

    Do you mean just a listing of objects with descriptions? You can find them in the online help docs here:

    http://www.tessituranetwork.com/Help_System/Content/Security/Table%20of%20Security%20Objects.htm

    We also have a quick SQL script that pulls a spreadsheet-type of list of which of our usergroups have what and the description of each. If you'd like a copy of that, let me know...

    I remember spending DAYS figuring this out when we first started. Essentially we just went with best guesses and then tweaked as we've gone along. Best of luck!

    Beth

  • $organization in reply to Beth Gilliland

    Beth, I'd love a copy of the SQL script as well if you wouldn't mind!  We're long time users, but our security user groups and constituencies are in need of a major clean up.  My head is about to explode as I figure out the scope of this clean up, but having that type of list would help tremendously!

    Thank you!

    Marley

  • $organization in reply to Marley Wynne

    Marley-

    Sure! I think I posted it earlier, but here it is again (I've gone back to clean it up a bit, and also add constituencies, in case you also dole out permissions based on those..


    select distinct b.UG_name, c.name, c.description, c.object_id, c.object_type, ISNULL(a.adding,'') as 'adding', ISNULL(a.deleting,'') as 'deleting', ISNULL(a.editing,'') as 'editing', ISNULL(a.viewing,'') as 'viewing',

    ISNULL(d.short_desc,'') as 'constituency'

    from TX_SECURITY_RIGHTS a

    join t_metusergroup b on a.ug_id=b.ug_id

    join t_app_objects c on a.object_id=c.object_id

    left outer join tr_constituency d on a.constituency=d.id

    order by b.UG_name, c.object_id, constituency, c.name, c.object_type, adding, deleting, editing, viewing, c.description

    You can also sort the results by object instead of user to get a sense of who has access to a particular object instead of an entire group's permissions.
    I should also mention (although you probably already know this part) that this is all made easier if all the user groups already have an entry established in TX_SECURITY_RIGHTS for every security object. Otherwise, they won't show up in this listing. I know we have a few holes here and there - you might find the same.
    Hope that helps!
    Beth
Reply
  • $organization in reply to Marley Wynne

    Marley-

    Sure! I think I posted it earlier, but here it is again (I've gone back to clean it up a bit, and also add constituencies, in case you also dole out permissions based on those..


    select distinct b.UG_name, c.name, c.description, c.object_id, c.object_type, ISNULL(a.adding,'') as 'adding', ISNULL(a.deleting,'') as 'deleting', ISNULL(a.editing,'') as 'editing', ISNULL(a.viewing,'') as 'viewing',

    ISNULL(d.short_desc,'') as 'constituency'

    from TX_SECURITY_RIGHTS a

    join t_metusergroup b on a.ug_id=b.ug_id

    join t_app_objects c on a.object_id=c.object_id

    left outer join tr_constituency d on a.constituency=d.id

    order by b.UG_name, c.object_id, constituency, c.name, c.object_type, adding, deleting, editing, viewing, c.description

    You can also sort the results by object instead of user to get a sense of who has access to a particular object instead of an entire group's permissions.
    I should also mention (although you probably already know this part) that this is all made easier if all the user groups already have an entry established in TX_SECURITY_RIGHTS for every security object. Otherwise, they won't show up in this listing. I know we have a few holes here and there - you might find the same.
    Hope that helps!
    Beth
Children
  • Former Member
    Former Member $organization in reply to Beth Gilliland

    Hi all

    Just playing with that script....

    If you do it like this, it will return all possible combinations of UG and object, so you can see which ones aren't there, as well as which ones are..

    -------------------------------------------

    select ug.UG_id

    , ug.UG_name

    , ao.object_id object_id

    , ao.name object

    , ao.object_type

    , isnull(sr.id, 0) rights_id

    , ISNULL(sr.adding,'') as 'adding'

    , ISNULL(sr.deleting,'') as 'deleting'

    , ISNULL(sr.editing,'') as 'editing'

    , ISNULL(sr.viewing,'') as 'viewing'

    ,ISNULL(d.short_desc,'') as 'constituency'

    from T_METUSERGROUP ug

    cross join T_APP_OBJECTS ao

    left outer join TX_SECURITY_RIGHTS sr on sr.UG_id = ug.UG_id and sr.object_id = ao.object_id

    left outer join tr_constituency d on sr.constituency=d.id

    order by  ug.UG_name , ao.name

    --------------------------------------------------------------

    Ken