MIS 431

1
(No Transcript)
2
MIS 431Dr. Steve RossWinter 2004

• Lecture 2.
• Relational Database Management Systems and SQL
  Server

Material for this lecture is drawn from Guerrero
and Rojas, SQL Server 2000 Programming, and the
professors experience. Pink Panther images from
http//www.high-tech.com/panther/index.html
3
Server Components

• Microsoft SQL Server Service
• Microsoft SQL Server Agent
• Microsoft Search
• Microsoft SQL Server OLAP Service
• Microsoft Distributed Transaction Coordinator
• Server Network Libraries

4
Microsoft SQL Server Service

• Open Data Services
• Communication with the client
• Relational Engine
• Parses, optimizes, and executes queries
• Enforces security
• Storage Engine
• Manages physical storage,Transaction logging and
  recovery,Database backup and restore,Locking

5
Client Tools

• Client application
• e.g., VB application or web page (via .ASP)
• Database library
• Client network utilities

6
Client Applications

• Enterprise manager
• Local and remote management
• Query analyzer
• Developer tool
• Profiler
• Traces activity
• Upgrade wizard
• Service manager
• Command-line utilities

7
SQL Server Security Model

• Authentication Mode
• Logins
• Users
• Roles
• Permissions

8
SQL Server Security Authentication Mode

• Windows only
• When only valid Windows users will have access
  to SQL Server
• SQL Server and Windows mixed
• When non-Windows users will have access
• More commonly used in my experience
• A Windows user is a person logged on to a
  Windows domain that is trusted by this instance
  of SQL Server.

9
SQL Server Security Logins

• Creating a new individual account
• sp_addlogin a system stored procedure that adds
  an account to the set of users
• sp_addlogin _at_loginame 'login' , _at_passwd
  'password' , _at_defdb 'database'
  , _at_deflanguage 'language' , _at_sid
  sid , _at_encryptopt 'encryption_option'
• Look up sp_addlogin in Books Online
• Use Query Analyzer to execute this sp. Loginame
  should be your login plus x e.g., rossxDefdb
  should be your copy of berry e.g.
  SCRSalesOrders
• To find the new user, in Enterprise Manager, look
  under Security, Logins

10
SQL Server Security Logins

• Granting access to an existing user or group
• sp_grantlogin a system stored procedure that
  adds an existing Windows login to the set of
  users

11
SQL Server Security More than a Login is needed.

• Logins are at the server level and allow
  connection to the SQL Server software, but dont
  let the logged-in person do anything. The person
  must be given permission at the database or
  server level.
• Database user
• Server role
• Database role

12
SQL Server Security Database Users

• Creating a user in a database
• sp_grantdbaccess gives the named login
  (individual or group) access to the
  databasesp_grantdbaccess _at_loginame
  'login'    ,_at_name_in_db 'name_in_db'
  OUTPUT
• Look up sp_grantdbaccess in Books Online
• Use Query Analyzer to execute this sp. USE your
  database before executing the sp.Loginame should
  be your login plus x e.g., rossx
• To find the new user, in Enterprise Manager, look
  under your database, Users

13
SQL Server Security Roles

• Three kinds of role
• Fixed server roles
• Fixed database roles
• User-defined database roles

14
SQL Server Security Fixed Server Roles

• Apply to all database objects
• Rarely assigned to users
• Sysadmin full permissionsYou have this role in
  this instance of SQL Server
• Others
• Bulkadmin
• Dbcreator
• Diskadmin
• Processadmin
• Securityadmin
• Serveradmin
• Setupadmin

15
SQL Server Security Fixed Database Roles

• Standardized sets of permissions that apply to a
  specific database
• The db_owner is the person who created the
  database (look at the properties of your
  database)
• Other fixed database roles normally assigned to
  IT staff, not users

16
SQL Server Security User-Defined Database Roles

• You define these
• Very helpful in assigning the same permissions to
  groups of users
• Look at context diagram for inspiration

17
SQL Server Security Permissions

• Statement permissions
• Who can execute which statements (commands)
• Nine possibilities
• Data access permissions
• Who can do what to which data objects
• Lots of possibilities
• Continued

18
SQL Server Security Permissions Continued

• Using Data Access Permissions in conjunction with
  other techniques
• Assign logins to appropriate database
  user-defined groups
• e.g., executives, managers, salesstaff
• Create views (in Access we called them queries)
  that limit the data returned
• Stored procedures and user-defined functions can
  also be used
• Assign permissions to the groups that limit which
  views they see and what they can do to those
  views
• Continued

19
SQL Server Security Permissions Continued

• Allowing a person to edit only his/her own data
• In the database, keep a table that links the
  login identity to the primary key of the users
  data (could be an attribute in tblPerson)
• Create views that return only the data for that
  person

20
Next Lecture

• Elements of Transact-SQL and Working with Tables