Grant different owner ability to create view on table that I own

[Bud]

We have scenarios where we want to grant a different owner/user the ability to create a view on an table that our user owns. Currently, the only way to do that is to submit a ticket to the DBA which is a time consuming process.

It would be better if it could be done through the privileges window in Catalog, where all other geodatabase privileges are managed.

Could that functionality be added to ArcGIS Pro?

Oracle 18c 10.7.1 EGDB

[MarceloMarques]

The Oracle DBA still needs to "grant create view privilege" for the data owner user to be able to create the view, this is not a new functionality that needs to be added in ArcGIS Pro, if your Oracle DBA does not want to grant the privilege then there are security reasons for it, and you will need to discuss further with your Oracle DBA team. I hope this clarifies.

[Bud]

@MarceloMarques 

It’s my understanding that the only thing that our DBA needs to do is run the following statement using a connection to NON_IT_USER1:

GRANT SELECT ON NON_IT_USER1.FC1 TO NON_IT_USER2 WITH GRANT OPTION;

I don’t think I understand why that can’t be done though ArcGIS Pro.

I worked in IT previously, and often ran statements like

GRANT SELECT ON NON_IT_USER1.FC1 TO IT_USER WITH GRANT OPTION; 

Are you suggesting the DBA had to do a one-time operation to allow NON_IT_USER1 to run GRANT SELECT ON … WITH GRANT OPTION for all tables in that owner — going forward? In other words, are you saying the DBA needs to perform a generic step before a user can GRANT SELECT ON … WITH GRANT OPTION?

If so, could Esri add a “Grant [other users to create views]” column to the Privileges window in Pro, and just grey-out the checkboxes if the DBA hasn’t performed the prerequisite step yet?

None of this data is sensitive. There are no security concerns about these grants. It’s just a nuisance more than anything.

[MarceloMarques]

I recommend to not grant any permission on Oracle Geodatabases using the option "with grant option" because this can lead into security vulnerabilities, and exploitation.

You need to understand that there are objects grants like "select, insert, update, delete" on tables and there are other system privileges like "create view", "create table" permissions.

For a data owner to create views it needs to have the "create view privilege".

Example: connect /as sysdba; grant create view to userA; 

if userA is already a geodatabase data owner then userA will be able to create views using tables that the userA owns.

For userA to create a view that uses tables from another data owner user, such as userB, then UserA must be granted select on the tables of the userB.

Example: connect /as sysdba; grant select on userB.Table1 to UserA;

These privileges and grants are managed directly in the Oracle database by the Oracle DBA, and many of these require sysdba access that only the Oracle DBA has access.

I hope this clarifies your question.