oracle表空间与用户创建

-------------------------创建表空间与用户

创建临时表空间


CREATE TEMPORARY TABLESPACE test_temp
TEMPFILE ‘/u01/oracle/product/10.2.0/oradata/orcl/test_temp01.dbf ‘
SIZE 32M
AUTOEXTEND ON
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;

创建用户表空间


CREATE TABLESPACE test_data
LOGGING
DATAFILE ‘/u01/oracle/product/10.2.0/oradata/orcl/my_data01.dbf ‘
SIZE 32M
AUTOEXTEND ON
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;


创建用户并制定表空间


CREATE USER username IDENTIFIED BY password
DEFAULT TABLESPACE TEST_DATA
TEMPORARY TABLESPACE TEST_TEMP;


--------------------用户赋权与查看用户权限

给用户授予权限


GRANT
   CREATE SESSION, CREATE ANY TABLE , CREATE ANY VIEW , CREATE ANY INDEX , CREATE ANY PROCEDURE ,
   ALTER ANY TABLE , ALTER ANY PROCEDURE ,
   DROP ANY TABLE , DROP ANY VIEW , DROP ANY INDEX , DROP ANY PROCEDURE ,
   SELECT ANY TABLE , INSERT ANY TABLE , UPDATE ANY TABLE , DELETE ANY TABLE
   TO username;


将role这个角色授与username,也就是说,使username这个用户可以管理和使用role所拥有的资源

GRANT role TO username;

查看所有用户

SELECT * FROM DBA_USERS;
SELECT * FROM ALL_USERS;
SELECT * FROM USER_USERS;

查看用户系统权限

SELECT * FROM DBA_SYS_PRIVS;
SELECT * FROM USER_SYS_PRIVS;

查看用户对象或角色权限

SELECT * FROM DBA_TAB_PRIVS;
SELECT * FROM ALL_TAB_PRIVS;
SELECT * FROM USER_TAB_PRIVS;

查看所有角色

SELECT * FROM DBA_ROLES;

查看用户或角色所拥有的角色

SELECT * FROM DBA_ROLE_PRIVS;
SELECT * FROM USER_ROLE_PRIVS;



本文出自 “努力奔向前方” 博客,请务必保留此出处http://liucb.blog.51cto.com/3230681/1582387

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。