Tuesday, July 10, 2007

Don't only CONNECT

What's wrong with this code?

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.

Labels:

9 Comments:

Anonymous Laurent Schneider said...

you surely mean in 10gR2 ;-)

10 July 2007 09:40:00 GMT-7  
Blogger Gary Myers said...

"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.

10 July 2007 15:50:00 GMT-7  
Blogger APC said...

>> 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

11 July 2007 01:08:00 GMT-7  
Blogger APC said...

>> 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

11 July 2007 01:09:00 GMT-7  
Anonymous Pete Finnigan said...

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

11 July 2007 03:04:00 GMT-7  
Blogger Don said...

Yeah this is just in 10gR2, as I blogged about earlier. I wasn't aware that CONNECT as deprecated though, thanks for the tip!

11 July 2007 11:19:00 GMT-7  
Anonymous zantac said...

companies marketing mineral makeups and also get the best bargains in mineral makeup you can imagine,
find aout how to consolidate your students loans or just how to lower your actual rates.,
looking for breast enlargements? in Rochester,
homeopathy for eczema learn about it.,
Allergies, information about lipitor,
save big with great bargains in mineral makeup,

change edition interviewing motivational people preparing second
,

interviewing motivational people preparing second time
,

interviewing people motivational preparing for a second time
,

black mold exposure
,

black mold exposure symptoms
,

black mold symptoms of exposure
,

free job interview questions
,

free job interview answers
,

interview answers to get a job
,

lookfor hair styles for fine thin hair
,

search hair styles for fine thin hair
,

hair styles for fine thin hair
,

beach resort in the philippines
,

great beach resort in the philippines
,

luxury beach resort in the philippines
,
iron garden gates, here,
iron garden gates,
wrought iron garden gates
, here
,
wrought iron garden gates
,
You: The Owner's Manual: An Insider's Guide to the Body That Will Make You Healthier and Younger
,
eat eating mindless more than think we we why
,


texturizer,
texturizers here,
black hair texturizer,
find aout how care curly hair,
find about how to care curly hair,
care curly hair,
lipitor rash,
lipitor reactions,
new house ventura california,
the house new houston tx,
new house washington dc,
new house pa philadelphia,
san antonio tx house new,
house new pa philadelphia,
new house washington dc,
new house ventura california,
the house new houston tx,
house new san antonio tx,
the house new houston tx, that you are looking for,
new house ventura california, you need to buy,
new house washington dc,
house new pa philadelphia,
new house san antonio tx,

hair surgery transplant
,

air filter allergy
,

refurbished dell laptop computers
,

hair surgery transplant
,

air filter allergy
,

refurbished dell laptop computers
,

hair surgery transplant
,

air filter allergy
,

refurbished dell laptop computers
,

chocolate esophagus heartburn study
,

chocolate esophagus heartburn study
be informed,

digestion healing healthy heartburn natural preventing way
,

digestion healing healthy heartburn natural preventing way
,
sew skirts, 16simple styles you can make!,
sew what skirts 16 simple styles you,
rebates and discounts on sunsetter awnings,
sunsetter awnings discounts and rebates,
discount on sunsetter awnings


truck and bus tires 12r 22.5, get the best price,
tires truck and bus 12r 22.5 best price,
tires truck bus tires12r 22.5 best price,
plush car seat strap covers,
car seat strap covers,plush,
car seat strap, plush covers,
oscoda voip phone systems, the best!,
oscoda voip the phone system,
oscoda voip phone systems,
exterior iron gates,
oriental wrought iron gates,
powder coated iron garden fencing,

9 February 2008 10:26:00 GMT-8  
Anonymous iron gates said...

black mold exposure,
black mold symptoms of exposure,

wrought iron garden gates,
your next iron garden gates, here,

hair styles for fine thin hair,
search hair styles for fine thin hair,

night vision binoculars,
buy, night vision binoculars,

lipitor reactions,
lipitor reactions,

luxury beach resort in the philippines,
beach resort in the philippines,

homeopathy for baby eczema.,
homeopathy for baby eczema.,

save big with great mineral makeup bargains,
companies marketing mineral makeups,

prodam iphone praha,
Apple prodam iphone praha,

iphone clone cect manual,
manual for iphone clone cect,

fero 52 binoculars night vision,
fero 52 night vision,

best night vision binoculars,
buy, best night vision binoculars,

computer programs to make photo albums,
computer programs, make photo albums,

26 March 2008 14:57:00 GMT-7  
Blogger Jerry said...

cheap wedding gowns
discount bridal gowns
China wedding dresses
discount designer wedding dresses
China wedding online store
plus size wedding dresses
cheap informal wedding dresses
junior bridesmaid dresses
cheap bridesmaid dresses
maternity bridesmaid dresses
discount flower girl gowns
cheap prom dresses
party dresses
evening dresses
mother of the bride dresses
special occasion dresses
cheap quinceanera dresses
hot red wedding dresses

18 June 2009 09:24:00 GMT-7  

Post a Comment

<< Home