Question

1. Use SQL to create a polyinstantiated table including a primary key and a unique constraint

2.Use SQL to insert multiple records for each security classification with the same ID. You must have 4 classifications.

3.Use SQL to create 4 schemas, one for each security classification 4.Use SQL to create a view in each schema that restricts the records to those belonging to a particular security classification and restricts the columns to only those columns that have relevant data.

5.Select from each of the views. Take a screen shot of the query results for each user(total of 4 for this step).

6.Use SQL to create 4 logins, one for eachsecurity classification

7.Use SQL to create 4 database users, one for each login with a default schema that matches the security classification for the login.

8.Use SQL to create 4 roles at the database level, one for each security classification.

9.Use SQL to assign each user to its specific role.

10.Use SQL to grant select on the appropriate view to the roles.

11.Execute tests as each of the four users, trying to select from their respective views. Take a screen shot of the query results for each user(total of 4 for this step).

12.Use SQL to revoke select on the view from the role

13.Use SQL to remove the users from the role

14.Use SQL to drop the users

15.Use SQL to drop the logins

16.Use SQL to drop the roles1

7.Use SQL to drop the views

18.Use SQL to drop the schemas

19.Use SQL to drop the table

Answer 1

(1).Use SQL to create a polyinstantiated table including a primary key and a unique constraint

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);

(2).Use SQL to insert multiple records for each security classification with the same ID. You must have 4 classifications.

ANSWER: to insert multiple records in a table we use INSERT statement

INSERT INTO table_name (column_list)
VALUES (value_list_1), (value_list_2), ... (value_list_n);

(3).Use SQL to create 4 schemas, one for each security classification

SELECT s.name AS schema_name, u.name AS schema_owner FROM sys.schemas s INNER JOIN sys.sysusers u ON u.uid = s.principal_id ORDER BY s.name;

(4).Use SQL to create 4 logins, one for eachsecurity classification

-- Create a login for SQL Server by specifying a server name and a Windows domain account name.  

CREATE LOGIN [<domainName>\<loginName>] FROM WINDOWS;  

(5).Use SQL to create 4 database users, one for each login with a default schema that matches the security classification for the login.

   

CREATE LOGIN Khamal  

WITH PASSWORD = '340$Uuxwp7Mcxo7Khy';  

GO  

-- Creates a database user for the login created above.  

CREATE USER AbolrousHazem FOR LOGIN AbolrousHazem;  

GO

(8).Use SQL to create 4 roles at the database level, one for each security classification

CREATE TABLE Sales(

    UserName khamal(50),

    Country khamal(50),

    Sales INT

)

    

INSERT INTO Sales VALUES ('tred','nepal',10000)

INSERT INTO Sales VALUES ('sam','india',9500)

INSERT INTO Sales VALUES ('Tam','France',9600)

INSERT INTO Sales VALUES ('Fola','Spain',9200)

INSERT INTO Sales VALUES ('Chris','Germany',9000)

(9).Use SQL to assign each user to its specific role

CREATE USER 'dev1'@'localhost' IDENTIFIED BY 'manager'; CREATE USER 'read_user1'@'localhost' IDENTIFIED BY 'assistant manager'; CREATE USER 'read_user2'@'localhost' IDENTIFIED BY 'watch man'; CREATE USER 'rw_user1'@'localhost' IDENTIFIED BY 'clerk';

(10).Use SQL to grant select on the appropriate view to the roles

GRANT <permission> [ ,...n ] ON

[ OBJECT :: ][ schema_name ]. object_name [ ( column [ ,...n ] ) ]  

TO <database_principal> [ ,...n ]

[ WITH GRANT OPTION ]  

[ AS <database_principal> ]  

  

<permission> ::=  

ALL [ PRIVILEGES ] | permission [ ( column [ ,...n ] ) ]  

  

<database_principal> ::=

Database_user

| Database_role

| Application_role

| Database_user_mapped_to_Windows_User

| Database_user_mapped_to_Windows_Group

| Database_user_mapped_to_certificate

| Database_user_mapped_to_asymmetric_key

| Database_user_with_no_login

(12).Use SQL to revoke select on the view from the role

REVOKE [ GRANT OPTION FOR ] <permission> [ ,...n ] ON

[ OBJECT :: ][ schema_name ]. object_name [ ( column [ ,...n ] ) ]  

{ FROM | TO } <database_principal> [ ,...n ]

[ CASCADE ]  

[ AS <database_principal> ]  

  

<permission> ::=  

ALL [ PRIVILEGES ] | permission [ ( column [ ,...n ] ) ]  

  

<database_principal> ::=

Database_user

| Database_role

| Application_role

| Database_user_mapped_to_Windows_User

| Database_user_mapped_to_Windows_Group

| Database_user_mapped_to_certificate

| Database_user_mapped_to_asymmetric_key

| Database_user_with_no_login

(13).Use SQL to remove the users from the role

EXEC sp_droprolemember 'operator', 'John';

(14).Use SQL to drop the users

  DROP USER [ IF EXISTS ] user_name

(15).Use SQL to drop the logins

DROP LOGIN login_name

(16).Use SQL to drop the roles1

  

DROP ROLE [ IF EXISTS ] role_name

(17).Use SQL to drop the views

  

DROP VIEW [ IF EXISTS ] [ schema_name . ] view_name [ ...,n ] [ ; ]

(18).Use SQL to drop the schemas

  

  

DROP SCHEMA [ IF EXISTS ] schema_name

(19).Use SQL to drop the table

DROP TABLE [IF EXIST] table_name