그룹 관리(Role) / 제약 조건
기본 구성
use mysql;
select user, host from user;
Ex. 사용자 수정
SQL> use mysql;
SQL> update user set host='192.168.1.1' where user='rot' and host='localhost';
SQL> flush prvileges;
SQL> exit
HostPC> mysql -h 192.168.1.x -u root -p 접근 확인하기
Host PC에서 잘 접근이 되는 것을 확인할 수 있다.
Ex. 01
MySQL 서버 : 192.168.1.X, Host’PC 클라이언트 : 192.168.1.1
MySQL 서버
SQL> create database db01;
SQL> use db01;
SQL> create table t1(id int);
SQL> insert into t1 values(1);
SQL> insert into t1 values(2);
SQL> create database db02;
SQL> use db02;
SQL> create table t2(id int);
SQL> insert into t2 values(3);
SQL> insert into t2 values(4);
SQL> select * from t2;
- 사용자, 퍼미션 주기
SQL> create user user1@'%' identified by '1';
SQL> create user user2@'%' identified by '2';
SQL> grant select,update,delete,insert on db01.t1 to user1@'%';
SQL> grant select on db01.t1 to user2@'%';
SQL> grant select,update,delete,insert on db02.t2 to user2@'%';
SQL> grant select on db02.t2 to user1@'%';
SQL> flush privileges;
결과
- Host PC에서 확인
mysql -u user1 -p -h 192.168.1.X -> show grants;
mysql -u user2 -p -h 192.168.1.X -> show grants;
Role
권한의 집합
- 많은 사용자에게 권한의 집합을 할당
- 일반적으로 같은 권한들을 많은 사용자에게 할당하기 위해, grant와 revoke 권한을 각 사용자마다 권한을 변경하는 것은 효율적이지 않다.
Step 01. ROLE 생성
Step 02. ROLE에 퍼미션 할당
Step 03. 사용자에게 ROLE 할당
Ex. 01
create database db01;
use db01;
create table t1(id int, name varchar(30));
insert into t1 values(1, '홍길동'), (2, '서길동');
select * from t1;
- role 생성 (기본값 %)
create role role1;
create role role2;
create role role3;
- role에 퍼미션 할당
grant all on db01.* to role1;
grant select on db01.* to role2;
grant select,update,delete on db01.* to role3;
drop user user1;
drop user user2;
- 사용자 생성 및 role 할당
create user user1@localhost identified by '1234';
create user user2@localhost identified by '1234';
create user user3@localhost identified by '1234';
grant role1 to user1@localhost;
grant role2 to user2@localhost;
grant role3 to user3@localhost;
확인 1.
mysql -u user1 -p
select current_role();
+----------------+
| current_role() |
+----------------+
| NONE |
+----------------+
set role all; -- role 활성화
select current_role();
+----------------+
| current_role() |
+----------------+
| `role1`@`%` |
+----------------+
- role 활성화 후 - user1
mysql -u user1 -p
use db01;
select * from t1; -- 성공
insert into t1 values(3, '남길동'); -- 성공
update t1 set name='이순신' where id=1; -- 성공
delete from t1 where id=1; -- 성공
- role 활성화 후 - user2
mysql -u user2 -p
set role all;
select current_role();
use db01;
select * from t1; -- 성공
insert into t1 values(3,'남길동'); -- 실패
update t1 set name='이순신' where id=1; -- 실패
delete from t1 where id = 1; -- 실패
- role 활성화 후 - user3
mysql -u user3 -p
set role all;
select current_role();
use db01;
select * from t1; -- 성공
insert into t1 values(3,'남길동'); -- 실패
update t1 set name='이순신' where id=1; -- 성공
delete from t1 where id = 1; -- 성공
확인
mysql -u root -p
- role에 퍼미션 취소
revoke all on db01.* from role1;
revoke select on db01.* from role2;
revoke select,update,delete on db01.* from role3;
확인 - role 삭제
drop role role1;
drop role role2;
drop role role3;
Ex. 02
- DB03 -> t1
| id | name | addr |
|---|---|---|
| 1 | 홍길동 | 부산 |
| 2 | 서길동 | 서울 |
| 3 | 남길동 | 광주 |
| 4 | 북길동 | 인천 |
- admins(role) : all (user7)
- sales(role) : select (user1, user2)
- humans(role) : select insert update delete (user3, user4)
제약 조건
- 데이터의 무결성을 지키기 위해, 데이터를 입력 받을 때 검사 규칙을 의미함
- 제약 조건은 CREATE문으로 테이블 생성할 때나, ALTER문으로 컬럼을 추가할 때도 설정할 수 있다.
- NOT NULL
- UNIQUE
ex. 1,2,3, 1(X), NULL, NULL(O)
주의) MSSQL은 NULL 한 번만 가능
- PRIMARY KEY
ex. NULL(X), 1,2,3, 1(X)
- FOREIGN KEY
- DEFAULT
- CHECK
제약 조건 사용법
- 제약 조건 확인
select * from information_schema.table_constraints where table_name = '테이블명';
desc '테이블명';
- 제약 조건 추가 및 제거하기
create table t1(id int, name varchar(30));
alter table t1 add primary key(id); --primary key 제약 추가
select * from information_schema.table_constraints where table_name = 't1';
alter table t1 drop primary key; --primary key 제약 제거
select * from information_schema.table_constraints where table_name = 't1';
alter table t1 modify id int not null; --not null 제약 추가
select * from information_schema.table_constraints where table_name = 't1';
alter table t1 add nuique(id); --unique 제약 추가
select * from information_schema.table_constraints where table_name = 't1';
alter table t1 drop constraint 제약조건이름; --unique 제약 제거
- 연습
drop table t1;
create table t1(id int primary key, job varhchar(30) unique);
create table t2(id int primary key, job varchar(30));
alter table t2 add foreign key(job) references t1(job); -- foreigh key 제약 추가
insert into t1 values(1,'음악');
insert into t2 values(2,'음악');
insert into t2 values(2,'미술'); --에러
select * from information_schema.table_constraints where table_name = 't2';
alter table t2 drop constraint 제약조건이름; -- foreign key 제약 제거
insert into t2 values(2,'미술');