Oracle GRANT to Directories

[jdonnerstag]

3. We grant privs to Oracle Directory objects, e.g.
   'GRANT READ ON DIRECTORY '||R.DIRECTORY_NAME||' TO ' || v_owner;
   How to do that in Obevo?
   

    Below is the SELECT stmt to identify the directories in scope. It has a WHERE clause
    FOR R IN (SELECT DIRECTORY_NAME FROM ALL_DIRECTORIES where DIRECTORY_NAME like '%&&DIR_SUFFIX') LOOP
   
    May be the XML could be extended like
    <permission scheme="DIRECTORIES"
        select_stmt="%1 FROM ALL_DIRECTORIES where DIRECTORY_NAME like '${token}'"
        grant_stmt="${privileges} ON DIRECTORY ${groups} TO MY_USER"
    >
        <grant groups="%1" privileges="READ" />
   
    It would allow to keep all GRANT in one place, and executed at the correct time. I assume that is
    the reason why grants don't have a <schema>/<directory> but rather are configured in the XML?
   
    Similar example: how to:
    FOR R IN (select object_name from user_objects where object_type in ('PROCEDURE', 'FUNCTION','PACKAGE')) LOOP
        EXECUTE IMMEDIATE 'GRANT EXECUTE ON '||R.object_name||' TO ' || v_connect;
   
    Or ..
     --EXECUTE IMMEDIATE 'GRANT EXECUTE ON CMN_DROP_PARTITION TO ' || v_maint;
   

It seems to me there are many more GRANTS than just users and groups currently supported by Obevo. I like the idea of all GRANTS being in one place.

/Juergen

[shantstepanian]

This will be a multi-part answer, as I need a few things clarified (I haven't used Oracle DIRECTORY before).

1. This support doesn't exist today. For now, in the worst-case, you can fall back to the /migration object type to execute any SQL that you want, including these grants. Though it would bypass the permission scheme functionality, it gets you what you need.

2. For my understanding, can you explain how directories are used in Oracle, and if/how they interact with other objects in Oracle?

3. Per the doc, it looks like this object type doesn't belong to any schema. Can you please confirm?

4. In terms of the general grant handling in Obevo, I'll answer this question in two parts: first for new directory objects created after onboarding to Obevo, and then for existing objects.

(First, a question for you - are you onboarding an existing system where you've already deployed database objects? If so, are you doing the reverse-engineering steps?)

A) For new DIRECTORY objects:

Currently, we don't support the DIRECTORY object type.

For Oracle, we support: table, view, sp, sequence, function, trigger, synonym, package + package body; along with the staticdata and migration data types.

It should be possible to add support for the DIRECTORY object type soon.

Once done, then the permission scheme should handle it just as it would for other object types, e.g.

        <permission scheme="DIRECTORY">  <!-- scheme name corresponding to object type "directory" -->
            <grant users="SUPERUSER" groups="RO_GROUP1,RO_GROUP2,RW_GROUP" privileges="SELECT" />
        </permission>

Out of curiosity, have you been able to deploy DIRECTORY so far in your proof-of-concept, given that we don't support /directory as of today?

B) For existing directory objects, i.e. all objects in the schema

We have an open issue (#3) to apply the permission scheme to existing objects, particularly if we had onboard existing objects. The goal here is two-fold:

1. To apply any missing grants to existing objects
2. To (optionally) remove any extra grants not specified in your permission scheme from existing objects (i.e. cleanup)

I believe this would have the same intended effect of your "select_stmt". Let me know if this fits what you would want.

We hope to get #3 out in the next 3 months

---

There was a lot of info in there - feel free to reply in this issue for questions on anything raised above

[shantstepanian]

Hi, please let us know if you have more questions on this.

I will leave the ticket open for now, as we can use this to track the feature to add DIRECTORY support to Obevo

[shantstepanian]

@jdonnerstag FYI - development on this issue is in progress

[shantstepanian]

Hi @jdonnerstag , this functionality is now available for you to try out.

You can obtain the command-line binary here: https://www.dropbox.com/sh/xrfe6bp954do1pw/AAAi2ogHzTG2gklf6eipAph2a?dl=0

For info on how to use it, see the SNAPSHOT documentation: https://shantstepanian.github.io/obevo/oracle-notes.html.

We hope to release later this month.

Please let us know any feedback that you have.