Grant schema permissions effective for everything existing and everything that will exist

Question

I'm looking to do what this answer explains but in an Oracle environment:

https://dba.stackexchange.com/a/125400

Essentially what I'm looking to do is

"GRANT schema permissions that are effective for everything existing and everything that will exist in that schema."

with emphasis on "everything that will exist."

The programming department where I work is small (two of us), but the higher-ups have decided to turn on DB auditing as an added measure of security. We currently share one schema (let's call it MAIN) that has read only privileges on the production instance of the company's main database. In addition to be read only, it houses all the functions, packages, and procedures we use on a day-to-day basis to do our work.

So now instead of using one schema, we want to each use our own (USER1 and USER2) that looks at MAIN and sees everything it sees in real time. If USER1 goes in and creates a table/function/procedure called EXAMPLE in MAIN, we want USER2 to be able to go in and immediately see EXAMPLE without grants needing to be run. We're looking for this functionality because it's pretty common that months down the road after creating a query or something for someone, they'll come back and ask for that data again. If that query is stored in USER1's schema, and USER2 is the one fielding the request, they'll have to start from scratch to get the results for the person asking.

Is there a way to do this in Oracle?

Answer 1

Short answer - No.

You could do this by handing out massively overpowered sets of permissions - don't.

If I understand correctly, your MAIN schema exists in the production database, holds all your production tables, packages and procedures. If you were to do "something stupid" in there, you'd make one H*** of a mess.

Currently, you are using the credentials for the MAIN schema, which allows you to do all sorts of "fun" stuff, like creating indexes on the fly to keep application performance flying high, spinning up new Views to meet Users' reporting needs in record time ... and potentially destroying Production tables and thereby crippling your entire company. It is, perhaps, understandable that the "higher ups" want Auditing in place, so that they can blame anybody who breaks anything.

You need to stop using these credentials for anything that you do "by hand".
Right now.

Build yourself a "deployment system" that takes changes that you want to make in any database and applies them in an automated, controlled (and Audited) manner. If your company has something like this for application code changes, consider working with that. It will almost certainly mean working with other people to get "simple" changes put Live, but, for "covering your behind" in case things go horribly wrong, there's a lot to be said for it.

The system that you create will use those highly sensitive credentials to apply your changes on your behalf and you can test your "release" in at least one database before the change goes into Production, all the time reducing the Risk. Sadly, unlike other DBMSs, Oracle doesn't do Transactional DDL so you'll have to come up with something a bit more complex to apply structural changes to tables (DBMS_REDEFINITION, if you're feeling really brave), but taking this sort of "hands-off" approach to database changes really will pay dividends.

Oh, and part of this automated, change-applying process should be granting the correct permissions on the modified object(s).

Answer 2

One way to do this is to create a custom role. Many places use the DBA role out of the box but this often grants way more permissions than are really needed such as "DROP DATABASE".

Create a role, for example, LIMITED_PRODUCTION_DBA_ROLE, and grant

select, insert, update, delete any table;

This allows "Query tables, views, or materialized views in any schema except SYS". You can grant other privilege as required to the role. Then grant the role to your users. For development you will want to have more privileges so in your refresh script you can add a DEVELOPMENT_DBA_ROLE and grant that.