Learning is not a spectator sport

Even with smart use of roles to manage privileges, one of the frustrations for DBAs with security privileges has been the disconnect between privileges and typical usage hierarchy. Object privileges have always worked on two tiers only, namely, you get access a single object (eg GRANT SELECT), or you get access to every object (GRANT SELECT ANY).

However, in the real world of database usage, we generally work in three tiers not two. Live the tiers above, some people need access to objects on an individual basis, or people (most likely with some sort administrative level of authority) will have access to all objects. But there is a common third tier that sits between these two, namely, the role (no pun intended) of schemas in our database. The schema often is indicative of an access tier, eg, “All the Human Resources staff can access anything in the HR schema” and so forth.

Historically, because object level grants had no concept of this third tier, we had to manage this ourselves. For example, if I have a new user “JANE_DOE” who should have rights to the HR schema, I would need to do something like the following:

SQL> create user jane_doe identified by jane_doe;

User created.

SQL> select table_name
  2  from   dba_tables
  3  where  owner = 'HR'
  4  order by 1;

TABLE_NAME
-----------------------------
COUNTRIES
DEPARTMENTS
EMPLOYEES
JOBS
JOB_HISTORY
LOCATIONS
REGIONS

7 rows selected.


SQL> grant select on HR.COUNTRIES   to jane_doe;

Grant succeeded.

SQL> grant select on HR.REGIONS     to jane_doe;

Grant succeeded.

SQL> grant select on HR.LOCATIONS   to jane_doe;

Grant succeeded.

SQL> grant select on HR.DEPARTMENTS to jane_doe;

Grant succeeded.

SQL> grant select on HR.JOBS        to jane_doe;

Grant succeeded.

SQL> grant select on HR.EMPLOYEES   to jane_doe;

Grant succeeded.

SQL> grant select on HR.JOB_HISTORY to jane_doe;

Grant succeeded.

Obviously this is both cumbersome (if you’ve ever seen the number of objects in an eBiz schema you’ll know this acutely), and creates an ongoing maintenance nuisance because as objects are created in the HR schema, the privileges must be continuously revisited. You can see my post “How to a grant on an entire schema” for details on how this can be managed in current versions of the database.

Luckily, in 23ai, all this becomes a thing of the past with the new schema level privileges. Finally the tiering system in security reflects the three tier logical concepts we use for objects, schemas and databases. Now when I wish to let JANE_DOE have SELECT access to the anything in the HR schema, it is a single DDL

SQL> grant select any table
  2    on schema hr
  3    to jane_doe;

Grant succeeded.

Don’t let the “ANY” frighten you. We have retained the use of “ANY” because it is indeed true that we are allowing JANE_DOE access to ANY-thing in the HR schema.

The great thing with this new feature is that it removes the hassles of ongoing maintenance. Now, for example, when I create a new table in the HR schema

SQL> create table hr.new_table as
  2  select * from scott.emp;

Table created.

JANE_DOE will automatically be able to see that table due to the schema level privilege.

SQL> conn jane_doe/jane_doe@db23
Connected.

SQL> select * from hr.new_table;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500                    30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

Note that the schema level privilege is not “additive/subtractive” from individual object level privileges. For example, if you want JANE_DOE to have access to all object except the EMPLOYEES table in the HR schema, then doing the following REVOKE statement

SQL> revoke select on hr.employees from jane_doe;

Revoke succeeded.

will not have the desired effect. The schema level privilege is still in place, and thus JANE_DOE still have access to the EMPLOYEES table.

SQL> conn jane_doe/jane_doe@db23
Connected.
SQL> select count(*) from hr.employees;

  COUNT(*)
----------
       107
       

Image credit https://en.wikipedia.org/wiki/User:Yskyflyer