5.2 The CONNECT Role
The CONNECT role was originally intended to allow users to log in to the database. In versions of Oracle before version 6, the CONNECT privilege enabled a user to create a session in a database and allowed little else. In version 6, the CONNECT role was given the privileges shown in Table 5.1 and allowed the user to connect to the database, to create views, synonyms, and database links, and to perform table or user exports. The CONNECT role now conveys far more privileges than the original CONNECT privilege did. The most critical system privilege allowed by this role is CREATE SESSION; without this system privilege, the user cannot log on to the database.
5.2.1 System Privileges for the CONNECT Role
The system privileges for the CONNECT role, shown in Table 5.1, are the same for versions 7.X and 8.0 of the database.
Table 5.1. CONNECT Role System Privileges
CREATE DATABASE LINK
Only the CREATE SESSION privilege is required for a user to log in to the database. The remaining privileges, except for CREATE SYNONYM, are generally not required by a user whose primary interface is through applications.
5.2.2 Problems with the CONNECT Role
A user who has been granted all of the privileges listed in Table 5.1 can actually create problems when using an interactive interface like SQL*Plus. Assume that user mary is the production schema owner. Also assume that user ralph has SELECT privilege on mary 's employee table, has enough quota granted to him on a tablespace to create a table, and does something like this:
SQL> CREATE TABLE employee 2 AS SELECT * 3 FROM mary.employee; Table created
This creates ralph 's copy of the employee table and copies all of mary 's data into it.
22.214.171.124 The situation begins to compound
Next, ralph performs some analysis queries, perhaps even makes some "what if " changes to the data, and eventually logs off the system. Later, ralph runs an application that uses the employee table. The search precedence looks at the owner's objects first. Since ralph has an employee table, his table (rather than the mary.employee table), will be used by the application. Since ralph has changed the data in his private copy of the employee table, and also since no other user can get to that copy (yet), incorrect information will be returned if ralph attempts to do any work. The incorrect values will not be known to the application since all objects being used have the correct structure.
126.96.36.199 Getting further into trouble
To further complicate the issue, suppose that ralph tells ed he has explored some interesting "what if " situations and asks ed to take a look at the data. ralph grants access to his table with the command:
SQL> GRANT ALL ON employee 2 TO public ; grant succeeded
ed now connects to his own account and issues the following command:
SQL> CREATE SYNONYM employee 2 FOR ralph.employee; synonym created
188.8.131.52 The plot thickens
Now there is a real problem. When both ralph and ed run applications that require the employee table, the ralph.employee copy, not mary.employee, will be used. Also, because ralph granted ALL to public on his employee table, ed or anyone else could physically alter the table structure, probably causing the application to fail. Finally, because ralph granted the privilege to public, any user on the system can access his employee table as well as create a synonym for it. So, unless these two object owners subsequently drop their objects ( ralph 's table and ed 's synonym), the system could rapidly fall apart.
184.108.40.206 Name of the role
Another problem with the CONNECT role is its misleading name. In most implementations of roles, the role name conveys some intelligence regarding its usage within the system. The CONNECT role does not do this.