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; } } }

Privileges for Oracle users created using Create Database User tool

JonathanBailey

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 user, I can't see any tables.

Is there something in creating the database user that I've missed?

Thanks,

Jon.

Find more posts tagged with

Accepted answers

JonathanBailey

It is the correct behaviour that the GFPTest user can't see the tables in SQL Developer, as the GFPTest user is not the owner of the tables.

In order to see the tables in SQL Developer, in the Connections window in SQL Developer, expand the database connection (connected as the GFPTest user), expand the Other Users folder, expand the SDE user node, and expand the Tables folder. The Tables folder will contain the tables owned by the SDE user.

Source: Esri Canada technical support

All comments

JonathanBailey

There was also another user that was complicating our analysis -- I offer this as an aside in case it might help someone else.

We were also using a third-party client to connect to the geodatabase. When using this client to connect as the SDE user, everything worked as expected. However, when using this client to connect as the GFPTest user, an error was generated when the client attempted to list items in the geodatabase. The error indicated that the GDB_ITEMS table couldn't be found.

There are two issues here:

The client shouldn't be querying the GDB_ITEMS table directly -- in this case, the client was built on ArcObjects, and there are ArcObjects methods available to query the contents of a workspace.
When constructing a query on an enterprise geodatabase , the table and column names must be qualified properly, using the methods on the ISQLSyntax interface. The SDE user can query the geodatabase system tables without qualification (presumably because it owns them?), so both of the following queries will work when executed as the SDE user:
select * from gdb_items;
select * from sde.gdb_items;
However, when executed as the GFPTest user, only the second query will work. The first query generate an error indicating that the GDB_ITEMS table can't be found.

JonathanBailey

It is the correct behaviour that the GFPTest user can't see the tables in SQL Developer, as the GFPTest user is not the owner of the tables.

Source: Esri Canada technical support

JonathanBailey

Hi George,

I'm trying to create a new geodatabase user -- so that user would need to be able to read the SDE and GDB system tables, wouldn't they?

Thanks,

Jon.

George Thompson

You would need to grant permissions of select on those specific tables to the user. I would not recommend this as modification of these table can cause geodatabase issues (and is unsupported).

Why does that data owner need access to those tables?

-George

JonathanBailey

Hi George,

Thanks for your response.

Yes, if I create a new table in ArcCatalog using the GFPTest user, I can see it in SQL Developer.

How can I configure the user so that I can see the rest of the contents in the database (especially the SDE and GDB schema tables)?

Thanks,

Jon.

George Thompson

Hi Jon,

When you use that tool it creates a user that can own data in the geodatabase. Unless the GFPTest user has an data owned there will be nothing to display in SQLPlus.

Create a new feature class (just a empty, dummy one) on ArcCatalog as that GFPTest user then look in SQLPlus, does that user now have tables listed?

-George

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

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