Transcript Database Application Security Models

Database Application
Security Models
Dr. Gabriel
1
Definitions
• 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
2
Types of Users
• Types:
– Application administrator
• Administering app users
• No specific db privileges required
– Application owner
• Owner of app-related db objects
– Application user
• Use application
– Database administrator
3
Types of Users
– Database user
• User with db privileges
– Proxy user
• DB user with specific roles and privileges
– Isolating app users from db
– Schema owner
• Owner of db objects
– Virtual user
4
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
5
Security Models (continued)
6
Security Models (continued)
• 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
7
Security Models (continued)
8
Security Models (continued)
9
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
10
Client/Server Applications
11
Client/Server Applications (continued)
• 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
12
Client/Server Applications (continued)
13
Web Applications
• Evolved with the rise of dot-com and Webbased 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)
14
Web Applications (continued)
15
Web Applications (continued)
• Components:
–
–
–
–
–
Web browser layer
Web server layer
Application server layer
Business logic layer
Database server layer
16
Web Applications (continued)
17
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 database server
• Accessed by software applications or reporting
applications: online analytical processing
(OLAP)
18
Data Warehouse Applications
(continued)
19
Application Security Models
• Models:
–
–
–
–
–
Database role based
Application role based
Application function based
Application role and function based
Application table based
20
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
21
Security Model Based on Database
Roles (continued)
22
Security Model Based on Database
Roles (continued)
• 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
23
Security Model Based on Database
Roles (continued)
• Implementation in SQL Server (continued):
– Create and drop application roles using the
command line:
• CREATE APPLICATION ROLE
• DROP APPLICATION ROLE
– You can activate application roles using
SP_SETAPPROLE
24
Security Model Based on Database
Roles (continued)
• 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
• Great article on app roles:
– SQL Server Security: Pros and Cons of Application Roles
By Brian Kelley
– http://www.sqlservercentral.com/articles/Security/sqlserve
rsecurityprosandconsofapplicationroles/1116/
25
Security Model Based on Database
Roles (continued)
• Example
CREATE LOGIN appuser WITH PASSWORD = 'appuserpwd'
CREATE USER appuser FOR LOGIN appuser;
CREATE APPLICATION ROLE approle WITH PASSWORD = 'approlepwd'
create table t (col1 int, col2 int)
insert into t values (1,2)
grant select on t to approle
select * from t
SP_SETAPPROLE approle,'approlepwd'
select * from t
26
Security Model Based on Application
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)
27
Security Model Based on Application
Roles (continued)
28
Security Model Based on Application
Roles (continued)
• 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
29
Security Model Based on Application
Roles (continued)
• Example:
grant select, insert, update, delete on t to appuser
create table tusers
(userid varchar(50) primary key,
pwd varchar(50) not null)
insert into tusers values ('bob','pwd')
create proc pverifylogin
@id varchar(50),
@pwd varchar(50)
as
select count(*)
from tusers
where userid=@id and pwd=@pwd
exec pverifylogin 'bob','pwd‘
exec pverifylogin 'bob','pwd2'
30
Security Model Based on Application
Roles (continued)
• Example:
CREATE SYMMETRIC KEY EncryptedData WITH ALGORITHM = DES
ENCRYPTION BY PASSWORD='1234567'
OPEN SYMMETRIC KEY EncryptedData DECRYPTION BY PASSWORD ='1234567'
CREATE TABLE tencrypteddatatest (Data VARBINARY(255))
DECLARE @Key_GUID UNIQUEIDENTIFIER
SELECT @Key_GUID = Key_GUID
FROM sys.symmetric_keys
WHERE Name = 'EncryptedData'
INSERT INTO tencrypteddatatest (Data) VALUES (EncryptByKey(@Key_Guid, 'Bob', 1))
SELECT Data FROM tencrypteddatatest
SELECT CONVERT(VARCHAR(20), DecryptByKey(tencrypteddatatest.Data, 1)) AS Data
FROM tencrypteddatatest
DROP TABLE tencrypteddatatest
CLOSE SYMMETRIC KEY EncryptedData
31
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
32
Security Model Based on Application
Functions (continued)
33
Security Model Based on Application
Roles and Functions
•
•
•
•
Combination of models
Application authenticates users
Application is divided into functions
Highly flexible model
34
Security Model Based on Application
Roles and Functions (continued)
35
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
36
Security Model Based on Application
Tables
• Privileges:
–
–
–
–
–
–
0 -no access
1 –read only
2 – read and add
3 –read, add, and modify
4 – read, add, modify, and delete
5 – read, add, modify, delete, and admin
37
Security Model Based on Application
Tables (continued)
38
Security Model Based on Application
Tables (continued)
• 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
39
Application Security Models
40
Application Security Models
(continued)
41
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
42
Questions ?
43