不同数据库创建用户,数据库的SQL语句整理

不同数据库创建用户,数据库的SQL语句整理

MySQL

mysql -uroot -p # 输入密码登录数据库

CREATE DATABASE IF NOT EXISTS xxxdata_someinfo DEFAULT CHARSET utf8mb4 ;
create user 'xxx_someinfo'@'%' identified by 'Testsomepassword';
grant all privileges on xxxdata_someinfo.* to 'xxx_someinfo'@'%' ;
flush privileges ;

Oracle

su - oracle
sqlplus / as sysdba # 登录数据库控制台

create tablespace xxxdata_someinfo datafile '/u01/app/oracle/oradata/ora12c/xxxdata_someinfo.dbf' size 1024m autoextend on next 1024m ; 
create user xxx_someinfo default tablespace xxxdata_someinfo identified by Orasomepassword; 
grant resource,connect,create any table, create any view,create any sequence,unlimited tablespace  to xxx_someinfo ;

PostgreSQL

mkdir -p /data/lib/pgsql/12/xxxdata_someinfo 
# 创建目录

psql  
# 登录数据库

CREATE ROLE "xxx_someinfo" WITH ENCRYPTED PASSWORD 'Testsomepassword';
ALTER ROLE "xxx_someinfo" WITH LOGIN;
create tablespace xxxdata_someinfo owner xxx_someinfo location '/data/lib/pgsql/12/xxxdata_someinfo';
CREATE DATABASE "xxxdata_someinfo"
WITH
  OWNER = "xxx_someinfo"
  TEMPLATE = "template0"
  ENCODING = 'UTF8'
  TABLESPACE = "xxxdata_someinfo" ;

\c xxxdata_someinfo ;
create schema xxx_someinfo ;  

grant all on database xxxdata_someinfo to xxx_someinfo with grant option;
grant all on tablespace xxxdata_someinfo to xxx_someinfo ;
# grant all on schema xxx_someinfo to xxx_someinfo ; 
# PG数据库貌似不需要执行针对schema的授权, 注意schema 跟role应该是同名. 跟数据库和表空间不一样. 
\c postgres

Kingbase

su - kingbase 

mkdir -p /opt/Kingbase/xxxdata_someinfo

ksql -U system -d kingbase # 输入密码 

CREATE ROLE "xxx_someinfo" WITH ENCRYPTED PASSWORD 'Testsomepassword';
ALTER ROLE "xxx_someinfo" WITH LOGIN;
create tablespace xxxdata_someinfo owner xxx_someinfo location '/opt/Kingbase/xxxdata_someinfo';
CREATE DATABASE "xxxdata_someinfo"
WITH
  OWNER = "xxx_someinfo"
  TEMPLATE = "template0"
  ENCODING = 'UTF8'
  TABLESPACE = "xxxdata_someinfo" ;

\c xxxdata_someinfo ;
create schema xxx_someinfo ;  

grant all on database xxxdata_someinfo to xxx_someinfo with grant option;
grant all on tablespace xxxdata_someinfo to xxx_someinfo ;
grant all on schema xxx_someinfo to xxx_someinfo ;
\c kingbase

OpenGauss

su - omm 
mkdir -p /data/openGauss/data/xxxdata_someinfo ;

gsql -U root -d postgres  (Testsomepassword?!)

CREATE ROLE "xxx_someinfo" WITH ENCRYPTED PASSWORD 'Gssomepassword';
ALTER ROLE "xxx_someinfo" WITH LOGIN;
create tablespace xxxdata_someinfo owner xxx_someinfo location '/data/openGauss/data/xxxdata_someinfo';
CREATE DATABASE "xxxdata_someinfo"
WITH
  OWNER = "xxx_someinfo"
  TEMPLATE = "template0"
  ENCODING = 'UTF8'
  TABLESPACE = "xxxdata_someinfo" ;
 
\c xxxdata_someinfo ;
需要输入密码

create schema "xxx_someinfo" ;
grant all on database xxxdata_someinfo to xxx_someinfo with grant option;
grant all on tablespace xxxdata_someinfo to xxx_someinfo ;
grant all on schema xxx_someinfo to xxx_someinfo;

DaMeng 达梦数据库

su - dmdba
disql SYSDBA/SYSDBA:5236

create tablespace xxxdata_someinfo datafile '/dm8/data/DMDB/xxxdata_someinfo.dbf' size 256;

create user xxx_someinfo identified by Dmsomepassword default tablespace xxxdata_someinfo ;

grant dba,resource to xxx_someinfo ;

Oracle数据库备份恢复

select username,default_tablespace from dba_users;
# 查询表空间信息
# 备份脚本
expdp system/Oracle12#@xxx.xxx.xxx.90/ora12source schemas=xxx_someinfo directory=dir dumpfile=xxx_someinfo.dump logfile=somepassword.log   exclude=statistics

# 创建表空间与用户权限. 
create tablespace xxxDATA_someinfo datafile '/u01/app/oracle/oradata/ora12c/xxxDATA_someinfo.dbf' size 1024m autoextend on next 1024m ; 
create user xxx_someinfo default tablespace xxxDATA_someinfo identified by Orasomepassword;
grant resource,connect,create any table, create any view,create any sequence,unlimited tablespace  to xxx_someinfo ;
#导入脚本 特殊字符密码需要用单引号括起来.
impdp system/'Testsomepassword?!'@xxx.xxx.xxx.210/ora12c schemas=xxx_someinfo directory=dir dumpfile=xxx_someinfo.dump logfile=somepassword.log

For循环批量创建数据库与用户-TiDB为例

for i in  aa bb cc dd  ; do 
echo  "CREATE DATABASE IF NOT EXISTS xxxdata_${i}_sufix DEFAULT CHARSET utf8mb4 ;
create user 'xxx_${i}_sufix'@'%' identified by 'Testsomepassword';
grant all privileges on xxxdata_${i}_sufix.* to 'xxx_${i}_sufix'@'%' ;
flush privileges;
"; done >mysqldb.sql 

for i in   aa bb cc dd   ; do 
echo  "CREATE DATABASE IF NOT EXISTS xxxdata_${i}_dev DEFAULT CHARSET utf8mb4 ;
create user 'xxx_${i}_dev'@'%' identified by 'Testsomepassword';
grant all privileges on xxxdata_${i}_dev.* to 'xxx_${i}_dev'@'%' ;
flush privileges;
"; done >mysqldb.sql 

# 删除数据库

for i in   aa bb cc dd  ; do
echo " 
drop DATABASE IF  EXISTS xxxdata_${i}_sufix ; 
drop user IF  EXISTS 'xxx_${i}_sufix'@'%' ;
"; done >mysqldb.sql