look at what connect conveys:
ops$tkyte@ORA920> select * from dba_sys_privs where grantee='CONNECT';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
CONNECT CREATE VIEW NO
CONNECT CREATE TABLE NO
CONNECT ALTER SESSION NO
CONNECT CREATE CLUSTER NO
CONNECT CREATE SESSION NO
CONNECT CREATE SYNONYM NO
CONNECT CREATE SEQUENCE NO
CONNECT CREATE DATABASE LINK NO
8 rows selected.
create table? create view? why all of that -- CREATE SESSION should be more then sufficient for a normal user.
How about resource then?
see
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:853427230099 <code>
for user_role_hierarchy
sys@ORA920> drop user a cascade;
User dropped.
sys@ORA920> grant create session, resource to a identified by a;
Grant succeeded.
sys@ORA920> grant select on dba_sys_privs to a;
Grant succeeded.
sys@ORA920> @connect a/a
a@ORA920> select distinct PRIVILEGE
2 from dba_sys_privs
3 where grantee in ( select * from sys.user_role_hierarchy )
4 /
PRIVILEGE
----------------------------------------
CREATE CLUSTER
CREATE INDEXTYPE
CREATE OPERATOR
CREATE PROCEDURE
CREATE SEQUENCE
CREATE SESSION
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
UNLIMITED TABLESPACE
10 rows selected.
unlimited tablespace? do you want joe or josephine develop to really create junk in SYSTEM at will? (granting resource ALWAYS conveys unlimited tablespace)Did you even know what grants you were giving with connect and resource?
And how about your DBA's. Are all DBA's created equal? No, they are not. Do you even know what DBA conveys? How much power that is? do they all need that? No -- they don't *need* most of it. The concepts of least privs (giving each person the LEAST privs they need) to do their job is always more secure then giving them the keys to the kingdom.
UPDATE 2017-09-17As of 10.2 the connect role only contains CREATE SESSION.
In 12.1 this expanded to include SET CONTAINER.