eeee.sql - master - sudz1/sql

eeee.sql
211 строк6 KB
sudz1
create: eeee.sql
30 май 2026, 04:59
30 май 2026, 04:59eeac574
<!-- тема 4 пр3 -->
## 1 Вывести список студентов в возрасте от 18 до 20 лет, отсортированных по фамилии в алфавитном порядке.

SELECT last_name, first_name, age
FROM students
WHERE age BETWEEN 18 AND 20
ORDER BY last_name ASC;


## 2 Показать количество студентов каждого возраста, но только тех возрастов, где количество студентов больше 5. Результат отсортировать по возрасту по убыванию.

SELECT age, COUNT(*) AS student_count
FROM students
GROUP BY age
HAVING COUNT(*) > 5
ORDER BY age DESC;


## 3 Найти все занятия, которые проходят во вторник или четверг, начинаются не раньше 10:00 и заканчиваются не позже 16:00. Результат отсортировать по времени начала.

SELECT day_of_week, 

   ПРАКТИЧЕСКОЕ ЗАДАНИЕ 2
   Управление ролями и правами доступа в PostgreSQL

   ЗАДАНИЕ 1
   Создание пользователя app_user с ограничениями
 

CREATE ROLE app_user
LOGIN
PASSWORD 'secure_pass'
NOSUPERUSER
NOCREATEDB
NOCREATEROLE;


Создается пользователь app_user.
LOGIN позволяет входить в систему.
NOSUPERUSER запрещает полный административный доступ.
NOCREATEDB запрещает создавать базы данных.
NOCREATEROLE запрещает создавать новые роли.

  #ЗАДАНИЕ 2
  Создание групповой роли и пользователей
  

CREATE ROLE dev_team NOLOGIN;

CREATE ROLE alex
LOGIN
PASSWORD 'alex_pass';

CREATE ROLE maria
LOGIN
PASSWORD 'maria_pass';

GRANT dev_team TO alex;
GRANT dev_team TO maria;


#dev_team является групповой ролью и не может входить в систему.
Пользователи alex и maria добавляются в группу dev_team.
Все права, выданные группе, автоматически наследуются её участниками.
*/

SELECT
    roleid::regrole AS group_role,
    member::regrole AS member_name
FROM pg_auth_members
WHERE roleid = 'dev_team'::regrole;



ЗАДАНИЕ 3
Выдача прав группе dev_team


GRANT CONNECT ON DATABASE company TO dev_team;

GRANT USAGE ON SCHEMA public TO dev_team;

GRANT SELECT, INSERT, UPDATE
ON TABLE employees
TO dev_team;

GRANT SELECT, INSERT, UPDATE
ON TABLE departments
TO dev_team;


CONNECT позволяет подключаться к базе company.
USAGE позволяет использовать схему public.
SELECT, INSERT, UPDATE дают право читать,
добавлять и изменять данные в таблицах.
Права автоматически получают alex и maria.




ЗАДАНИЕ 4
 Настройка прав по умолчанию


ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO dev_team;


Все новые таблицы, создаваемые в схеме public,
будут автоматически предоставлять группе dev_team
право на чтение данных (SELECT).


CREATE TABLE test_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

Проверка назначенных прав 
SELECT *
FROM information_schema.role_table_grants
WHERE table_name = 'test_table';


ЗАДАНИЕ 5  Отзыв прав и удаление пользователя


REVOKE INSERT
ON TABLE employees
FROM maria;


У пользователя maria отзывается право
на добавление новых записей в таблицу employees.

DROP OWNED BY maria;

DROP ROLE maria;


DROP OWNED удаляет все объекты и привилегии,
принадлежащие пользователю.
После этого роль maria можно безопасно удалить.

  ЗАДАНИЕ 6
 Создание административной роли


CREATE ROLE db_admin
LOGIN
PASSWORD 'admin_pass'
CREATEDB
CREATEROLE
NOSUPERUSER;

db_admin может создавать базы данных и роли,
но не обладает правами суперпользователя.

SET ROLE db_admin;

CREATE ROLE reporter
LOGIN
PASSWORD 'reporter_pass';

RESET ROLE;

После переключения на роль db_admin
создается новый пользователь reporter.



   ЗАДАНИЕ 7
   Делегирование прав через GRANT OPTION


CREATE ROLE data_analyst
LOGIN
PASSWORD 'analyst_pass';

CREATE ROLE analyst_user
LOGIN
PASSWORD 'user_pass';

GRANT SELECT
ON ALL TABLES IN SCHEMA public
TO data_analyst
WITH GRANT OPTION;


WITH GRANT OPTION позволяет роли data_analyst
не только использовать право SELECT,
но и передавать его другим пользователям.

SET ROLE data_analyst;

GRANT SELECT
ON TABLE sales
TO analyst_user;

RESET ROLE;

Роль data_analyst передает пользователю
analyst_user право чтения таблицы sales.

SELECT *
FROM information_schema.role_table_grants
WHERE grantee = 'analyst_user';


Запрос показывает все права,
которые были выданы пользователю analyst_user.