I'm looking to do what this answer explains but in an Oracle environment:
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?