wiki:Developer/KHIS2/Permissions

Permissions

Permissions are stored in the following tables:

  • access.Account_Rights
  • access.Collection_Rights
  • access.Comm_Rights
  • access.Contect_Rights
  • access.Doc_Rights
  • access.Org_Rights
  • access.Project_Rights
  • access.Resource_Rights
  • access.User_Default_Rights
  • access.User_Rights

3 key scripts are used to handle permissions, etc.:

  • 3.19 followup data bootstrap.sql
  • 3.20 create template users.sql
  • 3.21 UserCreation?.sql

The basic sequence of events is:

  1. Create collections (to hold only users - i.e. @pAllowUser=1, @pAllow…=0)
  2. Create a template org
  3. Create a template contact (belonging to the template org)
  4. Create a template user from the template contact
  5. Link template user to relevant collections
  6. Link template user to account and set account defaults
  7. Set default access rights for template user
  8. Create "real" user from relevant template user

3.19 followup data bootstrap.sql

  • Grant SYSTEM USER full access to their own contact record
    exec contact.spAddUpdAccessRight @pContactID=-1, @pAllowedEntityType='CONTACT', 
       @pAllowedEntityID=-1, @pPermissionLevel=3, @pMyContact=@myContact, @pMyAccount=@myAccount
    
  • Grant everyone read-only access to the SYSTEM USER's contact record
    exec contact.spAddUpdAccessRight @pContactID=-1, @pAllowedEntityType='GLOBAL', 
       @pAllowedEntityID=-1, @pPermissionLevel=2, @pMyContact=@myContact, @pMyAccount=@myAccount
    
  • Grant the SYSTEM USER full access to the SYSTEM ORG
    exec org.spAddUpdAccessRight @pOrgID=-1, @pAllowedEntityType='CONTACT', 
       @pAllowedEntityID=-1, @pPermissionLevel=3, @pMyContact=@myContact, @pMyAccount=@myAccount
    
  • Grant everyone read-only access to the SYSTEM ORG
    exec org.spAddUpdAccessRight @pOrgID=-1, @pAllowedEntityType='GLOBAL', 
       @pAllowedEntityID=-1, @pPermissionLevel=2, @pMyContact=@myContact, @pMyAccount=@myAccount
    
  • Create batch of collections (to hold users only - i.e. @pAllowUser=1, @pAllowXXX=0)
    • Create "All Users" collection (this will be used to grant all users full access to all orgs and contacts)
      print '******** Adding Collection for All Users **********'
      insert into @collectionbucket
      exec collection.spAdd  @pAllowOrg=0, @pAllowProject=0, @pAllowContact=0, @pAllowAccount=0, 
      	@pAllowResource=0, @pAllowUser=1, @pAllowComm=0, @pAllowDoc=0, @pMyContact=@myContact, @pMyAccount=@myAccount
      select @thisCollection=collectionid from @collectionbucket
      delete from @collectionbucket
      
      exec collection.spAddUpdAttribute @pCollectionID=@thisCollection, @pEnumeration='NAME', @pData='All Users', @pMyContact=@myContact, @pMyAccount=@myAccount
      
      set @allUsersCollection = @thisCollection
      
    • Create "KH Managers" collection
    • Create "All KH Users" collection
    • Create "KH Central" collection
    • Create "All Northumbria Users" collection
    • Create "Northumbria Central" collection
    • Create "All Durham Users" collection
    • Create "Durham Central" collection
    • Create "All Sunderland Users" collection
    • Create "Sunderland Central" collection
    • Create "All Teesside Users" collection
    • Create "Teesside Central" collection
    • Create "All Newcastle Users" collection
    • Create "Newcastle Central" collection
  • Give the SYSTEM USER + ORG some names
    exec org.spAddUpdAttribute @pOrgID=-1, @pEnumeration='NAME', @pData='SYSTEM', @pMyContact=@myContact, @pMyAccount=@myAccount
    exec contact.spAddUpdAttribute @pContactID=-1, @pEnumeration='FNAME', @pData='SYSTEM', @pMyContact=@myContact, @pMyAccount=@myAccount
    exec contact.spAddUpdAttribute @pContactID=-1, @pEnumeration='LNAME', @pData='USER', @pMyContact=@myContact, @pMyAccount=@myAccount
    
  • Link the SYSTEM USER to the "KH Managers" and all "…Central" Collections
    exec collection.spLinkCollectionToUser @pCollectionID=@KHManagers, @pContactID=@myContact, @pMyContact=@myContact, @pMyAccount=@myAccount
    exec collection.spLinkCollectionToUser @pCollectionID=@KHCentralCollection, @pContactID=@myContact, @pMyContact=@myContact, @pMyAccount=@myAccount
    exec collection.spLinkCollectionToUser @pCollectionID=@NorthumbriaCentralCollection, @pContactID=@myContact, @pMyContact=@myContact, @pMyAccount=@myAccount
    exec collection.spLinkCollectionToUser @pCollectionID=@DurhamCentralCollection, @pContactID=@myContact, @pMyContact=@myContact, @pMyAccount=@myAccount
    exec collection.spLinkCollectionToUser @pCollectionID=@SunderlandCentralCollection, @pContactID=@myContact, @pMyContact=@myContact, @pMyAccount=@myAccount
    exec collection.spLinkCollectionToUser @pCollectionID=@TeessideCentralCollection, @pContactID=@myContact, @pMyContact=@myContact, @pMyAccount=@myAccount
    exec collection.spLinkCollectionToUser @pCollectionID=@NewcastleCentralCollection, @pContactID=@myContact, @pMyContact=@myContact, @pMyAccount=@myAccount
    
  • Link the SYSTEM USER to an account otherwise they will not be able to log in
    exec [user].spLinkUserToAccount @pContactID=@myContact, @pAccountID=@myAccount, @pMyContact=@myContact, @pMyAccount=@myAccount
    
  • … and set some attributes
    exec [user].spAddUpdAttribute @pContactID=@myContact, @pEnumeration='ACCOUNT_DEFAULT', @pData=1, @pMyContact=@myContact, @pMyAccount=@myAccount
    exec [user].spAddUpdAttribute @pContactID=@myContact, @pEnumeration='SECURITY_ROLE,' @pData='DEFAULT,IS_KHCENTRAL,IS_KHMANAGER,CAN_ALL_READ_RESPONSES,SYSTEM_USER', @pMyContact=@myContact, @pMyAccount=@myAccount
    
  • Finally, set default access rights for the SYSTEM USER on all 9 entities

3.20 create template users.sql

New users are created from pre-defined "template users" by passing a templateContactID into [user].spMakeUser; this script creates those template users!

  • As a contact must belong to an org, first we need to create a template org
    print '******** Adding Template org **********'
    insert into @orgbucket
    exec org.spAdd @pParentOrgID=null,@pMyContact=@myContact,@pMyAccount=@myAccount
    select @thisOrg=orgid from @orgbucket
    delete from @orgbucket
    
    exec org.spAddUpdAttribute @pOrgID=@thisOrg, @pEnumeration='NAME', @pData='Template Organisation', @pMyContact=@myContact,@pMyAccount=@myAccount
    exec org.spAddUpdAttribute @pOrgID=@thisOrg, @pEnumeration='ADDRESS1', @pData='Do Not Use.', @pMyContact=@myContact,@pMyAccount=@myAccount
    exec org.spAddUpdAttribute @pOrgID=@thisOrg, @pEnumeration='ADDRESS2', @pData='Ever.', @pMyContact=@myContact,@pMyAccount=@myAccount
    
  1. Create a new "KH Central" template user
    print '******** KHCentral **********'
    insert into @contactbucket
    exec contact.spAdd  @pOrgID=@templateOrg, @pMyContact=@myContact, @pMyAccount=@myAccount
    select @KHCentraltemplateContact=contactid from @contactbucket
    delete from @contactbucket
       
    select @KHCentraltemplateContact as something
    exec contact.spAddUpdAttribute @pContactID=@KHCentralTemplateContact, @pEnumeration='FNAME', @pData='KHCentral', @pMyContact=@myContact,@pMyAccount=@myAccount
    exec contact.spAddUpdAttribute @pContactID=@KHCentralTemplateContact, @pEnumeration='LNAME', @pData='TEAM', @pMyContact=@myContact,@pMyAccount=@myAccount
    
  2. Promote that contact to a user
    exec [user].spMakeUser @pContactID=@KHCentraltemplateContact, @pTemplateContactID=@baseTemplateContact, @pLogin='KHCentral User', @pPassword='ph7frEkade', @pMyContact=@myContact,@pMyAccount=@myAccount
    
  3. Link the template user to the relvant collections
    exec collection.spLinkCollectionToUser @pCollectionID=@allUsersCollection, @pContactID=@KHCentraltemplateContact, @pMyContact=@myContact,@pMyAccount=@myAccount
    exec collection.spLinkCollectionToUser @pCollectionID=@KHManagers, @pContactID=@KHCentraltemplateContact, @pMyContact=@myContact,@pMyAccount=@myAccount
    exec collection.spLinkCollectionToUser @pCollectionID=@KHCentralCollection, @pContactID=@KHCentraltemplateContact, @pMyContact=@myContact,@pMyAccount=@myAccount
    exec collection.spLinkCollectionToUser @pCollectionID=@NorthumbriaCentralCollection, @pContactID=@KHCentraltemplateContact, @pMyContact=@myContact,@pMyAccount=@myAccount
    exec collection.spLinkCollectionToUser @pCollectionID=@DurhamCentralCollection, @pContactID=@KHCentraltemplateContact, @pMyContact=@myContact,@pMyAccount=@myAccount
    exec collection.spLinkCollectionToUser @pCollectionID=@SunderlandCentralCollection, @pContactID=@KHCentraltemplateContact, @pMyContact=@myContact,@pMyAccount=@myAccount
    exec collection.spLinkCollectionToUser @pCollectionID=@NewcastleCentralCollection, @pContactID=@KHCentraltemplateContact, @pMyContact=@myContact,@pMyAccount=@myAccount
    exec collection.spLinkCollectionToUser @pCollectionID=@TeessideCentralCollection, @pContactID=@KHCentraltemplateContact, @pMyContact=@myContact,@pMyAccount=@myAccount
    
  4. Link the template user to the relevant account and set account defaults
    exec [user].spAddUpdAttribute @pContactID=@KHCentralTemplateContact, @pEnumeration='ACCOUNT_DEFAULT', @pData=@khaccount, @pMyContact=@myContact, @pMyAccount=@myAccount
    exec [user].spAddUpdAttribute @pContactID=@KHCentralTemplateContact, @pEnumeration='SECURITY_ROLE',  @pData='DEFAULT,IS_KHCENTRAL,IS_KHMANAGER,CAN_ALL_READ_RESPONSES', @pMyContact=@myContact,@pMyAccount=@myAccount
    exec [user].spLinkUserToAccount @pContactID=@KHCentralTemplateContact, @pAccountID=@northumbriaAccount, @pMyContact=@myContact,@pMyAccount=@myAccount
    
  5. Set default access rights for the template user on all 9 entities
  • Repeat steps 1-5 (above) for the following collections:
    • All Northumbria Users
    • Northumbria Central
    • All Durham Users
    • Durham Central
    • All Sunderland Users
    • Sunderland Central
    • All Teesside Users
    • Teesside Central
    • All Newcastle Users
    • Newcastle Central

3.21 UserCreation?.sql

This script creates user accounts for "real" users, by passing the relevant templateContactID into [user].spMakeUser - e.g.

select @contactID=contactid from data.contact_attributes oa
	inner join descr.contact_attribute_types oat on oat.contactattributetypeid = oa.contactattributetypeid
	where oat.enumeration = 'LEGACY_USER_ID' and oa.data='2930'

exec [user].spmakeuser @pContactID=@contactID, @pLogin='lrayne', @pPassword='B8A4ruwr5B', @pTemplateContactID=@KHCentralTemplateContact, @pMyContact=@myContact,@pMyAccount=@myAccount

NOTE This is where the access.user_default_access_rights table comes into play. If a templateContactID other than 0 is passed into [user].spMakeUser, the default rights assigned to that templateContactID (as stored in the access.user_default_access_rights) are copied to the new user.

Last modified 2 years ago Last modified on 17 Sep 2015 10:06:59