DB version : 12.1
I was already logged in as SCOTT user from Toad.
As sys, I granted the following roles to SCOTT user.
SQL> grant CUST_ROLE_R to SCOTT; --- This role includes SELECT privile on JOHN.DEPT_DETAILS table shown below.
Grant succeeded.
SQL> grant CUST_ROLE_W to SCOTT;
Grant succeeded.
But, the privileges in the role did not take effect for the already logged in SCOTT user.
When I was running the following , I was getting "ORA-01031: insufficient privileges" error.
select * from JOHN.DEPT_DETAILS where rownum < 5
After wasting around 45 minutes getting ORA-01031, I logged out as SCOTT user from toad and logged in back again.
Now the ORA-01031 issue is resolved and above query is working !!
So, is this the expected behaviour ? Is it documented ?