oracle中用户权限表空间,oracle表空间用户角色权限

1、创建数据表空间

create tablespace dev01

logging

datafile '/u01/app/oracle/oradata/dev01.dbf'

size 100m

autoextend on

next 32m maxsize 2048m

extent management local;

2、创建临时表空间

create temporary tablespace dev01_temp

tempfile '/u01/app/oracle/oradata/dev01_temp.dbf'

size 32m

autoextend on

next 32m maxsize 2048m

extent management local;

3、创建用户

create user dev01 identified by dev01

default tablespace dev01 temporary tablespace dev01_temp;

4、给用户赋权限

grant connect,resource,dba to dev01;

temporary TABLESPACE 已创建。

tablespace DEV01 已创建。

user DEV01 已创建。

grant 成功。

//创建临时表空间

SQL> create temporary tablespace pe20ts_temp tempfile 'C:\app\Administrator\oradata\pe20db\pe20ts_temp01.dbf' size 50m autoextend on;

//创建数据表空间

SQL> create tablespace pe20ts datafile 'C:\app\Administrator\oradata\pe20db\pe20ts01.dbf' size 1g autoextend on;

//创建用户并指定表空间

create user pe20 identified by jupiter default tablespace pe20ts temporary tablespace pe20ts_temp;

//给用户授予权限

grant connect,resource,dba to pe20;

grant execute on dbms_isched to pe20;

grant execute on dbms_scheduler to pe20;

Grant Create Job To pe20;

Grant CREATE ANY TRIGGER to pe20;

// import

imp system/PowerEgg20@PE20DB ignore=y file=pe20_kjj20131212.dmp fromuser=PE20 touser=pe20 log=pe20imp.log

// delete tablespace

DROP TABLESPACE pe20ts INCLUDING CONTENTS AND DATAFILES;

// delete user

drop user pe20 cascade;

查询用户拥有哪里权限：

SQL> select * from dba_role_privs;

SQL> select * from dba_sys_privs;

SQL> select * from role_sys_privs;