USE MICROSOFT SQL SERVER 22Murach's SQL Server 2016 for Developers: My Guitar Shop exercises (modified)Chapter 17How to manage database securityExercises1 Write a script that creates a user-defined database role named OrderEntry in theMyGuitarShop database. Give INSERT and UPDATE permission to the new role forthe Orders and OrderItems table. Give SELECT permission for all user tables.2 Write a script that (1) creates a login ID named "BillyHalliday" with the password"HelloBilly"; (2) sets the default database for the login to the MyGuitarShopdatabase; (3) creates a user named "BHalliday" for the login; and (4) assigns the userto the OrderEntry role you created in exercise 1.3 Write a script that uses dynamic SQL and a cursor to loop through each row of theAdministrators table and (1) create a login ID for each row that consists of theadministrator's last and first name with no space between; (2) set a temporarypassword of "dump" for each login; (3) set the default database for the login to theMyGuitarShop database; (4) create a user for the login with the same name as thelogin; and (4) assign the user to the OrderEntry role you created in exercise 1.4 Using the Management Studio, create a login ID named "ASmithfield" with thepassword "ASmi9999," and set the default database to the MyGuitarShop database.Then, grant the login ID access to the MyGuitarShop database, create a user for thelogin ID named "RBrautigan", and assign the user to the OrderEntry role you createdin exercise 1.Note: If you get an error that says "The MUST CHANGE option is not supported",you can deselect the "Enforce password policy" option for the login ID.Save a screen print of the user list after adding this user. Name it ex17-04a.png.(Any image format will be fine)5Write a script that removes the user-defined database role named OrderEntry. (Hint:This script should begin by removing all users from this role.)6Write a script that (1) creates a schema named Admin, (2) transfers the table namedAddresses from the dbo schema to the Admin schema, (3) assigns the Admin schemaas the default schema for the user named RobertHalliday that you created in exercise2, and (4) grants all standard privileges except for REFERENCES and ALTER toRobert Halliday for the Admin schema. AdministratorsAdmin DEmailAddressPasswordFirstNameLastNameOrderitemsItem DOrder D7ProductsProductIDCategoryIDProduct CodeProductNameDescriptionListPriceProductIDItemPriceCategoriesCategoryIDDiscountAmountQuantityDiscountPercentDate AddedCategoryNameQAddressesAddressIDCustomerlDLinelLine2CityStateZipCodePhoneDisabledOrders8OrderIDCustomerlDOrderDateShipAmountTaxAmountShipDateShipAddressIDCard TypeCardNumberCardExpiresBillingAddressIDCustomersCustomer DEmailAddressPasswordFirstNameLastNameShippingAddressIDBillingAddress D

Please find the attached solution of the given problem:

Write a script that creates a user-defined database role named OrderEntry in the MyGuitarShop database. Give INSERT and UPDATE permission to the new role for the Orders and OrderItems table. Give SELECT permission for all user tables. Write a script that (1) creates a login ID named "BillyHalliday" with the password "HelloBilly"; (2) sets the default database for the login to the MyGuitarShop database; (3) creates a user named "BHalliday" for the login; and (4) assigns the user to the OrderEntry role you created in exercise 1.

Write a script that creates a user-defined database role named OrderEntry in the MyGuitarShop database. Give INSERT and UPDATE permission to the new role for the Orders and OrderItems table. Give SELECT permission for all user tables. Write a script that (1) creates a login ID named "BillyHalliday" with the password "HelloBilly"; (2) sets the default database for the login to the MyGuitarShop database; (3) creates a user named "BHalliday" for the login; and (4) assigns the user to the OrderEntry role you created in exercise 1.

A Guide to SQL

9th Edition

ISBN:9781111527273

Author:Philip J. Pratt

Publisher:Philip J. Pratt

Chapter7: Database Administration

Section: Chapter Questions

Problem 4TD: Write, but do not execute, the commands to grant the following privileges: a. User Ashton must be...

See similar textbooks

Similar questions

Write, but do not execute, the commands to grant the following privileges: a. User Ashton must be able to retrieve data from the ITEM table. b. Users Kelly and Morgan must be able to add new orders and order lines. c. User James must be able to change the price for all items. d. User Danielson must be able to delete customers. e. All users must be able to retrieve each customers number, name, street, city, state,and postal code. f. f User Pere7 must be able to create an index on the ORDFRS table. g. User Washington must be able to change the structure of the ITEM table. h. User Grinstead must have all privileges on the ORDERS table.
Which of the following data dictionary objects should be used to view information about the constraints in a database? USER_TABLES USER_RULES USER_COLUMNS USER_CONSTRAINTS None of the above objects should be used.
Write, but do not execute, the commands to grant the following privileges: a. User Rodriquez must be able to retrieve data from the TRIP table. b. Users Gomez and Liston must be able to add new reservations and customers to thedatabase. c. Users Andrews and Zimmer must be able to change the price of any trip. d. All users must be able to retrieve the trip name, start location, distance, and type forevery trip. e. User Golden must be able to add and delete guides. f. User Andrews must be able to create an index for the TRIP table. g. Users Andrews and Golden must be able to change the structure of the CUSTOMERtable. h. User Golden must have all privileges on the TRIP, GUIDE, and TRIP_GUIDEStables.
Chapter 17 How to manage database security Exercises using the My Guitar Shop Database Use Microsoft SQL Server Write a script that creates a user-defined database role named OrderEntry in the MyGuitarShop database. Give INSERT and UPDATE permission to the new role for the Orders and OrderItems table. Give SELECT permission for all user tables. 2.Write a script that (1) creates a login ID named “RobertHalliday” with the password “HelloBob”; (2) sets the default database for the login to the MyGuitarShop database; (3) creates a user named “RobertHalliday” for the login; and (4) assigns the user to the OrderEntry role you created in exercise 1.
Oracle Database 18c XE comes with a sample database user named HR. This user owns several database tables in a sample schema for a fictional Human Resources department. However, for security reasons, this user's account is locked. You need to unlock this account before you can view the HR objects or build any applications that use these objects. Assume that you are logged in as Systems Administrator, provide the SQL*plus commands for unlocking the HR account, making sure that the password reuse and life time is unlimited.
In this exercise, you will create tablespaces and change their characteristics.1. Connect to the database as user SYSTEM.2. Create a tablespace (MYTBSP_YourName) in a suitable directory—anydirectory on which the Oracle owner has write permission will do:3. Create a table in the new tablespace, and determine the size of the firstextent:4. Add extents manually, and observe the size of each new extent byrepeatedly executing this command,5. Take the tablespace offline, Try to insert some data in to the table, try todelete data from table, observe the effect, and bring it back online. 6. Drop the tablespace, and use an operating system command to confirmthat the file has indeed gone:
Hello. I am having trouble with a question for my database devlopment class. I need help with this because I have been struggling with this and haven't found a clear answer. I would appreciate the help. Thanks. Display a list of all books in the BOOKS table. If a book has been ordered by a customer, also list thecorresponding order number and the state in which the customer resides. Use the tablesdb1.casteel.books, db1.casteel.orderitems, db1.casteel.orders, & db1.casteel.customers.[ HINT: You should get 35 rows and three columns ]
IN MYSQL in DBA oracel Create a user with the name IS Sead and password a789 and give him permission to connect to the databaseCreate a role function that includes allowing to create a table and create a view and then granting that function to the Sead . user Make the sead user give permission to query and enter on the student table for all users in the databaseHave the user sead withdraw the entry on the student table from user saa
create Database CTU_YOURICASNOGOUSE CTU_YOURICASNO;GOCREATE TABLE Course( CourseID int PRIMARY KEY, CourseName varchar(255), CourseDesc varchar(255),);CREATE TABLE Students( ICASNo int Primary Key, StudentName varchar (255)NOT NULL, StudentSurname varchar (255)NOT NULL, StudentContact varchar (255)NOT NULL, StudentEmail varchar(255)NOT NULL, HighSchoolAttended varchar(255)NOT NULL, courseID int,);CREATE TABLE Semester( SemesterID int Primary Key, SemesterStartDate varchar(255)NOT NULL, SemesterEndDate varchar(255)NOT NULL, ICASNo varchar(255),);CREATE TABLE Facilitator( FacilitatorID int Primary Key, FacilitatorName varchar(255) Not Null, FacilitatorContact varchar(255)Not Null, FacilitatorEmail varchar(255)Not Null, ICASNo varchar(255)Not Null, ); CREATE TABLE Module( ModuleID int Primary Key, ModuelName varchar(255)Not Null, ModuleDuration varchar(255)Not Null, FacilitatorID varchar(255)Not Null,); Using the…
Create a MySQL Database called “CSC306Class”. Create three (3) tables in the database called “studentInf”, “IntructorInf”, and “LikedAndDisliked”. Each table should consist of five columns that are relatable to the table name. Insert at least five row of data in each table. The data can be any information as long as it is related to the table name.
You are given a database - MGMT2006_202324S02_Boutique Inc Student File.accdb, containing Currencies, Sales, and Locations tables to provide some results for MGMT Boutique INC. (That is, your CC will provide you with the database). You should: 1. Rename the database as MGMT2006_Boutique_ is your actual name. 2. Create relationships between pairs of tables and enforce referential integrity 3. Append two (2) new customers, two (2) old customers and two (2) new POS transactions. Be sure to include sales for your two new customers. 4. Create the following queries: a. List the names of places from which the company sells. Show all columns in the table. Name this query Q1-SELLING-LOCATIONS
Task 5: The Developers team also wants you to ensure that emails are converted to lowercase after an update operation. Currently, new insertions are guaranteed to have lowercase emails, but there is no such guarantee for legacy emails. Therefore, the team wants to ensure that emails are being retained in a lowercase irrespective of any changes in the database. Create a new TRIGGER called email_update for the USERS table that runs before an UPDATE operation. Task: Create a TRIGGER to run on the USERS table before any UPDATE operation. (SQL Database Test): Create a TRIGGER before any UPDATE operations on the USERS table