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

runqu

已于 2024-03-19 16:20:17 修改

阅读量994

点赞数 19

分类专栏：数据库文章标签：数据库 oracle mysql sql

于 2024-03-19 14:10:13 首次发布

本文链接：https://blog.csdn.net/runqu/article/details/136840687

版权

数据库专栏收录该内容

11 篇文章 0 订阅

订阅专栏

本文详细介绍了如何在人大金仓Kingbase、Oracle、MySQL、ClickHouse、GBase和达梦数据库中创建只读账户、普通用户以及设置表空间，同时展示了不同数据库系统的权限管理方法。

摘要由CSDN通过智能技术生成

一、人大金仓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;