In computer security work, the principle of Least Privilege states that users should have the minimum privileges needed to do their jobs. Oracle has made a small change in Database 10g Release 2 (10gR2) to be more consistent with this principle: It has changed the meaning of the CONNECT role.
Initially, the Oracle database had only three privileges: CONNECT, RESOURCE, and DBA. With the CONNECT privilege, you became an end user of the database—you could log in to the database and create and use common objects like tables and views. With the RESOURCE privilege, you could do more advanced, development-related things like create stored procedures. And finally, with the DBA privilege, you were the database administrator and could do pretty much everything.
In version 7, Oracle introduced the security model we have now, with separate privileges for the various SQL statements and multiple object privileges for each type of object. As a transitional measure, Oracle also added three built-in roles with the same names, and approximately the same privileges, as the earlier built-in privileges. Developers got into the habit of just granting the CONNECT role (or CONNECT and RESOURCE roles) to new users who are created. In fact, scripts that automate user creation often do just that.
It's time to re-examine those scripts, because in 10gR2, the CONNECT role does significantly less than in previous versions. In fact, the only privilege it contains is CREATE SESSION, which is required to log in to Oracle. All other privileges must be explicitly granted, either directly to the user or via some other role.
Because CONNECT, RESOURCE, and DBA were only meant to be transitional, and it's been many releases since the original 7.0 in which they appeared, Oracle is gently nudging developers toward the stated best practice of creating application-specific roles that closely match the Least Privileges required for each application.
Miss a tip?
Check out the Oracle archive, and catch up on our most recent Oracle tips.
Bob Watkins (OCP, MCDBA, MCSE, MCT) is a computer professional with 25 years of experience as a technical trainer, consultant, and database administrator. Visit Bob's site.
