262 Databases Demystified
• User Each database account is called a user. As with Microsoft SQL Server and Sybase, the user account may be authenticated externally (that is, by the operating system) or internally (by the DBMS). Each user is automatically allocated a schema (defined next), and this user is the owner of that schema, meaning it automatically has full privileges over any object in the schema. The following predefined users are created automatically when the database is created (not shown in Figure 10-2):
• The SYS user is the owner of the Oracle instance and contains objects that Oracle uses to manage the instance. This user is equivalent to the “sa” user in Microsoft SQL Server and Sybase.
• The SYSTEM user is the owner of the Oracle database and contains objects that Oracle uses to manage the database. This user is similar to the master database in Microsoft SQL Server and Sybase.
and Mgr125 schemas, which are owned by the Employees, Products, and Mgr125 users, respectively. Schema and user names are always identical in Oracle. Mgr125 is a workaround to a special challenge we face with Oracle’s security architecture, as discussed in the “Schema Owner Accounts” section that follows.
In Figure 10-2, note that the Mgr125 user owns no tables but does have some priv-ileges granted to it by the Employees and Products users. This is to work around a fundamental challenge with Oracle’s security architecture. If we allowed a database user to connect to the database using a user such as Employees or Products, the user would automatically have full privileges to every object in the schema, including in-sert, delete, and update against any table, and also the ability to create and alter tables without restriction. This is fundamentally the same issue as allowing use of the “sa”user or the DBO and DBA roles in Microsoft SQL Server and Sybase. The Mgr125 user mimics the behavior of the login with the same name as shown in Figure 10-1. With the right system privileges, we can prevent the Mgr125 user in Oracle from be-ing able to create any tables of its own.
You may have noticed the synonyms for user Mgr125 in Figure 10-2. A synonym is merely an alias or nickname for a database object. The synonyms are for the con-venience of the user so that names do not have to be qualified with their schema name. To select from the T1 tables in the Employees schema directly, user Mgr125 would have to refer to the table name as Employees.T1 in the SQL statement. This is not only inconvenient, but also can cause no end to problems if we ever decide to