Dickson K'W' Chiu

1
Database Security

• Dickson K.W. Chiu
• Ph.D., SMIEEE, SMACM
• Connolly Begg, Database Systems, 4th Edition,
  Chapter 18

2
Chapter 18 - Objectives

• Scope of database security.
• Why database security is a serious concern for an
  organization.
• Type of threats that can affect a database
  system.
• How to protect a computer system using
  computer-based controls.
• SQL security GRANT and REVOKE
• Security measures provided by Microsoft Access
• Approaches for securing a DBMS on the Web.

3
Database Security

• Data is a valuable resource that must be strictly
  controlled and managed, as with any corporate
  resource.
• Part or all of the corporate data may have
  strategic importance and therefore needs to be
  kept secure and confidential.
• Mechanisms that protect the database against
  intentional or accidental threats.
• Security considerations do not only apply to the
  data held in a database. Breaches of security may
  affect other parts of the system, which may in
  turn affect the database.

4
Database Security

• Involves measures to avoid
• Theft and fraud
• Loss of confidentiality (secrecy)
• Loss of privacy
• Loss of integrity
• Loss of availability
• Threat
• Any situation or event, whether intentional or
  unintentional, that will adversely affect a
  system and consequently an organization.

5
Summary of Threats to Computer Systems
6
Typical Multi-User Computer Environment
7
Countermeasures Computer-Based Controls

• Concerned with physical controls to
  administrative procedures
• Authorization - The granting of a right or
  privilege, which enables a subject to
  legitimately have access to a system or a
  systems object.
• Authentication - A mechanism that determines
  whether a user is who he or she claims to be.
• Views
• Dynamic result of one or more relational
  operations operating on the base relations to
  produce another relation.
• A virtual relation that does not actually exist
  in the database, but is produced upon request by
  a particular user, at the time of request.
• Backup - Process of periodically taking a copy of
  the database and log file (and possibly programs)
  to offline storage media.
• Integrity - Prevents data from becoming invalid,
  and hence giving misleading or incorrect results.
  
• Encryption - The encoding of the data by a
  special algorithm that renders the data
  unreadable by any program without the decryption
  key.
• RAID technology

8
RAID (Redundant Array of Independent Disks)
Technology

• Hardware that the DBMS is running on must be
  fault-tolerant, meaning that the DBMS should
  continue to operate even if one of the hardware
  components fails.
• Suggests having redundant components that can be
  seamlessly integrated into the working system
  whenever there is one or more component failures.
  
• Main hardware components that should be
  fault-tolerant include disk drives, disk
  controllers, CPU, power supplies, cooling fans.
• Disk drives are most vulnerable components with
  shortest times between failure of any of the
  hardware components.

9
RAID Technology

• One solution is to provide a large disk array
  comprising an arrangement of several independent
  disks organized to improve reliability and
  increase performance.
• Performance is increased through data striping
  the data is segmented into equal-size partitions
  (the striping unit), which are transparently
  distributed across multiple disks.
• Reliability is improved through storing redundant
  information across the disks using a parity
  scheme or an error-correcting scheme.

10
Access Control in SQL - Authorization Identifiers
and Ownership

• Authorization identifier is normal SQL identifier
  used to establish identity of a user. Usually has
  an associated password.
• Used to determine which objects user may
  reference and what operations may be performed on
  those objects.
• Each object created in SQL has an owner, as
  defined in AUTHORIZATION clause of schema to
  which object belongs.
• Owner is only person who may know about it.

11
Privileges

• Actions user permitted to carry out on given base
  table or view
• SELECT Retrieve data from a table.
• INSERT Insert new rows into a table.
• UPDATE Modify rows of data in a table.
• DELETE Delete rows of data from a table.
• REFERENCES Reference columns of named table in
  integrity constraints.
• USAGE Use domains, collations, character sets,
  and translations.

12
Privileges

• Can restrict INSERT/UPDATE/REFERENCES to named
  columns.
• Owner of table must grant other users the
  necessary privileges using GRANT statement.
• To create view, user must have SELECT privilege
  on all tables that make up view and REFERENCES
  privilege on the named columns.

13
GRANT

• GRANT PrivilegeList ALL PRIVILEGES
• ON ObjectName
• TO AuthorizationIdList PUBLIC
• WITH GRANT OPTION
• PrivilegeList consists of one or more of above
  privileges separated by commas.
• ALL PRIVILEGES grants all privileges to a user.

14
GRANT

• PUBLIC allows access to be granted to all present
  and future authorized users.
• ObjectName can be a base table, view, domain,
  character set, collation or translation.
• WITH GRANT OPTION allows privileges to be passed
  on.

15
Example 6.7/8 - GRANT

• Give Manager full privileges to Staff table.
• GRANT ALL PRIVILEGES
• ON Staff
• TO Manager WITH GRANT OPTION
• Give users Personnel and Director SELECT and
  UPDATE on column salary of Staff.
• GRANT SELECT, UPDATE (salary)
• ON Staff
• TO Personnel, Director

16
Example 6.9 - GRANT Specific Privileges to PUBLIC

• Give all users SELECT on Branch table.
• GRANT SELECT
• ON Branch
• TO PUBLIC

17
REVOKE

• REVOKE takes away privileges granted with GRANT.
• REVOKE GRANT OPTION FOR
• PrivilegeList ALL PRIVILEGES
• ON ObjectName
• FROM AuthorizationIdList PUBLIC
• RESTRICT CASCADE
• ALL PRIVILEGES refers to all privileges granted
  to a user by user revoking privileges.

18
REVOKE

• GRANT OPTION FOR allows privileges passed on via
  WITH GRANT OPTION of GRANT to be revoked
  separately from the privileges themselves.
• REVOKE fails if it results in an abandoned
  object, such as a view, unless the CASCADE
  keyword has been specified.
• Privileges granted to this user by other users
  are not affected.

19
Example 6.10/11 - REVOKE Specific Privileges

• Revoke privilege SELECT on Branch table from all
  users.
• REVOKE SELECT
• ON Branch
• FROM PUBLIC
• Revoke all privileges given to Director on Staff
  table.
• REVOKE ALL PRIVILEGES
• ON Staff
• FROM Director

20
Security in Microsoft Access DBMS

• Provides two methods for securing a database
• setting a password for opening a database (system
  security)
• user-level security, which can be used to limit
  the parts of the database that a user can read or
  update (data security).

21
Securing the DreamHome Database Using a Password
22
User and Group Accounts Dialog Box for the
DreamHome Database
23
User and Group Permissions Dialog Box
24
Creation of a New User with Password
Authentication Set
25
Log on Dialog Box
26
Setting the Permissions
27
DBMSs and Web Security

• Internet communication relies on TCP/IP as the
  underlying protocol.
• However, TCP/IP and HTTP were not designed with
  security in mind. Without special software, all
  Internet traffic travels in the clear and
  anyone who monitors traffic can read it.
• Must ensure while transmitting information over
  the Internet that
• inaccessible to anyone but sender and receiver
  (privacy)
• not changed during transmission (integrity)
• receiver can be sure it came from sender
  (authenticity)
• sender can be sure receiver is genuine
  (non-fabrication)
• sender cannot deny he or she sent it
  (non-repudiation).
• Must also protect information once it has reached
  Web server.

28
DBMSs and Web Security

• Download may have executable content, which can
  perform following malicious actions
• Corrupt data or execution state of programs.
• Reformat complete disks.
• Perform a total system shutdown.
• Collect and download confidential data.
• Usurp identity and impersonate user.
• Lock up resources.
• Cause non-fatal but unwelcome effects.

29

• References

30
DBMSs and Web Security

• Measures include
• Proxy servers
• Firewalls
• Message digest algorithms and digital signatures
• Digital certificates
• Kerberos
• Secure sockets layer (SSL) and Secure HTTP
  (S-HTTP)
• Secure Electronic Transactions (SET) and Secure
  Transaction Technology (SST)
• Java security
• ActiveX security.

31
Proxy Servers

• Proxy server is computer that sits between
  browser and Web server.
• It intercepts all requests to Web server to try
  to fulfil requests itself.
• Has two main purposes
• improve performance
• filter requests.

32
Firewalls

• Designed to prevent unauthorized access to/from a
  private network.
• Can be implemented in both hardware and software,
  or a combination of both.
• Several types of firewall techniques
• Packet filter.
• Application gateway.
• Circuit-level gateway.
• Proxy server.

33
Message Digest Algorithms

• Message digest algorithm takes an arbitrary-sized
  string (message) and generates fixed-length
  string (digest or hash).
• A digest has following characteristics
• Should be computationally infeasible to find
  another message that will generate same digest.
• Digest does not reveal anything about message.

34
Digital Signatures

• Digital signature consists of two parts
• string of bits computed from data being signed
  
• private key of individual or organization wishing
  the signature.
• Can be used to verify data comes from this
  individual or organization.

35
Digital Signatures

• Digital signature has many useful properties
• Authenticity can be verified, using public key.
• Cannot be forged (assuming private key is kept
  secret).
• Function of data signed and cannot be claimed to
  be signature for any other data.
• Signed data cannot be changed or signature will
  no longer verify data as being authentic.

36
Digital Certificates

• Attachment to electronic message used for
  security purposes (e.g. verify user sending
  message), and provide receiver with means to
  encode reply.
• Sender applies for certificate from Certificate
  Authority (CA).
• CA issues encrypted certificate containing
  applicants public key and other identification
  information.

37
Digital Certificates

• CA makes its own public key readily available.
• Recipient uses CAs public key to decode
  certificate attached to message, verifies it as
  issued by CA, and obtains senders public key and
  identification information held within
  certificate.
• With this information, recipient can send an
  encrypted reply.
• CAs role is critical, acting as go-between in
  relationship between two parties.

38
Kerberos

• A server of secured user names and passwords.
• Provides one centralized security server for all
  data and resources on network.
• Database access, login, authorization control,
  and other security features are centralized on
  trusted Kerberos servers.
• Has similar function to that of Certificate
  server to identify and validate a user.

39
Secure Sockets Layer (SSL)

• Encryption protocol for transmitting private
  documents.
• Designed to prevent eavesdropping, tampering, and
  message forgery.
• Works by using private key to encrypt data that
  is transferred over SSL connection.
• Layered between application-level protocols such
  as HTTP and TCP/IP transport-level protocol.
• Thus, may be used for other application-level
  protocols such as FTP and NNTP.

40
Secure-HTTP (S-HTTP)

• Protocol for securely transmitting individual
  messages over Web.
• Both SSL and S-HTTP use techniques such as
  encryption, and digital signatures, and
• allow browsers and servers to authenticate each
  other
• allow controlled access to Web site
• ensure data exchanged between browser and server
  is secure and reliable.

41
Java Security (Reference)

• Sandbox ensures untrusted application cannot gain
  access to system resources.
• Involves three components
• class loader
• bytecode verifier
• security manager.
• Safety features provided by language and JVM, and
  enforced by compiler and runtime system.
• Security is a policy built on top of safety layer.

42
Class Loader

• Allocates (hierarchically structured) namespace
  for each class.
• Never allows class from less protected
  namespace to replace class from more protected
  namespace.
• Thus, I/O primitives, defined in local Java
  class, cannot be invoked or overridden by classes
  from out with local machine.
• As browsers and Java applications can provide
  their own class loader, this may be viewed as
  weakness in security.

43
Bytecode Verifier

• JVM verifies bytecode instructions before
  allowing application/applet to run.
• Typical checks include verifying
• Compiled code is correctly formatted.
• Internal stacks will not overflow/underflow.
• No illegal data conversions will occur.
• Bytecode instructions are appropriately typed.
• All class member accesses are valid.

44
Security Manager

• Each Java application defines and implements its
  own security policy.
• A Java-enabled browser contains its own Security
  Manager, and any applets it downloads are subject
  to its policies.
• Generally, downloaded applets are prevented from
• Reading and writing files on clients file
  system.
• Making network connections to machines other than
  host.
• Starting other programs on the client.
• Loading libraries.
• Defining method calls.
• These restrictions apply to applets downloaded
  over Internet/intranet.
• Also do not apply to applets on clients local
  disk and in directory on CLASSPATH.
• Local applets are loaded by file system loader
  and can read and write files, exit JVM, and are
  not passed through bytecode verifier.

45
ActiveX Security

• ActiveX security model places no restrictions on
  what a control can do.
• Instead, each ActiveX control can be digitally
  signed by its author using system called
  Authenticode.
• Digital signatures are then certified by CA.
• This security model places responsibility for the
  computers security on the user.