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 | Privilege | | ALTER SESSION | | CREATE CLUSTER | | CREATE DATABASE LINK | | CREATE SEQUENCE | | CREATE SESSION | | CREATE SYNONYM | | CREATE TABLE | | CREATE VIEW | 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. 5.2.2.1 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. 5.2.2.2 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 5.2.2.3 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. 5.2.2.4 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.  | 
