Database Security

Oracle, PostGIS and SQL Server all have built-in security which prevents unauthorised users from accessing data.

To read data from the database users should supply the correct user name and password.

When creating new overlays in Cadcorp SIS Desktop if an attempt is made to base the overlay on data from a database Cadcorp SIS Desktop will prompt the user for security information, if the authentication process is successful the user will be presented with a list of tables to choose from. SIS Workspace Definition (SWD) files containing data based on a database table store the security information, this allows the SWD to be opened without prompting the user for security information.

The security must be set up by the database administrator before spatial data is migrated to the database. 

Cadcorp SIS Desktop authenticates users as having either view only or editable access to data stored in a database. Cadcorp SIS Desktop applies the appropriate status to the overlay based on the authentication results. The database administrator can adjust the authentication level for users by modifying the access privileges to the database table for the given security account.

Note: A database user name is only required to add or export an overlay based on a spatial database table. Database user names are stored in the SWD when it is saved. It then becomes important to ensure that windows file security is used to protect unauthorised users from accessing the SWD containing these encoded user names and passwords.

The GIS officer and the database manager should discuss the type of security model they want to enforce in their organisation. The following are examples of common approaches used:

1. Each user who needs data from the database is given their own security account on the database.
   
   
2. A security account on the database is shared by a number of people in a given department. Each person in the department uses the same security account to gain access to the database.
   
   
3. Users are not given a security account, they open ready made SWDs which have been set up to give the user the appropriate level of access.

Database Security Model

Cadcorp recommend the following databases for use with SIS Desktop 9 for managing spatial data:

1. Oracle (10g release2, 11g release1 or 2)
   
2. SQL Server 2008
   
3. PostgreSQL/PostGIS (8.4.1/1.4.0)

The database should be installed on a server that is dedicated to the purpose of running the database. There should be no other services running on the server including using the server as a map server for storing raster data etc.

Security Model

Database accounts should be split into those that own data and those that are used to manage data access:

Data Holding Accounts

Users should create one or more data holding accounts in their database.

These accounts own the data and should only be used by the GIS Manager to manage the data.

Cadcorp recommend that only the GIS Manager be responsible for creating spatial layers in the database. Users who need to upload data to the database should forward the data to the GIS Manager as a Cadcorp BDS file.

The GIS Manager can check the data quality before uploading the data into the database. This mechanism prevents users from unnecessarily uploading data to the database which can lead to data duplication and other issues.

For example a user called gisadmin can be created to own all the GIS data. The GIS Manager would use this account when exporting data from SIS Desktop 9 to the database.

Data Access Account

To manage data access requirements, users can create as many accounts as needed.

Generally a minimum of two accounts is required per department.

For example the planning department could operate using one account that has read-only access to the appropriate spatial layers and one account that has edit access to the appropriate spatial layers.

Multiple end-users can use the same database account to access the data, e.g. user "a" and user "b" both require read-only access to the planning layers. They can both log onto the database and create overlays using the same database account.

In practice they will use a SWD that has overlays in it that were created using the same read only planning account. User "c" requires editable access to the planning layers, a second SWD can be created where the spatial layers are created using an account that has edit access to the data.

If there is a need for user "c" to connect to the database the database account with edit access to the planning data can be used.

Separate data access accounts should be created for each identifiable set of data access requirement. Departmental user accounts can be created where departments do not want to share data.

Each department database account only has access to the department’s data. These can be further split down to accounts that have read or read/write access to spatial layers. There may be a need to split this down further for sensitive data.

Pre-Created SWDs

SIS Desktop 9 can be deployed throughout an organisation in such a way end users need never manually log onto the database.

In this scenario the GIS Manager creates projects for the users. Each project contains the spatial layers that an end user requires to fulfil their job role.

A project can be for one or many people. The database administrator adds the appropriate spatial layers to the project and supplies the correct password to access the database.

Once the spatial layers have been added to the project the database passwords are encrypted into the project.

When the project is opened by the end user the project automatically checks authentication and creates the appropriate connections to the database to retrieve data.

This approach requires very few database accounts and may suit organisations who do not have a database administrator.

TIP: To know more about this feature, contact Cadcorp Technical Services.

User Managed SWDs

Cadcorp SIS Desktop can be deployed such that end users are responsible for managing their own projects. In this scenario end users will add the spatial layers they require to the project. The database needs to be set up so that the database account being used by the end users have the appropriate level of access to the required data.

When connecting to a database to retrieve data the user will need to supply the password. Once the spatial layer has been added to the project and saved the user will not be challenged for the password the next time the project is opened.

Database Roles

Oracle, SQL Server and PostGIS all support database roles. Cadcorp SIS Desktop will respect data access privileges granted to database accounts via database roles. Generally, it is good practice to create a role for each set of database privileges. The role can then be assigned to one or more database accounts as required.

Examples of database security models

Organisations that have a very low number of end users can get by with minimal database accounts in some cases a single database account may suffice.

SWDs can be created by the GIS Manager which contain the required spatial layers for a given user. End users are given access to the appropriate SWD via windows folder permissions.

If the end-users require editable access to the data a single database account can be used to build the various SWDs. If the user groups can be broken down into editors and viewers it may be possible to get by with only two database accounts, one that has editable access to the data and one that has view only access to the data.

Departmental security

If there is no sharing of data in a multi-departmental organisation, database accounts should be created for each department.

Each department will probably require two accounts, one with edit access to all the departmental layers and one with view only access to all the department layers.

If everyone in a department is allowed to edit/view any spatial layer then a single account may suffice. If some users in a department are not allowed to access some of the data, additional user accounts need to be created with the appropriate level of access.

Individual accounts

Each physical user in the organisation can have their own database account. This approach is particularly useful if auditing (Cadcorp archive Manager) is switched on for a particular layer. Managers can get a detailed report of who has edited, created or deleted data.

Tips

• Keep the number of accounts to a minimum.
  
• SWDs should be created using a single database account.
  
• SWDs managed by the GIS Manager do not require the end user to log onto the database (the password for an account does not need to be given to them).

SQL Server 2008 security model.

Create a database account that will own the data and map this account to dbo schema.

e.g.:

Create a user called gismanager1:

USE [master]
GO
CREATE LOGIN [gismanager1]
WITH PASSWORD=N 'spatial',
DEFAULT_DATABASE=[security model]
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
GO
USE[security model]
GO
CREATEUSER [gismanager1] FOR LOGIN [gismanager1]
GO

Grant the following privileges to the gismanager1 account:

USE [security model]
GO
GRANT CREATE TABLE TO[gismanager1];
GRANT CONNECT TO [gismanager1];
GRANT CONTROL ON SCHEMA::[dbo] TO [gismanager1]
GO

Export a spatial layer from SIS Desktop 9 to the database using the gismanager1 account, in the following example the layer is called ‘SQUARE030810’.

Note: A spatial layer is synonymous to table. A table called: ‘SQUARE030810’ will exist in the database.

Create a user that will have view only access to the layer called ‘SQUARE030810’

Note: This user should not be given the ‘CONTROL’ or CREATE TABLE’ privileges. 

USE [master]
GO
CREATE LOGIN [gisviewer1]
WITH PASSWORD=N 'spatial',
DEFAULT_DATABASE=[security model]
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
GO
USE[security model]
GO
CREATEUSER [gisviewer1] FOR LOGIN [gisviewer1]
GO
USE [security model]
GO
GRANT select onSQUARE030810 TO [gisviewer1];
GO

Create a user that will have editable access to the layer called ‘SQUARE030810’

Note: This user should not be given the ‘CONTROL’ or CREATE TABLE’ privileges. 

USE [master]
GO
CREATE LOGIN [gisviewer2]
WITH PASSWORD=N 'spatial',
DEFAULT_DATABASE=[security model]
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
GO
USE[security model]
GO
CREATEUSER [gisviewer2] FOR LOGIN [gisviewer2]
GO
USE [security model]
GO
GRANT select,insert,update,delete onSQUARE030810 TO [gisviewer2];
GO

Security can be managed using database roles:

Create some data in the 'gismanager' account. 

Export a layer called: parks
Export a layer called: planning

Create two user accounts that will be given different level of access to the data:

Create user gis_parks_edit
Create user gis_plan_view

Create two database roles and assign them to each user:

Create role rl_parks_edit - this role will be given editable access to the parks layer
Create role rl_plan_view - this role will be given view only access to the plan layer

Map the user to the appropriate role:

EXEC sp_addrolemember N'rl_parks_edit', N'gis_parks_edit'
EXEC sp_addrolemember N'rl_plan_view', N'gis_plan_view'

 Grant the appropriate privileges on the layer to the roles:

grant select,insert,update,delete on gisdata.parks to rl_parks_edit
grant select on gisdata.planning to rl_plan_view

When a connection is made from Cadcorp SIS Desktop to SQL Server and the user account gis_parks_edit is used Cadcorp SIS Desktop will only list the parks layer – when added to Cadcorp SIS Desktop the layer will be editable.

If the connection is made using the gis_plan_view account Cadcorp SIS Desktop will only list planning layer. When added to Cadcorp SIS Desktop the layer will be view only.

PostgreSQL/PostGIS security model

Create a user account in PostGIS that will own the data:

CREATE ROLE gisadmin LOGIN ENCRYPTED PASSWORD '123' VALID UNTIL 'infinity';

GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE geometry_columns TO gisadmin;
GRANT ALL ON TABLE spatial_ref_sys TO gisadmin;

Export a layer from SIS Desktop 9 into PostGIS connecting to the database using account gisadmin.

Create two users and two database roles in PostGIS. Map the users to the roles:

CREATE ROLE rl_plan_viewer VALID UNTIL 'infinity';
CREATE ROLE rl_plan_editor VALID UNTIL 'infinity';

GRANT rl_plan_editor TO plan_edit;
GRANT rl_plan_viewer TO plan_view;

Grant select access on the planning layer to user plan_view:

GRANT select ON TABLE planning TO GROUP rl_plan_viewer;

Grant edit access on the planning layer to user plan_edit:

GRANT select,insert,update,delete ON TABLE planning TO GROUP rl_plan_editor;

When a connection is made from SIS Desktop 9 to PostGIS and the user account gis_view is used SIS Desktop 9 will only list the planning layer – when added to SIS Desktop 9 the layer will be view only.

If the connection is made using the plan_edit account SIS Desktop 9 will only list planning layer. When added to SIS Desktop 9 the layer will be editable.

Granting editable access to PostGIS layer not owned by the current user

To allow users to edit an overlay created from a PostgreSQL/PostGIS spatial table the following permissions need to be given to the user in the database:

Grant access to the database WITHOUT ability to create new tables:

GRANT SELECT ON geometry_columns TO user2;
GRANT SELECT ON spatial_ref_sys TO user2;

Grant access to the database with ability to create new tables:

GRANT SELECT,INSERT,UPDATE,DELETE ON geometry_columns TO user2;
GRANT SELECT ON spatial_ref_sys TO user2;

Grant view only access to a table:

GRANT SELECT ON [my_table] TO USER2;

Grant editable access to a table:

GRANT SELECT,INSERT,UPDATE,DELETE ON [my_table] TO USER2;

Grant additional permission on any associated sequence:

GRANT USAGE ON my_sequence TO user2;

Oracle security model

Create a user account in PostGIS that will own the data:

CREATE USER gisadmin IDENTIFIED BY password;

GRANT CONNECT, RESOURCE TO gisadmin;

Export a layer from SIS Desktop 9 into oracle connecting to the database using account gisadmin

Create two users and two database roles in Oracle. Map the users to the roles.

These users can not export data to the database.

CREATE USER plan_edit IDENTIFIED BY password;
GRANT CONNECT TO plan_edit;

CREATE USER plan_view IDENTIFIED BY password;
GRANT CONNECT TO plan_view;

GRANT ROLE rl_plan_viewer ;
GRANT ROLE r1_plan_editor ;

GRANT rl_plan_editor TO plan_edit;
GRANT rl_plan_viewer TO plan_view;

Grant select access on the planning layer to user plan_view:

GRANT select ON planning TO rl_plan_viewer;

Grant edit access on the planning layer to user plan_edit:

GRANT select,insert,update,delete ON planning TO rl_plan_editor;

When a connection is made from SIS Desktop 9 to Oracle and the user account gis_view is used SIS Desktop 9 will only list the planning layer – when added to SIS Desktop 9 the layer will be view only.

If the connection is made using the plan_edit account SIS Desktop 9 will only list planning layer. When added to SIS Desktop 9 the layer will be editable.

---