Required database privileges

Note:

Database connections are supported in Insights in ArcGIS Enterprise and Insights desktop.

There are two types of data operations in ArcGIS Insights:

• 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 your deployment's hosted data store if you don't have the required database privileges to use data caching.

Google BigQuery

In BigQuery, privileges are applied to users through roles. The user who creates a database connection to BigQuery must have the privileges of the Basic roles (Editor, Owner, or Viewer), or the equivalent privileges in a Custom role.

Microsoft SQL Server

Type of operation	Required privileges	Purpose
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 and/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.
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	The CREATE TABLE permission allows Insights to create temporary tables or views in the user's schema.
	INSERT	The INSERT permission allows users to insert rows into the tables in the database.
	UPDATE	The UPDATE permission allows users to use the UPDATE statement on the tables in the database.

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 CREATE TABLE 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 at the database level:

use <databaseName>;
GO

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

Grant the SELECT privilege to read-only users on specific tables:

use <databaseName>;
GO

GRANT SELECT ON OBJECT::<schema>.<tableName1> TO <userName>;
GRANT SELECT ON OBJECT::<schema>.<tableName2> TO <userName>;

Oracle

Type of operation	Required privileges	Purpose
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 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 CREATE SEQUENCE TO <userName>;

Grant privileges to read-only users:

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

PostgreSQL

Type of operation	Required privileges	Purpose
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 operation	Required privileges	Purpose
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>;

Snowflake

In Snowflake, privileges are applied to users through roles. The role for the user who creates a database connection to Snowflake must include the following privileges:

Required privilege	Purpose
USAGE on <database>	Required to access tables in the database. USAGE must be granted on the database for the user who creates a database connection.
USAGE on <schema>	Required to access tables from a schema. The default schema is PUBLIC.
SELECT on <table>	Required to see and query data from a table. SELECT must be granted on each table used from a database connection.
USAGE on <warehouse>	Required to run queries in the specified warehouse.

Examples

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

Grant privileges on all tables in the database:

grant USAGE on DATABASE <databaseName> to role <roleName>;
grant USAGE on SCHEMA <schemaName> to role <roleName>;
grant SELECT ON ALL TABLES IN DATABASE <databaseName> to role <roleName>;
grant USAGE on WAREHOUSE <warehouseName> to role <roleName>;

Next steps

Now that you've verified and updated your database privileges, if necessary, the next step is to create connection to your database so you can access your database tables directly in Insights. For more information, see Create a database connection.