MYSQL 권한/계정생성

계정생성

create database 데이타베이스명;
insert into user values("localhost",'유저아이디',password('유저패스워드'),'Y','Y','Y','Y','Y','Y','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N',' ', '',' ',' ','0','0','0','0');
insert into db values("localhost",'데이타베이스명','유저아이디','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','N','N','N','N','N','N','N');

commit;

 

 mysql 해당계정만 외부에서 접속할 수 있도록 설정

$ grant all privileges on 데이베이스이름.* to '마이에스큐엘 사용자 계정'@'%' identified by '패스워드' with grant option;

GRANT ALL ON demo.* TO 'root'@'127.0.0.1' IDENTIFIED BY 'root' WITH GRANT OPTION;

GRANT ALL ON demo.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;

UPDATE `user`

SET 

 Select_priv='Y'

, Insert_priv='Y'

, Update_priv='Y'

, Delete_priv='Y'

, Create_priv='Y'

, Drop_priv='Y'

, Reload_priv='Y'

, Shutdown_priv='Y'

, Process_priv='Y'

, File_priv='Y'

, Grant_priv='Y'

, References_priv='Y'

, Index_priv='Y'

, Alter_priv='Y'

, Show_db_priv='Y'

, Super_priv='Y'

, Create_tmp_table_priv='Y'

, Lock_tables_priv='Y'

, Execute_priv='Y'

, Repl_slave_priv='Y'

, Repl_client_priv='Y'

, Create_view_priv='Y'

, Show_view_priv='Y'

, Create_routine_priv='Y'

, Alter_routine_priv='Y'

, Create_user_priv='Y'

, Event_priv='Y'

, Trigger_priv='Y'

, Create_tablespace_priv='Y';

FLUSH PRIVILEGES;

권한/계정 생성 반영.. 재실행 안해도 됨..
$ flush privileges;

select, insert, update, delete, create, drop 권한을 승인
$ grant select, insert, update, delete, create, drop on testdb.* to 'test'@'%' identified by 'testp';
승인한다  testdb데이터베이스와 하위 테이블 to 'test계정(id)@모든곳에서의 접속' identified by ' testp(비밀번호)'



예제)
DB명 - CS
사용자명 - abcde
접속 허용 호스트 - 210.102.217.456
password - aaaaa

mysql> create database dbname;
mysql> grant all privileges on dbname.* to userid@localhost identified by 'passwd';