5.7 Default User Accounts 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. 5.7.1 Default Users and Their Roles 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. | | 5.7.1.1 Scott and his tiger 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. 5.7.1.2 Demo, dbsnmp, and po8 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 don't believe that we are revealing anything sacred by telling you these passwords. We believe that Oracle has perhaps made these passwords too easy to figure out, and that, if you are aware that these accounts exist, you will be able to more effectively protect your database. | | 5.7.1.3 Example queries 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. 5.7.1.4 When to allow default users 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. 5.7.1.5 Checking on users and access 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. 5.7.2 Grants to "public" 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!  | 
