There are 4 tables used by Aeries Web Version to implement security: UGN, UGA, UGP and USR.


UGN – User/Group Names

  • UID – User ID – int - Primary Key
    • Values in this field are created by the system as one more than the current highest number already in the system.  Aeries has special logic to handle concurrency issues with the assignment of new numbers.
  • UN – User/Group Name – varchar(255)
  • SY – System – varchar(25)
    • Stores the word “AERIES.NET” to indicate that these accounts are valid for Aeries Web.  This table is designed to be used by other Aeries systems in the future.
  • TY – Type of Account – smallint
    • 1 : User   -or-   2 : Group
  • UTY – User Type – varchar(255)
    • This field distinguishes between normal users, groups, and administrators.  Valid values are: "group", "user", "admin", "teacher", "teachersub", "aduser", "adadmin", "adteacher", and "adteachersub".
  • HT – Hash Type for the password - smallint
    • -1 : Clear Text
    • 0 : MD5
    • 1 : SHA1
    • 2 : SHA256
    • 3 : SHA384
    • 4 : SHA512
    • 6 : HMACSHA256 (the Aeries system default)
  • PW – Password – varchar(255)
    • If importing data into the UGN table, you can use -1 for the HT field and enter the imported password into the PW field in Clear Text.  Using "welcome" or "changeme" in the password field will require the user to change their password on first login.
  • CD – Creation Date – datetime
    • This field has a default value of the current Date/Time.
  • XD – Expiration Date – datetime
    • A user will not be permitted to login if the account expiration date has passed. A populated date will allow access until midnight.
  • ST – Account Status – tinyint
    • 0 : Active (default value) – Accounts must have an Active Status in order to login.
    • 1 : Locked
    • 2 : Disabled
  • LDT – Last Login Date/Time – datetime
    • The system logs when this user last logged in previous to the current login.
  • LC – Login Count – int
    • The system keeps a count of how many times a user logs on to the system.
  • IP – Current Login IP Address – varchar(25)
    • The IP Address of the workstation that was last used to login to the system
  • EM – Email Address – varchar(100)
    • All user accounts are required to have an email address in order to run reports.
  • FN – First Name – varchar(50)
  • LN – Last Name – varchar(50)
  • CM – Comments – nvarchar(max)
  • LIP – Last Login IP Address – varchar(25)
    • The IP Address from which this user last logged in previous to the current login.
  • CDT – Current Login Date/Time – datetime
    • The date and time this user last logged in.
  • LPR – Last Page Request Date/Time – datetime
    • The system logs when this user last requested a page in the system.  This is used to determine how many users are actively interacting with the system.
  • ID – Student ID – int
    • This is not currently used, but might be used at a later date to support student and parent accounts in the UGN table.
  • SID – Staff ID – int
    • Related to the STF table, the Aeries District Staff ID for the current account.  This is a required field for all teacher accounts and optional (although recommended) for all accounts.
  • PLC – Password Last Changed – datetime
    • Records the date and time the password for this account was last changed.
  • SLT – Password Salt – nvarchar(255)
    • Used to store the salt for the hashed passwords. 
  • IDP – Identity Provider – smallint
    • This is used to support alternative authentication methods in addition to storing passwords in Aeries. Currently, the valid values are
      • 0: Aeries
      • 1: Google
  • CPW – Must change password – bit
    • If true (1), the user’s password must be changed on the next login. This can be set by an administrator either upon account creation or after resetting a user’s password.

  

UGA – User/Group Associations

  • UID – User ID – int
    • The user that is a member of the group. Can also be another group that is inheriting from the group. Related to the UGN.UID field
  • GID – Group ID – int
    • The group. Related to the UGN.UID field
  • IID – NOT USED


UGP – User/Group Permissions

  • SC – School Code – smallint
    • For Portal Groups, permissions are specific to a school. For all other groups, the school code will always be 0.
  • UID – User/Group ID – int
    • Related to the UGN.UID field for both users and groups
  • TC – Table Code/Security Area – varchar(255)
  • FC – Field Code – varchar(255)
    • Only certain tables such as STU, CON, and STF currently support field-level permissions
  • RD – Read Permissions – tinyint
    • 0 : Not set
    • 1 : Allow
    • 2 : Deny
  • UP – Update Permissions – tinyint
    • Same values as above
  • INS – Insert Permissions – tinyint
    • Same values as above
  • DL – Delete Permissions – tinyint
    • Same values as above
  • MU – Mass Update Permissions – tinyint
    • Same values as above
    • This is only used for certain security areas
  • AD – Administer Permissions – tinyint
    • Same values as above
    • This is only used for certain security areas
    • Normally denotes "Admin" level security to a specific area of the system.
  • YR – Year – tinyint
    • The relative school year to which the permission applies
    • 0: Current Year
    • 1: Last Year
    • 2: Before Last Year
  • UXD – Update Expiration Date – datetime
    • Can be used to temporarily elevate permissions. After the expiration date passes, only Read permissions will apply to this security area for the user or group.


USR – User School Access Permissions

  • NM – Username – varchar(255)
    • Related to the UGN.UN field for Aeries Web users and to SQL users used by Aeries Client.
    • All regular users in the UGN table need at least 1 record in USR.  Teachers do not need USR records as their Staff ID is used to link to TCH and grant access to schools.  Admins also do not need records in USR as they have full access to the entire system.
  • SCH – School Code – smallint
    • The Aeries School Code.
  • RO – Read Only Flag – bit
    • Indicates that although the current user has permission to access this school site, all permissions are to be set to read-only.  This allows users to have full access to certain schools and have read-only access to other schools or even to the district level (school 0).
  • CG – Communication Group Flag – bit
    • For districts using Aeries Communications, the Communication Group field will soon be used by the system to manage which users are included in school-level groups. This flag defaults to True (1) when a School Access is selected for a user.
    • For customers not using Aeries Communications, the Communication Group flag will still be set to 1 for all users with school access; however, the flag will have no functionality.


Relationships

UGN is related to UGA is 2 ways. 

The first relationship is between the UGN.UID and UGA.UID fields.  This is a 1:Many relationship.

The second relationship is between the UGN.UID and UGA.GID fields and is used to indicate which group the user is a member of.  This is a 1:Many relationship.

UGN is also related to UGP using the UGN.UID and UGP.UID fields.  This is a 1:Many relationship.