数据库的创建-循序渐进Oracle

eygle 2019-09-10
432

在第一章中我们已经介绍了数据库的创建,在创建完成之后,数据库就准备好对外提供服务了;但是如果要想使用这个数据库,还需要一把钥匙来打开这个数据库之门,这个钥匙就是-用户。本章将着重介绍Oracle数据库的用户创建与管理。


数据库的连接


在创建数据库时,我们曾经在如图4-1所示的界面中为数据库的初始用户定义过口令,而这里列出的就是数据库初始创建的用户。


企业微信截图_15681102413410.png


图4-1  数据库身份证明


在命令行下,可以通过以上用户及口令登录进入数据库,当然最常见的是通过如下方式连接数据库:


C:\>sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 1月 9 15:10:59 2007
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
 
SQL>

 

通过SQL*Plus的GUI界面同样可以使用类似的方式登录:

 

企业微信截图_156811032832.png      企业微信截图_15681103531509.png

图4-2 运行sqlplusw程序                                                               图4-3  SQL*Plus的图形登陆


当我们开始学习数据库的时候,通常都曾经很惊奇地看到资深的DBA以这种方式登录数据库。


这种登录方式既不需要输入口令,又可以以最高权限登入数据库,甚至通过任意的用户名及口令都可以以SYSDBA身份登录数据库:

SQL> connect a/a as sysdba
已连接。
SQL> connect eygle/julia as sysdba
已连接。


很多人会提问:这样的话数据库的安全何在呢?


其实在这种情况下,Oracle采用的操作系统认证方式,当属于操作系统DBA组用户登录到数据库服务器,那么Oracle认为这样的用户就可以被授权以SYSDBA身份登录数据库,这时的用户名及口令就是行同虚设,登录到数据库之后我们通过show user命令可以看到用户都是SYS:

SQL> connect / as sysdba
Connected.
SQL> select name from v$database;
NAME
---------
EYGLE
SQL> show user
USER 为 "SYS"


在Windows系统中,我们可以从“计算机管理”→“本地用户和组”→“组”中找到ORA_DBA组,右键单击ORA_DBA,从弹出的快捷菜单中选择“属性”命令,打开“ORA_DBA属性”对话框,如图4-4所示,可以看到此时ORA_DBA组中有eygle成员。


企业微信截图_15681104411300.png

图4-4  ORA_DBA属性


如果当前用户不属于ORA_DBA组,那么是不能以SYSDBA身份直接登录数据库的,我们可以简单试验,当把用户eygle从ORA_DBA组中删除后再次登录:

C:\>sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 9月 25 10:26:17 2006
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
ERROR:
ORA-01031: insufficient privileges
 
请输入用户名:


此时数据库提示输入用户名及口令,也就是说操作系统认证没能通过。

 

基于操作系统的认证还和另外一个设置有关。我们可以在$ORACLE_HOME\network\admin下找到sqlnet.ora文件,这个文件中存在一个参数设置:SQLNET.AUTHENTICATION_SERVICES = (NTS)。

 

可以来检查一下这个文件:

C:\>cd oracle\10.2.0\NETWORK\ADMIN
C:\Oracle\10.2.0\NETWORK\ADMIN>type sqlnet.ora
# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.
 
SQLNET.AUTHENTICATION_SERVICES = (NTS)


注释部分已经对这个文件作了简要说明,这个文件由netca产生,如果安装软件时我们选择“Software Only(仅安装软件)”,那么这个文件可能并不存在,如果这个文件并不存在,那么本地认证(Native Authentication)将不可用。


这个文件中的SQLNET.AUTHENTICATION_SERVICES 参数用来指定认证方式,当这个参数设置为NTS,将启用系统认证;当这个参数被注释,或者设置为NONE:

SQLNET.AUTHENTICATION_SERVICES = (NONE)

 

那么操作系统认证将被关闭:

C:\Oracle\10.2.0\NETWORK\ADMIN>sqlplus "/ as sysdba"
 
SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 9月 25 10:43:10 2006
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
ERROR:
ORA-01031: insufficient privileges

 

请输入用户名:

如果为了提高安全性,可以考虑修改该参数,关闭基于操作系统的认证方式。


在Linux/UNIX上存在同样的问题,我们同样可以修改sqlnet.ora中的参数来限制登录,当未设置SQLNET.AUTHENTICATION_SERVICES参数时,可以通过操作系统认证来登录数据库:

[oracle@jumper admin]$ tail -1 sqlnet.ora
#SQLNET.AUTHENTICATION_SERVICES = (NONE)
[oracle@jumper oracle]$ sqlplus /nolog
SQL*Plus: Release 9.2.0.4.0 - Production on Mon Jun 25 13:46:29 2007
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
SQL> connect / as sysdba
Connected.


当设置SQLNET.AUTHENTICATION_SERVICES参数为NONE之后,我们登录数据库就必须提供用户名及口令信息:

[oracle@jumper admin]$ tail -1 sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES = (NONE)
[oracle@jumper admin]$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Mon Sep 25 10:35:26 2006
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
ERROR:
ORA-01031: insufficient privileges
 
Enter user-name:


SQLNET.AUTHENTICATION_SERVICES这个参数的设置,在Windows的不同版本上经常会引发一些问题,Oracle有很多Bug于此相关,已知的某些版本在Windows 2000上安装,如果SQLNET.AUTHENTICATION_SERVICES设置为NTS可能会遇到错误;Oracle10g ASM的安装在NTS设置下可能会遇到Bug。


继续前面的讨论,既然能够以管理员的身份登录数据库,接下来就可以创建其他用户了。


用户的创建


如果把Oracle数据库比作一个公司,那么创建用户就相当于在公司中注册,只有在公司中注册,成为公司的一员,你才可能访问这个公司;同样,当一个公司雇佣一个员工时,公司就要为员工分配工位,同时也要分配给你一些公用空间,如茶水间、更衣室等;在数据库中,一个用户被创建时同样需要分配空间,这个空间包括被用于用户的数据存储的空间,也包括临时使用的公用空间,如临时空间等。


当然,缺省的,数据库中已经有了两位管理人员:SYS和SYSTEM,这两个用户在创建数据库时被缺省创建,并且被赋予了DBA的角色,具有数据库中最高的权限。由于SYS用户具有管理数据库的所有权限,所以仅允许以SYSDBA或者SYSOPER身份登陆数据库:

SQL> connect sys/oracle
ERROR:
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER
SQL> connect sys/oracle as sysdba
Connected.
SQL>


SYSDBA和SYSOPER是在数据库中执行高级别操作所需要的管理权限,这些操作包括创建数据库、起停数据库、备份或恢复数据库等。SYSDBA的系统权限包含了完全的数据库管理权限,而SYSOPER系统权限允许用户执行基本的数据库管理任务,这两者的权限在Oracle的数据库Vault组件中得到了进一步的限制和约束,防止DBA干扰或访问用户敏感数据。 SYSDBA的缺省Schema是SYS,而SYSOPER的缺省Schema是PUBLIC。

 

用SYS身份连接到数据库,创建用户的最简单语法如下:

CREATE USER user IDENTIFIED BY password ;

 

这个子句已经过于简单,省略了空间分配的子句(请注意作为一个DBA,要尽量避免这样的语句),通常这是不推荐的写法,可是很多客户确实就是如此创建用户的。


需要理解一下的是,这个语句在Oracle的不同版本中会有不同的结果。我们分别以Oracle 8i、Oracle 9i、Oracle 10g的主要版本为例,做简要的说明。


首先看看Oracle 8i中的结果:

SQL> create user eygle identified by eygle;
User created.
SQL> SELECT username, default_tablespace, temporary_tablespace
  2    FROM dba_users
  3   WHERE username = 'EYGLE';
USERNAME   DEFAULT_TABLESPACE   TEMPORARY_TABLESPACE
---------- -------------------- --------------------
EYGLE      SYSTEM               SYSTEM


这里的DEFAULT_TABLESPACE就是用户缺省的数据表空间,TEMPORARY_TABLESPACE就是用户缺省的用于磁盘排序等操作的临时表空间,我们注意到缺省的Oracle将用户的数据表空间和临时表空间都指定在SYSTEM表空间上,这是极度不合理的,在Oracle 8i中,很多用户因为将用户数据和系统数据混合存放于SYSTEM表空间,引发了很多性能问题和管理问题。我经常见到用户的SYSTEM表空间达到几十个G bytes的。


在Oracle 9i中,Oracle做出了改进,我们看一下Oracle 9i中Oracle的处理:

SQL> select * from v$version where rownum <2;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
SQL> CREATE USER eygle IDENTIFIED BY eyglee;
User created.
 
SQL> SELECT username, default_tablespace, temporary_tablespace
  2    FROM dba_users
  3   WHERE username = 'EYGLE';
USERNAME   DEFAULT_TABLESPACE   TEMPORARY_TABLESPACE
---------- -------------------- --------------------
EYGLE      SYSTEM               TEMP


这里注意用户的缺省临时表空间已经自动指定为TEMP表空间,而数据表空间仍然是SYSTEM表空间,不管怎样,Oracle在9i中改进了一步。

 

在Oracle 10g中,Oracle最终解决了这个问题,这得益于全局缺省数据表空间的引入:

SQL> select * from v$version where rownum <2;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
SQL> CREATE USER eygle IDENTIFIED BY eyglee;
User created.
SQL> SELECT username, default_tablespace, temporary_tablespace
  2    FROM dba_users
  3   WHERE username = 'EYGLE';
USERNAME   DEFAULT_TABLESPACE   TEMPORARY_TABLESPACE
---------- -------------------- --------------------
EYGLE      USERS                TEMP


通过这个例子我们可以看到,Oracle实际上一直在针对用户的习惯来修正数据库,以期达到更好的性能。


注意:在Oracle10g中,全局缺省的临时表空间和数据表空间不能被删除。试图删除缺省的数据表空间会收到如下错误:

SQL> drop tablespace users;
 drop tablespace users
 *
 ERROR at line 1:
 ORA-12919: Can not drop the default permanent tablespace


如果确实需要删除缺省的临时表空间或数据表空间,那么首先需要创建一个新的临时表空间或数据表空间(当然也可以使用现有的表空间),然后将全局缺省的表空间更改到新创建的表空间上,此后,原来的表空间就可以被删除。


更改缺省数据表空间的语法为:

alter database default tablespace <tablespace_name>;


更改缺省临时表空间的语法为:

alter database default temporary tablespace <tablespace_name>;

 

由于一直以来存在用户空间分配和使用问题,我们建议在创建用户时就为用户指定缺省的表空间。那么更为完善的创建用户的语句应该类似以下语句:

CREATE USER <username> IDENTIFIED BY <password>
DEFAULT TABLESPACE <tablespace_name>
TEMPORARY TABLESPACE <tablespace_name>;


修正一下之前的语句,较为完善的用户创建过程应该如下:

SQL> CREATE USER eygle IDENTIFIED BY eygle
  2  DEFAULT TABLESPACE users
  3  TEMPORARY TABLESPACE temp;
User created.


基于管理和性能方面的考虑,通常我们需要为不同的用户建立独立的表空间,在第5章中我们将详细地介绍关于表空间的知识。


如果需要删除用户,则可以通过如下命令完成:

DROP USER username CASCADE


但是需要注意,DROP USER是DDL语句,不可回退,在删除用户时一定要小心谨慎,确认用户数据确实不再需要才可以执行删除用户的操作。通常建议,在生产系统中,删除用户前先将用户锁定一段时间,如果确定不再有用户通过该用户访问数据库,则可以安全删除。


如下一条命令,可以将用户账号锁定:

ALTER USER username ACCOUNT LOCK;


缺省的数据库已经锁定了部分缺省用户:

SQL> select username,account_status from dba_users where account_status<>'OPEN';

USERNAME                       ACCOUNT_STATUS

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

OUTLN                          EXPIRED & LOCKED

DIP                            EXPIRED & LOCKED

WMSYS                          EXPIRED & LOCKED

ORACLE_OCM                     EXPIRED & LOCKED

TSMSYS                         EXPIRED & LOCKED

锁定用户,也是基于安全考虑常用的手段之一。


口令的管理

由于用户的口令和数据库安全严密相关,所以Oracle在不断通过各个角度增强数据库的口令管理。


最后修改时间:2019-09-23 17:04:26
「喜欢文章,快来给作者赞赏墨值吧」
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论