Revoke database privileges from the ArcGIS geodatabase metadata tables

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