CHAPTER 7 SQL SERVER MODELING SERVICES – SECURITY
184
Enter (local) for the server name, enter Repository for the database name, and click the OK button.
You should see the connection string displayed in the M Deployment area of the Properties pane (shown
in Figure 7-29). Look at this connection string to make sure everything makes sense. If you need to
change something, it can be directly edited in the prompt.
Figure 7-29. M Deployment area showing the connection string for the newly created
MfgComponentModel database
If you want to make sure everything is in order after setting up the connection string, click again on
the ellipsis button to the right of the connection string prompt to bring up the Connection Properties
dialog, and then click the Test Connection button in the lower-left corner. You should get a notification
that the “Test connection succeeded” (see Figure 7-30). Click OK in the notification window, then the
Cancel button in the Connection Properties dialog to return to the M Deployment area in the Properties
pane.
Download from Wow! eBook <www.wowebook.com>
CHAPTER 7 SQL SERVER MODELING SERVICES – SECURITY
185
Figure 7-30. Testing the database connection string
At this point, you should be ready to deploy the model to the Repository database. Make sure you’ve
saved all files by using the Ctrl-Shift-S Save All action. (No asterisk should appear on any tab.) Right-click
again on the MfgComponentModel project in the Solution Explorer, then select Deploy (as shown in
Figure 7-31).
Download from Wow! eBook <www.wowebook.com>
CHAPTER 7 SQL SERVER MODELING SERVICES – SECURITY
186
Figure 7-31. Selecting the Deploy option in the project context menu
The deployment process writes a log to the Visual Studio Output window. If the deployment is
successful, you’ll see an indication in the last line of this window, as shown in Figure 7-32.
Figure 7-32. Visual Studio Output window, showing the deployment succeeded
If, for some reason, the deployment is unsuccessful, you may get a partial deployment of the
database, with the SQL Server system tables, but without the MfgComponentModel schema. You may be
able to delete the database by right-clicking on the MfgComponentModel database name in the SSMS
Object Explorer, and start over. If that doesn’t work, follow the recovery procedure described in the
sidebar.
Download from Wow! eBook <www.wowebook.com>
CHAPTER 7 SQL SERVER MODELING SERVICES – SECURITY
187
REFRESHING THE COMPONENTMODEL DATABASE
A corrupted or nonworking ComponentModel database can be restored using the mx.exe command-line
tool. This involves entering three
mx.exe commands:
1.
mx.exe create /database:ComponentModel /force. This forces a re-creation
of the ComponentModel database. Existing data will be overwritten.
2.
mx.exe install Repository.mx /database:ComponentModel
/server:(local) /property:rct=+ /property:ra=+
. (The command should
be entered in the command-prompt window as all one line.) This installs the
Repository schema in the database, enables change tracking (rct=+), and enables
auditing (ra=+).
3.
mx.exe install
C:\Oslo\PatternApplication\bin\Debug\PatternApplication.mx
/database:ComponentModel. (Again, all one line.) This installs the
PatternApplication code for supporting the pattern hooks. The path used in this
command assumes you downloaded and installed the pattern application image to
C:\Oslo\PatternApplication. If this was installed to a different location, make
the appropriate adjustment in the path.
If you find yourself using this restore procedure more than once, it may be easier to create a
refreshdb.bat batch file containing these three commands, using a text editor. This batch file should be
located in the same folder as the mx.exe executable, which would normally be
C:\Program
Files\Microsoft Oslo\1.0\bin
It can be executed from the SQL Server Modeling CTP command prompt.
Creating the QC Folders
Recall that you have two manufacturing lines at two different plants: Cars at one plant, and toasters at
another plant. You want to design your QC system so that the CarQC manager can manage his data, the
ToasterQC manager can manage her data, and the top-level QC manager has access to all QC data.
You will set up the QC folders to reflect this, so the folder hierarchy should look like the following
(numbers in parentheses are the assigned folder Id):
QC (100)
• QC-Cars (110)
• QC-Cars-Critical (111)
• QC-Cars-High (112)
• QC-Cars-Std (113)
• QC-Toasters (120)
• QC-Toasters-Critical (121)
Download from Wow! eBook <www.wowebook.com>
CHAPTER 7 SQL SERVER MODELING SERVICES – SECURITY
188
• QC-Toasters-High (122)
• QC-Toasters-Std (123)
To create this folder structure, bring up SQL Server Management Studio, select the Repository
database in the Databases section, and expand the Views section of the Repository (see Figure 7-33). Hit
the R key to home in on the view names starting with Repository, and select Repository.Item.Folders.
Right-click on the view name, and select Edit Top 200 Rows. Since any user has rights to view the top-
level Repository folder, this view (and its table) should have only one row for the Repository folder and
nothing else, unless folders have been previously created because of other activities. This is shown in
Figure 7-33.
Figure 7-33. Editing Repository.ItemFolders view in the Repository database
Click in the Id column of the second row and enter 100 for the Folder Id, and QC for the Name.
Leave the Folder value as NULL, since you want the QC folder to be a top-level folder, with no parent
folder (see Figure 7-34). The exclamation points in the red circles indicate that the cells have changed,
but the data is not committed. As soon as you click on the next row, the data in this row will be
committed.
Download from Wow! eBook <www.wowebook.com>
CHAPTER 7 SQL SERVER MODELING SERVICES – SECURITY
189
Figure 7-34. Adding the top-level QC folder to Repository.ItemFolders view
Continue to add new folder rows according to the plan laid out at the start of this section. When you’re
finished, the Repository.Item.Folders data should look like the right pane shown in Figure 7-35.
Figure 7-35. Adding the child QC folders to Repository.Item.Folders
Download from Wow! eBook <www.wowebook.com>
CHAPTER 7 SQL SERVER MODELING SERVICES – SECURITY
190
Building the Sample Data
Now you’re ready to build some sample manufacturing component data to test your security sample
design. You can use Quadrant to do this, so open Quadrant. If any sessions or workpads were left open in
the interface when you exited the last time Quadrant was being used, close these so that you have a fresh
canvas with nothing on it. Also, use the File
Delete Session menu option to delete any existing sessions
(named other than Quadrant) left over from the last time you closed Quadrant. Quadrant is the name of
the default session, which can’t be deleted.
On the top menu, select File
New Session (or use the Ctrl-Shift-N shortcut) to open a new
database session (see Figure 7-36). The default in the Server prompt of the resulting New Database
Session dialog should be a period (.), which is the equivalent of the (local) instance of SQL Server. Accept
the default for the server instance, or change it to whatever server instance you have been using for this
exercise. (Remember: You need to be working with SQL Server 2008 or newer in order to have the
features that work with M and SQL Server Modeling.) In the Database prompt, select Repository from
the drop-down menu, or simply enter Repository. For the session name, enter Loading MfgComponent
Data. Click the Create button to open the session.
Figure 7-36. Creating a new Quadrant session on Repository to load MfgComponentsTable
This should bring up a database Explorer pane on the canvas, showing three items: Database, QC,
and Repository. Note the icons associated with each item: The QC and Repository items each show a
folder symbol, while the Database item shows (of course!) a database symbol (see Figure 7-37).
Download from Wow! eBook <www.wowebook.com>
CHAPTER 7 SQL SERVER MODELING SERVICES – SECURITY
191
Figure 7-37. Initial Quadrant Explorer pane after opening the new Repository database session
Just to assure yourself everything is good as far as the folder setup us concerned, expand the QC
item by clicking the triangle to its left, and then expand the two items at the next level: QC-Cars and QC-
Toasters. You should see something very close to what’s shown in Figure 7-38.
Figure 7-38. MfgComponentModel database Explorer with QC folders expanded
The folder structure looks good—at least if it looks like Figure 7-38—so let’s expand the Database
item by clicking in the triangle symbol (see Figure 7-39). Click and drag the square symbol for
MfgComponentsTable onto the Quadrant canvas (as shown by the arrow in the figure). This will open an
Explorer pane for MfgComponentsTable, which will be empty, since you haven’t created the sample data.
Download from Wow! eBook <www.wowebook.com>
CHAPTER 7 SQL SERVER MODELING SERVICES – SECURITY
192
Figure 7-39. Dragging the MfgComponentsTable onto the Quadrant canvas to open a new Explorer pane
Click on an empty part of the grayed background canvas (you should see a hand cursor appear),
and drag the canvas with the two explorer panes to the left until only the MfgComponentsTable Explorer
pane is visible in the main window (as shown in Figure 7-40).
Figure 7-40. After dragging the canvas to the left to isolate the MfgComponentsTable Explorer pane
To load the sample data, use the same procedure you used in Chapter 4 for creating the sample
records for the car example. Click in the Explorer pane, then use the Ctrl-I shortcut to bring up a form for
adding a new record (see Figure 7-41). (This is equivalent to using the Data Insert Item menu option.)
Download from Wow! eBook <www.wowebook.com>
CHAPTER 7 SQL SERVER MODELING SERVICES – SECURITY
193
Figure 7-41. Adding the top-level instance of Car to the MfgComponentsTable
Note that in the prompt for the Folder value, you have a drop-down list that allows you to choose
the appropriate QC folder. Even the top-level instance (in this case, named Car) must pass QC
certification, so let’s assign it a QC level of High. Leave the PartOfComponent prompt as Null, since the
Car value is a top-level item and has no parent component. Once you’ve entered all the values for the
new record, press Ctrl-S to save it. It should immediately appear in the table.
Table 7-1 shows the sample data to enter into the MfgComponentsTable in Quadrant. There are four
Car component rows and three Toaster component rows. The CarQC manager should only see the four
Car rows, and the ToasterQC manager should see only the three Toaster rows when they query the table.
Table 7-1. Sample Data for the MfgComponentsTable
Name Level Description Qty MfgLine Folder PartOfComponent
Car 1 Acme Runabout 1 Cars QC-Cars-High <null>
Drive Train 2 Makes the car go 1 Cars QC-Cars-High Car
Rear Wheel
Assembly
3
Includes brake
Assembly
2 Cars QC-Cars-Critical Drive Train
Brake Assembly 4 Disk Brakes 4 Cars QC-Cars-Critical Rear Wheel Assembly
Toaster 1 Acme Bunmaster 1 Toasters QC-Toaster-High <null>
Heater Assembly 2 1 per slot 4 Toasters QC-Toaster-Critical Toaster
Heater Element 3
2 per heater
Assembly
8 Toasters QC-Toaster-Critical Heater Assembly
Download from Wow! eBook <www.wowebook.com>
Không có nhận xét nào:
Đăng nhận xét