Oracle, PostGIS and SQL Server all have built in security which prevents unauthorised users from accessing data. In order to read data from the database users will have to supply the correct user name and password. The database will authenticate the connection information before allowing SIS to access the data. The security must be set up by the database administrator before spatial data is migrated to the database. When creating new overlays in SIS if an attempt is made to base the overlay on data from a database SIS 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.

SIS authenticates users as having either view only or editable access to data stored in a database. SIS 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:

Each user who needs data from the database is given their own security account on the database.
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.
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.

Back to top of page

Database Security Model

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

Oracle (10g release2, 11g release1 or 2)
SQL Server 2008
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.

Back to top of page

Database Installation

For most requirements a standard installation of the database is adequate.

Back to top of page

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 quality check the data before uploading the data into the database. This mechanism prevents users from unnecessarily uploading data to the database, which can lead to data duplication etc.

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 to the database.

Data Access Account

Users can create as many accounts as they wish to manage their data access requirements. 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 by 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.

Back to top of page

Pre-Created SWDs

SIS can be deployed throughout an organisation in such a way that the end users never manually log onto the database, this results in the end users not requiring the password for the database accounts. In this scenario the GIS Manager creates projects (called SWDs or SIS files) for the users. Each project contains the spatial layers that an end user requires to fulfil their job role. A project can be for a single person for use by multiple people. The database administrator adds the appropriate spatial layers to the project and supplies the appropriate 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 makes the appropriate connections to the database to retrieve data (end users do not need to enter a password). This approach requires very few database accounts and may suit organisations who do not have a database administrator.

Back to top of page

User Managed SWDs

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

Back to top of page

Database Roles

Oracle, SQL Server and PostGIS all support database roles. SIS 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.

Back to top of page

Examples of data base 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 in a multi departmental organisation there is to be no sharing of data, 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).

Back to top of page

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 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 SIS to SQL Server and the user account gis_parks_edit is used SIS will only list the parks layer – when added to SIS the layer will be editable.

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

Back to top of page

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 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 to PostGIS and the user account gis_view is used SIS will only list the planning layer – when added to SIS the layer will be view only.

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

Back to top of page

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;

Back to top of page

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 into oracle connecting to the database using account gisadmin

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

Note: 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 to Oracle and the user account gis_view is used SIS will only list the planning layer – when added to SIS the layer will be view only.

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

Top of page

Send comments on this topic.

Click to return to www.cadcorp.com