Torino, Italy
Security is one of the most important issues in data management and its importance is growing more and more in the Internet.
According to my personal experience in this field, security problems are not always considered as important by everybody and, although Oracle RDBMS offers several security features, most of them are not sufficiently known.
This paper presents several Oracle security tips and tricks and provides also a useful list of the most common errors to be avoided, in order to allow you to check and increase the security of your Oracle Database.
The latest version of this document can be found in https://www.md-c.it/meo.
In the global communication society the value and confidentiality of the information play a role always more important. Consequently, the data security is very important in managing and using an Information System.
Oracle is the most widespread RDBMS and has a lot of useful security features. But my personal experience in this field suggest how security problems are not always considered important by everyone and, although Oracle offers several security features, most of them are not sufficiently known.
The document is not focussed on the Kernel but it also presents points about Oracle tools, environment and applications with a particular interest on networking and Internetworking problems.
The paper is structured as follows: there is an Oracle RDBMS security overview that deals with all the security related Oracle features. You can skip it if You feel confident with basic Oracle security features.
The new Oracle 8 version introduces several new security features that are summarized in the following section.
The next section presents a lot of suggestion to increase the security of your system: Oracle Security Hint and Tips.
You can find other intresting information on Oracle Security in Secure Your Oracle DB by breaking into!
The Oracle RDBMS provides a complete environment for application development and data access towards a relational Database.
The Oracle Security System allows a really sophisticated control on data and security. Furthermore it is possible to plan a wide range of strategy definition.
For all the samples the referred Oracle release is the Version 7.3. Oracle 7.3, because its diffusion is, at the present time, more widespread that the recent Version 8.0. Some examples contain commands and structures typical of the Unix Environment; similar considerations are valid in others operating systems.
DBAs, programmers, end-users
Being secure, for a system, is mainly a question of users’ willpower: from the DBA down to the final customer. Each of them must be involved and accept the importance of a correct security policy.
If the human aspect is not addressed correctly, any solution to the technical aspects is useless.
Users should be involved in every aspect of system security: in the users and logical roles definition, in the security related operatives, ...
Oracle Users
In order to work on the data stored in an Oracle RDBMS, it is necessary to establish a connection specifying a user name and a password. Even for the passwords of the Oracle users you should keep in mind the general indication of defining good passwords.
The password of an Oracle user can be validated from Oracle itself or from the operating system; in the latter case it is not necessary that the Unix user specifies a password when he connects to Oracle.
After the connection is done, the user can access to his own objects, to granted objects and can perform grant activities.
Some Oracle users are granted RDBMS administrative rights. Those users can work on all the RDBMS structures, can create users, grant and revoke rights.
During the installation phase two administrators are defined: SYS, owner of the main system tables and SYSTEM, owner of the main system views.
Granting privileges
Granting and revoking privileges are easy using SQL statements. With these statements you can assign a user or a role some specific rights. With the WITH GRANT OPTION clause You can also give the right to grant privileges.
Grants can be related to object privileges or to system privileges.
Objects Privileges
The most interesting part in the security management of a relational Database relates to privileges assigned to objects.
A user or a role can be assigned privileges on database objects (usually assigned by the object owner).
Those privileges refer to tables, views, sequences, and procedures/Functions/Packages and Snapshots as summarized in the following table.
| Table | View | Sequence | Procedure | Snapshot | |
| Alter | X | X | |||
| Delete | X | X | |||
| Execute | X | ||||
| Index | X | ||||
| Insert | X | X | |||
| Reference | X | ||||
| Select | X | X | X | X | |
| Update | X | X |
You can assign different System privileges to a user. There are a lot of System privileges and they allow in depth definition of the actions granted to the user (connect, create table, create table on different schemas, and so on).
Generally the DBA is in charge of granting those privileges.
Some examples following:
In order to simplify a large number of users’ management, it is possible to create a role. This is a useful practice in order to assign rights to the role and so to assign that role to one or several users. In that way one change can be done in one single point without overhead for all the users involved.
A RDBMS defines at least the following roles:
Of course that roles can be modified in order to satisfy particular security needs.
There is a particular "role" that is always assigned to each PUBLIC user. To grant a right to PUBLIC means to grant the right to each user that connects to the database.
During the creation a profile is assigned to each user. The profile allows some limits to be defined for the user in the use of the resources (i.e. CPU-time per session). It is also possible to limit the number of concurrent sessions per user, which is a feature often useful for the security.
A big worry for a DBA is the space usage. Oracle allows to limits the space that a user can utilise, by defining a quota.
A user must be explicity granted a space quota for each tablespace he can use.
It is possible to use logical views on tables in order to mask the data and the structure in the RDBMS.
With a view you can create a query on the data which can change the structure of the involved tables (without showing columns or calculating imaginary columns) and showing only the data wanted (using the right WHERE clause). In the conditions (of the SELECT) can be used the USER in a way that each view can be customised.
If the view has been created with the WITH CHECK OPTION clause, the control contained in the WHERE clause is performed during the data input as well.
It is possible (starting from version 7.0) to define triggers on tables. In this way when a particular event (i.e. INSERT) occurs a specific action is executed (implemented in PL/SQL).
By programming a trigger properly it is possible to monitor all the activities done on a table. It is also possible to avoid that some actions are done when specific conditions do not occur.
Anyhow, it should be noticed that triggers are more used for the definition of business-rules on data rather than to create security controls.
With stored procedures and packages you can store in your database programs written in PL/SQL.
There are several security related features in the Oracle packages and stored procedures.
Users granted to execute a package don't need to have the rights to access the object the package uses. The package owner rights will be used during the package execution.
This means that the users granted the execution of a package or the stored procedure need not to be granted on undelying objects. This way the end user can only access to the data thought the package usage. This offer an hight degree of data hiding and security.
If a package needs a specific right to perform an action this right should be granted explicitly to the package owner. Granting rights to a role, which is granted to the package owner, is not enough.
It is very common the usage of Oracle in client/server systems or in distributed environment. In those configurations it is possible that the communication channel used is not secure. With the version 7.1 of Oracle and SQL*Net 2.X it is possible to use the DES cryptography of login and username (using the parameters). Previous versions of the system or of the communication software passed this information in clear and thus can be used only upon secure communication channels (however, we must also note that it is difficult to find the password analysing network communications).
From version 7.1 of Oracle it is possible to administer the database remotely. For security reasons, in case you have to use the distributed administrative features of Oracle, it is possible to activate another password checking level.
One of the most intresting capability of SQL*Net is to create database links. This allow to access data from different distribuited databases. With SQL*Net v.2 this can be used for both data selection and distribuited transaction.
Be careful on creating database links and on their grants. DB links are open doors on databases connected through the network.
The Advanced Network Option adds several security features the standard SQL*Net implementation.
Data encryption, digest messages and authentication are some of the features offered.
With Oracle it is possible to activate an auditing functionality on the RDBMS activities. The auditing collects all the activity required and stores the information on a specific table.
There are three different control levels:
It is also possible to register an event referring to some conditions like: success/failure, monitored user.
Each record of the audit contains, among the others, the following information:
In order to answer to the TCSEC requirements (DoD Orange Book specifications) a specific version of Oracle has been developed: Trusted Oracle. This version provides all the necessary functions for a trusted RDBMS system.
The version is available only on operating systems TCSEC compliant.
Oracle has completed several security evaluations. The results are reported in the following table:
|
Product |
Evaluation Criteria |
Rating |
|
Oracle7 |
TCSEC |
C2 |
|
Oracle7 |
European ITSEC |
F-C2/E3 |
|
Trusted Oracle7 |
TCSEC |
B1 |
|
Trusted Oracle7 |
European ITSEC |
F-B1/E3 |
A good and complete backup strategy is an important part of the security issues of a system.
The Oracle RDBMS allows logical (export) and physical backup. It allows to store the transactions occurred on specific files (log archiving).
A good backup strategy is realised with the right use of the different techniques.
During installation and manteniance operation several security related decision must be taken.
Installation is a good time to enforce
security. Later can be too late.
Applications
The Oracle RDBMS can be used from a wide range of applications: from very simple SQL scripts to very complex distributed applications.
Very often the applications defines their own level of "application security", in which they define which users can utilise menus or functions.
The Oracle RDBMS, from an operating system point of view, is an application installed on a normal system user. Therefore, all the indications related to a normal user hosted in the system are valid. In the following part we report the main indications regarding the Unix operating system.
The programs and the configuration files are installed under a particular user (normally user oracle, group dba) and the access to that user must be reserved to the DBA. In order to administer a non active database (i.e. startup) the Unix user oracle and an Oracle predefined user internal must be used. This access is allowed only to the Unix oracle user.
The database is stored on structures called datafile. This datafile can be a file of the Unix filesystem or disk partitions (raw devices). In both cases the ownership of the file must be reserved to the oracle user and no one else must have access to that file.
Oracle is almost always used in networked environment.
In most cases an RDBMS Oracle is used on tcp/ip networks. We have to remember that the tcp/ip does not offer encryption and authentication functions. It is thus necessary to protect the communications both at the logical and at the physical level.
The recently released Oracle RDBMS version 8 intrduces new important security features.
Oracle offers the Oracle Security Server (OSS) based on the standard X.509 certificates. Net8 and Oracle RDBMS are fully integrated with this Security Server allowing a global user, client and server authentication. An Oracle user can be designed to be autenticated with the SQL clause CREATE USER ... IDENTIFIED GLOBALLY.
Oracle8 user profiles are very rich and there are several enanchments. Several security features that on Oracle RDBMS v.7.X should be implemented in applications can be easly configured on user profiles (eg. FAILED_LOGIN_ATTEMPTS, PASSWORD_LIFE_TIME, PASSWORD_REUSE_TIME, PASSWORD_REUSE_MAX, PASSWORD_LOCK_TIME, PASSWORD_GRACE_TIME, PASSWORD_VERIFY_FUNCTION).
The new trigger INSTEAD OF can be used to update view allowing an hight data hiding. This trigger can be used on non updateable views in order to specify which action should be performed on INSERT/UPDATE/DELETE. The action is a PL/SQL function allowing a very powerful data management on views.
Last but not least the Object Option allow the creation of objects that are accessed by methods. This allow a very hight grade of data hiding and security. Of course a complete different approach must be used in the design and implementation of the whole system.
The following are hint and tips useful to increase Oracle RDBMS and tools security. Since there are different security levels, we put in italic the strictest requirements.
It's important that this steps are performed when the database is initially installed, then periodically and allways when a security break has been discovered.
DBAs, programmers, end users
Mantain the list of all users and their duties. Get the phone numbers too!
All users should be warned of the security importance. Probably this is the most important item.
A specific team should be chosen in order to verify Oracle and system security.
Complete security standards should be designed e verified for all production databases.
Oracle Users
Change all the default passwords (SYS/CHANGE_ON_INSTALL, SYSTEM/MANAGER, OBK, WWW_ADMIN, ...).
Change dba passwords periodically.
Assign good password for new users (many DB design tools and DBA assistant programs suggest username==password). This is very important for DBAs and schema owners.
Define personal users.
Revoke connect from SCOTT/TIGER and all the other demo users (eg. WWW_USER in OWS).
Make users change their password periodically.
Periodically check end user passwords. Look for password equal to the username and for easy to guess passwords (eg. customize and use the password guessing program chorpass).
System and Object Privileges
Avoid granting privilege like SELECT/INSERT/.. ANY TABLE to end-users.
Use the minimum privilege rule, that is grant only the minimum necessary for applications.
Don't grant privileges to PUBLIC. Use roles.
Avoid granting privileges to specific users. Use roles.
Periodically check system and object privileges with SQL scripts (i.e. dethtml.sql).
Don't grant INSERT/UPDATE/DELETE rigth to end users. Grant INSERT/UPDATE/DELETE rights only to batch users (eg. OPS$) or applications working with stored procedures or packages.
Don't give users direct access to the RDBMS. The end user should work with it only by application filters.
Roles
Use roles to simplify the design of object security in the system. A clear role and grant design is very important for the security of the system.
Carefully document users roles, rights and activities.
Review and eventually customize the standard roles (CONNECT, RESOURCE, DBA).
Profiles
Use profiles to limit the following parameters: SESSION_PER_USER, IDLE_TIME, CONNECT_TIME.
Quotas
Use specific tablespaces for different users' groups.
Use quotas to limit users' space.
Views
Using carefully designed view allow good data hiding. Use them!
Triggers
Triggers are generally used for implementing business rules on data. Anyway they can be used for logging purposes too. They can be used for logging activities on very important tables.
Stored Procedures and Packages
Using stored procedures and packages offers both good performances and data hiding. Use them!
SQL*Net
Set trace to user level (at least).
Doesn't allow remote management of the listener.
Set ORA_ENCRYPT_LOGIN on clients and DBLINK_ENCRYPT_LOGIN on servers to avoid sending unencrypted passwords on the network.
Change the listener port number; avoid the the well known port numbers 1521, 1525, 1526.
Distribuited DataBase
Don't put password in database links.
Don't use database links.
Advanced Network Option
The Advanced Network Option gives important security features (ie. data encryption). Use it when there are strictly security requirements.
Auditing
Use Oracle auditing. Check at least: connections, DBA operations and activity on the AUDIT_TRAIL itself. Very important: avoid auditing everything. It's not useful, degrades performance, can dramatically increase the sys.aud$ table size and exhaust the SYSTEM tablespace.
Audit most important tables and users.
Use scripts to check periodically Your database (i.e. audit.sql).
Backup
Define, use and check a complete backup strategy. Use both logical and physical backups. Use log archiving.
Be careful to protect the backups files and devices too.
Installation and Manteniance
Change SYS and SYSTEM password during the installation.
Use difficoult to guess password for the many usernames the installation process prompts (eg. listener admin, ...).
Don't install the Oracle Web Server demos. The WWW_DBA demo user is very powerfull and dangerous.
As a normal manteniance step monitor system and RDBMS usage. Periodically check system alerts, audit records, ... Usefull scripts can be found in https://www.md-c.it/meo .
Don't install DEMO users and tables (eg. SCOTT/TIGER).
During manteniance operation start the RDBMS in restrict mode and turn off the listener.
Applications
Never leave user/password in scripts or program files, use OPS$ users.
Don't use user and password as program parameters, use OPS$ users.
Carefully protect user/password information if they are stored in files.
Encrypt user/password information if they are stored in files.
Don't require specific Oracle users. Allow different Oracle users for different end users.
Check the "application security" sometimes it breaks the inner Oracle security for a more application oriented one!
Use only applications from a sure source.
Don't use DDL or reserved SQL statements in applications.
Don't give SQL escapes to the application user.
Never leave user/password in files, use OPS$ users instead (eg. batch processing).
Check side effects of an application looking for Trojan horses.
Check the strings contained in the applications looking for backdoors.
Don't grant direct access on Oracle to end users. End users should use only application driven access to data. Use one-way criptography algorithm to hide username and passwords.
Don't use dynamic SQL in applications.
Operating System
Carefully protect DBA users (oracle user and dba group on Unix) using good password and file premissions.
Check Oracle datafile permissions.
Check the host operating system security. It's useless securing an Oracle database if anyone can connect as root on the system. There are many good books, papers and tools for securing systems (a lot of them are free on Internet).
Don't grant direct access on the Operating System to end users.
Network
Record the IP-address of the Oracle users in the /etc/hosts file in order to easy check them.
Your system must be protected from the network. Physically protect the network from sniffing and spoofing. Use firewall for better protection (and logging). Most recent firewalls support Oracle SQL*Net.
Check the network security with programs and tools (a lot of them are free on Internet).
Use VPNs to encrypt the network traffic.
Use authentication services to check user and client identity.
Oracle 8
Use user profiles to set limits for FAILED_LOGIN_ATTEMPTS, PASSWORD_LIFE_TIME, PASSWORD_REUSE_TIME, PASSWORD_REUSE_MAX, PASSWORD_LOCK_TIME, PASSWORD_GRACE_TIME, PASSWORD_VERIFY_FUNCTION.
Use not updateable view and the INSTEAD OF trigger to completely hide tables from users.
Use globally authenticated user by the Oracle Security Server (OSS).
Database security is very important. The Oracle RDBMS offers several security features but they are often underused.
With some experience, some security tools or scripts and a lot of awareness the security of Oracle Databases can be very good.
The easy to use tips presented in this paper help You to increase Your Oracle DBMS security.
Title: Pratical Security in Oracle
Date: 2 Feb 1998
Version: 1.4.1
Author: mail@meo.bogliolo.name