[Chapter 2] 2.5 Database Security Review

2.5 Database Security Review

Even though security policies are developed and enforced by the DBA, you should still understand how database security issues can impact application design. For example, you should have a clear grasp of schemas and object privileges if you're going to secure your systems by allowing access only through a minimally privileged account.

TIP: Depending on the application, you might need to create an application-specific security scheme (unless you create a DAD for every account, which is a maintenance nightmare) to differentiate between users. For example, in Chapter 8, we'll look at a threaded discussion list application in which we create our own username and password list to allow users to post messages.

This section is a security refresher, covering security relationships among database users, database objects, object privileges, and roles. If these terms are new to you, or you need to dust off a few cobwebs, read on. Otherwise, feel free to skip to the next chapter.

2.5.1 Database Users

A user account is the first line of defense in an Oracle database. Similar to an account on a Unix or NT system, each user account has an associated username and password. A user must log in to a particular account by providing the correct password before running scripts, inputting data, executing PL/SQL programs, or performing any other meaningful activity.

The term schema is often used synonymously with "user" or "account." Although the concepts are closely related, schema is slightly more specific and refers not only to the account itself, but also to the collection of objects (tables, indexes, packages, etc.) owned by the account.

2.5.2 Database Objects

The word object is one of the most overused in the computer world. Languages like Java and C++ create objects with wonderful properties like polymorphism, inheritance, and a slew of other four-syllable words. Object-relational databases like Oracle8i give us pseudo-objects that mimic the important properties of the objects in Java. Finally, life itself gives us everyday objects like buses, rutabagas, and human resources managers.

In Oracle, a database object is a general term for anything created and stored in an Oracle database, including tables, indexes, views, synonyms, and stored procedures. Each object is owned by the account in which it was created. To follow our Unix example, database objects are sort of like files; they belong to the user that created them and to no one else.

Each object has a unique name. A table may be named emp, a procedure may be named give_raise, and an index may be named emp_pk1. Once a name is given to an object, no other object within the schema -- even if it is a different kind of object -- can use the same name. Attempting to create an object with an existing name results in the error "ORA-00955: name is already used by an existing object."

2.5.3 Privileges

Just as every Unix account shouldn't have system administrator authority, every Oracle account shouldn't have DBA power. For example, Bob in accounting shouldn't be able to issue the DROP TABLE command just to see what will happen. Privileges allow us to control how much power a particular account can have.

There are two types of privileges: system and object. As a rule of thumb, system privileges let an account execute SQL Data Definition Language (DDL) commands, while object privileges let an account execute SQL Data Manipulation Language (DML) commands.

A privilege is granted to or revoked from a specific user account with the SQL commands GRANT and REVOKE. For example, the following two commands give the scott account the right to create a table and select from the emp table in the HR schema:

GRANT CREATE TABLE TO scott;
GRANT SELECT ON hr.emp TO scott;

The next two commands show how to remove a privilege using the REVOKE statement:

REVOKE CREATE TABLE FROM scott;
REVOKE SELECT ON hr.emp FROM scott;

2.5.3.1 System privileges

System privileges give an account the right to perform specific actions. For example, an account must be given permission, usually by the DBA, to create, alter, drop, or execute various database objects. An account can also be given permission to perform these actions in other schemas by including the ANY option.

There are a number of system privileges,[3] including:

[3] The SYSTEM_PRIVILEGE_MAP data dictionary table lists all the system privileges.

CREATE TABLE

CREATE PROCEDURE

ALTER USER

EXECUTE ANY PROCEDURE

GRANT ANY PRIVILEGE

The most minimal system privilege is the CREATE SESSION privilege, which allows the account only to log in to the database. Unless granted other privileges, these accounts cannot do much damage. For this reason, they are often used as gateways to more privileged accounts, which selectively grant the account access to a limited number of objects.

2.5.3.2 Object privileges

Object privileges allow an account to make its objects available to other accounts. Each type of object has its own set of applicable privileges. After a privilege on an object is granted to another account, that account can perform a variety of operations that fall within the limits of the granted privileges. In WebDB, some of the most commonly used privileges (by object) include:

Tables and views
- SELECT: Select rows from the table.
- INSERT: Insert rows into the table.
- UPDATE: Update rows in the table.
- DELETE: Delete rows from the table.
Procedures, functions, and packages
- EXECUTE: Execute a stored program.
Sequences
- SELECT: Select the sequence value.

TIP: A stored procedure or function executes with the privileges of its owner, not those of the account that is executing it, unless overridden with the Oracle8i "invoker's rights" option.

An account may access an object once it has been granted the necessary privilege. If it tries to perform an action for which it does not have the necessary privilege, the RDBMS generates an error. The account references the object using its fully qualified object name, which is simply the object's name prepended with its owning schema. The two names are separated with a period. For example, suppose the accounts bob, sue, and cato each own a table named emp. Assuming we have the right privileges, we can use the fully qualified object name in the following SQL statement to get an aggregate list:

SELECT * FROM bob.emp
UNION
SELECT * FROM sue.emp
UNION
SELECT * FROM cato.emp;

For example, suppose Alice in accounting wants to dump some of her work on Bob, who was recently demoted to clerk for destroying a production database. She can use the SQL GRANT command to give Bob's account (bob) privileges on tables she owns so that Bob can run various scripts:

GRANT SELECT ON employees TO bob;
GRANT INSERT ON employees TO bob;

Once Bob has the proper privilege, he can execute SELECT commands on Alice's table by using the table's fully qualified name:

SELECT * FROM alice.employees

Notice that Alice has withheld the DELETE privilege; Bob will get an error if he tries the DELETE statement.

Occasionally, an object must be moved from one schema to another to accommodate new circumstances. For example, what happens if Alice is promoted and wants Cato (the new upstart in accounting) to own and maintain the employees table? Bob -- and anyone else using the table -- must update his scripts to reflect the table's new location. For this reason, it's a bad idea to embed an object's owner directly into a SQL or a PL/SQL statement.

Synonyms eliminate this problem. A synonym is simply an alias, or pointer, for another object. Used in place of a fully qualified object name, the synonym allows us to design systems that achieve location transparency. This is just a fancy way of saying that our programs do not depend on a particular account's owning a particular object. If an object is moved to a new owner, we can simply change the synonym in one place and everything will continue to work.

In the previous example, Bob could achieve location transparency by creating a synonym for the employees table:

CREATE SYNONYM employees_syn FOR
   alice.employees

He could then use the synonym name instead of making an explicit reference to the owning schema:

SELECT * FROM employees_syn;

When Alice moves the table to Cato, Bob can simply change the synonym to reflect the new location.

2.5.4 Roles

It would be a strange site indeed that let accountants control critical database objects. Instead, these objects are usually owned by a highly privileged account that is accessible to only the database administrator and a few trusted developers. The DBA creates less privileged accounts for average users and selectively grants them privileges based on their needs.

For example, an account named HR might own all the objects for the company's human resource management system. The DBA might create a view of the employees table and grant Bob's or Alice's account permission to query it. She might also create a separate account for data entry clerks with INSERT privileges on a few important tables. Finally, she might create an account for a web application that displays employee information stored in database snapshots. Ideally, each account has the minimum privileges it needs to fulfill its goal.

Managing these privileges by hand is a daunting task. Even a small company can have dozens of applications, hundreds of tables, and zillions of users. It is simply impossible for the DBA to manually grant the correct privileges to every user in a way that is convenient and safe. Remember, lurking in the back of every DBA's mind is the secret (and sometimes not so secret) fear that his or her users and developers are idiots who will drop critical tables just for kicks. It is crucial for both security and the DBA's sanity that each user have access to only what he or she needs.

This is where the concept of a database role comes to the rescue. A role is a collection of privileges grouped under a single name. Instead of granting privileges to individual users, the DBA grants them to a role. This role, in turn, is granted to the users that need the corresponding privileges. The DBA can add or revoke privileges from a role at any time, and these changes automatically flow to the users assigned to the role.

For example, the DBA might create a role called ACCOUNTANT and grant it selected privileges on tables and views owned by the HR account. The role reduces the maintenance on Alice's, Bob's, Cato's, and Xena's accounts to a single grant. Figure 2.3 shows a typical scenario in which roles are used to create access rights for various classes of users, such as pay clerks, managers, and receivables clerks.

Figure 2.3: A common use for roles

WARNING: When writing stored PL/SQL program units, such as packages, note that the compiler ignores the privileges granted through roles. To work around this problem you must either create the program in the account that owns the object or explicitly grant the necessary privilege to the account that owns the stored procedure. However, users can still execute procedures granted through roles.

A role is a database object just like any other. Assuming you have the CREATE ROLE privilege, you create a role with the following syntax:

CREATE ROLE role_name;

You then grant privileges on various objects to the new role, just as you would to a normal user. For example:

GRANT SELECT ON customers TO accts_pay_role;
GRANT SELECT ON cust_orders TO accts_pay_role;

Finally, you grant the role to individual users, treating the role as though it were a new privilege. For example:

GRANT ACCTS_PAY_ROLE TO bob;
GRANT ACCTS_PAY_ROLE TO cato;

Revoking a privilege from the role automatically revokes it from all the users assigned to the role.

2.5.5 Additional Security Precautions

In addition to database security features, there are three other security precautions worth noting, although we won't cover them in any detail. These include:

Secure Sockets Layer (SSL): A standard for securing a web session by encrypting the traffic between a browser and a web server. The method relies on a digital certificate, a special file assigned to a site by a trusted source called a certification authority (CA). The CA generates the certificate and sends it to the web site. Browsers wishing to connect to the site must first download and accept the certificate, which is used in an encryption scheme to secure the subsequent traffic.
Firewall: A machine placed between two networks that controls what traffic can cross the boundary. Companies almost always have a firewall between their internal network and, for instance, the Internet. The firewall can be configured to block traffic originating from certain areas, let through certain types of traffic (for instance, HTTP) and reject others (Telnet traffic), and force users to supply a username and password before they can go through the firewall.
Advanced Networking Option (ANO): A security server used to encrypt SQL*Net and Net8 traffic. This is useful in a web setting because (depending on the configuration of the firewall), users can establish a SQL*Net or Net8 connection over the Internet, allowing them to use tools like SQL*Plus. ANO secures the conversation.

Web security (and computer security in general) is a complex and broad subject that encompasses many different areas handled by different individuals. The DBA sets database security policies, system administrators check on operating system security, application developers build security features into their applications, and the network administrator designs hardware and software configurations to secure the network. Even the security guard at the front door has an important role in maintaining the physical security of your site.

TIP: No matter how you secure your system, you are still vulnerable to an attack. One of the simplest and most effective hacking techniques, social engineering, simply tricks people into revealing otherwise secure information through deception. For example, it's much easier to call an internal help desk and sweet-talk someone out of a password than to penetrate a secured system.

As an application developer, your main security duty is to make sure that your applications do as little as possible to compromise the system (for example, writing a web system that reveals a password list). In general, though, you'll have very little direct involvement in the other areas. While you'll certainly work with the DBA to gain privileges to various objects, you will probably not work with the network administrator (other than to listen to complaints about how the administrator doesn't have enough bandwidth for your application).


2.4 Database Integration		3. WebDB