You support a bookstore. They need an in-store kiosk customer support system so clients can easilt find what they desire. You create a Books table and an Authors table. Then you realize one book can have many authors and one author can write many books. This represents a many-to-many relationship. What do you do to enable searches for all books by one author and all authors by one book?
Answer – Create a junction table named BooksAuthors with a Foreign Key relationship to the Primary Key in the Books and Authors tables.
(A many-to-many relationship requires an in-between table that is itself one-to-many in both directions. By convention, this junction table assumes the names of the two tables referenced; in this case, Books and Authors.)
MS SQL Server 2008
- When establishing transparent data encryption, you used a certificate stored in SQL Server and a database encryption key created using this certificate. How should this certificate and key be handled as part of the regular backup process?
- Your database contains a table named customers, which has a column named credit_card that contains the primary credit card number that the customer uses for purchases. This column of data must be encrypted. How can you best encrypt this column so that regular database users cannot read a credit card number?
- How does C2 compare to various security and encryption methods?
- You have been asked to establish the use of extensible key management (EKM) for a database that contains highly valuable data. Currently your SQL Server is running Windows Server 2008 64 but with SQL Server Standard Edition also using 64 bit. What steps must you take in order to implement EKM?
- All SQL Server backups are made using transparent data encryption (TDE). The backup process involves first performing a backup to disk storage then copying the disk backup files to tape and storing the tapes at offsite facility. A courier service picks up each night’s backup tapes every morning. This morning, shortly after the courier picked up the tapes at your facility, the courier’s van was stolen. Upper management is concerned that the thief who now has your backup tapes can read highly confidential information from these tapes. What should you tell upper management?
- You are the administrator of a SQL Server system that will contain marketing, sales, and production data. Each of these departments is contains in a Windows group named after the department. Each of these departments should be able to read and modify its own data, but they should not be able to read or modify that data of other departments. You need to configure the server so it meets security requirements with minimal administrative overhead and resource consumption. What should you do?
- You are the administrator of a SQL Server system that will be used only for development access; the server will have no production databases on the server whatsoever. All your developers need to be able to create databases and object inside the databases, such as tables, views, and so on. To which roles should they be added at the server and database levls to accommodate these needs?
- You have a number of users in your customer service department who need Select, Insert, and Update permissions, but they should not be able to delete—only managers should have the permission to delete data. How can you ensure that only managers can delete data and users can only perform the tasks listed?
- You have an HR database that all users will be allowed to read from to obtain information, but only the HR department should be able to read from and update the data in the database. Select the easiest and most secure way to ensure this.
- You need to create a new login account for one of your Unix users named WoodsJ. Which command would you use to do this?
- You want to be able to use email. replication, and other interserver services with SQL Server. When you install SQL Server, which type of account should you use?
- You are setting up a kiosk in a library that hundreds of people will access every month. You want to make sure visitors to library have access to read data from the SQL Server, but they should not be able to change any of the data. You need to accomplish this with the least administrative overhead possible. What should you do?
- You have just installed a new SQL Server on your network, and you want to make sure no Windows administrator has administrative access on the SQL Server until receiving the proper training. What you do to keep a Windows administrator from trying to administer the new SQL Server and possibly damaging it?
- You have several SQL Servers in your organization that participate in linked server queries, with security configured as shown in Table 14-3. BobH complains that the linked server queries are not working. What can’t BobH use linked server queries?
- You have the authority to create both Windows accounts and SQL logins and roles on your network. You have a Windows server that contains a shared folder called Administration and a shared folder called Marketing. On your SQL Sever database you have database called Marketing. Ten of your users will be working on a short-term project together; all of them require the same access to the Marketing database on the SQL Server and the Marketing folder on the Windows server, but only four of them are allowed access to the Administration folder on the Windows server. Choose the best way to grant these users access to the database resources.
- You are the administrator of a SQL Server system that contains a database named Accounting. To maintain strict security on the database, you want to make sure users do not have any default permissions when their account is first created. What should you do?
- But Bob now complains he cannot access SQL Server when he logs in with his Windows account. Why not?
- You have spent a great deal of money and effort to create a custom accounting program in VisualBasic designed to meet some specific needs of your company. You find that some of your users still access your database through other methods such as Microsoft Excel and Query Analyzer, which causes problems with the integrity of your database. How can you fix this problem?
- You need to grant Robery permission to modify employee phone numbers in the Employees table, but you do not want him to be able to modify any other data in the table. Select the best way to accomplish this.
- Two developers named IversonB and JacksonT need to be able to create objects in the Inventory database as part of their regular duties. You need to give them the ability to create these objects without giving them too much author on the server. Choose the most secure way to do this.
- Andrea is a member of the Sales and Marketing roles in your database. She needs Select, Insert, and Update permissions on your table. With security configured as shown in Table 14-2, how can you grant her the necessary permissions?
- You are the administrator of a SQL Server system that contains databases named Marketing and Sales. Amanda has a Windows account that has been granted a login to the SQL Server, and she has been given access to the Marketing database. Now she needs view and edit permissions on the Sales database as well. Which T-SQL statements should you execute?
- Your SQL Server system stores information about suppliers in the Suppliers table. Table 14-1 shows the security setup for the table, Joe belongs to the Administration and Marketing roles in the database, and he needs to be able to perform Inserts, Updates, and Deletes on the table. Which command should you use to give these permissions?
- One of your users has created a table (John.table1) and granted Samantha Select permission of the table. Samantha, however, does not need to see all the data in the table so she create a view (Samantha.view1). Thomas now wants access to Samantha’s view, so Samantha grants Thomas Select permission on the view. What happens when Thomas tries to select from the view?
- Jason belings to a Windows group names Sales that has been grated access to SQL server via a Windows group account SQL Server. Jason should not have access to SQL Sever, but he needs the permissions afforded the Sales group on other servers. How can you remedy this?