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

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 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 users the ability to create object classes, feature classes, feature datasets, domains, networks, topologies, replication, etc., 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, then users will not be able to create objects in the geodatabase or possibly perform fundamental tasks, such as executing many of the geoprocessing tools that create or update the geodatabase.

If one proceeds to execute the provided procedures to remove the privileges, it is highly recommended that the organization tests their ArcGIS application prior to revoking the privileges in a production environment (testing will assist in identifying and understanding what geodatabase functionality will no longer be available to users who no longer have the necessary 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 that require the ability to 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 need to 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