create user joe_soap identified by password
/
grant create session, connect to joe_soap
/
Of course, CONNECT is a role which (prior to 10g) has a standard set of privileges granted to it, including CREATE SESSION as well as CREATE TABLE, CREATE SEQUENCE, etc. So there is no need to also explicitly grant CREATE SESSION.
The use of CONNECT role has long been discouraged because lazy DBAs and system designers - present company excepted - tended to grant it by default rather than considering which system privileges a user actually needed. As it happens, the CONNECT set of privileges is wrong for both developer accounts and regular user accounts in almost all cases.
CONNECT is wrong for developers because it is incomplete - it doesn't include more recent privileges like CREATE TRIGGER or CREATE TYPE. Also, developers ought to have the privileges granted to their accounts directly, so that they can build stored procedures on those privileges. CONNECT is wrong for users because most users should not be creating tables or other objects. Users who are application owners and need to create database objects should be granted the necessary minimum of system privileges.
In 10g Oracle officially deprecated the CONNECT role. It now just has the CREATE SESSION privilege. This is sensible but I know from the OTN forums that it catches out some people. These are the people who haven't read the Upgrade docs. So reactively lazy DBAs will be protected from one small consequence of their (in)action. Proactively lazy DBAs will probably just write a script to create a replacement role and grant all the privileges to that instead. But at least they've chosen that path.
6 comments:
you surely mean in 10gR2 ;-)
"Also, developers ought to have the privileges granted to their accounts directly, so that they can build stored procedures on those privileges."
I don't have a problem with this for DML privileges (including EXECUTE), even ALTER SESSION. Generally DDL privileges shouldn't need to be used in stored procedures so a role based privilege should be fine.
>> Generally DDL privileges shouldn't
>> need to be used in stored procedures
It's the "generally" bit. I agree that DDL is not the norm in application programs. However, sometimes it is necessary.
Also developers need to write programs which are not part of a proper application; for instance a "test data cleardown" routine which drives off the ALL_TABLES view and generates TRUNCATE table statements for a schema (or schemas). Now yes I could spool out the results to a script and then run that in SQL*Plus, but using EXECUTE IMMEDIATE in a PL/SQL loop is so much less pfaffing about. Also consider tools like UTLPLSQL, which works by building tables on the fly.
Of course, developers are only allowed to do such stuff in the development DB. And I work in an environment where everybody has their own instance, so the risks are minimal. But with a different setup you may want to play by different rules. Especially if you don't trust your developers.
Cheers, APC
>> you surely mean in 10gR2 ;-)
Thanks for the precision Laurent. As I'm still on lame old 9i I am not up to speed with when things were introduced in 10g.
Cheers, APC
Hi All,
I think the key message is that CONNECT was not intended for use originally buit was used extensively by Oracle and customers. I audit a lot of databases and everyone uses it.
The key principal to use is the "least privilege" principal. Its pointless to discuss grants via roles, direct, ddl, dml etc. each site is different and each site therefore requires privileges and grants designing for that site.
cheers
Pete
Yeah this is just in 10gR2, as I blogged about earlier. I wasn't aware that CONNECT as deprecated though, thanks for the tip!
Post a Comment