- Создать БД MovieBase, внутри БД создать таблицу movies (id, moviesNimi, moviesYear, movieDir и movieCost).
- Заполнить таблицу 7 записями.
- Создать пользователя DirectorNimi, пароль director с правами на обновление данных в полях movieDir и movieCost + 1 своя привелегия.
- Ход выполнения и проверку записать в конспект.
CREATE DATABASE DB_MovieBase
use DB_MovieBase;
CREATE TABLE movies (
id int PRIMARY KEY IDENTITY(1,1),
moviesNimi varchar(100),
moviesYear int,
movieDir varchar(100),
movieCost int)
INSERT INTO movies(moviesNimi, moviesYear, movieDir, movieCost)
VALUES ('Terminator', 1984, 'James Cameron', 6000000 );
INSERT INTO movies(moviesNimi, moviesYear, movieDir, movieCost)
VALUES ('Terminator 2', 1991, 'James Cameron', 10000000 );
INSERT INTO movies(moviesNimi, moviesYear, movieDir, movieCost)
VALUES ('RoboCop', 1987, 'Paul Verhoeven', 53000000 );
INSERT INTO movies(moviesNimi, moviesYear, movieDir, movieCost)
VALUES ('Tenet', 2020, 'Chrostopher Nolan', 205000000 );
INSERT INTO movies(moviesNimi, moviesYear, movieDir, movieCost)
VALUES ('Harry Potter and the Sorcerers Stone', 2001, 'Chris Columbus', 125000000 );
INSERT INTO movies(moviesNimi, moviesYear, movieDir, movieCost)
VALUES ('Harry Potter 2', 2002, 'Chris Columbus', 10000000 );
INSERT INTO movies(moviesNimi, moviesYear, movieDir, movieCost)
VALUES ('Harry Potter and the Prisoner of Azkaban', 2004, 'Alfonso Cuaron', 13000000 );
SELECT * FROM movies
Security— Login—New Login
Roles—Database Roles—New Database roles
GRANT SELECT to db_updateDirector
grant update on movies (MovieCost, MovieDir)
to DirectorDarja
Kontroll:
use DB_MovieBase
UPDATE movies
SET movieDir = 'Chris Columbus', movieCost = movieCost + 1
where id=1
select * from movies
XAMPP
GRANT SELECT (`movieDir`, `movieCost`), UPDATE (`movieDir`, `movieCost`) ON `moviebase`.`movies` TO 'DirectorDarja'@'localhost';
