Create Generic Security Schema

Organizing your data and objects into functional building blocks is essential for proper data management and security. We accomplish this through schemas, roles, and users.

The basic security model we are implementing has three (3) major prongs:

1. Data should be organized according to its 'owner,' which corresponds to the 'business unit,' 'department,' or 'organizational vertical.'

   1. For instance, data owned by the Water group should be owned by a corresponding database user named 'water' and exist within the 'water' schema. A database connection string for a water dataset would appear as enowa-sgis-db.water.someFeatureClass.

2. Role-based security should be applied to leverage rule-based security policies rather than individual/explicit grants.

   1. Role-based security should exist to generally organize users by their job & role. For instance, if the transmission group (tso) hires a GIS analyst, that analyst should have permissions applied for them based on the predefined permissions that the GIS analyst is 'supposed to' have.

3. Administrative, advanced, and functional activities should be isolated to the fullest extent possible and require users to change accounts to conduct different types of activities.

   1. As an example, consider the 'water' user referenced earlier. This user can read, query, create, delete and update objects, while 'typical' users can only read, query and, possibly, update objects. When creating new objects, the schema owner account should be used; however, when making edits to records or conducting regular business operations, users should use their individual accounts.

In addition, it is important to note that the GIS is only approved for Confidential and Public data. Data categorized as Secret/Top Secret and classified should not be present in the geodatabase as this would consistute a violation of the KSA regulations related to cloud providers. NourNet, our current cloud provider, was downgraded to class 'B' in March 2023, which introduced this limitation.

The basic security model is defined below:

• Data Owners/Schemas:

  • water

  • enowa

  • neom

  • dso

  • tso

  • hydrogen

  • demo

  • staging

  • geoadmin

  • sde

    • Note: this account owns the geodatabase system tables and should not own data

• Database Roles:

  • Viewer

    • Permissions: Connect, View Definition, View Database State, Select, Query (CVVdsSQ)

  • Editor

    • Permissions: CVVdsSQ, Update/Insert, Execute (CVVdsSQUE)

  • Writer (Schema Owners)

    • Permissions: CVVdsSQUE, Create Table, Create Procedure, Create View, Control, Delete

  • Domain Editors (CVVdsSQUE filtered by schema)

    • waterEditor

    • neomEditor

    • enowaEditor

    • dsoEditor

    • tsoEditor

    • hydrogrenEditor

    • demoEditor

    • GeoAdmin

As aforementioned, the 'sde' account is the geodatabase administrator account and is responsible for managing versions, the states, system tables, and general administrative tasks. It should not be used to own or edit data.