• Replies 10 replies
  • Subscribers 338 subscribers
  • Views 137 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

 

  • $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

  • Former Member
    Former Member $organization

    Good day, Beth …

     

    I know the pain you described when building a complex list of security rights, etc.

    It’s something I started but haven’t had time to review and update.

     

    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...

    If you can, would you send me your copy?

    We started Tessitura this past July and we’re just now trying to incorporate control & security groups in our organization.

     

    Many Thanks

    Wendell Baskin

    Bass Hall – Fort Worth

    wbaskin@basshall.com

     

     

     

     

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Beth Gilliland
    Sent: Thursday, September 12, 2013 13:30
    To: Wendell Baskin
    Subject: Re: [Tessitura Technical Forum] SECURITY SET UP - SECURITY OBJECT WORKSHEET

     

    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

    From: Charlotte Gross <bounce-charlottegross1066@tessituranetwork.com>
    Sent: 9/12/2013 10:52:49 AM

    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

     




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

  • $organization in reply to Former Member

    Sure thing, Wendell - I'll send you a copy of our "grid".

    This is the SQL we use to pull the data from our system, in case this is helpful for comparison:

    select distinct UG_name, c.name, c.description, c.object_id, c.object_type, adding, deleting, editing, viewing 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

    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
  • 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

  • $organization
    Ah, cross join, you're brilliant Ken - thanks!
  • $organization in reply to Beth Gilliland

    This is fantastic information!  Thank you both!

  • $organization in reply to Beth Gilliland

    Hi Beth, many thanks for this!

    A copy of your grid would be very useful indeed.

    I had been told that the Tessitura community is very helpful, and my first post here has proved that!

    Just have to try to keep up with everything which should happen once we are live!...

    Cheers,

    Charlotte