Chapter 12 Database Security

Authorization Rules

Controls incorporated in the data management system that restrict access to data and actions that people can take on data Authorization matrix for: Subjects Objects Actions Constraints CRUD Matrix

SQL Server Login & Users

Login (CH 12) CREATE LOGIN (ALTER & DROP) sp_addlogin Users (CH 12) CREATE USER (ALTER & DROP) sp_grantdbaccess Using dialog boxes

Security Policies and Procedures - READ

Personnel controls Physical access controls Maintenance controls Data privacy controls

Authorization - Integrity Controls

Protect data from unauthorized use Domains-set allowable values Assertions-enforce database conditions Triggers and Logging Stored procedures

Integrity Controls - Triggers

Three parts: name, action, execution Two virtual tables DELETED (created for DELETE and UPDATE) Contains copies of the rows that are deleted from the triggered table INSERTED (created for INSERT and UPDATE) Contains copies of the rows that are inserted from the triggered table

Authorization - Views

-Subset of the database that is presented to one or more users -User can be given access privilege to view without allowing access privilege to underlying tables -Does not exist physically (in contrast to tables) -Created dynamically from underlying base tables CREATE VIEW ALTER VIEW DROP VIEW SELECT Statement & Views INSERT Statement & Views WITH CHECK OPTION UPDATE Statement & Views DELETE Statement & Views

Threats to Data Security

Accidental losses attributable to: Human error Software failure Hardware failure Theft and fraud Loss of privacy (personal data) or confidentiality (corporate data) Loss of data integrity Loss of availability (through, e.g. sabotage)

Which user has been granted legitimate access to the system?

Authentication

Which access privileges are valid for a particular user?

Authorization

Database Software Security Features

Authorization: Views or subschemas Integrity controls Authorization rules User-defined procedures Authentication: Encryption Authentication schemes Backup, journalizing, and checkpointing (separate PowerPoint)

Maintenance

Database activity- database maintenance, performance analysis and tuning, error corrections, backup and recovery

Client Server & Web Site Security

Establishing Client/Server Security READ Security Issues for Web-Enabled Databases READ

Example Trigger - Audit Trail

Example 3: Create a trigger that logs every change of the standard price in the product table. CREATE TABLE audit_standard_price (product_id int null, user_name char(16) null, time datetime null, standard_price_old decimal(6,2) null, standard_price_new decimal(6,2) null );

SQL Authorization Commands

GRANT... TO DENY... TO REVOKE... FROM Using dialog boxes

Authentication Schemes

Goal - obtain a positive identification of the user Passwords: First line of defense Characteristics of a good password Strong Authentication Passwords are flawed

Security

Protection of the data against accidental or intentional loss, destruction, or misuse

Strong Authentication Passwords are flawed Possible solutions:

Two factor Three factor Biometric devices Third-party mediated authentication

SQL Server Roles - User Defined

Used for defining groups Can provide authorizations to certain groups and all users in the group will get same level of authorization CREATE ROLE (ALTER & DROP) sp_addrole (drop) sp_addrolemember (drop) Using dialog boxes

SQL Server Authentication Schemes: Two kinds of authentication

Windows authentication SQL Server authentication

SQL Server Authentication Schemes: Two authentication modes

Windows mode Mixed mode

SQL Server Roles - Built In : Fixed DB Roles

db_owner Dbaccessadmin Etc.

SQL Server Roles - Built In: Fixed Server Roles

sysadmin serveradmin Etc.