nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Hide system tables from SDE schema

NathanielWingfield

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?

Encuentra más publicaciones etiquetadas con

Accepted answers

All comments

bojanbukovic1

Hi all,

objects are not displaying in ArcCatalog anymore after revoking select on tables from public role in my case.
Using ArcSDE 10 SP4.

revoke select on lbacsys.all_sa_audit_options from public;
revoke select on lbacsys.all_sa_compartments from public;
revoke select on lbacsys.all_sa_data_labels from public;
revoke select on lbacsys.all_sa_group_hierarchy from public;
revoke select on lbacsys.all_sa_groups from public;
revoke select on lbacsys.all_sa_labels from public;
revoke select on lbacsys.all_sa_levels from public;
revoke select on lbacsys.all_sa_policies from public;
revoke select on lbacsys.all_sa_prog_privs from public;
revoke select on lbacsys.all_sa_schema_policies from public;
revoke select on lbacsys.all_sa_table_policies from public;
revoke select on lbacsys.all_sa_user_compartments from public;
revoke select on lbacsys.all_sa_user_groups from public;
revoke select on lbacsys.all_sa_user_labels from public;
revoke select on lbacsys.all_sa_user_levels from public;
revoke select on lbacsys.all_sa_user_privs from public;
revoke select on lbacsys.all_sa_users from public;
revoke select on lbacsys.lbacaudit_actions from public;
revoke select on lbacsys.lbac_audit_actions from public;
revoke select on lbacsys.user_sa_session from public;

Regards

ForrestJones

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,

AJR

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

EyadHammad

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 ?

EyadHammad

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 xyz -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

ForrestJones

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?

AJR

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 xyz -s yyy -i zzz

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

EyadHammad

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 ?

danan

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.

RussellBrennan

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

danan

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

ForrestJones

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

HelenYang

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 xyz from public. The tables are still showing in the ArcCatalog.

Wonder why?

chandesrisbasile1

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

XianzhouLu

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

StephenLewonka

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

MonikaFlach

We also have this problem.

see attachement

arccatalog.jpg

JimDickerson

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.

NathanielWingfield

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.

VinceAngelo

Don't grant SELECT to PUBLIC on these other schemas

- V

Únete a la Conversación

Los miembros registrados pueden publicar, seguir actualizaciones y más. ¿Nuevo aquí? Regístrate gratis.

Enlaces Rápidos

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.