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.

[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

[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]

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,

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.

[VinceAngelo]

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

[JonathanBailey]

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.

[VinceAngelo]

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.

[JonathanBailey]

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.

[JonathanBailey]

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.