In the various versions of Oracle,
when you create a database, some user accounts are automatically
created by default. In every version, sys and
system are created. In the later versions of Oracle7
(beginning in version 7.1.6 or version 7.2), the user
dbsnmp is created automatically to support the Oracle
Enterprise Manager intelligent agent. As of Oracle8,
scott
(a standard account you can use for
demonstrations, testing, and training users in a known environment)
is automatically created and populated, as are other users based on
the cartridges you have selected for installation. Let’s take a
closer look at the default user accounts that are created and their
potential impacts on database security.
When a Personal Oracle8 database is created using version 8.0.3, several users are created by default:
The sys
user is always assigned the
password
change_on_install. sys
is
the heart of the Oracle system. You must work very carefully when
logged on as sys,
since you have the ability to
do severe damage to the database from this account.
The system
user always receives the password
manager. Because the sys
and system
passwords are pre-set, the DBA always
knows the initial passwords necessary to log on as either
system
or sys
to begin
interacting with the database. system
is the
account from which you initially create your tablespaces, rollback
segments, and users.
Always be sure to change the sys and system passwords immediately after the database is created.
As we
mentioned at the beginning of this chapter, the user and associated
schema area scott
has been around as far back as
version 2.0 of the RDBMS. In every release of the Oracle software,
there has been a demonstration package included that you installed to
create several tables and views. The standard tables are BONUS, EMP,
DEPT, and SALGRADE. The user scott
is always
created with a password of tiger
and is used as
Oracle’s default demonstration database. The
scott
account has always required the default
Oracle roles CONNECT and RESOURCE.
As we mentioned in Chapter 4, the RESOURCE role
enables the user to create objects in ANY tablespace of choice by
granting the system privilege
UNLIMITED TABLESPACE. This might not be
viewed by the DBA as a particularly favorable action for any user to
be able to casually perform, since a user having the UNLIMITED
TABLESPACE privilege can create an object on any tablespace in the
database. This privilege overrides any tablespace quotas that have
been imposed on the user to help control the areas to which a user
has access. A user with UNLIMITED TABLESPACE can even create objects
in the system
tablespace. The potential problem
with this ability is that the system tablespace could
become very fragmented, thus degrading performance. The only way to
rebuild the system
tablespace is to:
Export the database
Drop and recreate the tablespaces and primary users
Import the database
This process is time-consuming to perform and prevents users from
accessing the system to perform work until the database is completely
rebuilt. With the changes in the composition of the CONNECT and
RESOURCE roles, we recommend that if you are going to maintain a
scott
account on your system you build the
account with only CONNECT privilege.
In past releases, if a DBA wanted the scott
account to be in his database, he’d have to explicitly create
the account and assign privileges to it. You would then run the
scripts to build the demonstration tables within your database. Along
with the scott
account, several other accounts
were created. They were adams,
jones
, clark
, and
blake
.
In the Oracle8 database, because scott
is
prebuilt, we suggest that you log on to SQL*Plus using a privileged
account and issue the following:
REVOKE RESOURCE from scott;
The idea of having a known area in which users can learn to write SQL
or PL/SQL scripts or gain insights into using various other Oracle
products like Oracle Developer is very worthwhile in a development
database. But we recommend having a noninvasive account that has been
granted no more privilege than CREATE SESSION to test SQL*Net
configurations, etc., in a production system under a username which
is not known universally. We feel that there is no reason to have a
scott/tiger
account in a production environment
and recommend strongly that you remove this account from your
production environments. Access to this account will enable a user to
see any objects on which privileges have been granted to
public in your production system. This access could
produce a security breach.
With the advent of the Oracle Enterprise Manager and the use of
intelligent agents (see Chapter 13), the Oracle
CATSNMP.SQL
script was run from
CATALOG.SQL
to establish the environment in a
new database to support the intelligent agent. The account
dbsnmp is automatically created in databases from
version 7.3.X through 8.0.X, as we describe in the next section.
The next user of interest is
demo
. Can
you guess demo
’s password? If you guessed
demo, you are correct. demo
is also granted CONNECT and RESOURCE by default.
dbsnmp’s password is equally predictable —
dbsnmp
. (dbsnmp is
automatically created in version 8.0.X.) Not only is
dbsnmp granted CONNECT and RESOURCE, but this user
also receives the role SNMPAGENT to enable an intelligent agent to
connect to the database to perform tasks which have been remotely
issued through the Oracle Enterprise Manager utility. If you intend
to use the Oracle Enterprise Manager to remotely administer your
databases, you must leave this account available and intact.
Predictably, po8
’s password also mirrors
the username.
The po8
account
is granted the canned DBA role by default. We highly recommend that
you revoke this privilege! Any user can gain access to your database
using this account and immediately perform any actions he or she
wants as a DBA. You can remove the DBA privilege
by issuing the command:
REVOKE DBA from po8;
We easily learned about these accounts by logging on to the newly created database and issuing three queries. The first query, used to determine what users had been created, was:
SELECT username FROM dba_users;
Once the usernames had been obtained, the next two queries issued for
each username presented the roles and system privileges granted to
each user. The queries for scott
and their
output are:
SQL> SELECT * 2 FROM dba_role_privs 3 WHERE grantee = 'SCOTT'; GRANTEE GRANTED_ROLE ADM DEF ------------------------------ ------------------------------ --- --- SCOTT CONNECT NO YES SCOTT RESOURCE NO YES SQL> SELECT * 2 FROM dba_sys_privs 3 WHERE grantee = 'SCOTT'; GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- SCOTT UNLIMITED TABLESPACE NO
The first Oracle database created in your environment for each new version of Oracle should be carefully examined to determine the users and schema areas created.
In a learning environment, the access provided by Oracle for
demonstration purposes is wonderful. However, the conscientious
database administrator will most certainly want to change the
sys
and system
passwords
immediately to ensure the safety of the new database from anyone who
has even a passing knowledge of the default Oracle database
structure. If the database is to be used for development of
applications, the decision might be made to leave the
scott
and demo
accounts
available for basic database verification purposes since each user
schema contains basic Oracle demonstration tables. The
scott
user contains the BONUS, EMP, DEPT, and
SALGRADE tables, while the demo
area contains
ten “company” tables for Forms and Reports demonstration
purposes. The dbsnmp
and
po8
users do not have any tables. A
scott
-like account, with the default roles
removed and the CREATE SESSION privilege granted, is a very nice
mechanism for the DBA to have available to determine unsuccessful and
successful configuration of network connectivity without violating
security.
For a production database, however, be sure to remove the
demonstration accounts and all others not needed to support the
application(s), except for the dbsnmp
users. If
remote database maintenance will not be performed on a production
database, the dbsnmp user account may safely be
removed. However, if, at a later date, the decision is made to enable
an intelligent agent on the production node, this account will need
to be rebuilt.
Periodically, the DBA should produce a report of all users and their granted access. The following script could be used to show who had access to the database and what roles and grants they have assigned to them. The formatting shown for this report is based on the known usernames in this database, and you should adjust it for your database.
This script eliminates the users sys and system from the report to conserve space and allow us to concentrate on the accounts whose accesses might have changed:
SQL> COLUMN username FORMAT a15 SQL> COLUMN granted_role FORMAT a30 SQL> COLUMN privilege FORMAT a30 SQL> SELECT username, granted_role privileges 2 FROM dba_users, dba_role_privs 3 WHERE grantee = username 4 AND username NOT IN ('SYS','SYSTEM') 5 UNION 6 SELECT username, privilege privileges 7 FROM dba_users, dba_sys_privs 8 WHERE grantee = username 9* AND username NOT IN ('SYS','SYSTEM'), USERNAME PRIVILEGES --------------- ------------------------------ DBSNMP CONNECT DBSNMP CREATE PUBLIC SYNONYM DBSNMP RESOURCE DBSNMP SNMPAGENT DBSNMP UNLIMITED TABLESPACE DEMO CONNECT DEMO RESOURCE DEMO UNLIMITED TABLESPACE PO8 DBA PO8 UNLIMITED TABLESPACE SCOTT CONNECT SCOTT RESOURCE SCOTT UNLIMITED TABLESPACE 13 rows selected.
Oracle supplies a convention that enables developers or the DBA to grant a specific access to the entire group of users who will access the database in one command. By granting the privilege to public, anyone who accesses the database will have that privilege just by virtue of having a database account. In the Personal Oracle8 database we have been using in examples in this chapter, the default number of accesses granted to public is 375 separate and distinct privileges. Of the privileges granted to public:
33 are EXECUTE on a database package or utility
3 allow the ability to insert into, update, and delete from a table called DEF$_TEMP$LOB
1 allows deletion from the PSTUBTBL table
338 enable selection from various other objects within the database
The DBA_TAB_PRIVS view (shown in Chapter 4) contains a list of all of the tables and views that exist in the database and who has access to them. The DBA should select the values from the DBA_TAB_PRIVS view that have been granted to public and evaluate their impact on the database’s security.
There are times when a DBA may be placed in a pressure situation in which the solution might seem to be to grant to public a privilege in order to quickly give access to an object. Don’t give in to the pressure! The use of the ability to sweepingly grant access to a database object to all users through the public mechanism should be carefully weighed and evaluated on a case-by-case basis to ensure that an access is not given to someone who should not be able to obtain the information. The general rule of thumb should be to never grant access to public unless you’ve thoroughly investigated the effects of this action. Database security is important. It is worth a little bit of time and trouble to be secure!
18.220.16.184