Privileges for Oracle users created using Create Database User tool

3839
20
Jump to solution
03-17-2015 01:46 AM
Highlighted
Occasional Contributor III

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.

Reply
0 Kudos
1 Solution

Accepted Solutions
Highlighted
Occasional Contributor III

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

View solution in original post

Reply
0 Kudos
20 Replies
Highlighted
Esri Frequent Contributor

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

--- George T.
Highlighted
Occasional Contributor III

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.

Reply
0 Kudos
Highlighted
Esri Frequent Contributor

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

--- George T.
Reply
0 Kudos
Highlighted
Occasional Contributor III

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.

Reply
0 Kudos
Highlighted
Esri Esteemed Contributor

Public access is granted to the SDE-owned tables by default.  Unless you've got some automated process that's revoked the PUBLIC grant, you have access to the tables (in fact, connecting as the user to the geodatabase proves you do have access).  ArcCatalog is not going to show any of the geodatabase implemntation tables owned by SDE (SDE.LAYERS,....), since they are "hidden" as part of the registry.

I think all you need to do is start using the geodatabase (though you should probably create new tablespaces for new data, and change the users' default tablespace to one of these, and configure DBTUNE first).

- V

Highlighted
Occasional Contributor III

Hi Vince,

Understood that I shouldn't be able to see the system tables in ArcCatalog -- but it seems that the GFPTest user can't access these tables at all.

This issue arose because I was trying to connect to the geodatabase using a third-party tool, and received an error indicating that the GDB_ITEMS table couldn't be found -- which is what led me to connect with SQL Developer to see what could be accessed.

Thanks,

Jon.

Edit: See reply at bottom for an explanation of why the third-party tool was generating an error.

Reply
0 Kudos
Highlighted
Esri Esteemed Contributor

Again, this is set up by default, by Create/Enable Enterprise Geodatabase (Data Management). It doesn't prevent someone from removing the PUBLIC grant, though.  If the PUBLIC grant is removed (by misguided security types, for example), it needs to be replaced by a GDBUSER_ROLE, and SELECT grants to that role to each table from which PUBLIC access was revoked, then grants to DGBUSER_ROLE would need to be added for each new user.

- V

BTW: You need to go back and edit your GSE post, to include this information.

Reply
0 Kudos
Highlighted
Occasional Contributor III

Hi Vince,

I'll check with our DBA to see if anything like that has been done. I still don't understand, however, why the user seems to have access through ArcCatalog but not through other clients like SQL Developer.

Thanks,

Jon.

Reply
0 Kudos
Highlighted
Occasional Contributor III

Following is what my DBA has reported. Does this make any sense?

select * from dba_tab_privs where grantee='PUBLIC' and owner='SDE'

order by table_name;

You’ll see the GDB_ITEMS there.

This could suggest that synonyms are missing. The query doesn’t specifically refer to SDE.GDB_ITEMS.

Is there a missing step to create those synonyms? There’s an easy way to do it:

Thanks,

Jon.

Reply
0 Kudos