lOMoARcPSD|22191715

CB3401-UNIT 5 - UNIT WISE NOTES

Database Management System and Security (Anna University)

Scan to open on Studocu

Studocu is not sponsored or endorsed by any college or university

Downloaded by sneha sivalingam (snehaakm2001@gmail.com)
 lOMoARcPSD|22191715

II BE CSE(CS) Prepared By: R. RESHMA

CB3401: DATABASE MANAGEMENT SYSTEMS AND SECURITY
LECTURE NOTES

UNIT V ACCESS CONTROL AND ENCRYPTION 9

Database Access Control – SQL-based access definition – Cascading Authorizations – Role-based access
control – Inference – Database encryption

Introduction to Access Control in DBMS

Access control is defined as a security technique used to regulate who has the authority to view what data;
while encryption simply encodes all data into an unreadable format and only allows access if one holds the
decryption key. Database access control, or DB access control, is a method of allowing access to a company’s
sensitive information only to user groups who are allowed to access such data and restricting access to
unauthorized persons to prevent data breaches in database systems.

Database Access Control in DBMS includes two main components: authentication and authorization.

Authentication is a means of confirming a person’s identity when accessing your database. It is important to
remember that user authentication is not enough to keep data safe. Authorization, which establishes whether a
user’s level of access or data access control is appropriate, is an additional layer of protection. Ultimately, there
is no data security without authentication and authorization. Every company today that has employees who
interact with data, and thus every organization, needs to establish data access control.

Database Security means keeping sensitive information safe and prevent the loss of data. Security of data base
is controlled by Database Administrator (DBA).
The following are the main control measures are used to provide security of data in databases:
1. Authentication
2. Access control
3. Inference control
4. Flow control
5. Database Security applying Statistical Method
6. Encryption
These are explained as following below.
1. Authentication :
Authentication is the process of confirmation that whether the user log in only according to the rights
provided to him to perform the activities of data base. A particular user can login only up to his
privilege but he can’t access the other sensitive data. The privilege of accessing sensitive data is
restricted by using Authentication.
Using these authentication tools for biometrics such as retina and figure prints can prevent the database
from unauthorized/malicious users.
2. Access Control: The security mechanism of DBMS must include some provisions for restricting access
to the database by unauthorized users. Access control is done by creating user accounts and controlling
the login process by the DBMS. So, database access of sensitive data is possible only to those people
(database users) who are allowed to access such data and to restrict access to unauthorized persons.
The database system must also keep track of all operations performed by certain users throughout the
entire login time.
3. Inference Control: This method is known as the countermeasure to statistical database security
problems. It is used to prevent the user from completing any inference channel. This method protects
sensitive information from indirect disclosure. Inferences are of two types, identity disclosure and
attribute disclosure.
4. Flow Control: This prevents information from flowing in a way that it reaches unauthorized users.
Channels are the pathways for information to flow implicitly in ways that violate the privacy policy of
a company and are called convert channels.
5. Database Security applying Statistical Method: Statistical database security focuses on the protection
of confidential individual values stored in and used for statistical purposes and to retrieve summaries of
values based on categories. They do not permit to retrieve the individual information. This allows to
access the database to get statistical information about the number of employees in the company but not
to access the detailed confidential/personal information about the specific individual employee.
6. Encryption: This method is mainly used to protect sensitive data (such as credit card numbers, OTP
numbers) and other sensitive numbers. The data is encoded using some encoding algorithms.

Dhanalakshmi Srinivasan College of Engineering, Coimbatore

Downloaded by sneha sivalingam (snehaakm2001@gmail.com)
 lOMoARcPSD|22191715

II BE CSE(CS) Prepared By: R. RESHMA

An unauthorized user who tries to access this encoded data will face difficulty in decoding it, but
authorized users are given decoding keys to decode data.

Access control is a security technique that regulates who or what can view or use resources in a computing
environment. It is a fundamental concept in security that minimizes risk to the business or organization.
There are two types of access control: physical and logical. Physical access control limits access to campuses,
buildings, rooms and physical IT assets. Logical access control limits connections to computer networks,
system files and data.
To secure a facility, organizations use electronic access control systems that rely on user credentials, access
card readers, auditing and reports to track employee access to restricted business locations and proprietary
areas, such as data centers. Some of these systems incorporate access control panels to restrict entry to rooms
and buildings, as well as alarms and lockdown capabilities, to prevent unauthorized access or operations.
Logical access control systems perform identification authentication and Authorization of users and entities by
evaluating required login credentials that can include passwords, personal identification
numbers, biometric scans, security tokens or other authentication factors. Multifactor authentication (MFA),
which requires two or more authentication factors, is often an important part of a layered defense to protect
access control systems.

Why is access control important?

The goal of access control is to minimize the security risk of unauthorized access to physical and logical
systems. Access control is a fundamental component of security compliance programs that ensures security
technology and access control policies are in place to protect confidential information, such as customer data.
Most organizations have infrastructure and procedures that limit access to networks, computer systems,
applications, files and sensitive data, such as personally identifiable information and intellectual property.
Access control systems are complex and can be challenging to manage in dynamic IT environments that
involve on-premises systems and cloud services. After high-profile breaches, technology vendors have shifted
away from single sign-on systems to unified access management, which offers access controls for on-premises
and cloud environments.

How access control works

Access controls identify an individual or entity, verify the person or application is who or what it claims to be,
and authorizes the access level and set of actions associated with the username or IP address. Directory services
and protocols, including Lightweight Directory Access Protocol and Security Assertion Markup Language,
provide access controls for authenticating and authorizing users and entities and enabling them to connect to
computer resources, such as distributed applications and web servers.
Organizations use different access control models depending on their compliance requirements and the security
levels of IT they are trying to protect.

Types of access control

The main models of access control are the following:
• Mandatory access control (MAC). This is a security model in which access rights are regulated by a
central authority based on multiple levels of security. Often used in government and military
environments, classifications are assigned to system resources and the operating system or security
kernel. MAC grants or denies access to resource objects based on the information security clearance of
the user or device. For example, Security-Enhanced Linux is an implementation of MAC on Linux.
• Discretionary access control (DAC). This is an access control method in which owners or
administrators of the protected system, data or resource set the policies defining who or what is
authorized to access the resource. Many of these systems enable administrators to limit the propagation
of access rights. A common criticism of DAC systems is a lack of centralized control.
• Role-based access control (RBAC). This is a widely used access control mechanism that restricts
access to computer resources based on individuals or groups with defined business functions -- e.g.,
executive level, engineer level 1, etc. -- rather than the identities of individual users. The role-based

Dhanalakshmi Srinivasan College of Engineering, Coimbatore

Downloaded by sneha sivalingam (snehaakm2001@gmail.com)
 lOMoARcPSD|22191715

II BE CSE(CS) Prepared By: R. RESHMA

security model relies on a complex structure of role assignments, role authorizations and role
permissions developed using role engineering to regulate employee access to systems. RBAC systems
can be used to enforce MAC and DAC frameworks.
• Rule-based access control. This is a security model in which the system administrator defines the
rules that govern access to resource objects. These rules are often based on conditions, such as time of
day or location. It is not uncommon to use some form of both rule-based access control and RBAC to
enforce access policies and procedures.
• Attribute-based access control. This is a methodology that manages access rights by evaluating a set
of rules, policies and relationships using the attributes of users, systems and environmental conditions.

Implementing access control

Access control is integrated into an organization's IT environment. It can involve identity management
and access management systems. These systems provide access control software, a user database and
management tools for access control policies, auditing and enforcement.
When a user is added to an access management system, system administrators use an automated provisioning
system to set up permissions based on access control frameworks, job responsibilities and workflows.
The best practice of least privilege restricts access to only resources that employees require to perform their
immediate job functions.

Challenges of access control

Many of the challenges of access control stem from the highly distributed nature of modern IT. It is difficult to
keep track of constantly evolving assets because they are spread out both physically and logically. Specific
examples of challenges include the following:

• dynamically managing distributed IT environments;

• password fatigue;
• compliance visibility through consistent reporting;
• centralizing user directories and avoiding application-specific silos; and
• data governance and visibility through consistent reporting.
Many traditional access control strategies -- which worked well in static environments where a company's
computing assets were help on premises -- are ineffective in today's dispersed IT environments. Modern IT
environments consist of multiple cloud-based and hybrid implementations, which spreads assets out over
physical locations and over a variety of unique devices, and require dynamic access control strategies.
Organizations often struggle to understand the difference between authentication and authorization.
Authentication is the process of verifying individuals are who they say they are using biometric
identification and MFA. The distributed nature of assets gives organizations many avenues for authenticating
an individual.
Authorization is the act of giving individuals the correct data access based on their authenticated identity. One
example of where authorization often falls short is if an individual leaves a job but still has access to that
company's assets. This creates security holes because the asset the individual used for work -- a smartphone
with company software on it, for example -- is still connected to the company's internal infrastructure but is no
longer monitored because the individual is no longer with the company. Left unchecked, this can cause major
security problems for an organization. If the ex-employee's device were to be hacked, for example, the attacker
could gain access to sensitive company data, change passwords or sell the employee's credentials or the
company's data.
One solution to this problem is strict monitoring and reporting on who has access to protected resources so,
when a change occurs, it can be immediately identified and access control lists and permissions can be updated
to reflect the change.

Dhanalakshmi Srinivasan College of Engineering, Coimbatore

Downloaded by sneha sivalingam (snehaakm2001@gmail.com)
 lOMoARcPSD|22191715

II BE CSE(CS) Prepared By: R. RESHMA

Another often overlooked challenge of access control is user experience. If an access management technology
is difficult to use, employees may use it incorrectly or circumvent it entirely, creating security holes and
compliance gaps. If a reporting or monitoring application is difficult to use, the reporting may be compromised
due to an employee mistake, which would result in a security gap because an important permissions change or
security vulnerability went unreported.

Access control software

Many types of access control software and technology exist, and multiple components are often used together
as part of a larger identity and access management (IAM) strategy. Software tools may be deployed on
premises, in the cloud or both. They may focus primarily on a company's internal access management or
outwardly on access management for customers. Types of access management software tools include the
following:
• reporting and monitoring applications
• password management tools
• provisioning tools
• identity repositories
• security policy enforcement tools

SQL-based access definition

SQL-based access control is a fundamental aspect of database security, ensuring that only authorized users can
access and manipulate data within a database. It involves the use of SQL statements to define and enforce
access privileges at various levels of granularity. Let's delve into the details of SQL-based access control in
database security:

1. **Authentication and Authorization**:

- **Authentication**: This process verifies the identity of users attempting to access the database. Once
authenticated, users are allowed to proceed to the next step.
- **Authorization**: Authorization determines what actions authenticated users are allowed to perform
within the database. SQL-based access control plays a crucial role in the authorization process.

2. **GRANT and REVOKE Statements**:

- **GRANT**: The `GRANT` statement is used to explicitly give specific privileges to users or roles.
Privileges can include `SELECT`, `INSERT`, `UPDATE`, `DELETE`, `EXECUTE`, etc.
```sql
GRANT SELECT, INSERT ON table_name TO user_name;
```
- **REVOKE**: The `REVOKE` statement is used to revoke previously granted privileges from users or
roles.
```sql
REVOKE INSERT ON table_name FROM user_name;
```

3. **Access Control at Different Levels**:

- **Database Level**: Access control can be applied at the database level to regulate overall access to the
entire database.
- **Table Level**: Access control can be granularly defined at the table level, specifying which users or roles
have access to specific tables.
- **Column Level**: Some database systems support column-level access control, allowing administrators to
restrict access to individual columns within a table.

4. **Roles**:
- Roles are named collections of privileges that can be assigned to users. This simplifies access control
management by grouping common sets of privileges together.
- Users can be granted or revoked roles, and the privileges associated with those roles are automatically
inherited.

Dhanalakshmi Srinivasan College of Engineering, Coimbatore

Downloaded by sneha sivalingam (snehaakm2001@gmail.com)
 lOMoARcPSD|22191715

II BE CSE(CS) Prepared By: R. RESHMA

```sql
CREATE ROLE admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON table_name TO admin;
GRANT admin TO user_name;
```

5. **Views and Stored Procedures**:

- Access control can also be applied to views and stored procedures, allowing administrators to restrict access
to these database objects.
- Users may be granted or denied permission to access specific views or execute stored procedures.

6. **Row-Level Security**:
- Some advanced database systems support row-level security, allowing access control to be applied at the
individual row level based on specified criteria.
- This enables administrators to enforce fine-grained access control policies based on the contents of the data
itself.

SQL-based access definition in database security provides a robust mechanism for controlling access to
sensitive data, ensuring that only authorized users can interact with the database according to predefined access
policies.

Cascading Authorizations
Cascading authorization in a database management system (DBMS) is a concept where the permissions granted
or revoked on a parent object automatically apply to its child objects. This mechanism simplifies access control
management by propagating access privileges throughout the hierarchical structure of database objects. Let's
delve deeper into the details:
1. Hierarchical Structure:
• Databases typically have a hierarchical structure where objects such as tables, views, schemas,
and databases themselves are organized in a parent-child relationship.
• For example, a database contains schemas, each schema contains tables, and each table may
have associated views, triggers, and procedures.
2. GRANT Cascading:
• When access privileges are granted on a parent object, such as a schema or table, those
privileges automatically cascade down to its child objects unless explicitly overridden.
• For instance, if a user is granted SELECT permission on a schema, they will also have
SELECT permission on all tables within that schema by default.
3. REVOKE Cascading:
• Conversely, when access privileges are revoked on a parent object, those revocations cascade
down to its child objects, again unless overridden.
• For example, if INSERT permission is revoked on a table, any views based on that table will
also lose the INSERT permission.
4. Examples:
• Table-Level Authorization: If a user is granted UPDATE permission on a table, any views or
procedures that reference that table will inherit the UPDATE permission.
• Schema-Level Authorization: If a user is granted CREATE TABLE permission on a
schema, any tables created within that schema will automatically inherit that permission.
• Database-Level Authorization: Similarly, permissions granted or revoked at the database
level can cascade down to all objects within the database.
5. Management and Control:
• While cascading authorization simplifies access control management, it's crucial for
administrators to understand and manage these cascading effects effectively.
• Administrators should regularly review and audit permissions to ensure that unintended

Dhanalakshmi Srinivasan College of Engineering, Coimbatore

Downloaded by sneha sivalingam (snehaakm2001@gmail.com)
 lOMoARcPSD|22191715

II BE CSE(CS) Prepared By: R. RESHMA

consequences do not arise from cascading authorizations.
6. Overriding Cascading Permissions:
• In some cases, it may be necessary to override cascading permissions to enforce more
restrictive access controls on child objects.
• This can be achieved by explicitly granting or revoking permissions on specific child objects,
overriding the inherited permissions from the parent object.
Cascading authorization is a powerful feature of DBMS that streamlines access control management by
automatically propagating permissions across the hierarchical structure of database objects. However, it
requires careful planning and management to ensure that security policies are properly enforced and
maintained.
Restrict versus Cascade
The RESTRICT and CASCADE options specify how the DBMS Server handles dependent privileges. The
CASCADE option directs the DBMS Server to revoke the specified privileges plus all privileges and objects
that depend on the privileges being revoked. The RESTRICT option directs the DBMS Server not to revoke the
specified privilege if there are any dependent privileges or objects.
The owner of an object can grant privileges on that object to any user, group, or role. Privileges granted by
users who do not own the object are dependent on the privileges granted WITH GRANT OPTION by the
owner.
For example, if user jerry owns the employees table, he can grant tom the ability to select data from the table
and to enable other users to select data from the table:

GRANT SELECT ON employees TO tom WITH GRANT OPTION;

User tom can now enable another user to select data from the employees table:
GRANT SELECT ON employees TO sylvester WITH GRANT OPTION;

The grant tom conferred on sylvester is dependent on the grant the table's owner jerry conferred on tom. In
addition, sylvester can enable other users to select data from the employees table.
If sylvester creates a view on the employees table, that view depends on the SELECT privilege that tom granted
to sylvester. For example:

CREATE VIEW njemps AS SELECT * FROM employees WHERE state='New Jersey'

To remove his grant to tom, all grants tom can have issued, and any dependent objects, jerry must specify
REVOKE...CASCADE:
REVOKE SELECT ON employees FROM tom CASCADE;

As a result of this statement, the SELECT privilege granted by tom to sylvester is revoked, as are any SELECT
grants issued by sylvester to other users conferring SELECT privilege for the employees table. The njemps
view is destroyed.
T
o prevent dependent privileges from being revoked, jerry must specify REVOKE...RESTRICT:
REVOKE SELECT ON employees FROM tom RESTRICT;
Because there are dependent privileges (tom has granted SELECT privilege on the employees table to
sylvester), this REVOKE statement fails, and no privileges are revoked. The njemps view is not destroyed.
Note: If privileges are revoked from specific authorization IDs (users, groups, and roles) that were also granted
to PUBLIC, privileges and objects that depend on the grants persist (until the privileges are revoked from
PUBLIC).

The RESTRICT and CASCADE parameters have the same effect whether revoking a specific privilege or the
GRANT OPTION for a specific privilege. In either case, RESTRICT prevents the operation from occurring if
there are dependent privileges, and CASCADE causes dependent privileges to be deleted. When revoking a
GRANT OPTION with CASCADE, all dependent privileges are revoked, not only the GRANT OPTION
portion of the dependent privileges.

Dhanalakshmi Srinivasan College of Engineering, Coimbatore

Downloaded by sneha sivalingam (snehaakm2001@gmail.com)
 lOMoARcPSD|22191715

II BE CSE(CS) Prepared By: R. RESHMA

RESTRICT or CASCADE must be specified when revoking privileges on tables, database procedures, or
database events. When revoking database privileges, CASCADE, RESTRICT or GRANT OPTION cannot be
specified (because database privileges cannot be granted with GRANT OPTION).

Role-based access control (RBAC)

Role-based access control (RBAC) is a sophisticated access control mechanism that streamlines permission
management by associating users with roles, which in turn are assigned specific permissions. This approach
enhances security and simplifies access control administration, especially in large organizations with complex
access requirements. In RBAC systems, users are assigned roles based on their job functions or responsibilities,
and permissions are granted to these roles rather than directly to individual users. Let's illustrate RBAC with
some code snippets and examples:
sql
-- Example 1: Creating Roles
CREATE ROLE admin;
CREATE ROLE manager;
CREATE ROLE employee;

sql
-- Example 2: Granting Permissions to Roles
GRANT SELECT, INSERT, UPDATE, DELETE ON employees TO admin;
GRANT SELECT, INSERT ON employees TO manager;
GRANT SELECT ON employees TO employee;

In this example, we grant different levels of access permissions on the "employees" table to the created roles.
The "admin" role has full access (SELECT, INSERT, UPDATE, DELETE), the "manager" role has limited
access (SELECT, INSERT), and the "employee" role has read-only access (SELECT).
sql
-- Example 3: Assigning Roles to Users
GRANT admin TO user1;
GRANT manager TO user2;
GRANT employee TO user3;

Here, we assign the roles we created earlier to specific users. "user1" is assigned the "admin" role, "user2" is
assigned the "manager" role, and "user3" is assigned the "employee" role.
sql
-- Example 4: Role Activation
SET ROLE admin;
-- Perform administrative tasks...
RESET ROLE;

Users can activate roles as needed to perform specific tasks within the system. In this example, the "admin" role
is activated temporarily to perform administrative tasks, and then it is reset afterward.

sql
-- Example 5: Checking Role Permissions
SELECT * FROM employees;

When a user with a role executes SQL statements, the permissions associated with their assigned role determine
what actions they can perform. For instance, a user with the "admin" role can execute SELECT, INSERT,
UPDATE, and DELETE operations on the "employees" table, while a user with the "employee" role can only

Dhanalakshmi Srinivasan College of Engineering, Coimbatore

Downloaded by sneha sivalingam (snehaakm2001@gmail.com)
 lOMoARcPSD|22191715

II BE CSE(CS) Prepared By: R. RESHMA

execute SELECT queries.

RBAC provides a flexible and scalable approach to access control, allowing organizations to define granular
permissions based on users' roles and responsibilities. By centralizing permissions management around roles,
RBAC simplifies administration and enhances security, making it a preferred choice for managing access
control in various systems, including databases, applications, and network resources. RBAC assigns
permissions to roles rather than individual users, making access management more scalable and manageable.
Let's delve into the details:
1. Roles:
• In RBAC, roles represent a collection of permissions or privileges that are typically associated
with a particular job function, responsibility, or group within an organization.
• Examples of roles might include "admin," "manager," "employee," "customer," etc.
2. Permissions:
• Permissions define the actions or operations that users with specific roles are allowed to
perform within the system.
• Permissions can include read, write, execute, create, delete, update, and various other actions
depending on the system's requirements.
3. Assignment of Roles:
• Users are assigned one or more roles based on their job responsibilities or organizational roles.
• By assigning roles to users, access control becomes more streamlined and manageable,
especially in large organizations with many users and complex access requirements.
4. Role Hierarchy:
• RBAC systems may implement role hierarchies, where roles are organized into a hierarchical
structure.
• Higher-level roles inherit permissions from lower-level roles, allowing for more efficient
management of permissions and simplifying access control administration.
5. Role Activation:
• Users activate roles as needed to perform specific tasks within the system.
• Users may have different roles activated at different times, allowing them to switch between
different sets of permissions dynamically.
6. Role-Based Access Control in Databases:
• In the context of databases, RBAC is implemented by granting roles specific privileges on
database objects such as tables, views, stored procedures, etc.
• For example, an "admin" role might be granted full access to all tables, while a "user" role
might only be granted read access to certain tables.
7. Benefits of RBAC:
• Scalability: RBAC scales well with growing organizations and complex access control
requirements since permissions are assigned to roles rather than individual users.
• Simplicity: RBAC simplifies access control administration by centralizing permissions
management around roles.
• Security: RBAC enhances security by reducing the likelihood of errors and inconsistencies in
access control settings.
8. Challenges and Considerations:
• Role Design: Designing roles that accurately reflect job responsibilities and access
requirements is crucial for the effectiveness of RBAC.
• Role Maintenance: Regular review and maintenance of roles are necessary to ensure that they
remain aligned with organizational changes and access requirements.
• Administration Overhead: While RBAC simplifies access control management, it requires
careful planning and administration to ensure that roles are properly designed, assigned, and
maintained.
RBAC is a powerful access control model that provides a scalable, manageable, and secure way to control
system access based on users' roles and responsibilities within an organization.

Dhanalakshmi Srinivasan College of Engineering, Coimbatore

Downloaded by sneha sivalingam (snehaakm2001@gmail.com)
 lOMoARcPSD|22191715

II BE CSE(CS) Prepared By: R. RESHMA

Inference In DBMS Security
Inference is a database system technique used to attack databases where malicious users infer sensitive
information from complex databases at a high level. In basic terms, inference is a data mining technique used to
find information hidden from normal users.

An inference attack may endanger the integrity of an entire database. The more complex the database is, the
greater the security implemented in association with it should be. If inference problems are not solved
efficiently, sensitive information may be leaked to outsiders. Two inference vulnerabilities that appear in
databases are data association and data aggregation. When two values taken together are classified at a higher
level than one of every value involved, this becomes a data association. When a set of information is classified
at a higher level than the individual level of data, it is a clear case of data aggregation. The sensitive data leaked
through inference involves bound data, where an attacker finds out a range of data holding expected data or
negative data, which is obtained as a result of certain innocent queries. An attacker might try to access sensitive
information through a direct attack, indirect attack or tracking.

A wide variety of inference channels have been discovered in databases. One way of inference is querying the
database based on sensitive information. In this method, the user queries the database sequentially and from the
series of outputs received, infers patterns in the database and information lurking behind the usual displayed
data. A series of queries by a normal user may reveal some information that can easily be guessed. Statistical
data may also fall prey to inference. In a statistical database, aggregate statistics on a group of people are made
public, while individual information is hidden. The threat against statistical database security is that queries can
be shelled out on aggregate statistics over a period of time and arithmetic operations may be performed that
enable the attackers to hack individual member information.

Inference detection can be achieved through the semantic inference model, security violation detection and
knowledge acquisition. The semantic inference model combines dependency, data schema and semantic
knowledge. It represents all possible relations between attributes of data sources. Security violation detection
combines a request log with a new query request and checks if the request is allowed as per the prespecified set
of instructions. Based on the analysis, it decides whether the query has to be answered.

nference in database management systems (DBMS) refers to the process of deducing sensitive information
indirectly by exploiting the permissions or access rights granted to users. It typically occurs in systems where
access control mechanisms, such as role-based access control (RBAC) or access control lists (ACLs), are in
place to restrict users' access to certain data. Despite these restrictions, users may still be able to infer sensitive
information by analyzing the data they have legitimate access to, combined with their knowledge of the
database schema and the system's behavior.
Here's how inference works in DBMS:
1. Access Control: DBMS typically employs access control mechanisms to restrict users' access to
sensitive data. For example, a user may have permission to access only certain columns or rows within
a table.
2. Indirect Deduction: Even though users may not have direct access to certain data, they may still be
able to deduce sensitive information by analyzing the data they do have access to in combination with
their understanding of the database schema and application logic.
3. Example:
• Suppose a database contains salary information for employees, but a particular user only has
access to view their own salary.
• However, the user may notice that certain employees with similar job titles or roles have
significantly higher salaries than others.
• By comparing their own salary with others they have access to, the user may deduce
information about the salaries of colleagues who they don't have direct access to.
4. Statistical Inference: In some cases, users may perform statistical analysis on the data they have
access to in order to infer sensitive information indirectly. This can include techniques such as data

Dhanalakshmi Srinivasan College of Engineering, Coimbatore

Downloaded by sneha sivalingam (snehaakm2001@gmail.com)
 lOMoARcPSD|22191715

II BE CSE(CS) Prepared By: R. RESHMA

mining, clustering, or correlation analysis.
5. Prevention:
• Preventing inference attacks in DBMS requires a combination of access control policies, data
anonymization techniques, and monitoring.
• Access control policies should be carefully designed to minimize the risk of inference, ensuring
that users only have access to the data they absolutely need.
• Data anonymization techniques, such as generalization, suppression, or perturbation, can be
applied to sensitive data to reduce the risk of inference.
• Monitoring and auditing access patterns can help detect suspicious behavior that may indicate
attempted inference attacks.
In summary, inference in DBMS involves deducing sensitive information indirectly by analyzing the data and
access patterns available to a user. Preventing inference attacks requires careful access control design, data
anonymization techniques, and proactive monitoring to detect and mitigate potential risks.

Database Encryption
Database encryption involves the process of converting data stored in a database into a ciphertext format using
encryption algorithms. This ensures that even if unauthorized users gain access to the database files, they
cannot interpret the data without the decryption key. Here's an explanation along with examples using SQL
Server's Transparent Data Encryption (TDE) feature:

1. **Enable Transparent Data Encryption (TDE)**:

- In SQL Server, TDE is a feature that enables encryption of entire database files, including data files, log
files, and backup files.
- To enable TDE, you first need to create a master key, certificate, and database encryption key.

```sql
-- Create a master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourMasterKeyPassword';

-- Create a certificate
CREATE CERTIFICATE TDECertificate WITH SUBJECT = 'TDE Certificate';

-- Create a database encryption key

USE YourDatabaseName;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECertificate;
```

2. **Enable TDE for the Database**:

- After creating the necessary keys, you can enable TDE for the database.

```sql
ALTER DATABASE YourDatabaseName
SET ENCRYPTION ON;
```

3. **Verify Encryption Status**:

- You can verify whether TDE is enabled for the database by querying the
`sys.dm_database_encryption_keys` view.

```sql

Dhanalakshmi Srinivasan College of Engineering, Coimbatore

Downloaded by sneha sivalingam (snehaakm2001@gmail.com)
 lOMoARcPSD|22191715

II BE CSE(CS) Prepared By: R. RESHMA

SELECT DB_NAME(database_id) AS DatabaseName,
encryption_state_desc
FROM sys.dm_database_encryption_keys;
```

4. **Perform Operations**:
- Once TDE is enabled, the database engine automatically encrypts data when it is written to disk and
decrypts it when it is read from disk. Applications interact with the database as usual without needing to
perform encryption/decryption operations.

```sql
-- Regular SQL operations
SELECT * FROM YourTableName;
INSERT INTO YourTableName VALUES (...);
UPDATE YourTableName SET ...;
DELETE FROM YourTableName WHERE ...;
```

5. **Backup and Restore**:

- Backups of an encrypted database are also encrypted. When restoring an encrypted backup, the destination
server must have the certificate used for encryption.

```sql
-- Backup the database
BACKUP DATABASE YourDatabaseName TO DISK = 'C:\YourBackupFile.bak';

-- Restore the database

RESTORE DATABASE YourDatabaseName FROM DISK = 'C:\YourBackupFile.bak'
WITH REPLACE;
```

6. **Disabling TDE**:
- To disable TDE, you need to decrypt the database first.

```sql
ALTER DATABASE YourDatabaseName
SET ENCRYPTION OFF;
```

Database encryption is crucial for protecting sensitive data at rest. By encrypting the entire database,
organizations can ensure that even if the physical storage is compromised, the data remains unreadable without
the appropriate decryption key.
*******

Dhanalakshmi Srinivasan College of Engineering, Coimbatore

Downloaded by sneha sivalingam (snehaakm2001@gmail.com)