Skip to content

Database Security Mechanisms

  • Securing the database alone is not enough, the entire system must be secured.
  • All parts of the system must be secure: the database, the network, the operating system, the building in which the database resides physically, and the persons who have an opportunity to access the system.
  • Objectives of Database security:
    • Privacy. Data should not be known by unauthorized users.
    • Integrity. Only authorized users can modify data.
    • Availability. authorized users should not be denied access to data.
  • DBA Roles:
    • Responsible for the security of the database.
    • Takes into account various threats to the database.
    • Manages the database authorization controls.
  • Types of threats:
    • Theft and Fraud.
    • Loss of privacy or confidentiality.
    • Loss of data integrity.
    • Loss of availability.
    • Accidental losses of data.
  • The most used measures to ensure the protection and integrity of data include access control, views, integrity controls, and encryption.
  • It is also necessary to establish appropriate security policies and procedures, which refer to personnel and physical access control.

Access Control

  • Two security approaches:
    • Discretionary access control. User has different access rights (privileges) to different objects in the database. e.g. user has access to table A but not to table B.
    • Mandatory access control. Each object in the database has an access level, and each user is given a permission level, if the user’s permission level is higher than the object’s access level, the user can access the object. hierarchial access control. e.g. a user with a permission level of 3 can access objects with access levels 1, 2, and 3, but not levels 4 and 5.

Authentication

  • The first action to take when connecting to the database.
  • Identifies the user requesting access to the database.
  • Done outside of the DBMS at the operating system level or third-party software such as Kerberos or Lightweight Directory Access Protocol (LDAP).
  • Authentication requires a User ID and an Authentication Token.
  • After the successful authentication of a user, the authenticated user ID is mapped to an authorization ID.
  • Authorization ID has two parts:
    • System authorization ID. The user ID is used by the DBMS to identify the user and check their privileges.
    • Session authorization ID. The connection ID.

Authorization

  • It is the process of allowing or denying access to resources for authenticated users.
  • The definition of authorization contains the terms subject and object, aka, authorize a subject to perform an action on an object.
    • The subject refers to a user or program.
    • The object refers to a table, a view, an application, a procedure, or any other object that can be created in the system.
  • Done by the DBMS itself.
  • DBMS saves authorization information in the metadata tables.
  • The DBMS requires that each user be specifically, implicitly, or explicitly authorized.

Administrative authority

  • Administrative authority confers to a person who has the right to control the database and has the responsibility for data integrity.
  • Hierarchical by nature.
  • The highest is SYSADM then there are two levels of authority (grouped privileges):
    • Instance level. Access all databases in an instance.
    • Database level. Access only one database.

Instance Level authority

  • SYS_AD. System administrator. Has the highest authority in the database. Can perform all administrative tasks. migrate databases, modify the database manager configuration and database configuration files, perform database and log file backups and restoration of database and database objects such as tablespaces, grant and revoke other authorities and privileges to and from users, groups, or roles, full control of instances and manage audit on the instance level.
  • SYS_CTRL. System controller. Control over operations that affect system resources. can create, update start, and stop a database. It can also start and stop an instance, but cannot access data from tables.
  • SYS_MAINT. System Maintainer. Allows performing maintenance tasks on all databases associated with an instance. update the database configuration, back up a database or a tablespace, restore an existing database, or monitor a database. SYSMAINT does not allow access to data.
  • SYS_MON. System Monitor. Authorized to use the database system monitor tool.

Database Level authority

  • DB_ADM. Database Administrator. Has SYS_AD over this database. A database administrator has all the privileges to create objects, execute database commands, and access all data. He can also grant and revoke individual privileges. A database administrator can create log files, query system catalog tables, update log history files, reorganize database tables or collect catalog statistics.
  • SEC_ADM. Security Administrator. has the authority to create, drop, grant/revoke authorization or privileges, and transfer ownership of security objects (e.g. roles and LBAC labels). It has no privileges to access data from tables.
  • CONNECT. Allows Connection to the database.
  • BIND_ADD. Allows the user to create new packages in the database.
  • CREATE_TAB. Allows the user to create new tables in the database.
  • CREATE_EXTERNAL_ROUTINE. Allows the user to create procedures in the database.
  • CREATE_NOT_FENCED_ROUTINE. allows the user to create a user-defined function (UDF) or procedure that is not fenced
  • IMPLICIT_SCHEMA permits any user to create a schema by creating an object by CREATE statement with a schema name that does not already exist. In this case, SYSIBM becomes the owner of the implicitly created schema and PUBLIC has the privilege to create objects in this schema.

Privileges

  • Privileges are authorities assigned to users, groups, or roles, which allow them to accomplish different activities on database objects
  • Privileges define the tasks a user can perform on database objects and can be granted to individual users, groups, roles, or PUBLIC.
  • PUBLIC means the user must be authenticated, but no need to be authorized. aka. All authenticated users are authorized to perform the action.
  • Privileges that are PUBLIC by default:
    • CONNECT, CREATETAB, BINDADD,
    • IMPLICIT_SCHEMA,
    • SELECT, UPDATE, EXECUTE, USE
  • Users With CONTROL privileges can give them to other users or groups.

Label-Based Access Control (LBAC)

  • Label-Based Access Control (LBAC) is a flexible implementation of mandatory access control (MAC).
  • LBAC acts at both the row level and the column level and completes the discretionary access control (DAC).
  • Configurations are performed by a security administrator by creating security policies that describe the criteria that will be used to decide who has access to what data.
  • Security labels are created within the Security Policy.
  • Security Labels are then associated with columns or rows in tables to protect their data.
  • Data protected by a security label is called protected data. A security administrator allows users access to protected data by granting them security labels. When a user tries to access protected data, their security label is compared to the security label protecting the data.
  • Exemptions are granted to users to bypass label-protected data.
  • Policies, Labels, and Exemptions are called LBAC credentials and are stored in the database catalogs.
  • The main advantage of using LBAC to protect important data is that no authority (SYSDBA, DBADM, and SECADM) has any inherent privileges to access your data.

Roles

  • A database role is an object that groups together one or more privileges or database authorities. It can be assigned to users, groups, PUBLIC, or other roles by a GRANT statement. For example, we can define a developer role and allow this role to insert, update and delete data on a set of tables.
  • By associating a role with a user, the user inherits all the privileges held by the role, i**n addition to privileges already held by the user**.
  • Roles can be updated without updating the privileges for every user.

Trusted Contexts

  • Trusted contexts establish a trusted relationship between DB2 and an external entity like an application server or another DB2 server. This trust relationship is based upon the following attributes: system authorization/IP address/domain name and data stream encryption.
  • After a database connection has been established, the connection is compared to the saved trusted contexts information in the DB, if it matches one, the connection is marked a trusted connection.
  • This trusted connection allows its initiator to acquire additional capabilities that are not available to them outside the scope of that trusted connection.
  • Trusted Connections can be:
    • Explicit. A connection that has explicitly requested to be trusted. Allowed to switch the current user ID on the connection to a different user ID, with or without authentication, and to acquire additional privileges
    • Implicit. A connection that has not explicitly requested to be trusted. Allowed extra privileges, but Can not switch the current user ID.
  • Trusted Connection attributes:
    • System authorization ID. The authorization ID of the user of the incoming connection.
    • IP addresses. List of IP addresses that the incoming connection can come from.
    • Data stream encryption. The level of encryption that must be used by an incoming connection in order to be trusted.
  • In a three-tier architecture:
    • A trusted connection is established between the application server and the database server.
    • The application server is the trusted context initiator and the database server is the trusted context target.
    • The application server is responsible for authenticating and authorizing users; hence, the connection privileges change by the authenticated user, so that the application server must have all privileges initially, then it can select some of these privileges to perform tasks on behalf of the user.

Views

  • Views allow a user to see information while hiding any information that the user should not be given access to
  • A view is the dynamic result of one or more relational operations that apply to one or more base tables to produce another table.
  • A view is always based on the current data in the base tables from which it is built.
  • The advantage of a view is that it can be built to present only the data to which the user requires access and prevent the viewing of other data that may be private or confidential.
  • A user may be granted the right to access the view but not to access the base tables upon which the view is based.

Integrity Control

  • The aim of integrity control is to protect data from unauthorized use and update, by restricting the values that may be held and the operations that can be performed on data.
  • The integrity controls may also trigger the execution of some procedure, such as placing an entry in a log that records what users have done what with which data.
  • There are more forms of integrity controls:
    • Integrity of the domain: any value to be inserted in a column must be within the domain of the column; aka, domain constraints.
    • Assertions: a condition that must be true for a row to be inserted or updated; the DBMS check assertions automatically when a transaction begins on tables or fields.
    • Triggers: a procedure that is executed when a certain event occurs; which runs only in response to INSERT, UPDATE, or DELETE statements.

Data encryption

  • Sensitive and personal data stored within the database tables and critical data transmitted across the network, such as user credentials (user ID and password), are vulnerable and should be protected against intruders.

Security policies and procedures

  • Personnel control:
    • Often the greatest threat to data security is internal rather than external, so adequate controls of personnel must be developed and followed.
    • The security authorization and authentication procedures must be enforced by procedures which ensure a selection hiring process that validate potential employees regarding their backgrounds and capabilities.
    • Employees should be trained in those aspects of security that are relevant to their jobs and encouraged to be aware of and follow standard security measures.
    • If an employee should need to be let go then there should be a set of procedures to remove authorizations and authentications and to notify other employees about the status change
  • Physical access control:
    • A proximity access card can be used to gain access to a secured area. In this case, each access can be recorded in a database.
    • Sensitive equipment can be controlled by placement in a secure area.
    • Backup data media should be kept in fireproof data safe or kept outside in a safe location.
    • Procedures must explicitly state a schedule for moving media or disposing of media, and establish labeling and indexing of such materials stored.
    • Encryption and multiple factor authentication can protect data.
    • Antitheft devices, like security cables or geographical tracking chips, can help determine theft and quickly recover laptops on which critical data are stored.

References

Sharma, N., Perniu, L., Chong, R.F., Iyer, A., Nandan, C., Mitea, A.C., Nonvinkere, M., & Danubianu, M. (2010). Database Fundamentals (1st ed.). Markham, ON: IBM Corporation. Chapter 9.