This question has 2 parts A and B: Question 1 Part A: Write a script that create

Question

This question has 2 parts A and B:

Question 1 Part A: 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.
Question 1 Part B:. 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.

Explanation / Answer

1-

DECLARE MyGuitarShop CURSOR
DYNAMIC
FOR
SELECT Orders_id, Orderitems_id FROM Orderitems;
DECLARE @order varchar(50), @item varchar(50);
DECLARE @initLog varchar(MAX);
OPEN MyGuitarShop
FETCH FIRST FROM MyGuitarShop;
   BEGIN
   WHILE @@FETCH_STATUS = 0
    SET @initLog = 'CREATE LOGIN ' + @order + @item +
    ' WITH PASSWORD = '+'''temp'''+'MUST_CHANGE, DEFAULT_DATABASE = MyGuitarShop;
    CREATE USER ' + @order + @item +
    ' ALTER ROLE OrderEntry ADD MEMBER ' + @order + @item + ';'
    EXEC(@initLog)
    FETCH NEXT FROM MyGuitarShop;
   END
CLOSE MyGuitarShop;
DEALLOCATE MyGuitarShop;

2-

function login (email, password, callback) {
var connection = mysql({
    host     : 'localhost',
    user     : 'RobertHalliday',
    password : 'HelloBob',
    database : 'MyGuitarShop'
});

connection.connect();

var query = "SELECT id, nickname, email, password " +
             "FROM users WHERE email = ?";

connection.query(query, [email], function (err, results) {
    if (err) return callback(err);
    if (results.length === 0) return callback();
    var user = results[0];

    if (!bcrypt.compareSync(password, user.password)) {
      return callback();
    }

    callback(null,   {
      id:          user.id.toString(),
      nickname:    user.nickname,
      email:       user.email
    });

});

}

Declare @defaultDataBaseName as varchar(50);
Declare @LoginCreationScript as varchar(max);
Declare @UserCreationScript as varchar(max);
Declare @TempUserCreationScript as varchar(max);
set @defaultDataBaseName = 'MyGuitarShop';
set @RobertHalliday = 'domainRobertHalliday';
set @LoginCreationScript ='CREATE LOGIN [{RobertHalliday}]
FROM WINDOWS
WITH DEFAULT_DATABASE ={MyGuitarShop}'

set @UserCreationScript ='
USE {MyGuitarShop}
CREATE User [{RobertHalliday}] for LOGIN [{RobertHalliday}];
EXEC sp_addrolemember ''db_datareader'', ''{RobertHalliday}'';
EXEC sp_addrolemember ''db_datawriter'', ''{RobertHalliday}'';
Grant Execute on Schema :: dbo TO [{RobertHalliday}];'

/*User creation and role assignment*/
set @TempUserCreationScript =Replace(@UserCreationScript, '{MyGuitarShop}', @defaultDataBaseName)
Execute(@TempUserCreationScript)
set @TempUserCreationScript =Replace(@UserCreationScript, '{MyGuitarShop}', 'db2')
Execute(@TempUserCreationScript)
set @TempUserCreationScript =Replace(@UserCreationScript, '{MyGuitarShop}', 'db3')
Execute(@TempUserCreationScript

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.