7.2 Types of Accounts
From the earliest releases of the Oracle database, a mechanism has been provided to let users connect to the database in order to perform tasks via user accounts. There are several different types of Oracle user accountsboth operating system and databasethat a company might implement:
Although they are created with the CREATE USER command, some accounts are used to house application schemas. These accounts own objects like tables, views, indexes, triggers, procedures, etc.
Another type of account is used by Oracle itself to enable the database engine work to be performed; these accounts are sys and system .
In later versions of the RDBMS, an account to enable the intelligent agent to connect to each database is automatically created during database creation. This account is dbsnmp and carries full DBA privileges.
Each application might need one or more accounts to enable work to be performed.
Each user in your system may require an individual Oracle account with specific privileges to enable the user to work with an application.
One or more accounts may be needed to enable one or more DBAs to perform database maintenance and duties .
Each account type must be considered and a decision reached on whether that account type will be used and how it will be set up and administered. In smaller organizations, there may be little need for some types of accounts discussed in this section. In very large organizations, there may be a need for more extensive divisions of database account types.
7.2.1 Administrator Accounts
The most obvious account type is the one used for database administration. A small company might have one person acting as system administrator, database administrator, and network administrator, while a larger company might have several people acting as administrators for specific areas. At your site, who will have access to the code area for installation and maintenance of the Oracle software? There may be one or more accounts that will need to be established for various administrative tasks as well as privilege setsboth operating system and database privilegesto perform the required tasks.
7.2.2 Security Manager
Who will serve as the security manager at your site? This person will be responsible for creating user accounts and monitoring user access and database security. In smaller organizations, the DBA will probably handle these tasks.
7.2.3 Application Manager
For each application, there will be developer accounts, application user accounts, and, possibly, application coordinator accounts. An application developer will need the ability to create objects and to code triggers, procedures, etc., while an application user might need only CREATE SESSION access. In larger companies, people are sometimes designated as "application coordinators." Their job is often to handle the administrative tasks for the specific database in which their application is housed. They may need all of the rights and privileges of a DBAboth at the operating system level and at the database level.
7.2.4 Network Manager
There may be a need for a network manager whose job is to administer the network products and oversee Names Servers and network configurations for Oracle databases. Will a separate person or people be needed for this task at your site? Again, in a small company, the individual DBA may be responsible for all Oracle database networking tasks.
7.2.5 Application Schema (User) Accounts
Oracle's mechanism for creating schemas in which application objects are stored is to actually create a form of "user" account, generally referred to as a "schema," which will probably require more privileges than a general user account. The security policy might state what specific system privileges an application account may or may not be permitted to have. In an Oracle version 8.0.3 database, there are 89 distinct system privileges available; these privileges are listed in Chapter 5. Few, if any, applications have a need to hold all 89 privileges. A security plan might list the privileges that are never to be given to any application. Almost all of the privileges that grant ANY should be carefully examined and evaluated for listing in the security plan. Here are a few very good examples of privileges that should never be granted to an application (without a really strong business case):
- ALTER/CREATE/DROP ANY INDEX/PROCEDURE /TABLE /TRIGGER, etc.
- BECOME USER
- GRANT ANY PRIVILEGE /ROLE
- EXECUTE ANY PROCEDURE
- UNLIMITED TABLESPACE
7.2.6 General User Accounts
More and more, applications are being provided with user validation present in the application itself. In these cases, a user account is created in the application. The user connects to the application and the application connects to the database on the user's behalf . The user's privileges are determined within the application. In cases where the application provides the user access control, the security plan might simply list what those accesses are and what user type should be granted which privileges.
In applications that rely on database authentication, a careful examination of what privileges a general user should receive may need to be identified and detailed in the security plan. In both Oracle's Financials Applications and the PeopleSoft HR packages, control of user access to the database is performed through the application's own internal utilities. Regardless of whether an application's security relies on the Oracle RDBMS or the application's own code, the security plan should clearly state what type of user accounts will receive each level of privilege in the system.
Oracle's HR/Payroll product, in releases 9 and 10 of the application, allows "secure users." Oracle recommends that the "secure users" account be one created with CONNECT and RESOURCE privileges (described in Chapter 5). However, a tablespace quota is made available to these accounts. With the CONNECT and RESOURCE roles both assigned to an account, the explicit UNLIMITED TABLESPACE privilege is given so a user with these roles can create objects in any tablespace with no overt quota assigned. With the CONNECT role assigned to an account and without a tablespace quota being assigned, the account is unable to actually create objects.