Oracle database security and PL/SQL programming

Many PL/SQL developers believe that only database administrators and security specialists should take care of security. Indeed, some security aspects (e.g. user and privilege management) should be taken care of by administrators.

But it would be a serious mistake to believe that PL/SQL developers do not care about this topic at all. First of all, security is not a problem that can be solved once; it is a means of achieving a goal that must be constantly remembered. Secondly, many administrators focus their efforts on protecting the database as a whole, rather than programming the security features of a particular application.

You have probably heard that the reliability of a system is determined by the reliability of its weakest link. This principle is equally applicable to application security.

Every element of the infrastructure – application, architecture, middleware, database, operating system – contributes to the overall security of the infrastructure, and security failure of one component poses a threat to the entire system.

Understanding the structural security elements and integrating them into the application architecture is not only desirable but absolutely necessary.

The security aspects of Oracle are divided into three general categories:

• Database, system, and network administrators only. These issues (such as user and privilege management) are outside the scope of my blog.
• Aspects of importance to architecture developers and designers that are not under the responsibility of administrators. One example is the choice of a call rights model when creating stored code; usually, the decision is made by the developer, not the administrator, during the design process.
• Aspects that are usually considered to be administrative, but that should be well known to developers and designers. This category includes encryption, line-level security, and application contexts. These are the aspects that my next articles will focus on.

How will the features and tools describe in my blog help PL/SQL developers and application architecture designers in their work? To answer this question, you should consider each topic separately:

• Encryption plays a vital role in data protection and is actively used in many situations when designing applications. You must have practical knowledge of Oracle’s encryption-related functionality, including the Transparent Data Encryption (TDE) mechanism presented in Oracle Database 10g Release 2 and the Transparent Table Encryption (TTE) mechanism presented in Oracle Database 11g.
• String level security (RLS, Row Level Security). When designing an application, you need to have a good understanding of the architecture of access and authorization checks to work with data. RLS allows you to limit the lines available to the user. In many cases, RLS simplifies understanding and implementation of applications, and sometimes even automatically ensures that ready-made applications comply with security rules adopted in your organization.
• Application contexts are name/value pairs that are defined in a session by performing specific stored procedures. Application contexts are typically used to manage access to database resources according to rules that vary depending on the current user.
• Detailed FGA (Fine-Grained Auditing) provides a mechanism for registering the fact that users issue certain commands and fulfill certain conditions. The FGA provides the developer with a lot of useful functions. For example, the FGA actually allows implementing SELECT triggers – user procedures that should be automatically performed at each data sampling from the table.

The topic of security in Oracle is truly immense; in my subsequent blogs, we will only look at its aspects of greatest interest to PL/SQL developers in the most general way. For more information on these and other Oracle security operations, see the Oracle PL/SQL for DBAs (Arup Nanda, Steven Feuerstein, O’Reilly) book.

Many other books have also been published to help you understand the intricacies of security.