Database control refers to the role of implementing regulations in order to provide authentic database users and applications with correct data. All data should conform to the integrity constraints specified in the database in order for correct data to be accessible to users. In addition , data should be filtered away from unauthorized users in order to protect the database's protection and privacy. One of the key roles of the database administrator ( DBA) is database control.
The three aspects of database control are –
Authentication is the process in a distributed database system in which only legitimate users can gain access to the data resources.
Two levels of authentication can be applied.
The access rights of a user refer to the privileges given to the user for DBMS operations, such as the rights to create a table, drop a table, add / delete / update tuples in a table, or to query the table.
It is not feasible to assign individual access rights to users in distributed environments, because there are large numbers of tables and yet greater numbers of users. So, those roles are specified by DDBMS. A position is a construct within a database system that has certain privileges. Once the various roles are established, one of these roles is allocated to the individual users. Sometimes, a hierarchy of roles is established according to the hierarchy of authority and obligation of the company.
For example , the following SQL statements establish a "Accountant" function and then assign it to a "ABC" user.
CREATE ROLE ACCOUNTANT; GRANT SELECT, INSERT, UPDATE ON EMP_SAL TO ACCOUNTANT; GRANT INSERT, UPDATE, DELETE ON TENDER TO ACCOUNTANT; GRANT INSERT, SELECT ON EXPENSE TO ACCOUNTANT; COMMIT; GRANT ACCOUNTANT TO ABC; COMMIT;
Semantic control of integrity determines and enforces the database system's integrity constraints.
The limits of integrity are as follows-
Data Type Integrity Constraint
The constraint of the data type limits the range of values and the type of operations that can be applied to the field with the data type defined.
For example, let us remember that there are three fields in a "HOSTEL" list-the number of the hostel, the name of the hostel and the capacity. The number of the hostel should begin with the capital letter 'H' and can not be NULL, and the capacity should not exceed 150. For data definition, the following SQL command can be used –
CREATE TABLE HOSTEL ( H_NO VARCHAR2(5) NOT NULL, H_NAME VARCHAR2(15), CAPACITY INTEGER, CHECK ( H_NO LIKE 'H%'), CHECK ( CAPACITY <= 150) );
Entity Integrity Control
Entity integrity control enforces the rules such that it is possible to distinguish and tuple from other tuples uniquely. A primary key is assigned for this. A primary key is a set of minimal fields that can identify a tuple uniquely. Entity integrity constraint states that for primary keys, no two tuples in a table can have identical values and that no field that is part of the primary key can have a NULL value.
For instance, the hostel number can be assigned as the primary key in the above hostel table via the following SQL statement (ignoring the checks) –
CREATE TABLE HOSTEL ( H_NO VARCHAR2(5) PRIMARY KEY, H_NAME VARCHAR2(15), CAPACITY INTEGER );
Referential Integrity Constraint
The rules of foreign keys are set out by referential integrity constraint. A foreign key is a field in a data table that is a similar table's primary key. The referential integrity constraint lays down the rule that the foreign key field value should either be one of the primary key values of the referenced table, or it should be NULL in its entirety.
For example, let us consider a student table where a student can choose to live in a hostel. To include this, in the student table, the primary key of the hostel table should be used as a foreign key. This is part of the following SQL statement –
CREATE TABLE STUDENT ( S_ROLL INTEGER PRIMARY KEY, S_NAME VARCHAR2(25) NOT NULL, S_COURSE VARCHAR2(10), S_HOSTEL VARCHAR2(5) REFERENCES HOSTEL );