Usual rule of thumb with grants is give the minimal possible access to do the job. Saves people getting themselves into trouble and others getting in trouble.
Select any xx would break quite a few rules (Sarbanes Oxley, COBIT and PCI compliance) as there's no breakdown by role for access. You also have to consider audit trails and independence from interference / centralised auditing.
What you've suggested is a good start, if you have a security / compliance team it's best to liaise with them rather than redevelop adhoc roles on what you think is best, at least then you can enhance role based access or limit access to the DB altogether via your middle tier.
You need to identify what access is required, have business sign off on this and then create and test roles before rolling out to business. If you have a compliance team they will usually give you a steer so at least you don't become embattled for revoking access.
While I tend to think that the solution you outlined above is the better approach, simply because you are getting rid of such a powerful grant, another possible solution might be to put row-level security policies on the 25 tables using a predicate function something like this:
create package Security_Policies as ... function Non_SelectAnyTable_Users is begin return q'[not exists ( select 1 from SYS.SESSION_ROLE r join SYS.ROLE_SYS_PRIVS p on p.ROLE = r.ROLE and p.PRIVILEGE = 'SELECT ANY TABLE' )]'; end; ... end;
While the users can still SELECT the tables, they can't see any rows.
For what it's worth...
Incase you want to restrict the access to schema wise, you can create a dedicated Read (or select) role for each schema and then map the roles to the users/application groups as needed.
Say you have schema A and B, so you now create 2 roles for each schema A_R and B_R, then you can assign these roles to the users as needed.
the role A_R can hve a select on all the tables of schema A, and similarly for schema B
This would not only help you in restricting the access, but would also make your role management pretty scalable and simple to manage.
Say people belonging to Team A need access to only schema A, so you can then go ahead and grant the role to those users. and not unnecessarily grant them access to schema B.
Hope this makes sense
Oracle Database Vault Realms can be used to protect an entire application or a specific set of tables within an application, providing highly flexible and adaptable security enforcement.