Skip To Content

Required database privileges

Note:

Database connections are supported in Insights Enterprise and Insights Local.

There are two types of data operations in Insights for ArcGIS:

  • Connect to and browse contents of a relational database without data caching.
  • Connect to and browse contents of a relational database with data caching.

Connect to and browse contents of a relational database with data caching allows Insights to create and manage temporary tables in the database.

The following tables list the minimum required privileges you need to connect to and browse the contents of a relational database and to optionally allow Insights to do data caching.

Note:

The connections are read-only. Insights does not permit you to create or edit data in the database.

Data will be copied to ArcGIS Data Store if you don't have the required database privileges to use data caching.

Microsoft SQL Server

Type of operationRequired privilegesPurpose

Without data caching

CONNECT

This privilege allows users to connect to the database.

The CONNECT privilege is granted on databases to the public database role by default. If you revoke this privilege from public, you must explicitly grant CONNECT on databases to specific roles or logins.

SELECT on other users' tables

Data viewers need select privileges on specific tables you want them to see and query.

If allowed to read all tables in the database, you can assign users to the db_datareader database role; otherwise, grant SELECT on specific tables and views.

VIEW DEFINITION

The VIEW DEFINITION privilege allows Insights to view metadata for the database.

With data caching

Note:

Connections with data caching also require the privileges for connections without data caching.

ALTER on schema

The ALTER permission allows Insights to create indexes and manage temporary tables in the user's schema.

CREATE TABLE and CREATE VIEW

The CREATE TABLE and CREATE VIEW permissions allow Insights to create temporary tables or views in the user's schema.

Examples

The following SQL code is an example of how privileges can be granted for SQL Server databases. Database administrators can customize these examples to grant privileges to database users in their organization.

Grant privileges to read-write users:

use <databaseName>;
GO

GRANT VIEW DEFINITION TO <userName>;
GRANT CREATE TABLE TO <userName>;
GRANT CREATE VIEW TO <userName>;
GRANT ALTER ON SCHEMA::dbo TO <userName>;

EXEC sp_addrolemember N'db_datareader', N'<userName>';
EXEC sp_addrolemember N'db_datawriter', N'<userName>';
GO

Grant privileges to read-only users:

use <databaseName>;
GO

GRANT VIEW DEFINITION TO <userName>;

EXEC sp_addrolemember N'db_datareader', N'<userName>';
GO

Oracle

Type of operationRequired privilegesPurpose

Without data caching

CONNECT role or CREATE SESSION

The CONNECT role or CREATE SESSION allows users to connect to the database.

Beginning in Oracle 10g Release 2 (10.2), the CONNECT role has only the CREATE SESSION privilege.

SELECT ANY DICTIONARY

This system privilege allows query access to any object in the SYS schema, including tables created in that schema, such as view SYS.DBA_TAB_PRIVS.

SELECT on other users' tables

Data viewers need SELECT privileges on specific tables you want them to see and query.

With data caching

Note:

Connections with data caching also require the privileges for connections without data caching.

CREATE TABLE*

The CREATE TABLE permission allows Insights to create temporary tables in the user's schema.

*Allocate a proper QUOTA to the user in a tablespace with the command ALTER USER <username> QUOTA <size> ON <tablespace>.

CREATE SEQUENCE

The CREATE SEQUENCE permission allows Insights to generate spatial indexes.

Examples

The following SQL code is an example of how privileges can be granted for Oracle databases. Database administrators can customize these examples to grant privileges to database users in their organization.

Grant privileges to read-write users:

GRANT CONNECT TO <userName>; 
GRANT CREATE TABLE TO <userName>; 
ALTER USER <userName> QUOTA <size> ON USERS; 
GRANT SELECT ANY DICTIONARY TO <userName>; 
GRANT CREATE SEQUENCE TO <userName>;

Grant privileges to read-only users:

GRANT CONNECT TO <userName>; 
GRANT SELECT ANY DICTIONARY TO <userName>; 
GRANT SELECT ON <schema>.<tableName> TO <userName>;

PostgreSQL

Type of operationRequired privilegesPurpose

Without data caching

CONNECT on database

This privilege allows users to connect to the database.

Grant CONNECT privilege on databases to specific database logins.

USAGE on users' schemas or SELECT on users' tables

Data viewer users need USAGE privilege on specific schemas containing user tables. Or SELECT privileges on specific user tables or views you want them to see and query.

Grant SELECT on specific tables and views.

With data caching

Note:

Connections with data caching also require the privileges for connections without data caching.

CREATE on schema

The CREATE permission allows Insights to create indexes and manage temporary tables in the user's schema.

Examples

The following SQL code is an example of how privileges can be granted for PostgreSQL databases. Database administrators can customize these examples to grant privileges to database users in their organization.

Grant privileges to read-write users:

GRANT CONNECT ON DATABASE <databaseName> TO <userName>;
GRANT USAGE ON SCHEMA <schemaName>TO <userName>;
GRANT CREATE ON SCHEMA <schemaName> TO <userName>;

Grant privileges to read-only users:

GRANT CONNECT ON DATABASE <databaseName> TO <userName>;
GRANT USAGE ON SCHEMA <schemaName>TO <userName>;   -- Give the access to all tables in the schema
GRANT SELECT ON <tableName> TO <userName>;  -- Or give the access to a specific table

SAP HANA

Type of operationRequired privilegesPurpose

Without data caching

SELECT ON sys.st_geometry_columns and sys.st_spatial_reference_systems

These privileges are required to read ST_GEOMETRY metadata for spatial operations.

SELECT ON <table1>,<table2>, <tablen>

Data viewers need SELECT privileges on specific tables you want them to see and query.

With data caching

Note:

Connections with data caching also require the privileges for connections without data caching.

CREATE TABLE

DROP TABLE

Alternatively, the user must be a HANA Standard User.

Allow Insights to create or drop tables in its own schema and insert data.

Examples

The following SQL code is an example of how privileges can be granted for SAP HANA databases. Database administrators can customize these examples to grant privileges to database users in their organization.

A standard read-write user has all the privileges required to use Insights.

Non-standard users

GRANT SELECT ON SCHEMA <schemaName> to <userName>;   -- executed by the schema owner 

GRANT SELECT ON sys.st_geometry_columns TO <userName>;   -- executed by SYSTEM account
GRANT SELECT ON sys.st_spatial_reference_systems TO <userName>;