Metadata, Security, and the DBA

So far in the DBMS we have looked at table schema for our database design. We have also considered views, and in many ways these act like tables. This table theme extends to all parts of a DBMS. In particular, Oracle makes this theme quite explicit.

In Oracle, everything is a table. Not only the things we think of as tables, but also the system things like user information. The philosophy is simple... implement the concept of a table and we have everything we need to build a DBMS. This includes security concepts; secure the table concept and everything is similarly secure.

Oracle has a special tablespace, called SYS, which holds all the system information. Various security levels protect SYS, so dependent on your access rights you may or may not be able to see all the tables held there. SYS in total holds hundreds of tables. The list below gives a few of these table names.

SQL> describe dba_users;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERNAME                                  NOT NULL VARCHAR2(30)
 USER_ID                                   NOT NULL NUMBER
 PASSWORD                                           VARCHAR2(30)
 ACCOUNT_STATUS                            NOT NULL VARCHAR2(32)
 LOCK_DATE                                          DATE
 EXPIRY_DATE                                        DATE
 DEFAULT_TABLESPACE                        NOT NULL VARCHAR2(30)
 TEMPORARY_TABLESPACE                      NOT NULL VARCHAR2(30)
 CREATED                                   NOT NULL DATE
 PROFILE                                   NOT NULL VARCHAR2(30)
 INITIAL_RSRC_CONSUMER_GROUP                        VARCHAR2(30)
 EXTERNAL_NAME                                      VARCHAR2(4000)

The DBA_USERS table holds the username of users, an ID number unique for each user, their login password, the tablespace where their personal tables and views are created, a space for calculating the results of queries (temporary tablespace), plus many more internal details.

An example of a table holding the more internal features of the DBMS is the USER_CONSTRAINTS table. This (extensively) documents the constraints which exist between tables in the database. A summary of the attributes is shown below.

SQL> describe user_constraints;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 CONSTRAINT_NAME                           NOT NULL VARCHAR2(30)
 CONSTRAINT_TYPE                                    VARCHAR2(1)
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 ......
 DEFERRABLE                                         VARCHAR2(14)
 DEFERRED                                           VARCHAR2(9)
 ......
 LAST_CHANGE                                        DATE
 ......

Here a row links the owner of the constraint to a constraint name and type. This constraint is on a table name. The date when this change was made is recorded. Oracle allows constraint checking to be put off till the end of a transaction, and this is known as DEFERRING. If a constraint can be deferred then it will be DEFERRABLE, and if it is currently deferred that too can be recorded.

select owner,table_name,constraint_name,constraint_type
from all_constraints
where owner = 'DBRW'
and table_name in ('EMPLOYEE','JOBHISTORY','DEPARTMENT')
;

OWNER	TABLE_NAME	CONSTRAINT_NAME	CON
DBRW	DEPARTMENT	SYS_C0010801	P
DBRW	EMPLOYEE	SYS_C0010803	P
DBRW	EMPLOYEE	SYS_C0010804	R
DBRW	JOBHISTORY	SYS_C0010807	P
DBRW	JOBHISTORY	SYS_C0010808	R

From the tutorials you may remember these tables. The constraints indicate that the DEPARTMENT has only a PRIMARY KEY constraint. EMPLOYEE and JOBHISTORY also have primary key constraints, but also have some foreign key referential integrity constraints (R). The constraint names are automatically generated when the tables are created. These names can be useful, as attempting to delete table DEPARTMENT results in a error indicating that this would violate constraint SYS_C0010804, and using this table shows that DEPARTMENT must have a foreign key relationship from EMPLOYEE, and therefore EMPLOYEE must be dropped first.

There is a great deal of metadata in a DBMS, extending well beyond the implementation of the user schema. This includes support for application links and schema documentation (such as comments). Exploring this metadata can give a valuable insight into DMBS construction and performance issues.

Security

The protection which security gives is usually directed against two classes of user

Legally there is the Data Protection Act, which places restrictions on databases which contain information on living people. This was created to protect the public from data contained on a computer, about themselves, to which the public had previously no legal right of access. Information on computers can be wrong, and decisions made on wrong information concerns the public and additionally is of no benefit to the company holding the data. The act supports the idea of the public querying data, and indicating errors in that data.

However, just because a database is legal does not make it socially or ethically acceptable. Collating medical records on computer for a hospital is acceptable, but not having enough security to prevent insurance companies accessing the database and using that as a basis for rejecting life assurance applications could be considered questionable. Frequently it is best to place the tightest restrictions on who can access data, and where necessary security is deliberately relaxed to allow only legitimate queries to take place.

Security often begins with physical controls. If a person cannot enter the building where the database runs and is accessed, then that person cannot access the database. Usually the construction of security is a layered approach, where a person bent on accessing the database must penetrate multiple levels of security. The simple precaution of having all the database access points behind locked doors can only add to the security of the system.

Security of a database is often the enforcement in the database of the company policy. All companies should have a policy statement, listing what is acceptable and what is not. Companies with weak policy statements will often have the weakest security. At a minimum, it should be policy that data stored in the database should not be made available to outside agents without written consent from a Managing Director. Without a policy statement, it is hard to argue that an employee has actually done anything wrong...

If only a single person has access to a database, security is certainly higher than if many people have access. However, if all the people in the UK had to phone the same one person to find out what their bank balance was the whole system would quickly become unworkable. Security considerations often have to be balanced against operational issues.

No matter how secure the database actually is, if a person can simply steal the hard drive on which the database is stored, then that person can access the database at leisure. This case is obvious, but less obvious security failures, such as taking a copy of a backup tape of the database, can be harder to safeguard against.

Most DBMS's run on top of an operating system (OS). Examples of OS's include Window 95, Windows NT, and Unix. The database may be secure from within the DBMS, but if the database can also be accessed from the OS using simple file handling programs, then a clear weakness in the security model exists.

Within the DBMS itself, if anyone can access anything then having any other sort of security seems pointless. The use of user accounts and password protection of user identities is a good starting point to improve security. User identities is also an aid to accountability. Protection of certain elements of the database with respect to certain users (or user groups) should always be considered where potentially confidential data is being stored. It is DBMS security which is the focus of this discussion.

Granularity of DBMS Security

The unit of data used in specifying security in the database can be, for example;

DBMS-level Protection

Often it is hard to prevent people from copying the database and then hacking into the copy at another location. It is easier to simply make copying the data a useless activity by encrypting the data. This means that the data itself is unreadable unless you know a secret code. The encrypted data in combination with the secret key is needed to use the DBMS.

If someone does penetrate the DBMS, it is useful to find out how they did it and what was accessed or altered. Audit Trails can be set up selectively to minimise disk usage, identify system weaknesses, and finger naughty users.

User-level Security for SQL

The GRANT command

The WITH GRANT OPTION permits the specified user can grant privileges which that user possesses on that table to other users. This is a good way to permit other users to look after permissions for certain tables, such as allowing a manager to control access to a table for his or her subordinates.

grantee need not be a username or a set of usernames. It is permitted to specify PUBLIC, which means that the privileges are granted to everyone.

GRANT and VIEWs

When a view is created is when the security of the view is checked. Thus if there was sufficient security for the view to execute when it was created, then the view will always work no matter what additional GRANTs are made. This can be used to restrict columns and rows from a user.

GRANT select ON employee to jim;
create view empjim as 
select empno, surname,forenames from employee;
GRANT select on empjim to jim;
REVOKE select ON employee from jim;

You can also restrict rows of a table to particular users by their username or other feature. In Oracle, the username of the current user is returned by the function USER. Thus the following creates a single table, but gives each user of the view the ability to look at only rows where the username matches their username.

CREATE table checker (
  username	varchar(200)
 ,secretinfo	varchar(100)
);
CREATE view userview as
  select * from checker
  where username = USER
;

select * from userview -- shows rows where the username matches.
;

The Database Administrator

As system controls increase usability of the system decreases. Actually it is perfectly possible to have an efficient and reliable system which no one can use effectively. This is never the explicit goal of the DBA, but there is a danger that it is an implicit goal.

The person who looks after the database needs to balance all needs of the users, whether they know they need it or not. No user wants security, for instance, yet if someone hacks in and deletes all their work the DBA becomes the target. Perfectly designed security is completely invisible to the valid user, but is automatic and total for the invalid user.

Security is not the only issue of importance for the DBA. They are also concerned with:

A good DBA is almost never seen. The fact is that if you have to phone the DBA then the DBA has failed. The system will be monitored continuously, and problems detected and fixed before they are noticed by users. Long term issues, such as data growth, diversification, the addition of new projects, do need to be discussed with the DBA, but the DBA should be able to detect most issues anyway and handle them transparently from the users and developers.

Metadata, Security, and the DBA

Contents