TIDY UP BY DROPPING THE PROFILE, THE ROLES, AND THE USERS

7. Tidy up by dropping the profile, the roles, and the users. Note the use of

CASCADE when dropping the profile to remove it from SALES, and on the

DROP USER command to drop their table as well. Roles can be dropped

even if they are assigned to users. The privileges granted on the table will be

revoked as the table is dropped.

connect system/oracledrop profile two_wrong cascade;drop role usr_role;drop role mgr_role;drop user sales cascade;drop user accounts;drop user webapp;

Database Security and Principle

of Least Privilege

The safest principle to follow when determining access to computer systems is that of

least privilege: no one should have access to anything beyond the absolute minimum

needed to perform their work, and anything not specifically allowed is forbidden. The

Oracle database conforms to this, in that by default no one can do anything at all, with

the exception of the two users SYS and SYSTEM. No other users can even connect—not

even those created by the standard database creation routines.

In addition to the use of password profiles, there are best practices that should be

followed to assist with implementing the least-privilege principle, particularly regarding

privileges granted to the PUBLIC account and certain instance parameters.

Public Privileges

The PUBLIC role is implicitly granted to every user. Any privileges granted to PUBLIC

have, in effect, been granted to everyone who can connect to the database; every

account you create will have access to these privileges. By default, PUBLIC has a large

number of privileges. In particular, this role has execute permission on a number of

PL/SQL utility packages, as shown in Figure 6-9.

You should always consider revoking the execution privileges on the UTL packages,

but remember that application software may assume that the privilege is there. Execution

privilege may be revoked as follows:

SQL> revoke execute on utl_file from public;

Some of the more dangerous packages listed in Figure 6-9 are

• UTL_FILE Allows users to read and write any file and directory that is accessible

to the operating system Oracle owner. This includes all the database files, and the

ORACLE_HOME directory. On Windows systems, this is particularly dangerous,

as many Windows databases run with Administrator privileges. The package is to

a certain extent controlled by the UTL_FILE_DIR instance parameter, discussed in

the next section.

PA R T I

Figure 6-9 Privileges granted to PUBLIC

• UTL_TCP Allows users to open TCP ports on the server machine for

connections to any accessible address on the network. The interface provided

in the package only allows connections to be initiated by the PL/SQL program;

it does not allow the PL/SQL program to accept connections initiated outside

the program. Nonetheless, it does allow malicious users to use your database

as the starting point for launching attacks on other systems, or for transmitting

data to unauthorized recipients.

• UTL_SMTP Written using UTL_TCP calls, this package lets users send mail

messages. It is restricted by the UTL_SMTP_SERVER instance parameter, which

specifies the address of the outgoing mail server, but even so you probably

do not want your database to be used for exchange of mail messages without

your knowledge.

• UTL_HTTP Allows users to send HTTP messages and receive responses—in

effect, converting your database into a web browser. This package also makes

use of UTL_TCP subprograms.

Always remember that, by default, these packages are available to absolutely

anyone who has a logon to your database, and furthermore that your database may

have a number of well-known accounts with well-known passwords.

EXAM TIP PUBLIC is a role that is granted to everyone—but when connecting

to the instance using the AS SYSOPER syntax, you will appear to be connected to

an account PUBLIC.

Security-Critical Instance Parameters

Some parameters are vital to consider for securing the database. The defaults are

usually fine, but in some circumstances (for which there should always be a good

business case), you may need to change them. All of the parameters described here