Oracle创建表空间、创建用户以及授权、查看权限

落日映苍穹つ 2021-12-07 15:19 1205阅读 0赞

[Oracle创建表空间、创建用户以及授权、查看权限][Oracle]

创建临时表空间

![ExpandedBlockStart.gif][]   
![None.gif][]CREATETEMPORARY TABLESPACE test\_temp  
![None.gif][]TEMPFILE 'C:\\oracle\\product\\10.1.0\\oradata\\orcl\\test\_temp01.dbf'  
![None.gif][]SIZE 32M  
![None.gif][]AUTOEXTEND ON  
![None.gif][]NEXT 32M MAXSIZE 2048M  
![None.gif][]EXTENT MANAGEMENT LOCAL;

创建用户表空间

![ExpandedBlockStart.gif][]   
![None.gif][]CREATE TABLESPACE test\_data  
![None.gif][]LOGGING  
![None.gif][]DATAFILE 'C:\\ORACLE\\PRODUCT\\10.1.0\\ORADATA\\ORCL\\TEST\_DATA01.DBF'  
![None.gif][]SIZE 32M  
![None.gif][]AUTOEXTEND ON  
![None.gif][]NEXT 32M MAXSIZE 2048M  
![None.gif][]EXTENT MANAGEMENT LOCAL;

创建用户并制定表空间

![ExpandedBlockStart.gif][]   
![None.gif][]CREATEUSER username IDENTIFIED BY password  
![None.gif][]DEFAULT TABLESPACE TEST\_DATA  
![None.gif][]TEMPORARY TABLESPACE TEST\_TEMP;

给用户授予权限

![ExpandedBlockStart.gif][]   
![None.gif][]GRANT  
![None.gif][]　　CREATE SESSION, CREATEANYTABLE, CREATEANYVIEW ,CREATEANYINDEX, CREATEANYPROCEDURE,  
![None.gif][]　　ALTERANYTABLE, ALTERANYPROCEDURE,  
![None.gif][]　　DROPANYTABLE, DROPANYVIEW, DROPANYINDEX, DROPANYPROCEDURE,  
![None.gif][]　　SELECTANYTABLE, INSERTANYTABLE, UPDATEANYTABLE, DELETEANYTABLE  
![None.gif][]　　TO username;

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

![None.gif][] GRANT  role  TO  username;

\-----------------------------------------------查看用户权限---------------------------------------------------------  
  
查看所有用户

![ExpandedBlockStart.gif][]   
![None.gif][]SELECT\*FROM DBA\_USERS;  
![None.gif][]SELECT\*FROM ALL\_USERS;  
![None.gif][]SELECT\*FROM USER\_USERS;

查看用户系统权限

![ExpandedBlockStart.gif][]   
![None.gif][]SELECT\*FROM DBA\_SYS\_PRIVS;  
![None.gif][]SELECT\*FROM USER\_SYS\_PRIVS;

查看用户对象或角色权限

![ExpandedBlockStart.gif][]   
![None.gif][]SELECT\*FROM DBA\_TAB\_PRIVS;  
![None.gif][]SELECT\*FROM ALL\_TAB\_PRIVS;  
![None.gif][]SELECT\*FROM USER\_TAB\_PRIVS;

查看所有角色

![ExpandedBlockStart.gif][]   
![None.gif][]SELECT\*FROM DBA\_ROLES;

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

![ExpandedBlockStart.gif][]   
![None.gif][]SELECT\*FROM DBA\_ROLE\_PRIVS;  
![None.gif][]SELECT\*FROM USER\_ROLE\_PRIVS;

\-------遇到**no privileges on tablespace 'tablespace '**

alter  user  userquota 10M \[ unlimited \]  on  tablespace;

## Oracle新建用户、角色，授权，建表空间 ##

oracle[数据库][Link 1]的权限[系统][Link 2]分为系统权限与对象权限。系统权限( database system privilege )可以让用户执行特定的命令集。例如，create table权限允许用户创建表，grant any privilege 权限允许用户授予任何系统权限。对象权限( database object privilege )可以让用户能够对各个对象进行某些操作。例如delete权限允许用户删除表或视图的行，select权限允许用户通过select从表、视图、序列(sequences)或快照(snapshots)中查询信息。

每个oracle用户都有一个名字和口令,并拥有一些由其创建的表、视图和其他资源。oracle角色(role)就是一组权限(privilege)(或者是每个用户根据其状态和条件所需的访问类型)。用户可以给角色授予或赋予指定的权限，然后将角色赋给相应的用户。一个用户也可以直接给其他用户授权。

### 　　一、创建用户    ###

oracle内部有两个建好的用户：system和sys。用户可直接登录到system用户以创建其他用户，因为system具有创建别 的用户的 权限。 在安装oracle时，用户或系统管理员首先可以为自己建立一个用户。例 如 ：

Sql代码

create user user01 identified by u01;

该命令还可以用来设置其他权限，详细情况参见自学资料。要改变一个口令，可以使用alter user命令：

alter user user01 identified by usr01;

现在user01的口令已由“u01”改为“usr01”。

除了alter user命令以外，用户还可以使用password命令。如果使用password命令，用户输入的新口令将不在屏幕上显示。有dba特权的用户可以通过password命令改变任何其他用户的口令;其他用户只能改变自己的口令。

当用户输入password命令时，系统将提示用户输入旧口令和新口令，如下所示：

password
    　　changing password for user01
    　　old password:
    　　new password:
    　　retype new password:

当成功地修改了口令时，用户会得到如下的反馈：

password changed

### 　　二 、删除用户    ###

删除用户，可以使用drop user命令，如下所示：

drop user user01;

如果用户拥有对象，则不能直接删除，否则将返回一个错误值。指定关键字cascade，可删除用户所有的对象，然后再删除用户。下面的例子用来删除用户与其对象：

drop user user01 cascade;

### 　　三 、3种标准角色    ###

qracle为了兼容以前的版本，提供了三种标准的角色(role)：connect、resource和dba。

#### 　　1. connect role(连接角色)    ####

临时用户，特别是那些不需要建表的用户，通常只赋予他们connectrole。connect是使用oracle的简单权限，这种权限只有在对其他用户的表有访问权时，包括select、insert、update和delete等，才会变得有意义。拥有connect role的用户还能够创建表、视图、序列(sequence)、簇(cluster)、同义词(synonym )、会话(session)和与其他数据库的链(link)。

#### 　　2. resource role(资源角色)    ####

更可靠和正式的数据库用户可以授予resource role。resource提供给用户另外的权限以创建他们自己的表、序列、过程(procedure)、触发器(trigger)、索引(index)和簇(cluster)。

#### 　　3. dba role(数据库管理员角色)    ####

dba role拥有所有的系统权限----包括无限制的空间限额和给其他用户授予各种权限的能力。system由dba用户拥有。下面介绍一些dba经常使用的典型权限。

(1)grant(授权)命令

下面对刚才创建的用户user01授权，命令如下：

grant connect, resource to user01;

(2)revoke(撤消)权限

已授予的权限可以撤消。例如撤消(1)中的授权，命令如下：

revoke connect, resource from user01;

一个具有dba角色的用户可以撤消任何别的用户甚至别的dba的connect、resource 和dba的其他权限。当然，这样是很危险的，因此，除非真正需要，dba权限不应随便授予那些不是很重要的一般用户。 撤消一个用户的所有权限，并不意味着从oracle中删除了这个用户， 也不会破坏用户创建的任何表;只是简单禁止其对这些表的访问。其他要访问这些表的用户可以象以前那样地访问这些表。

### 　　四、创建角色    ###

除了前面讲到的三种系统角色----connect、resource和dba，用户还可以在oracle创建自己的role。用户创建的role可以由表或系统权限或两者的组合构成。为了创建role，用户必须具有create role系统权限。下面给出一个create role命令的实例：

create role student;

这条命令创建了一个名为student的role。

一旦创建了一个role，用户就可以给他授权。给role授权的grant命令的语法与对对用户的语法相同。在给role授权时，在grant命令的to子句中要使用role的名称，如下所示：

grant select on class to student;

现在，拥有student 角色的所有用户都具有对class 表的select权限。

### 　　五、删除角色    ###

要删除角色，可以使用drop role命令，如下所示：

drop role student;

指定的role连同与之相关的权限将从数据库中全部删除。

### 六、删除表的注意事项 ###

在删除一个表中的全部数据时，须使用

truncate table 表名

因为用drop table，delete \* from 表名时，tablespace表空间该表的占用空间并未释放，反复几次drop，delete操作后，该tablespace上百兆的空间就被耗光了。

## oracle 查看 用户，用户权限，用户表空间，用户默认表空间 ##

1.查看用户和默认表空间的关系。

select username,default\_tablespace from dba\_users;

2.查看当前用户的表：

select table\_name from user\_tables;

3.查看所有用户的表名：

select table\_name from all\_tables;

4.查看所有表名（其中包括系统表）

select table\_name from all\_tables;

5.查看所有的表:

select \* from tab/dba\_tables/dba\_objects/cat;

下面介绍 [Oracle][Oracle 1]查询用户表空间

◆Oracle查询用户表空间：select \* from user\_all\_tables

◆Oracle查询所有函数和储存过程：select \* from user\_source

◆Oracle查询所有用户：select \* from all\_users.select \* from dba\_users

◆Oracle查看当前用户连接：select \* from v$Session

◆Oracle查看当前用户权限：select \* from session\_privs

◆Oracle查看用户表空间使用情况：

1.select a.file\_id "FileNo",a.tablespace\_name "Tablespace\_name", a.bytes "Bytes",a.bytes-

sum(nvl(b.bytes,0)) "Used", sum(nvl(b.bytes,0)) "Free", sum(nvl(b.bytes,0))/a.bytes\*100 "%free"

from dba\_data\_files a, dba\_free\_space b where a.file\_id=b.file\_id(+) group by a.tablespace\_name ,

a.file\_id,a.bytes order by a.tablespace\_name;

1.查看所有用户：

select \* from dba\_user;

select \* from all\_users;

select \* from user\_users;

2.查看用户系统权限：

select \* from dba\_sys\_privs;

select \* from all\_sys\_privs;

select \* from user\_sys\_privs;

3.查看用户对象权限：

select \* from dba\_tab\_privs;

select \* from all\_tab\_privs;

select \* from user\_tab\_privs;

4.查看所有角色：

select \* from dba\_roles;

5.查看用户所拥有的角色：

select \* from dba\_role\_privs;

select \* from user\_role\_privs;

6.查看角色所拥有的权限:

select \* from role\_sys\_privs;

select \* from role\_tab\_privs;

7.查看所有系统权限

select \* from system\_privilege\_map;

8.查看所有对象权限

select \* from table\_privilege\_map;

**关于Oracle用户表空间权限提问**：在SYS用户下建立用户A,默认的表空间为A1,临时表空间为A2

然后建立表空间A3,怎样让用户A具有在A3里建立表的权限?前提是不赋予A CREATE ANY TABLE 和 DBA权限(即用户A可使用的表空间为A1,A2,A3).

当A3换成SYS默认的表空间SYSTEM后你们所回答的方法还可行吗?

对了再问下oracle里的TABS表有什么用?

**关于Oracle用户表空间权限回答：**

问题一

只要将a3表空间的限额分配给用户a就可以了,只要在sys下执行

SQL>ALTER USER a QUOTA UNLIMITED ON a3; --a用户可以无限使用a3表空间

或者

SQL>ALTER USER a quota 100M on a3;      --a用户可以使用100Ma3表空间的数据

问题二

tabs是user\_tables的同义词,是oracle为了简化用户书写sql语句而创建的,只要在system下执行下面的查询就知道了

select * from dba_synonyms    
       where synonym_name='TABS'

Oracle用户表空间权限的问题就介绍到这里。

# Oracle create tablespace语法详解 #

<table> 
 <tbody> 
  <tr> 
   <td>[日期：2011-04-09]</td> 
   <td align="center">来源：Linux社区&nbsp;作者：BOBO12082119</td> 
   <td align="right">[字体：大 中 小]</td> 
  </tr> 
 </tbody> 
</table>

*  CREATE \[UNDO\] TABLESPACE tablespace\_name
 *  \[DATAFILE datefile\_spec1 \[,datefile\_spec2\] ......
 *  \[\{MININUM EXTENT integer \[k|m\]
 *  |BLOCKSIZE integer \[k\]
 *  |logging clause | FORCE LOGGING
 *  |DEFAULT \{data\_segment\_compression\} storage\_clause
 *  |\[online|offline\]
 *  |\[PERMANENT|TEMPORARY\]
 *  |extent\_manager\_clause
 *  |segment\_manager\_clause\}\]
 *  1、undo
 *  说明系统将创建一个回滚表空间。
 *  在9i中数据库管理员可以不必管理回滚段，只有建立了undo表空间，系统就会自动管理回滚段的分配，回收的工作。
 *  当然,也可以创建一般的表空间,在上面创建回滚段.不过对于用户来说,系统管理比自己管理要好很多.
 *  如果需要自己管理,请参见回滚段管理的命令详解.
 *  当没有为系统指定回滚表空间时,系统将使用system系统回滚段来进行事务管理。
 *  // 
 *  2、tablespace\_name
 *  指出表空间的名称。
 *  // 
 *  3、datafile datefile\_spec1
 *  指出表空间包含什么空间文件。
 *  datefile\_spec1是形如:\['filename'\] \[SIZE integer \[ K | M \]\] \[REUSE\] \[autoextend\_clause\]
 *  \[autoextend\_clause\]是形如：AUTOEXTEND \{ OFF | ON \[ NEXT integer \[ K | M \] \] \[maxsize\_clause\] \}
 *  其中filename是数据文件的路径名，可以是相对路径，也可以是绝对路径。
 *  size是文件的大小,
 *  REUSE表示文件是否被重用.
 *  AUTOEXTEND表明是否自动扩展.
 *  OFF | ON 表示自动扩展是否被关闭.
 *  NEXT 表示数据文件满了以后,扩展的大小.
 *  maxsize\_clause表示数据文件的最大大小.形如MAXSIZE \{ UNLIMITED | integer \[ K | M \] \}.
 *  UNLIMITED 表示无限的表空间.
 *  integer是数据文件的最大大小.
 *  DATAFILE 'D:"[Oracle][Oracle 2]"oradata"IMAGEDATA01.dbf' SIZE 2000M,
 *  'D:"oracle"oradata"IMAGEDATA02.dbf' SIZE 2000M
 *  // 
 *  4、MININUM EXTENT integer \[k|m\]
 *  指出在表空间中范围的最小值。这个参数可以减小空间碎片，保证在表空间的范围是这个数值的整数倍。
 *  // 
 *  5、BLOCKSIZE integer \[k\]
 *  这个参数可以设定一个不标准的块的大小。如果要设置这个参数，必须设置db\_block\_size,
 *  至少一个db\_nk\_block\_size,并且声明的integer的值必须等于db\_nk\_block\_size.
 *  注意：在临时表空间不能设置这个参数。
 *  // 
 *  6、logging clause
 *  这个子句声明这个表空间上所有的用户对象的日志属性（缺省是logging），
 *  包括表，索引，分区，物化视图，物化视图上的索引，分区。
 *  // 
 *  7、FORCE LOGGING
 *  使用这个子句指出表空间进入强制日志模式。此时，系统将记录表空间上对象的所有改变，除了临时段的改变。
 *  这个参数高于对象的nologging选项。
 *  注意：设置这个参数数据库不行open并且出于读写模式。而且，在临时表空间和回滚表空间中不能使用这个选项。
 *  // 
 *  8、DEFAULT storage\_clause
 *  声明缺省的存储子句。
 *  // 
 *  9、online|offline
 *  改变表空间的状态。online使表空间创建后立即有效.这是缺省值.
 *  offline使表空间创建后无效.这个值，可以从dba\_tablespace中得到。
 *  // 
 *  10、PERMANENT|TEMPORARY
 *  指出表空间的属性，是永久表空间还是临时表空间。
 *  永久表空间存放的是永久对象，临时表空间存放的是session生命期中存在的临时对象。
 *  这个参数生成的临时表空间创建后一直都是字典管理，不能使用extent management local选项。
 *  如果要创建本地管理表空间，必须使用create temporary tablespace
 *  注意，声明了这个参数后，不能声明block size
 *  // 
 *  11、extent\_management\_clause
 *  这是最重要的子句，说明了表空间如何管理范围。一旦你声明了这个子句，只能通过移植的方式改变这些参数。
 *  如果希望表空间本地管理的话，声明local选项。本地管理表空间是通过位图管理的。
 *  autoallocate说明表空间自动分配范围，用户不能指定范围的大小。只有9.0以上的版本具有这个功能。
 *  uniform说明表空间的范围的固定大小，缺省是1m。
 *  不能将本地管理的数据库的system表空间设置成字典管理。
 *  oracle公司推荐使用本地管理表空间。
 *  如果没有设置extent\_management\_clause，oracle会给他设置一个默认值。
 *  如果初始化参数compatible小于9.0.0,那么系统创建字典管理表空间。
 *  如果大于9.0.0,那么按照如下设置：
 *  如果没有指定**default** storage\_clause,oracle创建一个自动分配的本地管理表空间。
 *  否则，
 *  如果指定了mininum extent,那么oracle判断mininum extent 、initial、next是否相等,以及pctincrease是否=0.
 *  如果满足以上的条件，oracle创建一个本地管理表空间，extent size是initial.
 *  如果不满足以上条件，那么oracle将创建一个自动分配的本地管理表空间。
 *  如果没有指定mininum extent。initial、那么oracle判断next是否相等,以及pctincrease是否=0。
 *  如果满足oracle创建一个本地管理表空间并指定uniform。否则oracle将创建一个自动分配的本地管理表空间。
 *  注意：本地管理表空间只能存储永久对象。
 *  如果你声明了local,将不能声明**default** storage\_clause,mininum extent、temporary.
 *  EXTENT MANAGEMENT LOCAL
 *  // 
 *  12、segment\_management\_clause
 *  段空间管理的方式，自动或者手动:
 *  SEGMENT SPACE MANAGEMENT \{AUTO|MANUAL\}
 *  \--
 *  实例1:创建永久表空间
 *  可以一次创建单个表空间，也可以一次性创建多个表空间
 *  1.1创建单个表空间
 *  create tablespace ts\_something
 *  logging
 *  datafile '/dbf1/ts\_sth.dbf'
 *  size 32m
 *  autoextend on
 *  next 32m maxsize 2048m
 *  extent management local;
 *  // 
 *  1.2创建多个表空间
 *  CREATE TABLESPACE TS\_IMAGEDATA
 *  NOLOGGING
 *  DATAFILE 'D:"oracle"oradata"DATA01.dbf' SIZE 2000M,
 *  'D:"oracle"oradata"DATA02.dbf' SIZE 2000M,
 *  'D:"oracle"oradata"DATA03.dbf' SIZE 2000M,
 *  'D:"oracle"oradata"DATA04.dbf' SIZE 2000M,
 *  'D:"oracle"oradata"DATA05.dbf' SIZE 2000M EXTENT
 *  MANAGEMENT LOCAL SEGMENT
 *  SPACE MANAGEMENT AUTO;
 *  // 
 *  CREATE TABLESPACE TS\_IMAGEDATA
 *  LOGGING
 *  DATAFILE 'E:"ORACLE"ORADATA"DATA\_01.DBF' SIZE 2000M REUSE
 *  AUTOEXTEND ON
 *  NEXT 51200K MAXSIZE 3900M,
 *  'E:"ORACLE"ORADATA"XL"DATA\_02.DBF' SIZE 2000M REUSE
 *  AUTOEXTEND ON
 *  NEXT 51200K MAXSIZE 3900M,
 *  'E:"ORACLE"ORADATA"XL"DATA\_03.DBF' SIZE 2000M REUSE
 *  AUTOEXTEND ON
 *  NEXT 51200K MAXSIZE 3900M,
 *  'E:"ORACLE"ORADATA"XL"DATA\_04.DBF' SIZE 2000M REUSE
 *  AUTOEXTEND ON
 *  NEXT 51200K MAXSIZE 3900M
 *  EXTENT MANAGEMENT LOCAL
 *  SEGMENT SPACE MANAGEMENT AUTO;
 *  实例2:创建撤销表空间
 *  create undo tablespace ts\_undo
 *  datafile '/dbf/undo.dbf' size 100M;
 *  // 
 *  CREATE UNDO TABLESPACE ts\_undo01
 *  DATAFILE '/data/ts\_undo01.dbf'
 *  SIZE 50000M REUSE
 *  autoextend on;

[Oracle]: http://www.blogjava.net/wolfman09/archive/2009/05/01/268536.html
[ExpandedBlockStart.gif]: /images/20211207/ff79694e3ade41189f379241cd5cea77.png
[None.gif]: /images/20211207/0187fea72cc248c39ecbc36a60ed4da0.png
[Link 1]: http://www.2cto.com/database/
[Link 2]: http://www.2cto.com/os/
[Oracle 1]: http://www.2cto.com/database/Oracle/
[Oracle 2]: http://www.linuxidc.com/topicnews.aspx?tid=12