Main menu

EN | RU | UK

To Home Page

11. Database unauthorized access security

On top

The objective of the chapter is to study problems and tasks of DB information security, to examine in details computerized and non- computerized counteractions getting acquainted with implementation of relational DB access control model.

11.1. Problems of DB protection

Concept of data protection refers not only data that is saved in DB [4]. Vulnerability of the protection system can arise in other parts that also endangers DB. Thus, protection must cover the whole object of information activity (of organization) in general: equipment, software that is used, staff and data [2, 4, 6, 7, 21].

DB protection is a prevention of any intentional or unintentional threats by means of different computer and non-computer assets [4].

Threat means any circumstances or events that can break information security policy and/or cause losses for the company [22].

Threat can be caused by situation or event that is able to harm the company. It can be the fault of a person or coincidence. Intentional threats are always carried out by people that can be authorized or non-authorized user of the system, or that can even be an employee of the company. Any threat must be considered as a potential possibility of system’s protection violation that in case of its implementation can make a negative impact of some kind (table 11.1). Duration of inactivity time and DB update depend on the series of factors:

  1. Is there a backup equipment with extensive software.
  2. When the file backup of the system was carried out last time.
  3. Time that is necessary for the system update.
  4. If there is a possibility to update and to implement data that was broken.

Table 11.1

Some kinds of threats and their consequences for DB
Threat Data stealing and falsification Loss of confidentiality Personal data inviolability breaking Loss of integrity Loss of availability
Using access rights of another person X X X    
Unlwaful data modification or copying X     X  
Modification of programs X     X X
Thoughtless methods and procedures that admit mixing of confidential and ordinary data within one document X X X    
Connection to cabling X X X    
Insert of incorrect data carried out by hackers X X X    
Blackmail X X X    
Making «loophole» in the system X X X    
Stealing of data, programs and equipment X X X   X
Protection system failure that causes exceeding of acceptable access level X X X    
Lack of staff and strikes       X X
Poor staff skills   X X X X
Examining and disclosure of secret data X X X    
Electronic homing and radiation       X X
Breaking data as a result of disconnection or over voltage in the power supply system       X X
Fires (due to short circuits, streaks of lightening, setting fire), floods, diversions       X X
Physical damage of equipment       X X
Breake or disconnection of cabling       X X
Inserting computer viruses       X X

DB is the most important corporate asset that must be duly protected by means of corresponding control devices that are determined by specific requirements that are established according to particularities of system operation. We will discuss the problem of DB protection considering such potential threats as:

1) data stealing and falsification;

2) loss of confidentiality (secret violation);

3) personal data inviolability breaking;

4) loss of integrity;

5) loss of availability.

These potential threats outline main areas where leadership must carry out safety precautions to lower the level of risks that is a potential possibility of data loss or violation. In some situations, all above mentioned aspects of data violation are closely connected with each other. So actions, that are dedicated for protection from system breaking in one direction, very often cause lowering of protection level in all other directions. Moreover, some events, for example personal data inviolability breaking or falsification, can arise due to intentional the same as unintentional actions. And it is not necessary that they are accompanied by any changes that can be somehow detected in the DB or system.

Data stealing and falsification can take place not only in the DB environment – the whole organization can be a subject of risk. However, actions realized to steal or falsify the information are always carried out by people. That is why main part of attention must be concentrated on reducing total quantity of comfortable conditions for such activity. Stealing and falsification are not necessarily connected to modification of some data and the same is valid for confidentiality loss or personal data inviolability breaking.

Confidentiality loss and personal data inviolability breaking causes loss of points in competition and legal consequences as a result of actions against company that were presented by aggrieved physical body. Usually, data is considered to be confidential being critical for the whole company, while the concept of data inviolability is determined regarding requirement of protecting the information about separate employees.

Loss of integrity and loss of availability will cause distortion or destruction of data that can have the most serious consequences for the further work of the company.

Loss of data availability is a situation when data, system or both of them become not available to users. This can threaten the further existence of the company. In some cases, events that caused transfer of the system to the state of unavailable can lead to the DB destruction at the same time. For the present moment, plenty of companies work in continuous conditions, providing service to customers 24 hours per day and 7 days per week. That is why the loss of data availability can cause huge losses due to flight of solvent clients.

11.2. Precautions – computer control methods

To protect computer systems precautions of several types can be implemented, starting with physical survey and up to administrative and management procedures. Regardless the wide range of computer control methods that are available on the market nowadays, general level of DBMS protection is determined by the possibilities of operational system that is applied. Operation of these two components is closely connected. Usually the following control methods are applied: authorization and authentication of users, data presenting, integrity support, file backup and DB update, data encryption, backup procedures.

11.2.1. Authorization

Authorization is providing authority that permits its owner to have the legal access to the system or its objects [4]; establishing conformity between message and its source [22].

Usually administrator is responsible for providing users with access to computer system. His duties include generating user accounts. Each user is assigned unique identifier that is used by the system to find its proprietor. Each identifier is attached to a certain password that is chosen by the user and is indicated in operational system.

Methods of users authorization can be nested directly to the software and manage not only rights of access to the system or its objects that are provided by the user, but also set of operations that user can carry out for each available object. For that reason the mechanism of authorization is often called access control subsystem. Term «proprietor» can represent user-person or a program. Term «object» can represent data table, presentation, application program, procedure or any other object that can be generated within the bounds of system.

One more aspect of authorization is procedure designing. It provides specific users rights of access to different objects of DB. It is very important to save historic information about the assignment of rights to users, especially if their professional duties change and thus they do not need access to some data arrays any more. Especially if user quits, it is extremely important to delete its account and annul all access privileges that were assigned as soon as possible. This will permit to stop any possible attempts to break the system protection.

11.2.2. Authentication

Authentication is a procedure of each object identifier conformity verification dedicated to find out if it is a part of this object, determination and confirmation of authentication [22].

At the moment of registration user has to present to the system his own password for authentication – that is to check if he is the person is authorized. Using passwords is the most popular method of user authentication. However this method doesn’t provide absolute guarantee that user is identified correctly.

Regarding providing necessary level of protection, it is very important that all passwords that are used are kept in secret. During the procedure of registration in the system password must not be displayed and list of users’ identifiers and passwords must be stored codified. Apart from it, company must determine some standard of choosing admissible password values. For example, all passwords must contain quantity of symbols not less then determined length, obligatory contain numbers and service digit and should be changed in determined period of time (let’s say five weeks). Special software must be applied to detect week passwords in the system. For example, real names or addresses of users can be used as a password. This is not acceptable. Also special programs can be used to detect old passwords.

11.2.3. Mechanism of views

Mechanism of views is a powerful and flexible instrument of protection. As a result, users will have no information about existence of any attributes or data rows that are not available through views that they have in their disposal. View can be determined on the base of several tables. After that necessary privileges of access to this view, but not basic tables, will be delegated to user. In this case applying view represents more firm mechanism of access control in comparison with providing user some rights of access to basic tables.

11.2.4. Means of data integrity support

Means of data integrity support makes their contribution in general safety of the DB as they prevent the transfer of data into uncoordinated form and getting mistaken or incorrect computation products.

11.2.5. Backup

Backup is a procedure that is periodically carried out to generate the copy of DB, its program file and programs on the medium to be saved apart from the system [4].

There are the following kinds of backup:

1) full backup;

2) differential backup;

3) incremental backup;

4) file-by-file backup;

5) block level incremental backup.

It is recommended to carry out full backup for DB with a small information volume. When the time required for file backup reaches its critical value, that hinders its efficient execution, differential or incremental backup is used. The difference between them is that differential backup is carried out only for the parts of DB that were modified after last full backup. Incremental backup is carried out for the parts of DB that were modified after the last backup of any kind. File-by-file backup of DB, that is considered to be a set of files, is carried out by means of operational system. For block level incremental backup sector-by-sector backup of hard disk space where the DB files are located.

Any modern DBMS must provide means of file backup that permit to update DB. Apart from it, it is recommended to create backup copies of DB and its journal file with determined periodicity. It is possible to backup big DB once a week or even once a month, but it is necessary to foresee obligatory incremental backup that must be carried out more often. Day and time of its execution must be determined by responsible experts. In case of failure, when DB becomes nonserviceable for the further operation, backup copy and fixed in journal file operational information is used for update till the last concerted state.

During twenty-four hours several backups can be carried out. It depends on the rate of making modifications. Created copy must be kept in a safety place. Storage for keeping last backups must be equipped with fireproof case. Apart from it, it is better to use some outer storage, where another copy of created backups will be kept. All mentioned details must be clearly depicted in designed procedures of file backup that must be steadily carried out by the staff.

Procedures that regulate processes of file backup creation are determined by type and size of DB, and also by the set of corresponding tools that are provided by DBMS. These procedures are composed from several stages that will be used to carry out the creation of file backup. In the backup procedures can be indicated what other parts of the system (for example application programs) apart from data itself must be a subject of backup.

Journaling is a process of creation and maintenance of the journal file that contains information about all modifications made in the DB from the moment of last file backup. It is dedicated for providing efficient restore of the system in case of its failure [4].

DBMS must represent means of system journaling, where the information about all changes of the DB state during the execution of its current transactions will be fixed. It is necessary to providing efficient restore of the system in case of its failure. The advantage of using such journal is that in case of DBMS disturbance or failure DB can be restored till the last known confirmed state using the last backup copy and operational information that is contained in the journal. If in the system that failed function of system journaling was not carried out, DB can be restored only up to the state that was fixed at the moment of last backup copy creation. All modifications that were made after the last backup copy was created will be lost.

Checkpoint is a moment of synchronization between the state of DB and state of journal of transactions execution. At this moment all buffers are obligatory unloaded to the means of secondary memory [4].

Usually modern DBMS provide means of creation checkpoints that permit to fix in the DB series of changes that were made within last period of time. Mechanism of checkpoints creation can be used together with system journaling that permits to improve efficiency of update process. At the moment when checkpoint creation DBMS carries out actions that provide recording on the disk of all data that was kept in the main memory of machine and makes a special record in the journal file regarding the checkpoint.

11.2.6. Update

The same as procedures of backup, procedures of update also must be carefully reasoned out and worked out. What update procedures will be carried out is determined by the type of back-off that is present (destruction of medium, software failure or breakdown of system equipment). Also procedures must consider particularities of update methods that are supported by DBMS that is used. In each case designed update procedures must be carefully tested as it is necessary to achieve complete guarantee that they operate correctly before the real failure will take place. In idea case, update procedures must be regularly tested with some interval.

11.2.7. Ciphering

Encryption is data coding according to special algorithm. As a result, data becomes inaccessible for reading by any program that doesn’t have decoder [4].

If rather important confidential information is kept in the DB, it is worth to encode it to prevent possible threat of unauthorized access from the outer side (regarding DBMS). Some DBMS include methods of encoding dedicated for application of similar tasks. Programs of such DBMS provide authorized data access (after decoding) even though it causes lowering of efficiency due to necessity of decoding.

11.2.8. Auxiliary procedures

Auxiliary procedures must be applied together with other mechanisms of protection. Audit

Audit is an auxiliary procedure that is dedicated to verify if all foreseen control means are applied and to check the conformity of DB protection level to determined requirements [4].

During the inspection execution, auditors can examine manual procedures that are applied, inspect computer systems and check the condition of all present documentation for this system. Besides, audit foresees control of the following control procedures and mechanisms:

  1. Support of inserted data accuracy.
  2. Support of data processing procedures accuracy.
  3. Prevention of appearance and timely carried out detection of errors in the programs execution process.
  4. Brief testing, documenting and maintainability of software tools that are designed.
  5. Prevention of unauthorized modification of the programs.
  6. Assignment of access and control rights for using programs.
  7. Keeping documentation in actual state.

All mentioned procedures and control methods must be quite efficient, and if not, they must be revised. To estimate activity of DB using, its journal files are analyzed. The same sources can be used to detect any unusual operations in the system. Regular execution of audits, that are carried out together with continuous control of journal files content to detect abnormal activity in the system, in most cases permit to find and stop any attempts of protection violation in time. Installation of new application software

Installation of new application software must be accompanied by series of regulated measures. New application programs that are designed without outside help or by outside companies must obligatory be carefully tested before approving decision to put it on a wide scale and to pass it for operating. If the level of testing is not sufficient, the risk of DB failure increases. It must be considered as a good practice to carry out DB backup directly before operating new application software. Besides, at the first period of new application program operating it is necessary to organize careful inspection of its functioning. Separate question that must be agreed with outside program designers is a right of property for the software. This problem must be resolved before the beginning of the process and it is especially important in cases when there is a possibility that later company will need to make some modifications to these programs. Risk of such situation is that company will not have legal right to use software that is created by outside designers if it is going to be modified without assistance. Installation or modernization of system software

Installation or modernization of system software when designer provides the next packet of modifications in turn is a duty of DB administrator. In some cases modifications that are made can be not significant and refer only small part of system modules. However, it can happen that total revision of the whole installed system is necessary. Usually, every packet of modifications is accompanied by printed or interactive documentation that contain detailed information about the nature and destination of modifications.

After studying accompanying documentation of the packet a plan of its installation must be worked out. This plan must reflect any changes that can make an impact on the DB and application programs, together with methods of their implementation. However regardless any required modifications, all of them must be taken into consideration and it is necessary to estimate run-time considering size of the whole present software. The main task of DB administrator is to provide blend from old version to the updated one.

In the operational system, that must be continuously available during operating time, installation of any packets or modernization must be carried out in standing time. For example weekends can be appropriate for this work. Full backup of existing system must be created directly before modernization for case of possible failure. Then installation of modernizations packet is carried out and all necessary changes are made in the program data. They are accompanied with testing procedures. System can be started with real data only after total completion of mentioned procedures.

11.3. Precautions – non-computer control methods

11.3.1. Classification of non-computer control methods

Classification of non-computer control methods divides them into inner and outer methods. Non-computer control methods include such methods as generating limitations, agreements and other administrative means that are not connected with computer support. Thet include:

1) means of providing safety and protection from unforeseen circumstances scheduling;

2) staff control;

3) protection of rooms and storages;

4) guarantee agreements;

5) maintenance agreements;

6) physical access control.

11.3.2. Means of providing safety and protection

Means of providing safety and protection from unforeseen circumstances scheduling are different things. The first one admits exhaustive determination of means that are applied to provide protection of computer system of the company. The second one is dedicated for determination of methods that are applied to support operating of the company in case of contingency. Each company must prepare and carry out both list of safety support means and protection from unforeseen circumstances scheduling.

The document regarding safety precautions should contain:

1) the area of business processes of the company;

2) responsibility and duties of separate employees;

3) disciplinary punishments that will be executed in case if breach of determined limitations will be detected;

4) procedures that are obligatory for execution;

Protection from unforeseen circumstances scheduling is designed to determine in details sequences of operations that must be carried out to find the solution in case of extreme situations that are not foreseen in the procedures of normal system operating, for example in case of fire or diversion. There can be one protection from unforeseen circumstances scheduling in the system, or several – separately for each branch. Typical protection from unforeseen circumstances scheduling must contain the following elements:

1) contact information of head responsible;

2) who and on what ground confirms the arising of force major situation;

3) technical requirements for the handing over the administration to standby services.

These requirements include:

1) information about alternative sites location;

2) information about necessary additional equipment;

3) information about necessity of additional communications channels;

4) organizational requirements regarding staff that carries out handing over the administration to standby services;

5) information about insurance that covers specific situation.

Any protection from unforeseen circumstances scheduling must be reviewed from time to time and also its practicability must be tested.

11.3.3. Protection of premises, storages and staff control

Protection of premises, storages and staff control permits to minimize risk of physical penetration of people that do not work in the company and also provides the possibility to narrow the access to equipment and information about employees of the company as much as possible, so that normal maintenance of DBMS and server hardware are guaranteed. Designers of commercial DBMS put the whole responsibility for the efficiency of the system management on the client. That is why attitude to the work of people who are involved in these processes is extremely important for the safety of the system.

Main equipment of the system including printers if they are used to print confidential information must be located in the room that is locked, with limited access that is assigned to chief employees only. The rest of equipment, especially portable, must be safely fixed in the place of location and provided with alarm. However it can happen, that to keep the room locked all the time is unrealizable as employees may need access to equipment that is located there.

Speaking about particularities of file backup we already mentioned that it is necessary to have protected room dedicated for storing information mediums. For any company is vitally important to have such well-protected room where copies of programs, system backups and other archival materials and papers will be kept. It is advisable that such room is located outside of the place where the main equipment of the company is placed. All information mediums including documentation, disks and magnetic tapes must be stored in fireproof cases. Everything that is kept in such room must be registered in the special catalogue with indication of date when the document or medium was placed to the storage. Periodicity of placing new material to such archive must be regulated by the designed backup procedures. Besides, companies can use outside storages, delivering there new created backups and other archive materials from time to time. In such case frequency of delivery must be also regulated by determined norms and procedures. There are some independent companies that specialize in organizing outside storages of information. They provide their service to numerous companies-clients.

11.3.4. Physical access control

Physical access control is an essential method of protecting rooms, storages and staff control. It can be inner and outer.

Inner control is applied inside separate buildings and is dedicated to control those who have access to specific rooms. For example, the most “responsible” rooms (where main computers are placed) can be equipped with systems of incoming control. Different methods can be used in such systems – for example special keys, cards, electronic-code locks or password means. The most complicated complexes can even apply fingerprints, pictures of iris, fixation of voice or handwriting particularities. However nowadays far not all commercial companies apply such refined control methods – mainly due to their high cost.

Outer control is applied outside the buildings and is dedicated to limit access to the territory or separate buildings. Special guard can be applied for observation of the territory. Its duties include the control of entry (going) out of the staff and visitors of the company. It is necessary to underline that the main purpose of any physical access control mechanisms is to provide its efficiency. However, required efficiency must be achieved without creating additional obstacles to employees that fulfill their obligations. Otherwise they can start looking for possible ways to by-pass these regulations.

11.3.5. Guarantee agreements

Guarantee agreements are legal agreements regarding software put between designer of programs and clients. On their base some third company provides preservation of program text that was designed for the client.. This is a kind of insurance for a client for case if company-designer will leave the project. In such case client will get the right to get texts of the program from the third Party. Otherwise client would remain with application program without any software product maintenance. In this branch of legislation very often mistakes and underestimations can take place. Some experts presume that 95% of all guarantee agreements regarding software do not reach their objectives. To avoid possible mistakes it is necessary to reason out very carefully the following:

1) types of materials that are stored;

2) procedures of these materials update and currency support;

3) information about usage of software from outside producers;

4) necessity of verification of materials that are stored;

5) terms of transfer materials into the ownership of the client;

6) distinct regulation of the stored materials transfer process.

11.3.6. Maintenance agreement

Maintenance agreement must be put for all equipment that is used in the company and software of outside design or production. Determined period of waiting in case of failure or error depends on the importance of element that failed for normal operating of the system. For example, in case of DB server failure, reaction of service company should be immediate, and the system must be able to work as soon as possible. However in case of printer failure the agreement in general can foresee the elimination of malfunction within one or even two-three working days. It is meant that it is always possible to find appropriate reserve printer that can be used before the malfunction will be eliminated. In some cases agreement can foresee the temporary substitution of defective equipment for the period of technical devices repair.

11.4. Particularities of statistic database security

Ordinary statistic DB is applied for the storage of corresponding information. For example, for estimation of average and reduced indexes of different data sets. Information of separate records of statistic DB must be stored confidentially and must not be available for users. The main problem of work with such DB types refers possibility to use replies on admissible queries to get replies on prohibited queries. Different strategies can be used to resolve this problem.

  1. To prevent carrying out queries that operate with small quantity of DB records.
  2. Accidental addition of extra rows the basic data set that is processed by query. As a result the answer will contain an error and will permit to estimate correct value only.
  3. Application of accidental outcome data sets to generate replies on queries.
  4. Saving historic information about the queries execution products and rejection of any queries that operate with significant quantity of outcome data processed by previous query.

11.5. Users access to the database control models

11.5.1. Basic access control models Mandatory model

Mandatory model is based on concept of secret circulation of documents that is applied in government institutions [23]. Levels of access that are assigned to each object and user are distinctly determined and ordered according to increase of secrecy. There are two basic rules:

  1. User can read only those objects that have access level similar to his one.
  2. User can modify only those objects that have access level similar to his one.

First rule is clear. Second rule supplements first one. It prevents intentional or unintentional disclose of secrets for users with lower access levels.

Such access system has some disadvantages:

  1. Model has no mechanism of access limitation for objects of equal level of secrecy in case when both users with equal access levels fulfill different obligations and must not be informed about activity of each other.
  2. Significant limitation of communication between users that have different access levels. From one side user has no feedback with his colleagues that have higher access level. He will never know if his information was received as he left the information for them on his access level. From the other side, comments of users will be never seen by those who have lower access level.

That is why mandatory model in practice is applied together with other models. Discretionary model

Discretionary model is based on access control that is carried out by explicit assignment of authority to users or groups of users to operate each object of the system..

To illustrate formalization of authority assignment access matrix is used. Rows of matrix correspond to users or groups of them. Columns correspond to objects or groups of objects that are assigned access. Each cell of matrix contains set of privileges that has a user regarding corresponding object. User who created object usually has complete range of privileges to carry out operations with object.

In comparison with mandatory model, discretionary model permits to create more flexible security system. From the other side, administration of discretionary model is mush more complicated than administration of mandatory model. Especially it can be felt when there is a big quantity of users and objects that must be assigned access privileges. The solution of this problem can be found in reducing access matrix size thanks to grouping users, typification of objects that must be assigned access and application of typical sets of privileges that are called access schemes.

When grouping of users is applied, privileges are assigned to groups and not to specific users. Moreover, the same user can be a member of several groups. Thanks to this method, quantity of access matrix rows can be reduced. Typification of access objects is carried out according to specific signs that provide the possibility to create groups of typical objects. The same as in case with users object can be a part of several groups. This permits to reduce quantity of access matrix columns.

In practice, it is very difficult to reason out Security Policy that is to divide privileges between users of system correctly, even when size of access matrix is reduced. Methods and means that permit to carry out such scheme are described in special literature [1, 3, 4]. Role model

Role model is based on grouping several operations or actions in one set that is called role.

Each user of the system plays his own role. His role in the system corresponds to the role that he plays in the company. To avoid intersection of operations within the bounds of several different roles, hierarchical subordination is determined. In such way, role that is higher according to subordination can include other roles (operations) that have lower status, and one or several separate operations that are natural for this role only.

A particularity of role model is that objects of the system have no «owner». User can not be assigned access to the object or series of objects. All information is considered to be one that belongs to the company in general. That is why administration of role model is much easier than discretionary one.

Simplicity of role modeling causes several disadvantages:

  1. Assignment of role to the user regarding all objects of the system causes necessity to separate fields of management or impact of users on business processes by means of creating several roles of the same type, that have attitude to administratively independent objects of the company. Thus, there will be as many roles «head of department» as there are department of the same type in the company. There can be other roles within departments. This problem can be resolved by dividing system on domains and using separate role model for each domain. Some experts consider this method to bemore setisfiying.
  2. Narrowing the means of the role model application for the system that doesn’t foresee application of author materials in the documents. This disadvantage is cause by lack of possibility to determine the «owner» of the object.
  3. Operations that can be carried out by users concern absolutely all objects of the system. Thus, for example, anyone can delete any object from the system either he created or used this object or not. The same principle concerns «creating» and «adjustment» of the object that can be unexpected for the colleagues who operate in the same field. To resolve this problem within the bounds of role model is possible thanks to elementary operations differentiation. Required identifier of the object is added to the name. For example: «canceling agreement», «removing bill of lading» and etc.

In some cases disadvantages of role model can be balanced with outer means. For example, assigning specific characteristics to the objects of the system: “owner of the object”, “secrecy level” and etc. In such case procedure of checking is placed in the appropriate part of application program to verify the information that is provided both with role security module or the object. Some libraries of role security provide the possibility to compose scripts of object execution operation verification. These scripts differ from abovementioned procedures of verification by the place of location (they are saved in library settings) and by the fact that they are written in another language.

11.5.2. DB objects discretionary access model implementation

Standard of SQL language uses DB objects discretionary access model. Both object and mechanism of protection are nested directly in DB. DBMS provides the possibility to adjust this mechanism. Users of DB are registered on the level of DBMS, but the information about them is reflected directly in the DB, where they are assigned some privileges.

Access privileges is a possibility to carry out determined kind of operation for specific DB objects. It is provided to the user [4].

Such method helps to avoid stealing information from DB without knowing names and passwords of users by means of its transfer to the system where the copy of DBMS is installed.

Access privileges can be determined both by system administrator (for example in the InterBase it is a user with name SYSDBA on default) and user, who is assigned such right by administrator. For example for DB «LIBRARY» this is a user with name S. Each object that is created in SQL environment has his owner. Owner of the object is the only person that is informed about its existence and is authorized to carry out some operations with it. Assignment and disaffirmation of users’ privileges in SQL is carried out by operators GRANT and REVOKE correspondingly. [10, 14, 15]. Operator GRANT has the following format:

GRANT <Privileges> ON [TABLE] {table name | view name}

    TO {<object> | <user list> [WITH GRANT OPTION]

    GROUP user group name of UNIX}

    EXECUTE ON PROCEDURE Name TO {<object> | <user list>}

    | <role granted> TO {PUBLIC | <role grantee list>}

    [WITH ADMIN OPTION], where

        <Privileges> = {ALL [PRIVILEGES] | <privileges list>};

        <privilege list> = SELECT | DELETE | INSERT | UPDATE [(col.[, col. …])] | REFERENCES [(col.[, col. …])][, <privilege list> …];

        <object> = PROCEDURE Name | TRIGGER Name | VIEW Name | PUBLIC [, <object> …];

        <user list> = [USER] user name | role name | {user group name of UNIX} [, <user list> …];

        <role granted> = role name[, role name …];

        <role grantee list> = [USER] user name[, [USER] user name …].

After proposition GRANT access privileges from the set accepted for ISO standard are determined: SELECT – carries out right to select data from the table; INSERT – carries out right to insert new rows to the table; UPDATE – carries out right to modify data in the table; DELETE – carries out right to delete rows from the table; REFERENCES – carries out to refer to columns of indicated table in the description of data integrity support requirements; USAGE – carries out right to use domains, verifications, sets of symbols and translations. If user if provided complete <privileges list>, instead of the list proposition ALL [PRIVILEGES] is indicated.

After proposition ТО must be obligatory mentioned list of objects or list of users that are assigned privileges of access to the table or data presentation indicated after ON [TABLE] or permission to carry out saving procedure (EXECUTE ON PROCEDURE Name). List of users is formed from names that are registered in DBMS. List of objects can contain saving procedures, triggers and data presentations that are determined inside DB. The proposition PUBLIC assigns privileges not only to all existing users, but also to all users who will be determined in the DB later.

Phrase WITH GRANT OPTION permits all users mentioned in the list to pass all authorities assigned to them concerning the object. If these users pass their own authorities to other users indicating the phrase WITH GRANT OPTION, they will get the right to pass authorities to others too. If this phrase is not mentioned, recipient of privilege will be unable to pass his rights to other users. Thus the owner of the object can control who gets the right to access object and what privileges are provided.

Construction GROUP users group name UNIX permits to provide privileges of the access to the tables and inserted data to users who are determined on the level of operational system UNIX. This option is appended to application programs-clients only. These programs work under the control of operational system. It is not standard for SQL. Name of group must be determined in /etc/group. Other particularities of operator GRANT are described in DBMS documentation.

Operator REVOKE has the following format:

REVOKE <privileges> ON [TABLE] {table name | view name}

    FROM {<object> | <userlist>

    GROUP {name of users group UNIX}

    EXECUTE ON PROCEDURE name ТO {<object> | <userlist>}

    | <role granted> ТO {PUBLIC | <role grantee list>}

    GRANT OPTION FOR {<userlist>}

All parameters have content similar to parameters of operator GRANT, except parameter GRANT OPTION FOR that takes away the right to provide privileges from users from the <userlist>. Privileges can be eliminated only by a person who provided them. If the user looses privileges of particular kind, privileges of other kinds he still owns.

Availability of operators CREATE ROLE and DROP ROLE in DBMS doesn’t indicate the presence of role access model elements to DB objects (see <role granted> of operator GRANT syntax). These operators are applied to create groups of users that have equal authorities. It completely corresponds to discretionary model. See the format of operators for the chosen DBMS in corresponding documentation. For example in DBMS InterBase is has the following format:

CREATE ROLE <role name>;

DROP ROLE <role name>.


  1. What problems arise when limiting mechanisms for protecting data in embedded DBMS?
  2. What factors determines the speed of database recovery after a negative interference?
  3. What potential threats to DB do you know?
  4. What computer control methods are applied for the DB protection?
  5. What computer control methods are applied to provide authorities and for identification of user?
  6. What mechanism of computer control gives the possibility to limit data access?
  7. What mechanisms of protection has DB itself?
  8. What are the particularities of applying auxiliary procedures that are responsible for the DB protection?
  9. Describe the classification of non-computer control means.
  10. What is the difference between protection means and unforeseen circumstances scheduling?
  11. What non-computer precautions that concern protection of rooms and staff control do you know?
  12. What legal precautions that provide data protection you know?
  13. What are the particularities of data protection in statistic DB?
  14. Give the definition of basic data access control models that you know.
  15. What are the particularities of mandatory access control model protection?
  16. In what data access control model and how the access matrix is applied?
  17. What methods are applied to reduce access matrix?
  18. What are the advantages and disadvantages of the role data access model?
  19. How is possible to compensate disadvantages of role model?
  20. What particularities of establishing and elimination of access privileges on the DBMS level you know?

DBMS is an essential part of information system of the company. So the problem of information security in the DB is a complex one. It must be resolved within the bounds of information activity object protection by computer and non-computer means. Thus implementation of user access control model is just the last defensive line to protect the system from unauthorized data access.

© Yaroslav Kuvaiev, 2005—2020.

All rights reserved.

All information placed on this Web site is designed for internal use only. Its reproduction or distribution in any form is prohibited without the written permission from Author.