How secure Is Your Database Information ?-Database Security

The scope of database security

Overview

All systems have ASSETS and security is about protecting assets. The first thing, then, is to know your assets and their value. In this content, will concentrate on database objects (tables, views, rows), access to them, and the overall system that manages them. Note that not all data is sensitive, so not all requires great effort at protection. All assets are under threat. 

The second thing is to know what threat are putting your valuable assets at risk. This includes things such as employee fraud, power failure. Note that threats are partly hypothetically , always changing and are always imperfectly known. Security activity is directed at protecting the system at perceived threats.

If a threat is potential, you must allow for it to become an actuality. When it becomes actual there is an IMPACT. Impact you can consider and plan for. But in the worst case, there will be a LOSS. Security activity here is directed at minimizing the loss and recovering the database to minimize the loss as well as further protecting from the same or similar threats.

Threats to the Database

You will build your security skills from two directions. One is from the appreciation and awareness of changing threats, and the other from the technical remedies to them. Threats include:

• Unauthorized modification: Changing data values for reasons of sabotage, crime or ignorance which may be enabled by inadequate security       mechanisms, or sharing of passwords or password guessing, for example.
• Unauthorized disclosure: When information that should not have been disclosed has been disclosed. A general issue of crucial importance,      which can be accidental or deliberate.
• Loss of availability: Sometimes called denial of service. When the database is not available it incurs a loss (otherwise life is better without the system!). So any threat that gives rise to time offline, even to check whether something has occurred, is to be avoided

Some of the categories of specific regulatory threats to database systems:

• Commercial sensitivity.
• Personal privacy and data protection.
• Computer misuse.
• Audit Requirements.

In considerations of logical access to the database, it is easy to lose sight of the fact that all system access imposes risks. If there is access to operating system utilities, it becomes possible to access the disk storage directly and copy or damage the whole database or its components. A full consideration has to take all such access into account. Most analysts would be looking to minimize communications (direct, network and telecommunications) and isolate the system from unnecessary threats. It is also likely that encryption would be used both on the data and the schema. Encryption is the process of converting text and data into a form that can only be read by the recipient of that data or text, who has to know how to convert it back to a clear message. You will find it easier to consider security and auditing as issues separate from the main database functions, however they are implemented. Visualize the security server and audit servers as separate functional modules.

You do need to accept that security can never be perfect. There is always an element of risk, so arrangements must be made to deal with the worst eventuality- which steps to minimize impact and recover effectively from loss or damage to assets. Points to bear in mind:

1. Appropriate security – you do not want to spend more on security than the asset is worth.
2. You do not want security measures to interfere unnecessarily with the proper functioning of the system.

The following controls are security models to consider when implementing your database structure:

• Access Control.
• Identification and Authentication.
• Authorization.
• Network Security.
• Non-Denial/ Audit Trail

Mandatory control is authorization by level or role. A typical mandatory scheme is the four level government classification of open, secret, most secret and top secret. The database implication is that each data item is assigned a classification for read, create, update and delete (or a subset of these), with a similar classification attached to each authorized user.

Access Control in SQL

This section is about the implementation of security within SQL. The basics are given in Oracle SQL but, as you will realise, much security is DBMS- and hardware specific. Where necessary, any specifics are given in the SQL of Oracle. For some ideas on Object database management systems (ODBMS) as distinct from Relational. Your first objective is to learn the specifics. The access requirements specification will be implemented using these statements. Your second objective is to extend your understanding of the problem through to the management and audit functions of an operating system.

Discretionary in SQL

This section introduces the SQL statements needed to implement access control. You should aim at having sufficient knowledge of this area of SQL to translate a simple specification into an SQL script. You should also be conscious of the limitations implicit in this script which hardwires passwords into text. The basics of SQL are inherently discretionary. Privileges to use a database resource are assigned and removed individually.

As an example, the supplied roles in Oracle include (among others):

• SYSOPER: Start and stop the DBMS.
• DBA: Authority to create users and to manage the database and existing users.
• SYSDBA: All the DBA’s authority plus the authority to create, start, stop and recover.

Schema Level

The first security-related task is to create the schema. In the example below, the authorization is established with the schema. The authorization is optional and will default to the current user if it is not specified.

Only the owner of the schema is allowed to manipulate it. Below is an example where a user is given the right to create tables. The creator of the table retains privileges for the tables so created. Similarly, synonyms are only valid for the creator of that synonym.

CREATE SCHEMA student_database AUTHORISATION U1;

The U1 refers to the authorisation identifier of the user concerned, who has the right to create database objects of this type – in this case, the schema for a new database. Provided the authorisation is correct, then the right to access the database using the schema can be granted to others. So to allow the creation of a table example below:

GRANT CREATETAB TO U1 ;

Authentication :  Using the client/server model approach, it is necessary first to connect to the database management system, effectively establishing both authentication and the complex layers of communication between the local (client DBMS)  and the Server.

GRANT CONNECT TO stundent_database AS U1,U2,U3 IDENTIFIED BY P1,P2,P3; in this case U1,U2,U3 refers to Users given access to the student database and the P1,P2,P3 refers to password given to  U1,U2,U3.

GRANT CONNECT TO student_database AS U4/P4;

Connect rights give no permission for any table within the database. U4/P4 are the identifiers known to this database security services.

Note
Users, roles and privilege levels can be confusing. The following are the key distinctions:

•      A user is a real person (with a real password and user account).
•     A role, or a user-role, is a named collection of privileges that can be easily assigned to a given or new user. A privilege is a permission to       perform some act on a database object.
•   A privilege level refers to the extent of those privileges, usually in connection with a database-defined role such as database administrator.

The below  figure from Oracle illustrate  a security model- approach that organization can implement to safeguard their valuable information from a database perspective:

Note: Its important that any organization in this information age, invest substantially in protecting itself against many threats that are waging out there,  A security breach can be catastrophic,  it is better to be safe that sorry , there is old saying that says, “prevention is better than cure”

For instance MySQL does not create default password that  can be exploited for access. This comes later through an administrator’s lack of understanding, sadly. More often than not, the grant will look something like this:

GRANT ALL PRIVILEGES TO ‘root ‘@’ %’ IDENTIFIED BY ‘12345′ WITH GRANT OPTION; You may scoff at the above (and rightly so). However, don’t discount this just yet: “123456” was the MOST USED password in 2016! So it’s reasonable to assume that somewhere out there this is a reality.

MySQL 5.7 has a new open source plugin; connection control  it delays the authentication of users that failed to login by default more than three times.  To view how many login failures from your MySQL DB Vault can issue this command: mysql> SELECT * FROM INFORMATION_SCHEMA.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;

Another approach which can limit the approach of having user granted privileges to the entire table is to grant a user specific privileges as per the role that they perform in a company, for example the below assigns a read privilege to a named table (note only a read privilege). The privilege extends to creating a read-only view on the table:

GRANT SELECT ON EMPLOYEES TO BILL; and that which can be given can be revoked; REVOKE SELECT ON EMPLOYEES  FROM BILL;

The main part of this aspect of security, though, is providing access to the data. In a Relational database we have only one data structure to consider, so if we can control access to one table we can control access to all. And as tables are two dimensional, if we can control access to rows and columns, we can deal with any request for data – including schema data. We still have to know what is allowed and what is not but, given the details, the implementation is not in itself a problem.

 To provide privileges at the level of the row, the column or by values, it is necessary to grant rights to a view. This means a certain amount of effort but gives a considerable range of control.  Assume the following view:

CREATE VIEW LARGE_ORDERS

AS SELECT   ordernr, ordername, quantity, orderamount
FROM ORDERS
WHERE ORDERAMOUNT < 20000;

‘and the privilege is now assigned’

GRANT SELECT ON LARGE_ORDERS TO Shelly
WITH GRANT OPTION;

The optional “with grant option” allows the user to assign privileges to other users. This might seem like a security weakness and is a loss of DBA control. On the other hand, the need for temporary privileges can be very frequent and it may be better that a user assign temporary privileges to cover for an office absence, than divulge a confidential password and user-id with a much higher level of privilege.

The optional “with grant option” allows the user to assign privileges to other users. This might seem like a security weakness and is a loss of DBA control. On the other hand, the need for temporary privileges can be very frequent and it may be better that a user assign temporary privileges to cover for an office absence, than divulge a confidential password and user-id with a much higher level of privilege.

The rights to change data are granted separately:

GRANT INSERT ON TABLE1 TO U2, U3;
GRANT DELETE ON TABLE1 TO U2, U3;
GRANT UPDATE ON TABLE1(salary) TO U5;
GRANT INSERT, DELETE ON TABLE1 TO U2, U3;

To provide general access to a database: GRANT ALL TO PUBLIC;

Treat the following principles as abstract. Every company that has implemented data protection has followed these guides but, as usual, ‘the devil is in the detail’. If you can be sure your database system complies with these you have done well (security Plan):

•  Identify the user community.
•  Gather the database information.
•  Determine the types of user account (i.e. associate database objects and    user roles).
•  Undertake a threat analysis.
•  Establish DBA authorities and procedures.
•  Establish policies for managing (creating, deleting, auditing) user   accounts.
•  Determine the user tracking policy.
•  Establish the user identification method.
•  Define security incidents and reporting procedure.
•  Assess the sensitivity of specific data objects.
•  Establish standards and enforcement procedures (as well as back-up and     recovery plans, of course).

Authentication and Authorization Schematic 

Generally speaking, when you log into a system, you want to be satisfied that you have logged into the right system and the system equally wants to be satisfied that you are who you claim to be. Think about this with an Internet Banking System as an example. Could a line be intercepted and the person on the other side pretend to be the bank while you disclosed passwords and account numbers? Could someone access the bank and empty your accounts?

The part of the process that deals with this area is the authentication server. There are only two ways to establish authentication: by means of shared secrets – things known only to you and the system – or by appealing to another system that has gone through the same process and is trusted. It is the second point that enables distributed transactions, at least without multiple log-ins. Shared secrets include passwords and personal information as reported to and stored  by the system.

In each case, a dialogue appropriate to the situation has to be developed, monitored and managed. In the latter case, notice the need for secure communication between different system components, as secret information or its surrogate has to be transmitted between systems. The result of authentication is a vector that contains an authentication identifier, usually with other information including date and time. Note that authentication is quite separate from access to database resources. You need to have obtained an authentication identifier before you start accessing the database. In an SQL database system, the authentication process is initiated by the CONNECT statement. After successful execution of CONNECT, the resources of the database become potentially available.

Each SQL object has an owner. The owner has privileges on the objects owned. No other user can have any privileges (or even know the object exists) unless the owner supplies the necessary permission. In normal development, the DBA or system administrator will be the owner of the major assets. The scheme is basically discretionary. You need to look at security in SQL more as a toolkit than a solution.

Access control in SQL is implemented by transactions using the GRANT statement. These associate privileges with users and assets. Assets include data items (tables, views) and the privileges are the responsibility of the asset owner.

Database security is define as the process by which “Confidentiality, integrity and availability” of the database can be protected. Hope this blog post will invoke and stimulate a number of questions regarding the protection of your valuable information assets in your organization, and will lead you in taking appropriate steps to safeguard your data.

If you any questions regarding this post, please leave them below, will attend to them ASAP. Check out my Next Post on “Database Backup,  restoration and recovery.”