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

revoke create on schema public from public;

createuser readertest withpassword'123456';

grantusageonschema public to readertest;

grantselectonalltablesinschema public to readertest;

alterdefaultprivilegesinschema public grantselectontablesto  readertest;

Create user xx connection limit - 1 password 'xx';

alter user xx SUPERUSER;

alter user xx CREATEDB;

alter user xx CREATEROLE;

alter user xx LOGIN;

/*第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;

create user 'test'@'%'identified by 'test@2024';

grant select on *.* to 'test'@'%';

flush privileges;

-- 创建用户

CREATE USER myuser IDENTIFIED BY 'mypassword';

-- 授予所有数据库的读写权限

GRANT ALL ON *.* TO myuser;

-- 只授予特定数据库的读写权限

GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.* TO myuser;

-- 创建新用户

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;