Hello,

i'd like a suggestion about the use case below

We have a database with 2 schemas, for the schemas 1 and 2 every objects are full granted

grant all on "object_name" to public

Each users have default role

"Resource","Connect"

.

As you can imagine, the security is done by the apps not the database.

We are planing to add an extra schema, this schema will only have BI tables.

I could set a role and add those tables in the role but unfortunatly it seems that it's not possible to connect with a selected role.

What i'd like to do is :
when the user log to access the database through the application he can access the whole database
when the user log for the BI he can only see the objects in the BI's schema

What could you suggest ?

Thanks

create table t (
  c1 int
);

grant all on t to public;

select privilege
from   dba_tab_privs
where  grantee = 'PUBLIC'
and    table_name = 'T';

PRIVILEGE           
ALTER               
DELETE              
INDEX               
INSERT              
SELECT              
UPDATE              
REFERENCES          
READ                
ON COMMIT REFRESH   
QUERY REWRITE       
DEBUG               
FLASHBACK

grant select any table to u;