1、ORACLE数据库设计说明第1章Oracle数据库安装配置当安装Oracle Database时，如果没有建立数据库，在安装完成之后可以使用DBCA工具建立数据库。数据库配置助手(Database Configuration Assistant)用于建立数据库、配置数据库选项、删除数据库和管理模板。当建立了Oracle数据库之后，为了使得服务器端可以监听该Oracle数据库，必须配置监听程序。为了使得客户端可以访问该数据库，必须在客户端配置网络服务名。只有合理地配置了监听程序和网络服务名之后，客户应用才能访问该数据库。配置监听程序和网络服务名可以使用网络管理工具Net Manager完成。(源
2、码网整理：.codepub.)1.1 配置监听程序监听程序用于接收客户端的连接请求。当客户应用访问Oracle Server时，监听程序会接收并检查连接请求，以确定是否可以为该客户应用提供数据服务。在建立了Oracle数据库之后，为了使得客户应用可以访问Oracle数据库，必须在监听程序中追加该数据库。一个监听程序可以监听多个Oracle数据库，多个监听程序也可以监听同一个Oracle数据库。当安装数据库产品时，会自动建立默认监听程序LISTENER。配置监听程序的具体步骤如下：展开监听程序，并选中LISTENER节点，此时在NET MANAGER窗口右端会显示默认监听位置，其中“协议”用于指
3、定监听程序要使用的网络协议（默认为TCP/IP）；“主机”用于指定服务器所在机器的主机名或IP地址；“端口”用于指定监听程序要使用的TCP/IP端口号（默认1521）。在Net Manager窗口上端的下拉列表中选择数据库服务，此时会显示默认的数据库配置，“全局数据库名”用于指定数据库的全局数据库名；“Oracle主目录”用于指定Oracle数据库软件的安装路径；“SID”用于指数据库例程名。为了监听新建的数据库（如DEMO），必须追加该数据库。单击“添加数据库”按钮，然后进行相应配置，在“全局数据库名”处输入DEMO数据库的初始化参数SERVICEHOME所对应的值，在SID处输入DEMO数
4、据库的例程名。配置了监听程序之后，保存网络配置信息。保存了监听程序配置之后，为了使得其网络配置生效，必须重新启动监听程序。（通过服务器管理器重新启动监听程序）1.2 配置网络服务名选中“服务命名”，然后单击+按钮，此时会显示“Net 服务名”界面，建议使用数据库名作网络服务名。选取与监听程序一致的网络协议“TCP/IP”。指定数据库所在主机名及其监听端口号。指定监听程序所配置的全局数据库名或者SID。测试网络服务名配置是否成功，如果成功则表示网络服务名配置正确。完成网络服务名配置之后，保存网络配置信息。第2章管理用户访问本章主要容：表空间、用户及方案概述用户访问创建用户修改用户授予用户访问权撤
5、消用户废除用户访问创建角色2.1表空间、用户及方案概述表空间是数据库的逻辑组成部分。从物理上说，数据库数据存放在数据文件中；从逻辑上说，数据库数据存放在表空间(tablespace)中，并且表空间是由一个或多个数据文件组成的。一个表空间是由一个或多个数据文件组成的。用户（也称为）是定义在数据库中的一个名称，它是Oracle数据库的基本访问控制机制。当连接到Oracle数据库时，默认情况下必须要提供用户名和口令。只有在输入了正确的用户名和口令之后，才能够连接到数据库，并执行各种管理操作和数据访问操作。方案(Schema)是用户所拥有数据库对象的集合。在Oracle数据库中对象是以用户来组织的，用
6、户与方案是一一对应的关系，并且二者名称相同。例SCOTT用户所拥有的所有对象都属于SCOTT方案，而SYSTEM用户所拥有的所有对象都属于SYSTEM方案。当访问数据库对象时，有一些注意事项：在同一个方案中不能存在同名对象，但不同方案可以具有同名对象。用户可以直接访问其方案对象，但如果要访问其他方案对象，则必须具有对象权限。如用户SCOTT可以直接查询其方案表EMP，但如果用户SMITH要检索SCOTT方案的表EMP，则必须在EMP表上具有SELECT对象权限。当用户访问其他方案对象时，必须加方案名为前缀。例，如用户SMITH要访问SCOTT方案的EMP表，则必须使用SCOTT.EMP。2.2
7、用户访问在多用户环境里，一个数据库可能有多个用户同时在访问。当有不同的用户同时访问数据库时，保护数据库安全，防非授权访问非常重要。因此，必须在数据库里创建用户，并为用户指定用户名和密码，这样可以保证只有经过授权的，即有正确用户名和密码的用户才能访问数据库。数据库管理员（DBA）是最高级别的用户，他可以创建其他用户。在创建了用户后，DBA需要按用户的需求为用户分配权限。权限指用户执行特定语句的许可，这意味并非所有用户都被允许修改重要数据。例如，某个用户可能只需要有连接数据库和查询某些表的记录的权限。类似地，另一个用户可能要求有创建和修改表的权限。DBA有访问数据库的一切权限，并有权为其他用户分配
8、权限。下表列出了部分DBA权限：DBA权限有权执行CREATE USER创建新用户DROP USER撤消用户DROP ANY TABLE撤消表BACKUP ANY TABLE为表制作备份SELECT ANY TABLE查询数据库对象，如表和视图CREATE ANY TABLE创建表为了维护存储在数据库中数据的安全，Oracle提供了以下数据库安全措施：管理和控制数据库访问用Oracle数据字典验证权限为指定用户提供对数据库特定对象（表、视图和序列等）的访问为数据库对象提供同义词可被数据库操作采用的数据库的安全策略包括：系统安全：系统安全涉及系统级的访问，如允许用户通过指出用户名和密码连接Ora
9、cle，为用户分配磁盘空间，限定用户所能执行的操作。用户能执行的操作包括：查询数据库对象的容、创建数据库对象和更改数据库对象。数据安全：数据安全涉及对数据库对象的访问和使用，以及用户在数据库对象上所拥有的权限的程度。2.3创建用户CREATE USER语句用于创建新用户。在创建新用户时，必须为新用户指定用户名和密码。为了使新用户能登录服务器和访问数据库，DBA必须显式地为用户分配权限。只有拥有CREATE USER权限的用户才能创建新用户。例如，DBA创建了新用户Susan，但Susan无权创建其他新用户。只有当Susan有了CREATE USER这个权限后，她才能创建别的新用户。创建用户的语
10、法示例：CREATE USER user IDENTIFIED BY passwordDEFAULT TABLESPACE data01TEMPORARY TABLESPACE tempQUOTA 3M ON data01PASSWORD EXPIRE;其中IDENTIFIED BY用于指定用户口令；DEFAULT TABLESPACE用于指定用户的默认表空间，当建立表或者索引时，如果不指定TABLESPACE子句，那么Oracle会自动在默认表空间上为这些对象分配空间；TEMPORARY TABLESPACE用于指定用户的临时表空间，当用户执行排序操作时，或临时数据超过PGA工作区，则会在该
11、表空间上建立临时段；QUOTA用于指定表空间配额，即用户对象在表空间上可占用的最大空间；PASSWORD EXPIRE用于指定终止口令，最终强制用户在登录时改变口令。当建立了新用户之后，需要注意以下问题：初始创建的数据库用户没有任何权限，不能执行任何数据库操作。如果在建立用户时不指定DEFAULT TABLESPACE子句，那么Oracle会将数据库默认表空间作为用户的默认表空间。在Oracle Database 10g之前，如果不指定DEFAULT TABLESPACE子句，那么Oracle会将SYSTEM表空间作为用户的默认表空间。如果在建立用户时不指定TEMPORARY TABLESPA
12、CE子句，那么Oracle会将数据库默认临时表空间作为用户的临时表空间。如果在建立用户时没有为特定表空间指定QUOTA子句，那么用户在特定表空间上的配额为0，这样用户将不能在相应表空间上建立数据对象。2.4修改用户修改用户信息是使用ALTER USER命令完成的。一般情况下，该命令是由DBA来执行的，如果以其他用户身份修改用户信息，必须要具有ALTER USER系统权限。1修改口令创建用户时为每个用户指定一个初始密码。之后可修改密码，修改密码的方法有两种：管理员修改：ALTER USER user IDENTIFIED BY password;(password为用户的新密码)用户自己修改：用
13、户登录后输入命令：PASSWORD；修改表空间配额表空间配额用于限制用户对象在表空间上可占用的最大空间。如果用户对象己经占满了表空间配额所允许的最大空间，那么该用户将不能在该表空间上分配新的空间。此时如果执行了涉及到空间分配的SQL操作（如INSERT、UPDATE、CREATE TABLE等），则会显示错误，修改表空间语法：ALTER USER user QUOTA 10M ON data01; 2.5 锁定用户账户 为了禁止特定数据库用户用户访问数据库，DBA可以锁定用户账户， ALTER USER user ACCOUNT LOCK;2.6 解锁用户账户 为了使得数据库用户可以访问数据库
14、，DBA可以解锁用户账户。 ALTER USER user ACCOUNT UNLOCK； 2.7 默认角色当将多个角色授予数据库用户之后，通过使用ALTER USER 命令可以设置用户的默认角色。（当为用户指定了默认角色后，以该用户身份登录时会自动激活其默认角色，并不激活非默认角色）ALTER USER user DEFAULT ROLE select_role;补充： 默认角色和非默认角色的区别是什么? 这个理解有多种，第一种：默认角色可以是我们创建数据库就可以见的connect/dba等，非默认角色需要我们自己创建；第二种：一个用户可以有多个角色，默认的角色登录即生效，非默认的需要激活才
15、能使用。 默认角色和权限集是Oracle安装过程中预先定义的。每个版本的默认角色都有所变化。 CREATE USER SMIS IDENTIFIED BY SMIS； grant dba,connect to SMIS; create role r_px; grant r_px to SMIS; select * from dba_role_privs where grantee=SMIS; alter user SMIS default role all except r_px; select * from dba_role_privs where grantee=SMIS;2.8 授予用户
16、访问权用户创建后，数据库管理员需要为他分配权限。权限关系到数据库的安全，它决定了用户在数据库上所能执行的操作。GRANT语句用于为用户分配权限。语法如下：GRANT privilege TO user; 注释：授予所有系统权限GRANT ALL PRIVILEGES to test_2;注释：授予所有对象权限 GRANT ALL PRIVILEGES ON DEMO.DEPT TO TEST_1;可分配给用户的权限有：系统权限：允许用户访问数据库的权限称为系统权限。对象权限：允许用户在数据库对象上执行查询、更新、删除或添加数据等操作的权限，此称为对象权限。常用的系统权限有：系统权限有权执行CR
17、EATE SESSION连接数据库CREATE TABLE创建表CREATE VIEW创建视图CREATE PUBLIC SYNONYM建立同义词CREATE SEQUENCE创建序列CREATE PROCEDURE建立过程、函数和包CREATE TRIGGER建立触发器CREATE CLUSTER建立簇CREATE TYPE建立对象类型CREATE DATABASE LINK建立数据库链另外，Oracle还提供了一类ANY系统权限，当用户具有该类系统权限时，可以在任何方案中执行相应操作。例如，如果用户具有SELECT ANY TABLE系统权限，那么用户可以查询任何方案的表（除数据字典基表和
18、数据字典视图DBA_XXX-DBA和特权用户专访）。如：GRANT CREATE SESSION,CREATE SEQUENCE,CREATE VIEW TO john;上述命令执行后，用户john将拥有创建会话、视图和序列的系统权限。*Oracle提供了100多种系统权限（ALTER TABLE，ALTER VIEW，ALTER PROCEDURE，DROP TABLE，DROP VIEW，DROP PROCEDURE等）。一般情况下，授予系统权限是由DBA来完成的；如果要以其他用户身份授予系统权限，则要求该用户必须具有GRANT ANY PRIVILEGE系统权限，或者具有相应系统权限及其
19、转授系统权限选项（WITH ADMIN OPTION），授予系统权限是使用GRANT命令来完成的，其语法如下：GRANT system_priv,system_priv,TO user | role | public,user | role | public.WITH ADMIN OPTION; 注：user也可以是用户组PUBLIC；UNLIMITED TABLESPACE权限不能被授予角色下表列出了所有对象权限。对象权限适用于ALTER表、序列DELETE表、视图EXECUTE过程INDEX表INSERT表、视图REFERENCES表（基于表建立从表）SELECT表、视图、序列UPDATE
20、表、视图如果用户在某个同义词上拥有权限，而这个同义词又需要引用其他基表，那么用户在同义词上拥有的权限会转换为在基表上的权限。缺省地，用户对他所创建的对象拥有完全的权限。如用户在用户模式上创建了一个表，那么缺省地，他在用户模式上拥有所创建的这个表的所有权限。授予对象权限一般情况下是由对象所有者或者DBA用户来完成的；如果以其他用户身份授予对象权限，则要求用户必须具有该对象权限及转授对象权限选项（WITH GRANT OPTION），语法如下：GRANT object_priv (columns) ,object_priv(columns) | ALL PRIVILEGES ON schema.o
21、bjectTO user | role | PUBLIC , user | role | PUBLIC WITH GRANT OPTION ;例：grant update on scott.emp to blake;Grant update(sal) on emp to blake;（只能在insert、update和references上授予列权限2.9撤消用户DROP USER语句用于删除用户。语法：DROP USER username;撤消用户时，用户创建的对象并没有被撤消。为了撤消创建对象的用户语法：DROP USER username CASCADE；（不指定CASCADE不能撤消创
22、建对象的用户）2.10废除用户访问一般情况下，收回系统权限是由DBA来完成的；如果以其他用户身份收回系统权限，则要求该用户必须具有相应系统权限及其转授系统权限选项(WITH ADMIN OPTION)。收回系统权限是使用REVOKE命令来完成的。语法如下：REVOKE system_priv,system_privFROM user | role | PUBLIC ,user | role | PUBLIC 用户的权限可使用REVOKE语句废除。一旦某个用户的权限被撤消，由他创建的所有用户和从他那获得权限的所有用户也都被撤消。如，Jim把CREATE TABLE权限授给John。当Jim的权限
23、被撤消时，同时自动撤消John的权限。如：GRANT CREATE TABLE TO John; REVOKE CREATE TABLE FROM John;（不能撤消自己的权限）一般情况下，收回对象权限是由对象所有者或者DBA用户来完成的；如果以其他用户身份收回对象权限，则要求用户必须具有该对象权限及转授对象权限选项（WITH GRANT OPTION）。语法如下：REVOKE object_priv , object_priv | ALL PRIVILEGES ON schema. objectFROM user | role | PUBLIC , user | role | PUBLIC
24、 CASCADE CONSTRAINTS; CASCADE CONSTRAINTS 用于删除任何与该对象相关的约束和对象，例如索引、触发器、权限、完整性约束等。2.11创建角色角色是相关权限的命名集合，使用角色的主要目的是为了简化权限管理。角色可以是权限的组合，也可以是角色的组合。角色包括预定义角色和自定义角色两类。常用的预定义角色有：（oracle 10g）CONNECT Role：分配给临时用户的角色。通常，为只需要查询材料而无须创建表的用户分配这个角色。RESOURCE Role：这个角色分配给常规用户DBA Role：这个角色拥有一切系统权限，包括不加限制的表空间配额以及WITH AD
25、MIN OPTION选项。默认的DBA用户为SYS和SYSTEM，他们可以将任何系统权限授予其他用户。读者需要注意，DBA角色不具备SYSDBA和SYSOPER特权。创建角色的语法：CREATE ROLE role NOT IDENTIFIED ; 不验证用于公用角色或用户默认角色或CREATE ROLE roleIDENTIFIED BY password; 数据库验证为角色分配密码或修改角色密码：ALTER ROLE roleIDENTIFIED BY password;（password为要设置的新密码）如：CREATE ROLE Acadre;ALTER ROLE Acadre IDE
26、NTIFIED BY success;为角色授予权限GRANT privilege TO role;例：只授予CONNECT权限给角色Acadre，这样他们不可能操作数据库。 GRANT connect TO Acadre;将角色指派给用户一个角色可指派给多个用户。类似的，一个用户也可具有多个角色。当把角色指派给用户时，赋予该角色的权限也自动分配给用户。为用户分配角色的语法：GRANT role TO user;也可以使用ALTER USER语句授权用户。ALTER USER John Default ROLE Student;授予用户John以缺省角色student。2.12 新建的用户怎样
27、才能成功创建一个表当一个用户刚被创建时是不具备任何权限的，因此要在该用户模式下创建表，需授予CREATE SESSION、CREATE TABLE、以及UNLIMITED TABLESPACE（或分配配额）权限，因为：当用户要连接到数据库时必须拥有CREATE SESSION权限当用户要创建表时必须拥有CREATE TABLE权限，同时用户还需要在表空间中拥有配额或者被授予UNLIMITED TABLESPACE。现在我们来做一个测试：1）、创建用户TEST，密码为passwd_1:SQL CREATE USER testIDENTIFIED BY passwd_I1用户已创建2）当用TEST
28、连接数据库时：SQL conn test/passwd_1ERROR:ORA-01045: user TEST lacks CREATE SESSION privilege; logon denied警告: 您不再连接到 ORACLE。/因为缺少CREATE SESSION的权限，登陆失败。3）利用SYS给TEST授予CREATE SESSION权限：SQL grant create session to test;授权成功。4）SQL conn test/passwd_1已连接。5）在test的方案中创建表exam1:SQL create table exam1 (student_id in
29、t,paper_id int);create table exam1ERROR 位于第 1 行:ORA-01031: 权限不足/因为未给TEST用户授予create table 权限，因此不能够创建表exam1.6) 给TEST用户授予CRETE TABLE 权限SQL grant create table to test;授权成功。7）SQL create table exam1(student_id int,paper_id int);create table exam1ERROR 位于第 1 行:ORA-01950: 表空间SYSTEM中无权限/因为在创建用户时没有指定表空间，因此默认的
30、表空间是SYSTEM表空间，而TEST用户还需要在表空间SYSTEM中既没有拥有配额又没有被授予UNLIMITED TABLESPACE权限，因此对于这种情况有两种解决办法：第一种方法：SQL alter user testquota 15m on system;用户已更改。/在SYSTEM表空间中，给用户TEST分配15M的使用空间SQL create table exam1(student_id int,paper_id int);表已创建第二种方法：SQL grant unlimited tablespace to test授权成功。SQL create table exam2(student_id int,paper_id int);第3章建立和管理表本章主要容：表简介创建表修改表截断和删除表显示表的信息3.1简介 表是Oracle数据库最基本的对象，它用于存储用户数据。设计表当设计表时，需要考虑以下因素：当规划表和列时，应该使用有意义的名称。当定义表名和列名时，只能使用字符（AZ，a-z）、数字（09）、_、$和#，名称必须以字符开始，并且长度不能超过30个字符。当规划表名和列名时，要使用一致的缩写格式、单数或复数格式。为了给用户和其他人员提供有意义的帮助信息
