LU09.A01 - SQL- DCL: Preparatory Work

It is high time to mess a litte around with our new SQL powers, don't you think? So, let's try it directly on our Webstorm.

As the database administrator, we want to create a new user and only grant this user the necessary rights to operate the web application, which includes DML operations such as INSERT, UPDATE, DELETE, but not DDL operations such as CREATE or DROP of tables. After all, we don't want the webapplication take control over our database, are we?

• Work type: individual
• Timeframe: 10 Minutes
• Means of aid:
  • only teaching materials, no websearch, no use of ai.
  • Webstorm with connection to the MySQL-DB
• Expected result: Prepare working enviroment, according to the requirements below.

Before we spend elbow grease, we need to prepare our working enviroment. Meaning we need to prepare our computer for the assignments later.

Login as root: Login in from Webstorm to your database as root (sysdba = systemadministrator for the datababase) with your a root-password.

New Database: Create a new database named myDatabase.

CREATE DATABASE myDatabase;

use db: Use that newly created db.

USE myDatabase;

Create a test table: Create a table user with 3 columns of your choice as a test table, and fill it with some test data.

CREATE TABLE user (
  user_id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL
);

INSERT INTO user (username, email) VALUES ('john_doe', 'john.doe@example.com');
INSERT INTO user (username, email) VALUES ('jane_smith', 'jane.smith@example.com');
INSERT INTO user (username, email) VALUES ('michael_brown', 'michael.brown@example.com');
INSERT INTO user (username, email) VALUES ('sarah_johnson', 'sarah.johnson@example.com');
INSERT INTO user (username, email) VALUES ('chris_williams', 'chris.williams@example.com');
INSERT INTO user (username, email) VALUES ('anna_lee', 'anna.lee@example.com');
INSERT INTO user (username, email) VALUES ('david_kim', 'david.kim@example.com');
INSERT INTO user (username, email) VALUES ('laura_clark', 'laura.clark@example.com');
INSERT INTO user (username, email) VALUES ('jake_lewis', 'jake.lewis@example.com');
INSERT INTO user (username, email) VALUES ('emily_martinez', 'emily.martinez@example.com');

Lösung

Volkan Demir