With Oracle database 23C, we finally have the ability to GRANT SELECT ANY TABLE on SCHEMA.

When I was originally introduced to multiple schemas in the Oracle database, I was baffled by why DBAs seemed to use scripts to grant SELECT privileges on tables via scripts that seemingly were hand-coded. Surely there was a way to grant using some wildcard, right? Not that I could find. So I saw scripts that would write code such as:

Select 'GRANT SELECT ON HR.'||Table_Name||' TO SCOTT;' 
From All_Tables Where Owner='HR'

Which returned:

GRANT SELECT ON HR.REGIONS TO SCOTT;
GRANT SELECT ON HR.COUNTRIES TO SCOTT;
GRANT SELECT ON HR.LOCATIONS TO SCOTT;
GRANT SELECT ON HR.DEPARTMENTS TO SCOTT;
GRANT SELECT ON HR.JOBS TO SCOTT;
GRANT SELECT ON HR.EMPLOYEES TO SCOTT;
GRANT SELECT ON HR.JOB_HISTORY TO SCOTT;

I then had to cut-and-paste those code-generated statements or write (or find) some routine that does this. But what happens when a new table was added to schema HR? I have to do this all over again? Really?

Well, with release 23C (now available for FREE as Oracle Database 23c Free Developer Release), Oracle has rectified that situation with a single command GRANT SELECT ANY TABLE on SCHEMA. So now I can simply replace all that stuff before with the following:

GRANT SELECT ANY TABLE ON SCHEMA HR TO SCOTT;

That’s so much simpler. Yes, perhaps in theory that’s not quite as secure, since someone may create a new table in schema HR that SCOTT should NOT get access to, but in my experience that just doesn’t happen very often.

One last thought: when you are looking at granting SELECT ANY TABLE, consider other types of objects like VIEWs at the same time.

Rather than writing more, I’ll just link to Russ Lowenthal’s excellent video on the subject at www.youtube.com/v/7fsVXfuhrIk, or now that you know what to look for, you can google Oracle Schema Privileges.

This feature greatly simplifies my task of providing an easy way to build an analytic warehouse that is easily maintained by novices that don’t want to get into the intricacies of how to grant privileges. Thanks, Oracle! Now we just need this feature in Oracle’s Autonomous Database offering, which IS designed for people that are not expert DBAs. I’m sure that’s coming soon as Oracle has said that it is putting 23c features in the autonomous database platform at some point.