ORACLE通过SQL语句创建表空间和用户并授权

ORACLE通过SQL语句创建表空间和用户并授权

1，

确定数据文件的存储地址，有两种情况，一个是明确存储地址，一个是跟其他表空间存在一个地方，但是不知道地址，这时候可以根据如下SQL进行查找：

select t.*  from sys.dba_data_files t where t.tablespace_name ='知道的表空间名称'

2，

创建新的表空间：

create tablespace NEW_TABLESPACENAME datafile 'E:/app/Administrator/oradata/orcl/NEW_TABLESPACENAME' size 1024M reuse; 

此处的datafile路径可以根据第一步进行设置，文件大小可以自定义

新建表空间的时候同时会创建数据文件，表空间太小会在初始化sql的时候报错（错误见图片），可以将size设置为1M试验一下。但是太多又会造成空间浪费，根据实际情况估算一下，然后再确定表空间大小，不要盲目的设大或设小。

还有一种方法是在创建表空间的设置自增加属性，这样在表空间不足的时候会自己增加，这是一种比较合理的策略

create tablespace NEW_TABLESPACENAME

  datafile 'E:/app/Administrator/oradata/orcl/NEW_TABLESPACENAME' 

  size 1M autoextend on next 50M maxsize unlimited; 

autoextend 自动增长 50M是自增的大小

3，

创建新的用户：

--新建用户

create user NEW_USERNAME

  identified by "NEW_PASSWORD"

  default tablespace NEW_TABLESPACENAME 

  profile DEFAULT

  ACCOUNT UNLOCK;

4，

给新建用户授DBA权限

grant dba to NEW_USERNAME;

grant unlimited tablespace to NEW_USERNAME;

也可以给已经创建的用户进行授权

至此，表空间和用户都已经创建完毕，登录后可以执行建表SQL。

-----------------------------------------------------------------------------

select t.*  from sys.dba_data_files t

create tablespace NEW_TABLESPACENAME

  datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\COST168\CJB.DBF'

  size 1M autoextend on next 50M maxsize unlimited;
   
  alter tablespace NEW_TABLESPACENAME rename to CJB;
   
  select * from dba_users;
 
  create user CJB   identified by "123456"  default tablespace CJB  profile DEFAULT  ACCOUNT UNLOCK;

grant dba to CJB;
grant unlimited tablespace to CJB;
grant connect,resource to CJB;
select * from dba_role_privs where grantee='CJB'
 grant select any table to CJB with admin option;

-------------------------------------------------------------------

评论

1 楼 hxsmile 2015-05-21  

我们知道，创建一个新用户时，网上各种的帖子包括书籍中经常用到一个grant connect,resource to user;，这样才能用这个用户登录数据库，那么这条语句的真正作用是什么呢？


1、首先，grant XXX to user;，grant是授权的作用，这里的XXX可以是一个角色role，也可以是权限，例如grant role to user;，或grant insert on table to user;。

MOS中给出的标准SQL语句：

创建角色：

create role <role name> [IDENTIFIED BY <password>/USING <package>/EXTERNALLY/GLOBALLY ];

赋予角色权限：

grant <object/system privilege> to <role name>;

从角色收回权限：

revoke <privilege> from <role name>;

将角色赋予另一个角色或用户：

grant <role> to <username or role> ;


2、其次，connect和resource是两个系统内置的角色，和dba是并列的关系。

参考一些帖子的说法，权限可以分为两类：

系统权限：系统规定用户使用数据库的权限。(系统权限是对用户而言)。

实体权限：某种权限用户对其它用户的表或视图的存取权限。(是针对表或视图而言的)。

接下来看系统权限，

DBA：拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。

RESOURCE:拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。

CONNECT：拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。

对于普通用户：授予connect, resource权限。

对于DBA管理用户：授予connect，resource, dba权限。

且系统权限只能由DBA用户授出：sys, system(最开始只能是这两个用户)。普通用户通过授权可以具有与system相同的用户权限，

但永远不能达到与sys用户相同的权限，system用户的权限也可以被回收。

另外，对于WITH ADMIN OPTION级联授权的问题，

1）如果使用WITH ADMIN OPTION为某个用户授予系统权限，那么对于被这个用户授予相同权限的所有用户来说，取消该用户的

系统权限并不会级联取消这些用户的相同权限。

2）系统权限无级联，即A授予B权限，B授予C权限，如果A收回B的权限，C的权限不受影响；系统权限可以跨用户回收，即A可以

直接收回C用户的权限。


3、证明下为什么resource和connect的角色不能创建视图。

SQL> select role, count(*) from role_sys_privs group by role;

ROLE                                    COUNT(*)

------------------------------ ----------

EXP_FULL_DATABASE            8

DBA                                      160

SCHEDULER_ADMIN             6

RESOURCE                            8

IMP_FULL_DATABASE           68

CONNECT                             1

可以看到RESOURCE和CONNECT具有的权限个数。


下面看看各自都有什么权限：

SQL> select grantee,privilege from dba_sys_privs where grantee='RESOURCE' order by privilege;

GRANTEE                              PRIVILEGE

------------------------------ ----------------------------------------
RESOURCE                            CREATE CLUSTER
RESOURCE                            CREATE INDEXTYPE
RESOURCE                            CREATE OPERATOR
RESOURCE                            CREATE PROCEDURE
RESOURCE                            CREATE SEQUENCE
RESOURCE                            CREATE TABLE
RESOURCE                            CREATE TRIGGER
RESOURCE                            CREATE TYPE
8 rows selected.

SQL> select grantee,privilege from dba_sys_privs where grantee='CONNECT' order by privilege;

GRANTEE                              PRIVILEGE
------------------------------ ----------------------------------------
CONNECT                             CREATE SESSION


结果不言自明了，CREATE VIEW权限并不在这两个角色中，因此需要额外grant CREATE VIEW to user;，才能让这用户可以创建视图。往往实验是最好的老师，这次又证明了这点。