[GIS] Privileges for Oracle users created using the ArcGIS Create Database User tool

Question

I've created an enterprise geodatabase on Oracle. I can connect to it from ArcCatalog and from SQL Developer using the SDE user.

I've created a new user named GFPTest using the Create Database User tool, choosing the defaults for Role and Tablespace Name.

I can connect to the database in ArcCatalog using the GFPTest user. However, when I connect in SQL Developer using the GFPTest tool, I can't see any tables.

What have I missed in creating the database user that prevents me from viewing the tables in SQL Developer?

Answer 1

An Oracle geodatabase administrator should have training in Oracle database administration, not just rely on a few helper tools in the ArcGIS toolbox.

Experienced Oracle DBAs create a number of tablespaces and roles before the first user, so letting the tablespace default in the tool is probably a mistake.

Best practice in database administration calls for designing the security model up front, with database roles defined for each class of user access, with users assigned to roles, and access to tables granted through roles.

Users should never be granted SELECT ANY TABLE access, so upon creation, without being granted any roles to access existing tables, and not having created any tables, it is proper that a user should not see any tables.

Some additional points:

• No additional tables should ever be created as the SDE user; the SDE login should be exclusively used for geodatabase administration.
• ArcGIS implements a "feature dataset" datatype which is used to group tables for cooperative editing. These feature datasets are not actual tables, so access cannot be controlled by database security. The contents of feature datasets are tables, but access is determined by an AND of permissions across all feature classes in the FDS, so a user missing SELECT access to one table wont see any FCs in the FDS (for this reason it is wise to grant FDS access to roles through ArcGIS tools).
• Oracle creates a number of phantom tables associated with LOB columns, which may be visible through SQL tools like Developer, but are hidden by ArcGIS. The same applies to certain tables to which PUBLIC has been granted SELECT access, and to tables created by ArcGIS to implement versioning, archiving, and other geodatabase constructs, so it is not unusual that the tables visible through Catalog and Developer should vary slightly.