DATABASE SECURITY

Most SQL based systems operate in a multi-user environment. This means that at any time, several different users can access the same database to query, insert, update or delete data. Such an environment requires safety devices that are both built into the DBMS itself and that prevent users from inadvertently corrupting the data. This chapter looks at the security features that are built into SQL itself and also addresses some of the wider aspects of database security.

The DBMS must implement security on two levels. First at the overall database level and second at the individual record level. In this chapter we will be dealing with database security designed to prevent unauthorized access at the overall database level.

8.1 "The Term Security is Defined as Protection":

SQL Privileges.

The term security is defined as protection, defense and safety. In this context, it is a very important aspect of an SQL (or any other) DBMS. Specifically, as part of it's security features, the DMBS should protect the data from unauthorized access. It should only allow approved users to use the data in the database and even then, only allow them to perform those functions for which they have authorization.

Without any security features, the data in the database will be accessible by all users. Anyone who felt like it could alter the rows in the tables either inadvertently or maliciously. In almost all organizations, this is not an acceptable state of affairs for a database that might hold vital business and personnel data to be in. Fortunately, the SQL language implements database security as an integral part of it's DDL structure.

SQL security is based on the concept of privileges. A privilege can be thought of as permission to perform a certain operation on a certain database object given to a certain user. There are three important concepts here. The first is the privilege which is what we have just described. The second is the idea of database operations which are the actions that you may want to restrict for certain users. Essentially, these operations boil down to queries, insertions, updates and deletions of data. The third is the idea of users who are the people who use the database system and issue SQL commands. The DBMS needs to be aware of everyone who is using the database at any time.

The ANSI/ISO standard defines four privileges that can be granted to or revoked from users: SELECT, INSERT, UPDATE and DELETE. These privileges correspond to the SQL operations that a user is allowed to perform on a given table. The SELECT

privilege allows the user to query a table or view. The INSERT privilege allows the user to add rows to a table or view. The DELETE privilege allows the user to delete rows from a table or view and the UPDATE privilege allows the user to modify data in a table or view. Unlike the other three privileges, you can grant UPDATE on selective columns in a table or a view.

8.2 "Users Must Introduce Themselves":

The Logon Procedure.

In order to be able to implement system wide security, the DBMS needs to be aware of exactly who is using the database at any point in time. To do this, almost all commercial SQL DBMSs rely on the concept of authorization-ids. The authorization-id is a label by which SQL identifies each person who is allowed to issue commands to the DBMS (note that a group of users may have similar requirements and may thus share an authorization-id, but is not very common. Usually, each user has his or her own authorization-id). An authorization-id may also be used to identify a program rather than a person that issues SQL commands.

SQL Tips

SQL Server and Sybase support group-ids which can be used to identify groups of users with similar needs.

New users are registered onto the system by the database administrator who must tell the DBMS to add the new user's authorization-id and password to the list of valid users.

Some commercial SQL implementations, including Ingres and Informix, use the username that is specified in the host computer's logon procedure as the authorization- id for the user. Other systems including Oracle require users to specify the username and also an associated password at the start of the interactive SQL session. The username is used as the authorization-id, but the password is not used in SQL.

SQL Tips

8.3 "The Library Database":

An example system.

For any table that you create, SQL assigns you as the owner of that table. Ownership of tables means that you automatically have full privileges for that table (these are the four standard ANSI/ISO privileges; SELECT, INSERT, UPDATE and DELETE, already described as well as any other non-ANSI privileges that are supported by your particular dialect of SQL). Initially, all the other users of the database will have no privileges on your new table. Figure 8.1 shows a group of users and the lending library database that they use. The structure of this simple database will be explained as we progress through this chapter.

The users: | |by | | The tables: | | |_______|_| |_______|_|

FRANK BARBARA ADAM PUBLIC JONES MORRIS O O O ☺☺☺☺☺☺ O O -|- -|- -|- ☺☺☺☺ -|- -|- I I / \ I I ☺☺ I I I I I I I~I I I ☺☺ I I I I / \ / \ | is owned by | --- | | | | |is owned --- | --- |__|_|___|_|__|__|_| -- |__|____|__| |__|_|___|_|__|__|_| |__|____|__| |__|_|___|_|__|__|_| |__|____|__| |__|_|___|_|__|__|_| |__|____|__| |__|_|___|_|__|__|_| |__|____|__| |__|_|___|_|__|__|_| |__|____|__| --- --- BOOKS /|\ VIDEOS |is based --- on The views: | ~~~~~~~~~ |_______|_| (Point) |_______|_| ---> TO BARBARA |_______|_| TITLES

THE CONTENTS OF VIDEOS:

('Star Trek','Universal', 'PG') ('Duck Tales','Disney', 'U') THE CONTENTS OF BOOKS:

('English','U.K.Author', 'IN') ('French','E.E.C.Author', 'OUT') THE CONTENTS OF TITLES view: ('German', 'Otto Matic');

SQL Tips

The ANSI/ISO standard allows authorization-ids to be up to 18 characters long, but many commercial implementations do not stick to this.

User Frank logs onto the system under the authorization-id FRANK and creates a table called BOOKS by:

CREATE TABLE BOOKS (