0594-0597

Previous Table of Contents Next

Page 594

SYSTEM

Like SYS, the SYSTEM account is created when the database is created. Whereas the SYS user owns tables and views that reference internal database information, the SYSTEM account owns tables that are owned by Oracle tools, such as SQL*Menu. This schema is used to install software products for most third-party tools.

SYSTEM has the initial password manager. Many textbooks (such as this one), in fact, show examples that use SYSTEM/MANAGER as the username and password. As with SYS, this password should be changed immediately after installation.

Although it is generally acceptable to use the SYSTEM account for day-to-day DBA operations, many DBAs do not. The SYSTEM account owns a number of important tables that drive some of the Oracle tools, applications, and so on.

Operating System Group : oracle

An oracle group can be created to provide access to the Oracle8 system to an authorized group of users. Adjust permissions appropriately on this group to allow users outside of the dba group to use Oracle8 Server utilities such as SQL*Plus and Server Manager.

Operating System Group: dba

Oracle provides a third method of authentication. It is very specific in nature that provides certain privileges and must be carefully monitored .

If a user is a member of the dba group, specified in the UNIX /etc/group file, he can do connect internal to the database. This is a privilege level used by the DBA that enables him to connect to the database as SYS with special privileges. The dba group is typically the default group of user oracle. It is necessary to do connect internal to perform startup and shutdown operations on the database.

WARNING

The umask of the UNIX /etc/group file should be at most 644 ”read/write owner, read group, read world ”and it should be owned by the root user. If anyone other than root can edit this file, he can set himself up as a privileged database user by placing his UNIX account in the dba group and issuing a connect internal.

A connect internal can be done only from within Oracle Server Manager or, in earlier releases of Oracle, SQL*DBA. This feature is not available from within other tools, such as SQL*Plus or SQL*Forms. As a member of this group, the DBA always has access to the database even if a co-DBA makes changes to the SYS or SYSTEM passwords. Although it is not so designed, the dba group ensures that the DBA can never be locked out of the database. For example:

Page 595

 % cat /etc/groups sys::3:root,uucp adm::4:root,adm daemon::5:root,daemon mail::6:root lp::7:root,lp tty::10: nuucp::11:nuucp users::20: nogroup:*:-2: dba::100:oracle % whoami oracle % svrmgrl SVRMGR> connect internal Connected. 

It is also possible to password-protect the internal connections. The specifics on this are covered later in this chapter in the section titled "Remote Passwords."

Operating System Group: oper

An optional group on a UNIX operating system called oper can be created to allow a specified group of users access to limited DBA privileges. This group allows flexibility for certain DBA commands to be executed but, at the same time, protects the use of connecting as INTERNAL. The Oracle commands that are associated with this role are

 STARTUP SHUTDOWN CREATE DATABASE ALTER DATABASE OPEN/MOUNT ALTER DATABASE BACKUP ARCHIVE LOG RECOVER 

Remote Passwords

Using the orapwd utility, a database internal password can be created for remote administration. That is, a user can connect as INTERNAL from a remote client by entering the appropriate password. The remote client can be another server or a PC equipped with Oracle client or third-party software. Database passwords are maintained in the ORACLE_HOME/dbs directory, the same location as the database INIT.ORA parameter files.

You run the orapwd utility (located in ORACLE_HOME/bin) as follows :

 $orapwd file=file_name  password=password  entries=maximum_users 

Page 596

where

• file_name is the name of the file to contain the password information.
• password is the remote database password to be used.
• maximum_users is the maximum number of users allowed to connect with the remote password.

The syntax for connecting as INTERNAL with a remote password is

 SVRMGRL> connect internal/password 

Object Versus System Security

At the heart of database security lies the concept that someone in a position to grant authority ”the DBA ”gives a user the specific ability to perform certain operations within the database. These privileges can be granted explicitly by issuing a grant command or implicitly by granting privileges to a role. According to Oracle, privileges fall into two distinct categories: system privileges and object privileges.

Object Security

Object security is responsible for defining the specific rights that a user has on a specific database object. The Oracle database itself implements a method of default security. A database user has full privileges on the database object that he owns. This user, in turn , can grant any and all privileges on these objects to another database user. Any user who attempts to access a database object to which he has not been granted access receives an error message. For example:

 % sqlplus susan Password: ...... SQL> select * from cat; TABLE_NAME                       TABLE_TYPE ------------------------------   ---------- PAYROLL                          TABLE POLICY                           TABLE SQL> select count(*) from policy;   COUNT(*) ----------        100 SQL> grant select on policy to mike; Grant succeeded. SQL> connect mike Enter password: ...... Connected. SQL> select * from cat; 

Page 597

 TABLE_NAME                       TABLE_TYPE ------------------------------   ---------- POLICY                           TABLE SQL> select count(*) from mike.policy;   COUNT(*) ----------        500 SQL> select count(*) from susan.policy;   COUNT(*) ----------        100 SQL> select count(*) from policy;   COUNT(*) ----------        500 SQL> delete from susan.policy; ERROR at line 1: ORA-01031: insufficient privileges SQL> delete from policy; 500 rows deleted. 

It is important to prefix the name of the table with the name of the database user who owns the table ”the schema. If this is not done, the database assumes that the table is owned by the current user's account. The use of synonyms alleviates a user from having to prefix a table to which he or she has access.

Access Rights

One of the key aspects of understanding object privileges is to understand what privileges are available to the database user. There are nine Oracle object privileges:

ALTER enables the user to alter a database table, including adjusting storage constraints, adding columns , and performing similar operations.
DELETE enables the user to remove rows from database objects by using the DELETE command from SQL.
EXECUTE enables the user to execute a stored procedure or package within the database.
INDEX enables the user to create a new index or to modify an existing index on a database table.
INSERT enables the user to create new rows in a database table.
REFERENCES enables the user to create a table that references characteristic information, by means of a foreign key, within another table.

Previous Table of Contents Next