Oracle创建用户、表空间、视图并赋权限

时间:2021-05-01 15:30:34

--表空间dcuser
create tablespace dcuser(表空间名) datafile '&1\dcuser.dbf(这里创建和表空间同名的dbf文件)' size 100m autoextend on next 10m;

这里是&1会在执行的时候,需要你自己填的表空间文件存放地址,一般放在数据库的实例名(orcl)下,例如:E:\app\xxx\oradata\orcl

--用户dcuser
create user dcuser identified by 123 default tablespace dcuser;

--赋登录权限
grant connect to dcuser;

--创建视图

CREATE OR REPLACE VIEW View_Axis_Employee

AS

select Employee_Name,

Employee_Account,

Employee_ICCard,

Dept_Name,

User_ID

from (select

t.name as Employee_Name,

t.no as Employee_Account,

t3.ic_no as Employee_ICCard,

t2.dept_name as Dept_Name,

t.id as User_ID

from sys_user t

left join sys_user_dept_position t1

on t.id = t1.user_id

left join sys_dept_v t2

on t2.id = t1.dept_id

left join sys_user_extend t3

on t3.sys_user_id = t.id);

--给用户dcuser查询权限

grant select on mes614.View_Axis_Employee to dcuser;