Oracle has introduced a very good and much needed feature which is the ability to grant object level permissions on schema level scope. In the past if you have a schema with many tables under it and you want to grant the application account SELECT permission on these tables…you have 2 options:

Option 1:  performing the grant select explicitly on each table in the schema…so if you have 300 tables in your schema “HR”…you will execute : GRANT SELECT on HR.XXXX to HR_APP for each table. And any newly introduced set of tables will require the re-grant operation against them.

Option 2:  granting the application account “HR_APP” select any table permissions….which is very excessive permission and violates the security boundary if you are in a multi-hosting environment as the account will have access to tables in other schemas.

Let us explore 23c feature:

I will create two tables in HR schema and populate it with dummy data.

create table HR.EMPLOYEES ( fname varchar2(10), lname varchar2 (10) , ID number, retired BOOLEAN , deptno varchar2 (10));

insert into HR.EMPLOYEES values(‘John’,’Smith’,13912,FALSE,’IT-1′);

commit;

insert into HR.EMPLOYEES values(‘Sarah’,’Richardson’,71918,FALSE,’HR-1′);

commit;

insert into HR.EMPLOYEES values(‘Ricardo’,’Lopez’,150,TRUE,’Finance-1′);

commit;

create table HR.DEPARTMENTS ( deptname varchar2(10), orgcode varchar2 (10) , no_employees number , deptno varchar2 (10));

insert into HR.DEPARTMENTS values(‘IT’,’CG981258′,320,’IT-1′);

commit;

insert into HR.DEPARTMENTS values(‘FINANCE’,’CG96251′,166,’FIN-1′);

commit;

insert into HR.DEPARTMENTS values(‘AUDIT’,’CG400311′,320,’AUD-1′);

commit;

Now, using the new SQL command feature:

SQL> grant select any table on schema HR to HR_APP;

When accessing the database using application account HR_APP , the account will be able to view the data for the two existing tables:

This is great….and this will be applied automatically to newly created tables….I will create a dummy table based on departments table and HR_APP will be able to view the data:

SQL> create table HR.DUMMY as select * from HR.DEPARTMENTS;