English

How To: Revoke database privileges from the ArcGIS geodatabase metadata tables

Summary

Instructions provided describe how to revoke INSERT, UPDATE, and DELETE privileges from the geodatabase gdb_* tables in enterprise geodatabase instances for Oracle and SQL*Server (applicable for ArcGIS 9.x and 10.x).

When a geodatabase is created, a set of geodatabase metadata tables named gdb_* are created in the adminstrator's schema (the SDE schema in Oracle and SQL*Server or the dbo schema in SQL*Server) and automatically have SELECT, INSERT, UPDATE, and DELETE privileges granted to PUBLIC for each table. These privileges are required to allow the creation of object classes, feature classes, feature datasets, domains, networks, topologies, replication, and so forth in the geodatabase. But unfortunately, the open privileges can also allow users to maliciously corrupt a geodatabase using SQL to directly modify the tables.

To prevent potential misuse and corruption, one can revoke the INSERT, UPDATE, and DELETE privileges from PUBLIC for the gdb_* tables. If the default privileges are removed from the geodatabase gdb_* tables, users cannot create objects in the geodatabase or possibly perform fundamental tasks, such as executing many of the geoprocessing tools that create or update the geodatabase.

Prior to revoking privileges in a production environment, it is recommended to test the ArcGIS application to identify and understand what geodatabase functionality is not available to users without privileges to INSERT, UPDATE, or DELETE from the geodatabase gdb_* tables.

If necessary, the geodatabase administrator can explicitly grant INSERT, UPDATE, and DELETE to specific users in the database who must create and modify geodatabase properties.

Procedure

The following procedures provide examples of how to revoke the INSERT, UPDATE, and DELETE privileges from PUBLIC for the gdb_* tables in Oracle and SQL*Server. Additional procedures are provided to grant INSERT, UPDATE, and DELETE to PUBLIC in the case that privileges must be restored.
 

The following is the Oracle procedure for revoking INSERT, UPDATE, and DELETE from PUBLIC for the gdb_* tables. Execute the procedure as the SDE user in SQL*Plus.
 

Code:
DECLARE

  CURSOR each_table IS
    SELECT DISTINCT table_name
      FROM user_tab_privs_made
     WHERE table_name LIKE 'GDB_%'
       AND privilege IN ('INSERT','UPDATE','DELETE')
       AND grantee = 'PUBLIC'
  ORDER BY table_name;

BEGIN

  IF USER <> 'SDE' THEN
    RAISE_APPLICATION_ERROR(-20000, 'USER must be SDE to execute this procedure.');
  END IF;

  FOR each_row IN each_table LOOP
    EXECUTE IMMEDIATE 'REVOKE INSERT, UPDATE, DELETE ON '||each_row.table_name||' FROM PUBLIC'; 
  END LOOP;

END;
/


The following is the Oracle procedure for granting INSERT, UPDATE, and DELETE to PUBLIC for the gdb_* tables. Execute the procedure as the SDE user in SQL*Plus.
 
Code:
DECLARE

  CURSOR each_table IS
    SELECT DISTINCT table_name
      FROM user_tab_privs_made
     WHERE table_name LIKE 'GDB_%'
       AND privilege = 'SELECT'
       AND grantee = 'PUBLIC'
  ORDER BY table_name;

BEGIN

  IF USER <> 'SDE' THEN
    RAISE_APPLICATION_ERROR(-20000, 'USER must be SDE to execute this procedure.');
  END IF;

  FOR each_row IN each_table LOOP
    EXECUTE IMMEDIATE 'GRANT INSERT, UPDATE, DELETE ON '||each_row.table_name||' TO PUBLIC'; 
  END LOOP;

END;
/
 

The following is the SQL*Server procedure for revoking INSERT, UPDATE, and DELETE from PUBLIC for the gdb_* tables. Execute the procedure as the SDE or dbo user in SQL*Server Management Studio.
 

Code:
Declare @TName varchar(100)
Declare @SQLStr varchar (500)

Declare	each_table	cursor
for
	select name from sys.tables where name like 'GDB_%'

open each_table
Fetch each_table into @TName
while (@@fetch_status=0) 
Begin
	set @SQLStr = 	'REVOKE INSERT, UPDATE, DELETE ON ' + @TName + ' FROM PUBLIC cascade'
	exec (@SQLStr)

	Fetch each_table into @TName
End
close each_table
Deallocate each_table


The following is the SQL*Server procedure for granting INSERT, UPDATE, and DELETE to PUBLIC for the gdb_* tables. Execute the procedure as the SDE or dbo user in SQL*Server Management Studio.
 
Code:
Declare @TName varchar(100)
Declare @SQLStr varchar (500)

Declare	each_table	cursor
for
	select name from sys.tables where name like 'GDB_%'

open each_table
Fetch each_table into @TName
while (@@fetch_status=0) 
Begin
	set @SQLStr = 	'GRANT INSERT, UPDATE, DELETE ON ' + @TName + ' TO PUBLIC'
	exec (@SQLStr)

	Fetch each_table into @TName
End
close each_table
Deallocate each_table