1、THE DATABASE APPLICATION TECHNOLOGY,Jin Zhan,3. SQL SERVER 2000 OVERVIEW,3.SQL SERVER 2000 OVERVIEW,HISTORY,A Brief History of SQL Server,3.SQL SERVER 2000 OVERVIEW,SQL SERVER 2K EDITIONS,SQL Server 2000 Enterprise Edition.This edition is the complete SQL Server offering for any organization. SQL Se
2、rver 2000 Standard Edition.This edition is the affordable option for small- and medium-sized organizations that do not require advanced scalability and availability features or all of the more advanced analysis features of the SQL Server 2000 Enterprise Edition. SQL Server 2000 Personal Edition.This
3、 edition includes a full set of management tools and most of the functionality of the Standard Edition, but it is optimized for personal use. SQL Server 2000 Developer Edition.This SQL Server offering enables developers to build any type of application on top of SQL Server. SQL Server 2000 Desktop E
4、ngine (MSDE).This edition has the basic database engine features of SQL Server 2000. This edition does not include a user interface, management tools, analysis capabilities, merge replication support, client access licenses, developer libraries. SQL Server 2000 Windows CE Edition.This edition is the
5、 version of SQL Server 2000 for devices and appliances running Windows CE.,3.SQL SERVER 2000 OVERVIEW,CLIENT and SERVER,3.SQL SERVER 2000 OVERVIEW,INTEGRATED COMPONENTS,3.SQL SERVER 2000 OVERVIEW,DEVELOPMENT INTERFACES,ODBC is an API for database access thats both a formal and industry standard. SQL
6、 Server provides a high-performance ODBC interface for all Windows-based programming environments. The SQL Server ODBC driver implements every function in the ODBC 3 specification. OLE DB can be considered an object version of ODBC but is more powerful in that it can access data from data sources be
7、yond those that ODBC can access. ADO is a higher-level object interface on top of OLE DB that provides much of the same functionality and performance. ADO is the recommended and supported interface for Internet applications written using the Microsoft Visual InterDev development tool. DB-Library is
8、a SQL Server-specific API that provides all the necessary macros and functions for an application to open connections, format queries, send them to the server, and process the results. DB-Library is the original SQL Server programming interface. ESQL/C SQL Server provides an ESQL/C precompiler that
9、allows developers to write SQL Server applications by embedding the SQL queries directly in their C source code.,3.SQL SERVER 2000 OVERVIEW,PROTOCOL,3.SQL SERVER 2000 OVERVIEW,CLIENT to SQL SERVER DB (1),1. The user selects an option in a client application. This option calls a function in the clien
10、t application that generates a query that is sent to SQL Server. The application uses a database access library to send the query in a way SQL Server can understand. 2. The database library transforms the original request into a sequence of one or more Transact-SQL statements to be sent to SQL Serve
11、r. These statements are encapsulated in one or more Tabular Data Stream (TDS) packets and passed to the database network library to be transferred to the servercomputer.3. The database network library uses the network library available in the client computer to repackage the TDS packets as network p
12、rotocol packets.,3.SQL SERVER 2000 OVERVIEW,CLIENT to SQL SERVER DB (2),4. The network protocol packets are sent to the server computer network library across the network. 5. The extracted TDS packets are sent to Open Data Services (ODS), where the original query is extracted.6. ODS sends the query
13、to the relational engine, where the query is executed in collaboration with the storage engine.7. The relational engine creates a result set with the final data and sends it to ODS.8. ODS builds one or more TDS packets to be sent to the client application, and sends them to the server database netwo
14、rk library.9. The server database network library repackages the TDS packets as network protocol packets and sends them. 10. The client computer receives the network protocol packets and forwards them to the network libraries.11. The network library sends the TDS packets to the database access libra
15、ry, where these packets are reassembled and exposed as a client result set to the client application.12. The client application displays information to the user.,3.SQL SERVER 2000 OVERVIEW,CLIENT APPLICATIONS,SQL Server 2000 includes some client applications you can use to administer and develop dat
16、abases: Enterprise Manager Query Analyzer Profiler Upgrade Wizard Service Manager Command-line utilities,3.SQL SERVER 2000 OVERVIEW,ENTERPRISE MANAGER,You can use Enterprise Manager to manage any SQL Server 2000 instance, including the default SQL Server 2000 instance, running locally or remotely.,E
17、nterprise Manager folders: Databases Data Transformation Management Replication Replication Monitor Security Support Services Meta Data Services,3.SQL SERVER 2000 OVERVIEW,QUERY ANALYZER,Query Analyzer is a client tool designed to send queries to SQL Server and to display results. it is a developer
18、tool used to manage databases and create database applications through the use of Transact-SQL scripts.,3.SQL SERVER 2000 OVERVIEW,SQL SERVER PROFILER,SQL Server Profiler is a client tool that captures SQL Server activity and sends this activity to a file, database table, or the screen, giving you a
19、 powerful analysis tool., Monitor real-time activity. Detect long-running queries. Trace locks and deadlocks. Summarize activity per database, user, host, and so on. Select which database objects are more heavily used to prioritize optimization decisions. Detect actual SQL Server activity from appli
20、cations in which the source code is not available. Monitor database autogrowth or autoshrink. Perform security audits.,3.SQL SERVER 2000 OVERVIEW,BASIC CONCEPT (1),The SQL Server Upgrade Wizard converts SQL Server 6.5 databases to the SQL Server 2000 format. You can upgrade the entire server or sele
21、cted databases. The upgrade process will transfer and convert thedatabase catalog, most of the server and database settings, and user data.,3.SQL SERVER 2000 OVERVIEW,LOGIN TYPE,1. A user needs a valid login to gain access to SQL Server.2. After a user has entered SQL Server, access to specific data
22、bases is controlled by the existence of avalid user on the target database.3. Users need specific permissions to execute specific statements at the database level.4. Users need permissions per object and action.5. Incomplete permissions to execute a given statement prevent the entire statement from
23、being executed., Windows Authentication only, when only valid Windows users will have access to SQL Server. Mixed mode, when SQL Server accepts either Windows Authentication or SQL Server authentication.,3.SQL SERVER 2000 OVERVIEW,Authentication Modes, Windows Authentication only, when only valid Wi
24、ndows users will have access to SQL Server. Using this type of authentication, SQL Server doesnt store password information for Windows logins. Mixed mode, when SQL Server accepts either Windows authentication or SQL Server authentication. If the user selects SQL Server Authentication, the supplied
25、login name and password mustcorrespond to a valid login name and password in SQL Server; otherwise, the connection will be refused and SQL Server will not try to connect with the users Windows credentials.,3.SQL SERVER 2000 OVERVIEW,DEFAULT LOGIN,When you install a new instance of SQL Server, you ha
26、ve only the following logins: BUILTINAdministratorsThis is the login associated with the local Administrator group in the local server where SQL Server is installed. Members of this group are considered SQL Server administrators by default. You can remove this login. saThis is the SQL Server system
27、administrator login account used for SQL Server authentication. This login cannot be removed, even if you select Windows Integrated Authentication only. YourDomainSQLServiceThis is the login account for the SQL Server service account, if you selected, as recommended, to use a domain account as a SQL
28、 Server service account.,3.SQL SERVER 2000 OVERVIEW,WINDOWS LOGIN TYPE,Execute sp_addlogin to create a new login using SQL Server Authentication. In this case, you can specify the password, default language, and default database for this login. Execute sp_grantlogin to grant access to SQL Server to
29、an existing local user in the server that is running SQL Server. In this case, the name of the login should have the format BUILTINUser . Execute sp_grantlogin to grant access to SQL Server to an existing local group in the server that is running SQL Server. In this case, the name of the login shoul
30、d have the format BUILTINLocalGroup. Execute sp_grantlogin to grant access to SQL Server to an existing domain user in a domain trusted by the domain in which SQL Server is running. In this case, the name of the login should have the format DomainNameUser. Execute sp_grantlogin to grant access to SQ
31、L Server to an existing domain global group in a domain trusted by the domain where SQL Server is running. In this case, the name of the login should have the format DomainNameGlobalGroup .,3.SQL SERVER 2000 OVERVIEW,Defaule DB Users, Windows Authentication only, when only valid Windows users will h
32、ave access to SQL Server. dboThis is the database owner with full permissions, by default, on the entire database. This user cannot be removed. guestThis is the default user for logins that dont have a specific user in a database. Because every system database, Northwind, and Pubs databases have a g
33、uest user, any valid login can use these atabases directly. This user can be removed to guarantee authenticated access to a database only.,SQL Server uses the Model database as a template to create new databases. The Model database does not have a Guest account; therefore, new databases will not hav
34、e a guest user unless youcreate it explicitly.Use sp_grantdbaccess to Grant Logins Access to a Database,3.SQL SERVER 2000 OVERVIEW,Grant Database User,USE NorthwindGO- Create a Tim user in the Northwind databaseEXEC sp_grantdbaccess loginame = Tim- Create a User in Northwind for the local Guest logi
35、nEXEC sp_grantdbaccessloginame = BUILTINGuest , name_in_db = LocalGuest- Create a user in Northwind for the domain Guest accountEXEC sp_grantdbaccessloginame = YourDomainGuest , name_in_db = GlobalGuestGranted database access to Tim.Granted database access to BUILTINGuest.Granted database access to
36、CallSQLGuest.,3.SQL SERVER 2000 OVERVIEW,Server and Database Roles,The most important fixed server role is sysadmin (System Administrators). Login members of the sysadmin server role are not affected by SQL Server security at all., Fixed server roles Group logins by general server permissions to sim
37、plify general logins administration. These fixed server roles cannot be modified or dropped, not even their permissions. You can only change their membership. Fixed database roles Group users in a database by functionality, reducing permissions maintenance overhead. Fixed database roles cannot be mo
38、dified or dropped, not even their permissions. You can only change their membership. User-defined database roles Extend the functionality of fixed database roles, providing extra flexibility on grouping users at the database level to apply specific permissions.,3.SQL SERVER 2000 OVERVIEW,Server Role
39、s, Members of the serveradmin role can configure and stop or restart SQL server. Members of the setupadmin role can manage linked servers and specify which stored procedures will automatically run when SQL Server starts. Members of the securityadmin role can manage logins, including changing passwor
40、ds, and assign permissions to create databases. Members of the processadmin role can stop processes running in SQL Server. Members of the dbcreator role can manage databases. Members of the diskadmin role can manage disk files and backup devices. Members of the bulkadmin role can execute BULK INSERT
41、 statements.Members of the sysadmin role have all the permissions listed for all other fixed server roles.,3.SQL SERVER 2000 OVERVIEW,Database Roles, db_ownerDatabase owners or users who have, by default, granted permissions to perform any action on every database object. db_accessadminDatabase acce
42、ss administrators manage users in the database. db_securityadminDatabase security administrators manage permissions. db_ddladminDatabase DDL administrators can execute any Data Definition Language statement. db_backupoperatorDatabase backup operators. db_datareaderDatabase users with permissions to
43、read data from any table in the database. db_datawriterDatabase users with permissions to modify data on any table in the database. db_denydatareaderDatabase users with denied permissions to read data from this database. db_denydatawriterDatabase users with denied permissions to modify data from thi
44、s database.,3.SQL SERVER 2000 OVERVIEW,Permissions, Statement permissions Permissions to execute specific Transact-SQL statements, to create database objects, and to execute backups. Data access permissions Permissions to read, delete, insert, update, or reference data, or permissions to execute sto
45、red procedures and user-defined functions., Neutral No permissions. In this case, the user will have permissions to perform the required action, depending on permissions applied to other roles or Windows groups the user might belong to. Denied The user cannot perform the required action. It does not
46、 matter whether the user belongs to other roles or groups with permissions to perform this action. Deny overrides everything else. Granted In principle, the user has permissions to proceed with this action. However, the final permissions depend on other existing permissions on the same action applie
47、d to other groups or rolesthis par ticular user belongs to.,3.SQL SERVER 2000 OVERVIEW,Data access permissions,3.SQL SERVER 2000 OVERVIEW,GrantPermissions,You can define permissions for the following statements: BACKUP DATABASETo execute full or differential database backups BACKUP LOGTo perform tra
48、nsaction log backups CREATE DATABASETo create databases CREATE DEFAULTTo create independent DEFAULT objects CREATE FUNCTIONTo create user-defined functions CREATE PROCEDURETo create stored procedures CREATE RULETo create independent RULE objects CREATE TABLETo create tables CREATE VIEWTo create views,To grant statement permissions, you use the GRANT statement with the following syntax:GRANT Statement TO Security_accountor GRANT ALL TO Security_account,3.SQL SERVER 2000 OVERVIEW,Permissions,Application Role Management,
