Security, Addressing the Connect Role Change - Part 1

Dear Readers,

My name is Franz Devantier, creator of this blog.  I am an Oracle Certified Professional (OCP DBA 11g) Security DBA.  I will be sharing with you the basic duties of an Oracle DBA, and also some of the undocumented, and not so well known tasks. 

Security, Addressing the Connect Role Change  -  Part 1

The “CONNECT” role was introduced in Oracle Database version 7.  The CONNECT role originally had the following privileges granted to it:

·         Alter Session

·         Create Session

·         Create Cluster

·         Create Synonym

·         Create Database Link

·         Create table

·         Create Sequence

·         Create View

Starting from Oracle Database 10g release 2 or 10.2, the CONNECT role only had one privilege granted to it:

·         Create Session

Connecting to the database does not require all those privileges that were previously granted to the CONNECT role.  All that was needed was the Create Session privilege, in order to be able to connect to the database.  Having such a limited CONNECT role, enables customers to enforce good security practices far easier than before.

The reason for this change to the CONNECT role is embedded in the principal of least privilege.  Each user should only have those privileges that are relevant for the tasks that they need to carry out.  The principal of least privilege mitigates risk by limiting privileges.  The user is able to do what is required of him/her, but cannot perform operations that are outside of their scope.  Applying this principal will minimize data being altered in inappropriate ways, both unintentionally and maliciously.

We will now look at the ways that Applications and Users are affected by this change to the CONNECT role.

Affect on Applications

When you upgrade your Oracle Database installation from a previous version of Oracle to version 10.2, then the CONNECT role is automatically updated.  The CONNECT role will now only have the Create Session system privilege.  Most applications will not have a problem with this, especially applications that have already been installed.  With applications that have already been installed,  no new tables, views, sequences, synonyms, clusters, or database links need to be created by the user with the connect role.

However, if your application is of such a nature, that the application user in question will be creating tables, views, sequences, synonyms, clusters, and database links;  then you could potentially have a problem with the application.  The failure message will be something like  “Insufficient Privileges”

To address this problem, of not having enough privileges to perform the application tasks;  there are a few solutions:

·         Grant the needed privileges directly to the user

·         Create a custom database role, and grant the privilege to this role, and then grant the role to the user in question.

·         New Database installations will potentially have the same problem of insufficient privileges, and so the grants, or Role grants must be migrated to the new database, or created on the new database; in order for the application to work properly.

Affect on Users

General users, may not have all the privileges that they need.  They may need additional privileges either granted directly or through roles.  By default users will be able to perform alter session operations like:  SQL> alter session set NLS_TERRITORY = FRANCE;

However there is a restriction on the “SQL> Alter session set events;” command.  Which is good, because setting events is a very powerful function.

·         Diagnostic events are build into the Oracle Kernel

·         Events should be used in a Sandbox environment, because they can cause irreversible data corruption, instance outages and bizarre behaviour.

·         You can activate events in a production environment under the instruction of Oracle support

Application Developers, will need additional grants, either directly or through a role to enable them to carry out the typical tasks of application developers; like creating tables,  views, sequences, synonyms, clusters, and database links.

Traditional client/server applications will not be affected by the limited CONNECT role.  However if these client /server applications use dynamic SQL to create private synonyms or temporary views or tables, then these applications will require specific grants or custom roles to be granted to them.

Franz Devantier,

Need a database health check, or a security audit?

devantierf@gmail.com

Income stabilizer (win-win opportunity)

Join the carefully selected and tested cash-flow generating program below to potentially create a long-term residual or annuity type income enhancer for yourself.

Traffic Wave - Free Report:  The report will give you all the information you need to start making a nice long-term residual income stream for yourself.