The admonition (since the Oracle 6 days if my memory serves) for good DBAs is to never use the default roles when granting privileges to users in Oracle databases. Always grant users the specific privileges they require and never rely on ‘CONNECT’ or ‘RESOURCE’, because they generally grant more rights than you really want.
Oracle seems to have addressed this in 10g2, at least according to this this blog post and my observation. Now, granting the ‘CONNECT’ role is exactly the same as explicitly granting the ‘CREATE SESSION’ privilege and the ‘RESOURCE’ role has a more reasonable list of privileges.
I’d still be wary of them though, because through sheer laziness I granted these roles to a user in my development database and then got an ORA-01031 error when I tried to create a view. That’s right, the ‘RESOURCE’ role doesn’t (and as far as I can tell never did) contain the privilege to create a view. How strange.
So this advice would appear to still be valid – only grant those privileges that your users need and do it explicitly.