Bài giảng Bảo mật cơ sở dữ liệu: Security models của Trần Thị Kim Chi

SECURITY MODELS

ắ ầ ừ

ế

Operating System Security Fundamentals Ti p theo bài 1 b t đ u t

Slide 10

Giảng Viên: Trần Thị Kim Chi

Agenda

a. Access control b. Inference and covert channels c. Open/close policy d. Database Application Security Models Discretionary/mandatory access control

Access control

• Access control is a security technique that can be used to regulate who or what can view or use resources in a computing environment.

• Access control systems perform authorization

identification, authentication, access approval, and accountability of entities through login credentials includingpasswords, personal identification numbers (PINs), biometric scans, and physical or electronic keys.

Types of Access control

• There are two main types of access control:

– Physical, – logical.

• Physical access control

limits access

to campuses, buildings, rooms and physical IT assets.

• Logical access limits connections to computer

networks, system files and data.

Types of Access control

The four main categories of access control are: • Mandatory access control • Discretionary access control • Role-based access control • Rule-based access control

Mandatory access control (MAC)

• Mandatory access control (MAC) is a system- controlled policy restricting access to resource objects (such as data files, devices, systems, etc.) based on the level of authorization or clearance of the accessing entity, be it person, process, or device.

• http://

searchsecurity.techtarget.com/definition/mandatory-access-control-MAC

Discretionary access control (DAC)

• Discretionary access control (DAC) is a type of the access control defined Trusted Computer System Evaluation Criteria "as a means of restricting access to objects based on the identity of subjects and/or groups to which they belong. The controls are discretionary in the sense that a subject with a certain access permission is capable of passing that permission (perhaps indirectly) on to any other subject (unless restrained by mandatory access control )".

• Discretionary access control is commonly discussed in contrast to mandatory access control (MAC, sometimes termed non-discretionary access control).

Role-based access control (RBAC)

• Role-based access control (RBAC) is a method of

regulating access to computer or network resources based on the roles of individual users within an enterprise...

• http://

searchsecurity.techtarget.com/definition/role-based-access-control-RBAC

• http://

searchsecurity.techtarget.com/tip/Role-based-access-control-for-effective-security-management

Rules Based Access Control

•

• Rules Based Access Control is a strategy for managing user access to one or more systems, where business changes trigger the application of Rules, which specify access changes. Implementation of Rules Based Access Control systems is feasible so long as the number of triggering business events and the set of possible actions that follow those events are both small. - See more at: http://hitachi- id.com/concepts/rules_based_access_control.html#sthas h.TJMhLiGM.dpuf

Authentication Methods

• Authentication:

– Verifies user identity – Permits access to the operating system

• Physical authentication:

– Allows physical entrance to company property – Magnetic cards and biometric measures

• Digital authentication: verifies user identity by digital

means

Authentication Methods

• Digital certificates: digital passport that identifies and

verifies holder of certificate • Digital token (security token):

– Small electronic device – Displays a number unique to the token holder; used with the

holder’s PIN as a password

– Uses a different password each time

Authentication Methods

• Digital card:

– Also known as a security card or smart card – Similar to a credit card; uses an electronic circuit instead of a

magnetic strip

– Stores user identification information

• Kerberos:

– Developed by MIT – Uses tickets for authentication purposes

Authentication Methods

• Lightweight Directory Access Protocol (LDAP):

– Developed by the University of Michigan – A centralized directory database stores: • Users (user name and user ID) • Passwords

•

Internal telephone directory

• Security keys

– Efficient for reading but not suited for frequently changing

information

Authentication Methods

• NTLM:

– Developed and used by Microsoft – Employs a challenge/response authentication protocol

• Public Key Infrastructure (PKI):

– User keeps a private key – Authentication firm holds a public key – Encrypt and decrypt data using both keys

Authentication Methods

• RADIUS: used by network devices to provide a

centralized authentication mechanism

• Secure Socket Layer (SSL): authentication

information is transmitted over the network in an encrypted form

• Secure Remote Password (SRP):

– Password is not stored locally

–

Invulnerable to brute force or dictionary attacks

Authorization

• Process that decides whether users are permitted to

perform the functions they request

• Authorization is not performed until the user is

authenticated

• Deals with privileges and rights

Operating System Authentication

• Many databases (including Microsoft SQL Server 2000) depend on OS to authenticate users

• Reasons:

– Once an intruder is inside the OS, it is easier to access the database

– Centralize administration of

users

• Users must be authenticated

at each level

User Administration

• Create user accounts • Set password policies • Grant privileges to users • Best practices:

– Use a consistent naming convention – Always provide a password to an account and force the user to

change it at the first logon

– Protect passwords – Do not use default passwords

Creating a SQL Server User

• Create a login ID first; controls access to SQL Server

system

• Associate login ID with a database user • Must be member of fixed server roles (SYSADMIN or

SECURITYADMIN) • Two types of login IDs:

– Windows Integrated (trusted) login – SQL Server login

Creating Windows Integrated Logins

• Command line:

– SP_GRANTLOGIN system stored procedure – Can be associated local, domain, group usernames

• Enterprise Manager:

– Use the Security container – Logins -> New Login

Creating Windows Integrated Logins

Creating SQL Server Logins

• Command line:

– SP_ADDLOGIN system stored procedure – Password is encrypted by default – Specify a default database

• Enterprise Manager: – Security container – Logins -> New Login – SQL Server Authentication option

Creating SQL Server Logins

• Command line:

– SP_ADDLOGIN system

stored procedure

is encrypted

– Password by default

– Specify

default

database

• Enterprise Manager: – Security container – Logins -> New Login – SQL

Server

Authentication option

Removing Users

• Simple process • Make a backup first • Obtain a written request (for auditing purposes)

SQL Server: Removing Windows Integrated Logins

• Command line: SP_DENYLOGIN system stored

procedure

• Enterprise Manager:

– Highlight the desired login – Choose Delete from the Action menu

Modifying Users

• Modifications involve: – Changing passwords – Locking an account

–

Increasing a storage quota • ALTER USER DDL statement

SQL Server: Modifying Windows Integrated Login Attributes

• Command line:

– SP_DEFAULTDB system stored procedure – SP_DEFAULTLANGUAGE stored procedure

• Enterprise Manager:

– Expand the security container – Select desired login – Properties (on the Action Menu)

Default Users

• Oracle default users:

– SYS, owner of the data dictionary – SYSTEM, performs almost all database tasks – ORAPWD, creates a password file

• SQL Server default users: – SA, system administrator – BUILT_IN\Administrators

Remote Users

Database Links

• Connection from one database to another: allow DDL

and SQL statements

• Types: PUBLIC and PRIVATE • Authentication Methods:

– CURRENT USER – FIXED USER – CONNECT USER

Database Links

• Connection from one database to another: allow DDL

and SQL statements

• Types: PUBLIC and PRIVATE • Authentication Methods:

– CURRENT USER – FIXED USER – CONNECT USER

Linked Servers

• Allow you to connect to almost any:

– Object Linking and Embedding Database (OLEDB) – Open Database Connectivity (ODBC)

• OPENQUERY function • Map logins in your SQL Server instance to users in

the linked database

• Remote servers: allow communication using RPC

Practices for Administrators and Managers

• Manage:

– Accounts – Data files – Memory

• Administrative tasks:

– Backup – Recovery – Performance tuning

Best Practices

• Follow company’s policies and procedures • Always document and create logs • Educate users • Keep abreast of database and security technology • Review and modify procedures

Best Practices

• For SQL server:

– Mimic Oracle’s recommended installation for UNIX – Use local Windows or domain Windows accounts

• Block direct access to database tables • Limit and restrict access to the server • Use strong passwords • Patches, patches, patches

Best Practices

• For SQL server:

– Mimic Oracle’s recommended installation for UNIX – Use local Windows or domain Windows accounts

• Block direct access to database tables • Limit and restrict access to the server • Use strong passwords • Patches, patches, patches

Best Practices

• Document tasks and procedures for auditing

purposes

• Creating users:

– CREATE USER statement in Oracle – Login ID in SQL Server

• Removing users:

– SQL DROP statement – SP_DENYLOGIN Windows system stored procedure

Best Practices

• Modifying user attributes: ALTER USER DDL

statement

• Local database and users • Remote users • Database links • Linked servers

Password Policies

• First line of defense • Dictionary attack: permutation of words in dictionary • Make hard for hackers entering your systems • Best password policy:

– Matches your company missions – Enforced at all level of the organization

Defining and Using Profiles

• Profile:

– Describes limitation of database resources – Defines database users behavior – Prevents users from wasting resources • Not offered by every database system:

– Oracle does – Microsoft SQL Server 2000 does not

Creating Profiles in SQL Server

• Profiles are not available in Microsoft SQL Server

2000 or 2005

• Query and connection time-outs: handled at

application level within OLEDB

Designing and Implementing Password Policies

• Password is the key to open a user account; strong

passwords are harder to break

• User authentication depends on passwords • Hacker violations begin with breaking a password • Companies spend on:

– Training – Education

What Is a Password Policy?

• Set of guidelines:

– Enhances the robustness of a password – Reduces the likelihood of password breaking

• Deals with: – Complexity – Change frequency – Reuse

Importance of Password Policies

• First line of defense • Most companies invest considerable resources to

strengthen authentication by adopting technological measures that protect their assets

• Forces employees to abide by the guidelines set by the company and raises employee awareness of password protection

• Helps ensure that a company does not fail audits

Designing Password Policies

• Complexity: set of guidelines for creating passwords • Aging: how long a password can be used • Usage: how many times a password can be used • Storage: storing a password in an encrypted manner

Implementing Password Policies

–

• Microsoft SQL Server 2000: Integrated server system

– Windows authentication mode

• NTLM:

– Challenge/response methodology – Challenge is eight bytes of random data – Response is a 24-byte DES-encrypted hash

Implementing Password Policies

–

• Microsoft SQL Server 2000: Integrated server system

– Windows authentication mode

• NTLM:

– Challenge/response methodology – Challenge is eight bytes of random data – Response is a 24-byte DES-encrypted hash

Implementing Password Policies

• Kerberos:

– A key known by client and server encrypts handshake data – Requires a Key Distribution Center (KDC) – Tickets – Time must be synchronized networkwide

Implementing Password Policies

Password Policies

• Best practices: – Password aging – Password reuse – Password history – Password encryption – Password storage and protection – Password complexity – Logon retries – Single sign-on

Granting and Revoking User Privileges

• Permit or deny access to data or to perform database

•

operations In Oracle: – System privileges:

• Granted only by a database administrator • Granted by a user with administration privileges

– Object privileges:

• Granted to a user by the schema owner • Granted by a user with GRANT privileges

Granting and Revoking User Privileges

•

In SQL Server (4 levels); system/server privileges: – Sysadmin – Serveradmin – Setupadmin – Securityadmin – Processadmin – Dbcreator – Diskadmin – Bulkadmin

Granting and Revoking User Privileges

•

In SQL Server (continued): – Database privileges:

• Fixed database roles • Statement permissions

– Grant permission using the GRANT statement – Revoke permission using the REVOKE statement – Enterprise Manager – Deny permission using the DENY statement

Granting and Revoking User Privileges

•

In SQL Server: – Table and database objects privileges: • GRANT, REVOKE, and DENY • EXECUTE permission • Enterprise Manager (3 methods)

– Column privileges:

• GRANT, REVOKE, and DENY • Enterprise Manager (2 methods)

Creating, Assigning, and Revoking User Roles

• Role:

–

– Used to organize and administer privileges It is like a user, except it cannot own object

– Can be assigned privileges – Can be assigned to users

Creating, Assigning, and Revoking User Roles

•

In SQL Server; user-defined roles: – Standard and application – Create roles using SP_ADDROLE system-stored procedure – Add members to a role using SP_ADDROLEMEMBER stored

procedure

– Drop members from a role using SP_DROPROLEMEMBER

stored procedure

Creating, Assigning, and Revoking User Roles

•

In SQL Server (continued): – User-defined roles (continued):

• Drop roles using SP_DROPROLE stored procedure • Use Enterprise Manager

– Fixed server roles:

• Cannot be modified or created • Add member to a role using SP_ADDSRVROLEMEMBER

stored procedure

Creating, Assigning, and Revoking User Roles

•

In SQL Server (continued): – Fixed server roles (continued):

• Drop members from a role using

SP_DROPSRVROLEMEMBER stored procedure

• Use Enterprise Manager

– Fixed database roles:

• Cannot be modified • Give access to database administrative tasks • Add members to a role using SP_ADDROLEMEMBER

stored procedure

Creating, Assigning, and Revoking User Roles

•

In SQL Server (continued): – Fixed database roles (continued):

• Drop members from a role using

SP_DROPROLEMEMBER stored procedure

• Use Enterprise Manager

– Public database role:

• Cannot be dropped • Users automatically belong to this role • Users cannot be dropped

Best Practices

• Develop a secure environment:

– Never store passwords for an application in plaintext – Change passwords frequently – Use passwords at least eight characters long – Pick a password that you can remember – Use roles to control and administer privileges – Report compromise or loss of a password – Report any violation of company guidelines

Best Practices

• Develop a secure environment (continued):

– Never give your password to anyone – Never share your password with anyone – Never give your password over the phone. – Never type your password in an e-mail – Make sure your password is complex enough – Use Windows integrated security mode

–

In Windows 2000/3 domain use domain users and take advantage of Kerberos

Best Practices

• When configuring policies:

– Require complex passwords with special characters in the first

seven bytes

– Require a password length of at least eight – Set an account lockout threshold – Do not allow passwords to automatically reset – Expire end-user passwords – Do not expire application-user passwords – Enforce a password history

Best Practices

• Profiles define database users behavior

•

In Oracle: – DBA_PROFILE view – ALTER USER

• SQL Server does not support profiles • Password policy:

– Enhances password robustness – Reduces likelihood of password breaking

Best Practices

•

In SQL Server: – NTLM – Kerberos In Oracle: – System privileges – Object privileges In SQL Server: – System or server, database, table and column privileges

Best Practices

• GRANT and REVOKE • Role is used to:

– Organize and administer privileges in an easy manner – Role is like a user but cannot own objects – Role can be assigned privileges – GRANT and REVOKE

• Best practices for developing a secure environment

E-mail Security

• Tool must widely used by public • May be the tool must frequently used by hackers:

– Viruses – Worms – Spam – Others

• Used to send private and confidential data as well as

offensive material

E-mail Security

• Used by employees to communicate with:

– Clients – Colleagues – Friends

• Recommendations:

– Do not configure e-mail server on the same machine were

sensitive data resides

– Do not disclose technical details about the

e-mail server

Security problems with files

• Common threats: – File permission – File sharing

• Files must be protected from unauthorized reading

and writing actions

• Data resides in files; protecting files

protects data

File Permissions

•

• Read, write, and execute privileges In Windows 2000: – Change permission on the Security tab on a file’s Properties dialog box

– Allow indicates grant – Deny indicates revoke

File Permissions

•

In UNIX – Three permission settings: owner; group to which owner

belongs; all other users – Each setting consist of rwx

•

r for reading, w for writing, and x for executing

– CHMOD command used to change file permissions

File Transfer

• FTP (File Transfer Protocol):

–

Internet service for transferring files from one computer to another

– Transmits usernames and passwords in plaintext – Root account cannot be used with FTP – Anonymous FTP: ability to log on to the FTP server without

being authenticated

File Transfer

• Best practices:

– Use Secure FTP utility if possible – Make two FTP directories:

• One for uploads with write permissions only • One for downloads with read permissions only

– Use specific accounts with limited permissions – Log and scan FTP activities – Allow only authorized operators

Sharing Files

• Naturally leads to security risks and threats • Peer-to-peer programs: allow users to share files

over the Internet

• Reasons for blocking file sharing:

– Malicious code – Adware and spyware – Privacy and confidentiality – Pornography – Copyright issues

Memory

• Hardware memory available on the system • Can be corrupted by badly written software • Two options:

– Stop using the program – Apply a patch (service pack) to fix it

• Can harm data integrity

Covert channels

• MLS designed to restrict legitimate channels of

communication

• May be other ways for information to flow • For example, resources shared at different

levels may signal information

• Covert channel: “communication path not intended as such by system’s designers”

Covert Channel Example

• Alice has TOP SECRET clearance, Bob has

CONFIDENTIAL clearance

• Suppose the file space shared by all users • Alice creates file FileXYzW to signal “1” to

Bob, and removes file to signal “0” • Once each minute Bob lists the files

–

If file FileXYzW does not exist, Alice sent 0 If file FileXYzW exists, Alice sent 1

• Alice can leak TOP SECRET info to Bob!

Covert Channel Example

Alice:

Create file

Delete file

Create file

Delete file

Bob:

Check file

Data:

Time:

Covert Channel Example

• Other examples of covert channels

– Print queue – ACK messages – Network traffic, etc., etc., etc.

• When does a covert channel exist?

1. Sender and receiver have a shared resource 2. Sender able to vary property of resource that

receiver can observe

3. Communication between sender and receiver can

be synchronized

Covert Channel Example

• Covert channels exist almost everywhere • Easy to eliminate covert channels…

– Provided you eliminate all shared resources and all

communication

• Virtually impossible to eliminate all covert

channels in any useful system – DoD guidelines: goal is to reduce covert channel

–

capacity to no more than 1 bit/second Implication is that DoD has given up trying to eliminate covert channels!

Covert Channel Example

• Consider 100MB TOP SECRET file

– Plaintext version stored in TOP SECRET place – Encrypted with AES using 256-bit key, ciphertext

stored in UNCLASSIFIED location

• Suppose we reduce covert channel capacity

•

to 1 bit per second It would take more than 25 years to leak entire document thru a covert channel

• But it would take less than 5 minutes to leak

256-bit AES key thru covert channel!

Inference Control Example

• Suppose we query a database

– Question: What is average salary of female CS

professors at SJSU?

– Answer: $95,000 – Question: How many female CS professors at

SJSU? – Answer: 1

• Specific information has leaked from responses to general questions!

Inference Control and Research

• For example, medical records are private but

valuable for research

• How to make info available for research and

protect privacy?

• How to allow access to such data without leaking

specific information?

Naïve Inference Control

• Remove names from medical records? • Still may be easy to get specific info from such

“anonymous” data

• Removing names is not enough

– As seen in previous example • What more can be done?

Less-naïve Inference Control

• Query set size control

– Don’t return an answer if set size is too small

• Randomization

– Add small amount of random noise to data

• Many other methods (cid:0)

none satisfactory

Turing Test

• Proposed by Alan Turing in 1950 • Human asks questions to one other human and

•

one computer (without seeing either) If human questioner cannot distinguish the human from the computer responder, the computer passes the test

• The gold standard in artificial intelligence • No computer can pass this today

Captchar

• CAPTCHA (cid:0)

Completely Automated Public

Turing test to tell Computers and Humans Apart

• Automated (cid:0)

test is generated and scored by

a computer program

program and data are public

• Public (cid:0) • Turing test to tell… (cid:0)

humans can pass the

test, but machines cannot pass the test

• Like an inverse Turing test (sort of…)

CAPTCHA Paradox

•

“…CAPTCHA is a program that can generate and grade tests that it itself cannot pass…” “…much like some professors…”

• Paradox (cid:0)

computer creates and scores test that

it cannot pass!

• CAPTCHA used to restrict access to resources

to humans (no computers)

• CAPTCHA useful for access control

CAPTCHA: Rules of the Game

• Must be easy for most humans to pass • Must be difficult or impossible for machines to

pass – Even with access to CAPTCHA software • The only unknown is some random number • Desirable to have different CAPTCHAs in case some person cannot pass one type – Blind person could not pass visual test, etc.

Do CAPTCHAs Exist?

• Test: Find 2 words in the following

qEasy for most humans qDifficult for computers (OCR problem)

CAPTCHAs

• Current types of CAPTCHAs

– Visual

• Like previous example • Many others

– Audio

• Distorted words or music • No text-based CAPTCHAs – Maybe this is not possible…

CAPTCHA’s and AI

• Computer recognition of distorted text is a

challenging AI problem – But humans can solve this problem

• Same is true of distorted sound – Humans also good at solving this

• Hackers who break such a CAPTCHA have

solved a hard AI problem

• Putting hacker’s effort to good use! • May be other ways to defeat CAPTCHAs…

Open/close policy

Open Systems I want to be able to work and play well with others. Systems that are described as open are built upon standards, protocols, and interfaces that have published specifications, which enable thirdparty vendors to develop addon components and devices. This type of architecture provides interoperability between products by different vendors of different operating systems, applications, and hardware devices. This interoperability is provided by all the vendors involved who follow specific standards and provide interfaces that enable each system to easily communicate with other systems and allow addons to hook into the system easily. A majority of the systems in use today are open systems. The reason that an administrator can have Windows NT 4.0, Windows 2000, Macintosh, and Unix computers on the same network communicating easily is because these platforms are open. If a software vendor creates a closed system, they are restricting their sales to proprietary environments instead of to the whole world. © FPT Software

Open/close policy

Closed Systems I only want to work and play with you and him. Systems that are referred to as closed use an architecture that does not follow industry standards. Interoperability and standard interfaces are not employed to enable easy communication between different types of systems and addon features. Closed systems are proprietary, meaning that the system can only communicate with like systems. A closed architecture can provide more security to the system because it does not have as many doorways in, and it operates in a more secluded environment than open environments. Because a closed system is proprietary, there are not as many tools to thwart the security mechanisms and not as many people who understand its design, language, and security weaknesses to exploit. However, more security brings less functionality. A majority of the systems today are built with open architecture to enable them to work with other types of systems, easily share information, and take advantage of the functionality that thirdparty addons bring. However, this opens the doors to more hacks, cracks, and attacks. You can’t have your cake and eat it too.

Open/close policy

Open security is the application of open source software (OSS) approaches to help solve cyber security problems. OSS approaches collaboratively develop and maintain intellectual works (including software and documentation) by enabling users to use them for any purpose, as well as study, create, change, and redistribute them (in whole or in part). Cyber security problems are a lack of security (confidentiality, integrity, and/or availability), or potential lack of security (a vulnerability), in computer systems and/or the networks they are a part of.

Security policy

• Set of rules defining who is authorized to access what and under which conditions, and the criteria under which such authorization is given or cancelled.

• Security policy is a definition of what it means to be

secure for a system, organization or other entity. For an organization, it addresses the constraints on behavior of its members as well as constraints imposed on adversaries by mechanisms such as doors, locks, keys and walls. For systems, the security policy addresses constraints on functions and flow among them, constraints on access by external systems and adversaries including programs and access to data by people.

Open/close policy

• To explain why closed security policies are better protection than open ones, let me give you an example. Let's suppose that my company has determined that the Oracle listener will listen on port 1599 (a nondefault port) and that all database passwords will contain 8 letters followed by 2 numbers. If I publish this information on the Web, I now let any hacker know exactly which port to point to and exactly how my database passwords are composed. Do you want to give this information to a potential hacker? It's better to safeguard this information within your company.

Open/close policy

Database Application Security Models

• Application:

– Solves a problem – Performs a specific business function

• Database: collection of related data files used by an

application

• Application user: user within the application schema

Database Application Security Models

• Types:

– Application administrator – Application owner – Application user – Database administrator – Database user – Proxy user – Schema owner – Virtual user

Security Models

• Access Matrix Model:

– Represents two main entities: objects and subjects:

• Columns represent objects • Rows represent subjects

– Objects: tables, views, procedures, database objects – Subjects: users, roles, privileges, modules – Authorization cell

Security Models

• Access Modes Model:

– Based on the Take-Grant model – Uses objects and subjects – Specifies access modes: static and dynamic modes – Access levels: a subject has access to objects at its level and

all levels below it

Security Models

Application Types

• Client/Server applications:

– Management Information System (MIS) department:

• Thirty year ago centralized information • Developed mainframe projects • Was a bottleneck

– Personal computer was introduced: developing need for

client/server applications

– Based on the business model

Client/Server Applications

Application Types

• Provides a flexible and scalable structure • Components: – User interface – Business logic – Data access

• Components usually spread out over several tiers:

– Minimum two – Normally, four to five

Application Types

Web Applications

• Evolved with the rise of dot-com and Web-based

companies

• Uses the Web to connect and communicate to the

server

• A Web application uses HTML pages created using:

– ActiveX – Java applets or beans – ASP (Active Server Pages)

Web Applications

• Components:

– Web browser layer – Web server layer – Application server layer – Business logic layer – Database server layer

Data Warehouse Applications

• Used

in decision-support

applications

• Collection of many types of data taken from a number of different databases

• Typically composed of a

• Accessed

software reporting online processing

database server by applications or applications: analytical (OLAP)

Application Security Models

• Models:

– Database role based – Application role based – Application function based – Application role and function based – Application table based

Security Model Based on Database Roles

• Application authenticates application users: maintain

all users in a table

• Each user is assigned a role; roles have privileges

assigned to them

• A proxy user is needed to activate assigned roles; all

roles are assigned to the proxy user

• Model and privileges are database dependent

Security Model Based on Database Roles

•

Implementation in SQL Server: – Use application roles:

• Special roles you that are activated at the time of

authorization

• Require a password and cannot contain members – Connect a user to the application role: overrules user’s

privileges

Security Model Based on Database Roles

•

Implementation in SQL Server (continued): – Create and drop application roles using the command line and

the Enterprise Manager: • SP_ADDAPPROLE • SP_DROPAPPROLE

– You can activate application roles using SP_SETAPPROLE

Security Model Based on Database Roles

•

Implementation in SQL Server (continued): – Connect to database as the proxy user – Validate the user name and password – Retrieve the application role name – Activate the application role

Security Model Based on Database Roles

• Application roles are mapped to real business roles • Application authenticates users • Each user is assigned to an application role;

application roles are provided with application privileges (read and write)

Security Model Based on Database Roles

•

Implementation in SQL Server – Create a database user – Connect the application to the database using this user – Create stored procedures to perform all database operations

Security Model Based on Application Functions

• Application authenticates users • Application is divided into functions • Considerations:

–

Isolates application security from database

– Passwords must be securely encrypted – Must use a real database user – Granular privileges require more effort during implementation

Security Model Based on Application Functions

Security Model Based on Application Roles and Functions

• Combination of models • Application authenticates users • Application is divided into functions:

– Roles are assigned to functions – Functions are assigned to users

• Highly flexible model

Security Model Based on Application Roles and Functions

Security Model Based on Application Tables

• Depends on the application to authenticate users • Application provides privileges to the user based on

tables; not on a role or a function

• User is assigned access privilege to each table

owned by the application owner

Security Model Based on Application Tables

•

Implementation in SQL Server: – Grant authorization on application functions to the end user – Alter authorization table from the security model based on database roles; incorporate the table and access columns required to support model

Application Security Models

Data Encryption

• Passwords should be kept confidential and preferably

encrypted

• Passwords should be compared encrypted:

– Never decrypt the data – Hash the passwords and compare the hashes

Summary

• Security: level and degree of being free from danger

and threats

• Database security: degree to which data is fully

•

protected from unauthorized tampering Information systems: backbone of day-to-day company operations

Summary

•

Information security architecture – Model for protecting logical and physical assets – Company’s implementation of a C.I.A. triangle • Enforce security at all levels of the database