14th December, 2006

Dont Use Default Roles in Oracle Databases

Filed under: database,oracle — admin @ 9:39 am

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.

4 Comments

  1. This is true of Sybase as well (yes they are still around). I used to work on a product for security compliance management which looked for stuff like this. It also looked for the old scott/tiger username and password. The old Oracle (pre 2000) would come stock with that root admin my default. There were some undisclosed breakins at Amazon, Pets.com, and Wells Fargo due to that one.

    Comment by Doug Napoleone — 14/12/2006 @ 2:51 pm

  2. This is true of Sybase as well (yes they are still around). I used to work on a product for security compliance management which looked for stuff like this. It also looked for the old scott/tiger username and password. The old Oracle (pre 2000) would come stock with that root admin my default. There were some undisclosed breakins at Amazon, Pets.com, and Wells Fargo due to that one.

    Comment by Doug Napoleone — 14/12/2006 @ 2:51 pm

  3. Does granting RESOURCE still grant the UNLIMITED_TABLESPACE privilege in 10g or have they finally sorted that out as well?

    Cheers, APC

    Comment by APC — 09/01/2007 @ 7:26 pm

  4. Does granting RESOURCE still grant the UNLIMITED_TABLESPACE privilege in 10g or have they finally sorted that out as well?

    Cheers, APC

    Comment by APC — 09/01/2007 @ 7:26 pm

RSS feed for comments on this post.

Sorry, the comment form is closed at this time.

Powered by WordPress