Oracle创建用户、表空间并设置权限

时间:2021-02-19 03:40:56

代码:

//dba账户登录
sqlplus

请输入用户名:dpp_data
as sysdba
请输入口令:dpp_data

//创建账号
create user techrpt_data identified by techrpt_data;

//创建临时表空间
create temporary tablespace TECHRPT_DATA_TEMP
tempfile
'D:\ORACLE\ORADATA\ORCL\TECHRPT_DATA_TEMP.dbf'
size 32m
autoextend
on
next 32m maxsize 2048m
extent management local;

//将临时表空间分配给用户
alter user techrpt_data temporary tablespace TECHRPT_DATA_TEMP;

//创建表空间
create tablespace TECHRPT_DATA
logging
datafile
'D:\ORACLE\ORADATA\ORCL\TECHRPT_DATA.ora'
size 50m
autoextend
on
next 50m maxsize 2048m
extent management local;

//将表空间分配给用户
alter user techrpt_data default tablespace TECHRPT_DATA;

//授权
grant dba to techrpt_data;
grant connect,resource to techrpt_data;
grant create session to techrpt_data;
grant create any sequence to techrpt_data;
grant create any table to techrpt_data;
grant delete any table to techrpt_data;
grant insert any table to techrpt_data;
grant select any table to techrpt_data;
grant update any table to techrpt_data;
grant unlimited tablespace to techrpt_data;
grant execute any procedure to techrpt_data;
grant create any view to techrpt_data;

grant select on v_$statname to techrpt_data;
grant select on v_$sesstat to techrpt_data;
grant select on v_$session to techrpt_data;
grant select on v_$mystat to techrpt_data;

//提交
commit;

//退出
quit;

//删除用户
drop user techrpt_data cascade;

//删除表空间
drop tablespace techrpt_data including contents and datafiles;

//修改密码
alter
user system identified by system;