Tài liệu Beginning SQL Server Modeling- P11 docx

CHAPTER 7  SQL SERVER MODELING SERVICES – SECURITY

201

Figure 7-50. Granting the Select permission for each QC user

Testing

Now you should be in a position to test what data is exposed to each user. You can do this in the SQL

Server Modeling Command Prompt window by impersonating a user with the runas command. Bring up

the SQL Server Command Prompt window, and execute the following code:

runas /user:CarQC "sqlcmd.exe /y25"

The /y25 switch on the sqlcmd.exe command is required, and sets the display width for the

SQLCMD prompt window. The system will prompt for the CarQC user’s password, which should be the

same as the user name (see Figure 7-51). After that is accepted, a separate SQLCMD window will appear,

where you can run SQL queries as the CarQC user against the MfgComponents view.

Download from Wow! eBook <www.wowebook.com>

CHAPTER 7  SQL SERVER MODELING SERVICES – SECURITY

202

Figure 7-51. Using the runas command to impersonate the CarQC user opening a SQLCMD window

In the SQLCMD window that comes up, you’re now running as the CarQC user. Enter the following

SQL code (see Figure 7-52):

use Repository

go

The system should present the following message: Changed database context to 'Repository'.

Next, enter the following SQL commands:

select Name, Level, Folder from [MfgComponentModel].[MfgComponents]

go

Now you might expect to see a listing of only the Car line components, since it is the CarQC user

who entered the query. But instead, you get this: The SELECT permission was denied on the object

'ReadableFoldersView', database ' Repository', schema 'Repository.Item'. (shown in Figure 7-52).

Figure 7-52. Permission denied to ReadableFoldersView

I forgot (intentionally) to set up user access to ReadableFoldersView. I wanted you to see the type of

error message that occurs in this situation. SQL Server gives detailed error messages when a permissions

error occurs, so instead of seeing something like “Access denied – error code 229,” you see exactly what

permission is being denied, and what object, database, and schema are involved. Detailed error

messages can be helpful in diagnosing and fixing these kinds of problems, so it’s always good to take a

close look. (If you’re an experienced programmer, this hardly needs to be said.)

In this case, the user needs SELECT access to ReadableFoldersView because the code that defines the

MfgComponents updatable view used the system-provided ReadableFoldersView (refer to Figure 7-3, line

20) to determine which rows of the view can be exposed to the user.

Download from Wow! eBook <www.wowebook.com>