不同数据库创建用户,数据库的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
标签:语句,grant,xxxdata,数据库,xxx,someinfo,SQL,tablespace,create
From: https://www.cnblogs.com/jinanxiaolaohu/p/17804599.html