Chap.08. DCL and TCL

CoLab

https://colab.research.google.com/drive/1wts3NIBojdz2mBUfBott6vAA0kqfsMq2

https://bit.ly/3qoXrs3

DCL

show grants;

# MySQL Workbench에서 실행하면 보기 편함
select * from mysql.user;

select host, user from mysql.user;

select host, user, select_priv from mysql.user;

select host, user, create_user_priv from mysql.user;
select host, user, super_priv, create_user_priv, grant_priv, show_view_priv from mysql.user;

#########################
create user backend identified by 'mysql3456';
show grants for backend;
select host, user, super_priv, create_user_priv, grant_priv, show_view_priv from mysql.user;



drop user backend;

select host, user, super_priv, create_user_priv, grant_priv, show_view_priv from mysql.user;

show grants for backend;
#########################

# create user backend@localhost identified by 'mysql3456';

# select host, user, super_priv, create_user_priv, grant_priv, show_view_priv from mysql.user;

# show grants for backend;
# show grants for backend@*;

# drop user backend@localhost;

#########################

quit

# Shell에서 실행
# mysql -u backend -p

show grants;

#########################

grant all privileges on  *.* to root@'%' identified by 'mysql1234';
#delete from mysql.user where host="localhost" and user="root";
flush privileges;
select host, user, super_priv, create_user_priv, grant_priv, show_view_priv from mysql.user;
quit
# open /etc/mysql/my.cnf
# 이 파일이 다른 파일 include

# vim /etc/mysql/mysql.conf.d/mysqld.cnf

#########################

rename user backend to webroot;
select host, user, super_priv, create_user_priv, grant_priv, show_view_priv from mysql.user;


drop user webroot;
select host, user, super_priv, create_user_priv, grant_priv, show_view_priv from mysql.user;


grant all privileges on  *.* to root@localhost identified by 'mysql1234';

show databases;
grant select on songdb.song to backend;

#################

show databases;
use songdb;
select * from song;

INSERT INTO song VALUES ('임창정', '소주한잔');
select * from song;

grant insert on songdb.song to backend;
show grants for backend;


use songdb;
select * from song;
INSERT INTO song VALUES ('임창정', '소주한잔');
select * from song;

### 
revoke all privileges, grant option from backend;
show grants for backend;

TCL

DROP DATABASE IF EXISTS songdb;
CREATE DATABASE songdb
	DEFAULT CHARACTER SET utf8
	DEFAULT COLLATE utf8_general_ci;
	
use songdb;

CREATE TABLE song (
	singer varchar(20),
	title varchar(20),
	PRIMARY KEY(singer, title)
) ENGINE=INNODB
	DEFAULT CHARACTER SET utf8
	DEFAULT COLLATE utf8_general_ci;
	
################################

INSERT INTO song VALUES ('나훈아', '테스형');
INSERT INTO song VALUES ('장범준', '잠이 오질 않네요');
INSERT INTO song VALUES ('방탄소년단', 'Life Goes On');
INSERT INTO song VALUES ('지코', '아무노래');
SELECT * FROM song;
################################

select * from song;

start transaction;
INSERT INTO song VALUES ('임재현', '사랑에 연습이 있었다면');
select * from song;

savepoint Ins1;

update song set title = '조금 취했어' where singer = '임재현';
select * from song;

savepoint Upd1;

delete from song where singer = '나훈아';
select * from song;

savepoint Del1;

INSERT INTO song VALUES ('임창정', '힘든 건 사랑이 아니다');
select * from song;

savepoint Ins2;

rollback;
select * from song;