topic Re: Hide system tables from SDE schema in Data Management Questions

Hide system tables from SDE schema

NathanielWingfield — Fri, 09 Apr 2010 12:53:44 GMT

I recently installed ArcSDE 9.3 on Oracle 11g. No less than 119 system tables from the APEX_030200 and ORDDATA schemas are showing up in ArcCatalog. Is there an easy solution to make these go away?

Hide system tables from SDE schema

VinceAngelo — Fri, 09 Apr 2010 13:17:19 GMT

Don't grant SELECT to PUBLIC on these other schemas

- V

Hide system tables from SDE schema

NathanielWingfield — Fri, 09 Apr 2010 13:31:49 GMT

This is a stock Oracle install; I have not explicitly granted anything to PUBLIC. I can revoke the privileges but I'm not sure what implications that may have.

Besides, the PUBLIC user has SELECT privileges on 2,143 database objects in 13 schemas. How come these 119 objects are visible, but not the others? Some explanation of the underlying mechanics would be helpful. Thanks.

Re: Hide system tables from SDE schema

JimDickerson — Fri, 11 Jun 2010 18:28:47 GMT

This is a stock Oracle install; I have not explicitly granted anything to PUBLIC. I can revoke the privileges but I'm not sure what implications that may have.

Besides, the PUBLIC user has SELECT privileges on 2,143 database objects in 13 schemas. How come these 119 objects are visible, but not the others? Some explanation of the underlying mechanics would be helpful. Thanks.

I also have this problem. I used Enterprise Manager to edit user Public's Object Privileges. I deleted a few and reconnected via ArcCatalog but they are still showing up. Like nwingfield, I don't know what will happen if I delete them all.

Re: Hide system tables from SDE schema

MonikaFlach — Mon, 28 Jun 2010 09:20:19 GMT

We also have this problem.

see attachement

Re: Hide system tables from SDE schema

StephenLewonka — Mon, 28 Jun 2010 18:59:39 GMT

Similiar problems for me.

here is the query ArcCatalog issues (with a little editing from me for clarification purposes).

I've just installed Apex 4.0 and many (tho not all) my APEX obects are showing.

so the APEX 4.0 schema would be apex_0400000 and APEX 3.2 schema is apex_030200.. both of these aren't filtered out with this query. I think you have to go back to APEX 3 where it was called "FLOWS_030000" (AC.owner != 'FLOWS_030000') for it not to show.

there should be a way to modify ugly piece of hardcoding.

SELECT AC.owner, AC.table_name
FROM ALL_CATALOG AC
WHERE
AC.owner != 'MTSSYS' AND
AC.owner != 'SYS' AND
AC.owner != 'SYSTEM' AND
AC.owner != 'PERFSTAT' AND
AC.owner != 'DMSYS' AND
AC.owner != 'EXFSYS' AND
AC.owner != 'WK_TEST' AND
AC.owner != 'PUBLIC' AND
AC.owner != 'SYSMAN' AND
AC.owner != 'SI_INFORMTN_SCHEMA' AND
AC.owner != 'MDSYS' AND
AC.owner != 'ORDSYS' AND
AC.owner != 'FLOWS_030000' AND
AC.owner != 'FLOWS_FILES' AND
AC.table_type != 'SEQUENCE' AND
AC.table_name != 'RASTER_COLUMNS' AND
AC.table_name != 'RECONCILED_STATES' AND
AC.table_name != 'SDE_UUID_TEMP$' AND
AC.table_name != 'SDO_GEOM_METADATA' AND
AC.table_name NOT LIKE 'F%_SQL_IX1_%' AND
AC.table_name NOT LIKE 'S%_IDX$' AND
AC.table_name NOT LIKE 'S%_PX$' AND
AC.table_name NOT LIKE '%_IX1_%$' AND
AC.table_name NOT LIKE '%_F%$' AND
AC.table_name NOT LIKE '%_H%$' AND
AC.table_name NOT LIKE '%_RT$%' AND
AC.table_name NOT LIKE '%_SDOLAYER' AND
AC.table_name NOT LIKE '%_SDODIM' AND
AC.table_name NOT LIKE '%_SDOGEOM' AND
AC.table_name NOT LIKE '%_SDOINDEX' AND
AC.table_name NOT LIKE '%RT_%$' AND
AC.table_name NOT LIKE '%QT_%$' AND
AC.table_name NOT LIKE '%BIN$%' AND
AC.table_name NOT LIKE 'SDE/_%' ESCAPE '/' AND
AC.table_name NOT LIKE 'DR$XML%' AND
AC.table_name NOT LIKE 'KEYSET_%' AND
AC.owner != 'AURORA$JIS$UTILITY$' AND
AC.owner != 'OSE$HTTP$ADMIN' AND
AC.owner != 'QS' AND
AC.owner != 'QS_CBADM' AND
AC.owner != 'QS_CS' AND
AC.owner != 'QS_ES' AND
AC.owner != 'QS_OS' AND
AC.owner != 'QS_WS' AND
AC.owner != 'WMSYS' AND
AC.owner != 'XDB' AND
AC.owner != 'WKSYS' AND
AC.owner != 'OLAPSYS' AND
AC.owner != 'RMAN' AND
AC.owner != 'ODM' AND
AC.owner != 'ODM_MRT' AND
AC.owner != 'DBSNMP' AND
AC.owner != 'OUTLN' AND
AC.owner != 'CTXSYS' AND
NOT (
AC.OWNER IN (SELECT instance_name FROM SDE.INSTANCES) AND
AC.table_name IN ('COLUMN_REGISTRY','GEOMETRY_COLUMNS','LAYERS','LOCATORS', 'METADATA','GCDRULES','MVTABLES_MODIFIED','SDE_LOGFILES', 'SDE_LOGFILE_DATA','SPATIAL_REFERENCES','STATE_LINEAGES','STATES', 'STATE_ID_GENERATOR','TABLE_REGISTRY','VERSION','VERSIONS', 'DBTUNE','LAYER_LOCKS','OBJECT_LOCKS','PROCESS_INFORMATION', 'SDE_TABLES_MODIFIED','STATE_LOCKS','TABLE_LOCKS','LINEAGES_MODIFIED', 'SERVER_CONFIG','SDE_XML_TAGS','SDE_XML_INDEX_TAGS','SDE_XML_INDEXES', 'SDE_XML_COLUMNS','SDE_LOGFILE_POOL','INSTANCES','ALL_ST_GEOMETRY_COLUMNS_V', 'ST_COORDINATE_SYSTEMS','ST_GEOMETRY_COLUMNS','ST_GEOMETRY_INDEX','ST_SPATIAL_REFERENCES', 'USER_ST_GEOM_INDEX_V','USER_ST_GEOMETRY_COLUMNS_V','ST_UNITS_OF_MEASURE')
)
ORDER BY AC.owner, AC.table_name

Re: Hide system tables from SDE schema

XianzhouLu — Tue, 24 Aug 2010 06:26:55 GMT

connect to oracle using sys as sysdba
then:
revoke all on apex_030200.apex_application_lovs from public;
and so on.

Re: Hide system tables from SDE schema

chandesrisbasile1 — Wed, 01 Sep 2010 09:10:06 GMT

It seems other people have found another solution:
http://forums.esri.com/Thread.asp?c=93&f=993&t=110733

Mark table as hidden in ArcSDE data dictionary
Author Michael Mannion
Date Nov 12, 2003
Message Rodrigo,

You can set an ArcSDE table registry flag to mark a table as hidden, preventing it from being displayed in the ArcCatalog Contents tab. If a table is already registered with ArcSDE, use:

sdetable -o alter_reg -t [table] -H hidden

Alternatively, you can register an independent database table with ArcSDE using:

sdetable -o register -t [table] -H hidden

To facilitate setting this for multiple tables, you can use SQL*Plus to spool a new script. By concatenating literals with the variable table names, you can quickly generate a template script from which you can easily delete the tables you do not want to mark as hidden. Then, run the script, refresh ArcCatalog, and the tables should be absent from the Contents tab. Refer to the text below for an example.

Good luck.

-Michael

SPOOL hide_tables.sh

SELECT
'sdetable '
|| '-o alter_reg '
|| '-t ' || table_name || ' '
|| '-H hidden '
|| '-u [user] '
|| '-p [password] '
|| '-s [server] '
|| '-i [instance] '
|| '-N'
FROM
user_tables;

SPOOL OFF

Michael Mannion
Mannion Geosystems, LLC
www.manniongeo.com

Re: Hide system tables from SDE schema

HelenYang — Mon, 01 Nov 2010 04:45:05 GMT

Don't grant SELECT to PUBLIC on these other schemas

- V

I did as advised (eg. REVOKE SELECT ON APEX_030200.APEX_APPLICATION_LOV_ENTRIES FROM PUBLIC;). However, ArcCatalog still shows all the APEX objects in ArcCatalog list.

I even tried REVOKE all on xxxx from public. The tables are still showing in the ArcCatalog.

Wonder why?

Re: Hide system tables from SDE schema

ForrestJones — Mon, 01 Nov 2010 15:26:44 GMT

Hi All,

Are you using Oracle 11gR2 and a version of sde earlier than 9.3.1 SP2? This nim has been fixed in 9.3.1 sp2 to filter those tables...

NIM052156: Ora11gR2 APEX* & ORDDATA* tables need to be filtered

http://resources.arcgis.com/content/nimbus-bug?bugID=TklNMDUyMTU2

Re: Hide system tables from SDE schema

danan — Fri, 05 Nov 2010 09:52:08 GMT

What about hiding the ArcSDE schema from other users on the instance? At 9.3.1, write privileges (I,U,D) are granted to PUBLIC for GDB_ tables. Read privs are given to most or all tables in the SDE schema. And execute is granted to PUBLIC for all packages and procedures in the SDE schema.

I submitted an idea for hardening the ArcSDE repository to ideas.arcgis.com. Vote on it if you'd like:

http://ideas.arcgis.com/ideaView?id=087300000008HY6AAM

Re: Hide system tables from SDE schema

RussellBrennan — Tue, 09 Nov 2010 13:17:12 GMT

What about hiding the ArcSDE schema from other users on the instance? At 9.3.1, write privileges (I,U,D) are granted to PUBLIC for GDB_ tables. Read privs are given to most or all tables in the SDE schema. And execute is granted to PUBLIC for all packages and procedures in the SDE schema.

I submitted an idea for hardening the ArcSDE repository to ideas.arcgis.com. Vote on it if you'd like:

http://ideas.arcgis.com/ideaView?id=087300000008HY6AAM

Is this the information you were looking for?
http://resources.arcgis.com/content/kbase?fa=articleShow&d=37824

Re: Hide system tables from SDE schema

danan — Wed, 10 Nov 2010 13:12:03 GMT

Is this the information you were looking for?
http://resources.arcgis.com/content/kbase?fa=articleShow&d=37824

Thanks Russell. I'll take a look.

Re: Hide system tables from SDE schema

EyadHammad — Wed, 28 Dec 2011 05:52:09 GMT

we have ArcGIS 10 with SP3 build 3600, yet we face the same problem of having APEX_040000 schema showing in ArcCatalog for all connections. any idea of how to filter it ?

Re: Hide system tables from SDE schema

AJR — Wed, 28 Dec 2011 13:38:59 GMT

there are two options. The first is properly administering the privileges on the offending tables (like Vince mentions above). If that is not an option the second choice is to register the offending tables with SDE and then hide them. This is done with the sde command line using the sdetable command.

To do this execture the following as the owner of the offending table(s)

sdetable -o register -t some_table -C none -H hidden -u table_owner -p xxx -s yyy -i zzz

The tables will then be hidden from all sde client applications (e.g. ArcGIS)

Re: Hide system tables from SDE schema

ForrestJones — Wed, 28 Dec 2011 15:55:18 GMT

we have ArcGIS 10 with SP3 build 3600, yet we face the same problem of having APEX_040000 schema showing in ArcCatalog for all connections. any idea of how to filter it ?

Is your ArcSDE database upgraded to 10.0 SP3?

Re: Hide system tables from SDE schema

EyadHammad — Mon, 09 Jan 2012 06:04:52 GMT

there are two options. The first is properly administering the privileges on the offending tables (like Vince mentions above). If that is not an option the second choice is to register the offending tables with SDE and then hide them. This is done with the sde command line using the sdetable command.

To do this execture the following as the owner of the offending table(s)

sdetable -o register -t some_table -C none -H hidden -u table_owner -p xxx -s yyy -i zzz

The tables will then be hidden from all sde client applications (e.g. ArcGIS)

hmmm , this requires to know the credentials of APEX schema. it was created by the system upon installation i think so how can we get this detail. in addition, its a very long list of tables .. is there a way to deal with a schema as a whole ?

by the way, another schema called LBACSYS is also showing lots of tables that we would love to hide from our users

Is your ArcSDE database upgraded to 10.0 SP3?

no. i am upgrading now

Re: Hide system tables from SDE schema

EyadHammad — Mon, 09 Jan 2012 08:24:28 GMT

SDE is upgraded to SP3 and geodatabase was upgraded yet situation got worse.

additional system schemas now are showing:

1- APEX_040000
2- APPQOSSYS
3- LBACSYS
4- OWBSYS

how to hide these in ArcCatalog for our users ?

Re: Hide system tables from SDE schema

AJR — Mon, 09 Jan 2012 15:43:58 GMT

The sdetable command operates on a table by table basis, there isn't any option (that I know of) to hide all the tables from a given schema. You could script a solution relatively easily using sql to repetitvely call the sdetable command. Something along the lines of select 'sdetable -o ...-t " || tablename || ' -s server -i ...." from user_tables ought to do the trick (either re-direct the output to a text file that you then execute or save the results to a text file (if using a GUI based tool).

Re: Hide system tables from SDE schema

ForrestJones — Tue, 10 Jan 2012 16:23:29 GMT

SDE is upgraded to SP3 and geodatabase was upgraded yet situation got worse.

additional system schemas now are showing:

1- APEX_040000
2- APPQOSSYS
3- LBACSYS
4- OWBSYS

how to hide these in ArcCatalog for our users ?

Something doesn't seem correct if they are still showing up and you are up to 10.0 SP3 on the client and sde is upgraded. Will you please contact esri support so we can narrow this down and determine if there is something more we need to fix?

Thanks,