常见数据库的创建用户和授权语句

一、人大金仓Kingbase数据库

1.创建只读账户

revoke create on schema public from public; create user readertest with password '123456'; grant usage on schema public to readertest; grant select on all tables in schema public to readertest; alter default privileges in schema public grant select on tables to  readertest;

2.创建普通用户

Create user xx connection limit - 1 password 'xx'; alter user xx SUPERUSER; alter user xx CREATEDB; alter user xx CREATEROLE; alter user xx LOGIN;

二、Oracle数据库 

/*第1步：创建临时表空间  */ create temporary tablespace TEST_TEMP tempfile 'E:\app\TEST_TEMP.dbf' size 50m autoextend on next 50m maxsize 512m extent management local;   /*第2步：创建数据表空间  */ create tablespace TEST_DATA logging datafile 'E:\app\TEST_DATA.dbf' size 50m autoextend on next 50m maxsize 1024m extent management local;   /*第3步：创建用户并指定表空间  */ create user TEST identified by TEST default tablespace TEST_DATA temporary tablespace TEST_TEMP;   /*第4步：给用户授予权限  */ grant connect,resource,dba to TEST;

三、MySQL数据库 

create user 'test'@'%'identified by 'test@2024'; grant select on *.* to 'test'@'%'; flush privileges;

四、clickhouse数据库

-- 创建用户 CREATE USER myuser IDENTIFIED BY 'mypassword'; -- 授予全部数据库的读写权限 GRANT ALL ON *.* TO myuser; -- 只授予特定数据库的读写权限 GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.* TO myuser;

 五、gbase数据库

-- 创建新用户 CREATE USER 'newuser'@'%' IDENTIFIED BY 'password'; -- 授权新用户在特定数据库上的特定权限 GRANT SELECT, INSERT, UPDATE ON mydatabase.* TO 'newuser'@'%'; -- 刷新权限，使授权见效 FLUSH PRIVILEGES;

六、达梦数据库

--创建用户 create user “TEST” identified by “password”; --授权 grant “PUBLIC”,“RESOURCE”,“SOI”,“VTI” to “TEST”; –授权查询其他模式下的全部表 DECLARE SQLSTMT STRING; CURSOR CUR FOR SELECT ID,NAME FROM SYSOBJECTS WHERE TYPE$ = ‘SCHOBJ’ AND SUBTYPE$ IN (‘STAB’,‘UTAB’) AND (PID=-1 OR PID=0) AND SCHID=(SELECT ID FROM SYSOBJECTS WHERE TYPE$=‘SCH’ AND NAME=‘模式名’ ); TYPE MYREC IS CUR%ROWTYPE; REC_V MYREC; BEGIN FOR REC_V IN CUR LOOP SQLSTMT = ‘grant SELECT ON “模式名”."’|| REC_V.NAME || ‘" TO “TEST”;’; EXECUTE IMMEDIATE SQLSTMT; END LOOP; COMMIT; END;

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。