Today in #oracle, a question on privileges led me to discuss the Oracle roles CONNECT and RESOURCE. These two roles are often misused, especially RESOURCE. Jon Emmons highlights this in an old blog post. Important to note is that the CONNECT role contained a lot of dangerous extras up until 10gR2. The user on IRC confirmed this on his 10gR1 install, being able to create tables with just the CONNECT role. I suggested that he revoke CONNECT and just grant CREATE SESSION, which ended up working great for him. When/if his organization upgrades to 10gR2, then he can grant CONNECT, which (as Emmons notes) only grants CREATE SESSION anyway.

So by default, an Oracle database user doesn't even have the privilege to log into the database. That is what CREATE SESSION does. And even then, the user can't create objects (tables, indexes, etc.) in its own schema unless those privileges (or the system-wide "ANY" privileges) are gratned, plus the user will need to have some quota in the tablespace that it hopes to use.

This way of doing things is definitely a good one, IMHO. If you have many users connecting to an OLTP application schema, they don't need to create/drop objects. Only grant what they need to perform their tasks. In a lot of applications, for example, this probably means not letting users delete rows from the application tables (using an "ACTIVE" flag instead).

So, to summarize, CONNECT and RESOURCE prior to 10gR2 are a mess. 10gR2 lets you just grant CONNECT to allow users to connect, and then grant RESOURCE if they need to create their own objects (still may need quota privileges).