Oracle数据库 语法操作汇总(五)（完结）

oracle各种语法操作的汇总第五部分

修改序列
alter sequence 序列名
【increment by n】
【maxvalue m】

删除序列
drop sequence 序列名
rowid:伪列，系统自动产生。rowid能唯一识别出每一条数据库记录的物理地址，通过rowid可以快速定位到一条行动记录。
woeid组成：数据对象编号（6位），相关文件编号（3位） 快编号（6位） 行编号（3位）

索引（index）
分类：单列索引：索引建立在一列上
          符合索引：索引建立在某几列上
创建索引：
          自动创建：当在建表的时候，使用了PRIMARY KET或者是UNIQUE约束时，数据库会自动创建一个索引
手工创建：
          CREATE INDEX 索引名
          ON 表名（列名1，列名2，...）
注意：索引名命名规范：idx_表名_列名
eg.CREATE index idx_emp_deptnojob
      ON emp(deptno,job);

CREATE e1 AS SELECT * FROM emp;复制emp未复制约束

删除索引：
DROP INDEX 索引名；

用户管理：
1）Oracl数据库的初始用户
a:SYS:超级管理员，权限最大的，可以修改，启动，关闭数据库。
b:SYSTEM:一般管理员（辅助管理员），他不可以启动和关闭数据。主要进行一些管理操作：创建用户，删除用户等
c:SCOTT:用来测试网络连接
d:PUBLIC: 它本质上时一个用户组，数据库中的任何一个用户都属于该组。因为要为数据库中的每个用户授权，则
可以把授权直接授予PUBLIC组

2）用户属性：
a:身份认证：数据库身份认证，外部身份认证，全局身份认证
b：默认表空间
c:临时表空间
d:表空间配额
e:概要文件
f:账号状态

3）创建用户
CREATE USER 用户名 INDENTIFIED
【BY 密码 EXTERNALLY GLOBALLY AS ‘external_name'】
【DEFAULT TABLESPACE tablespace_name】
【TEMPORARY TABLESPACE tablespace_name】
【QUOTA n K或M UNLIMITED ON tablespace——name】K M为单位 KB MB
【PROFILE profile name】
【PASSWORD EXPIRE】
【ACCOUNT LOCK或UNLOCK】
范例：创建一个用户user3,口令为user3,默认表空间为USERS,该表空间的配额为10MB，初始状态锁定
CREATE USER user3 IDENTIFIED BY user3
DEFAULT TABLESPACE USERS QUOTA 10M ON USERS ACCOUNT LOCK;

修改账号：与创建账号不同的地方有2处
1）ALTER USER 用户名 【IDENTIFIED】
2）【DEFAULT ROLE role_list ALL 【Except role_list】NONE】

范例：修改用户user3的默认表空间为ORCCTBS1,在该表空间的配额为20MB，在USERS表空间的配额为5MB
ALTER USER users DEFAULT TABLESPACE ORCCTBS1 QUOTA 20M ON ORCLTBS1
QUOTA 5 M ON USERS

删除用户：
DROP user 用户名 【cascade】;  cascade的作用：不论是否有表，直接删除用户，其他引用该没有用户的数据将变为失效状态

查询用户：
1）ALL-USERS：包含数据库所有用户的用户名，用户id与创建时间
2）DBA_USERS：包含数据库所有用户的详细信息
3）USER_USERS包含当前用户的详细信息
4）DBA_TS_QUOTAS:包含所有用户的表空间
5）USER_TS_QUOTAS:包含当前用户的表空间
6）V$SESSION:包含用户会话信息
7）V$OPEN_CURSOR:包含用户执行的SQL语句
eg.select * from V$OPEN_CURSOR

权限管理：系统权限，对象权限
系统权限：
1）注意事项：
a:只有DBA才能拥有ALTER DATABASE系统权限
b:应用程序开发者一般需要拥有的时CREATE TABLE,CREATE VIEW与CREATE INDEX
c:普通用户一般只句有CREATE SESSION系统权限
d:只有授权时带有WITH ADMIN OPTION子句时，用户才可以将获得的系统权限再次授予给其他用户，即系统权限的传递性
2）语法格式：
GRANT 系统权限 TO 用户角色 PUBLIC 【WITH ADMIN OPTION】 (权限传递)
注意：
a:如果授予多个系统权限，则之间用逗号分隔
b:如果授予多个用户，则之间用逗号分隔
eg.GRANT CREATE SESSION TO user3
eg.GRANT CREATE SESSION,CREATE TABLE,CREATE VIEW TO user2 WITH ADMIN OPTION

权限的回收
REVOKE 系统权限 FROM 用户列表|角色列表|PUBLIC
eg.REVOKE CRETAE TABLE FROM user1;

user1授予权限A给user2，uer2再将权限A授予user3,即使user2的A权限被user1回收，user3仍具有A权限

对象权限：
1)对象权限的授予：允许再授权，即user1给user2,user2可以给user3,但在非system用户情况下要加上with grant option
grant 对象权限列表 all on 对象 to 用户列表 角色列表 【with grant option】
eg.将emp表的select,update,insert权限授予user1
eg.grant select,update,insert on scott.emp to user1;(指明是scott下的emp表)
2)对象权限的回收
revoke 对象权限列表 all on 对象 from
eg.revoke select,update on scott.emp from user1;

查询权限信息
DBA_TAB_PRIVS
ALL_TAB_PRIVS
USER_TAB_PRIVS
DBA_COL_PRIVS
ALL_COL_PRIVS
USER_COL_PRIVS
DBA_SYS_PRIVS
USER_SYS_PRIVS
eg. select * from DBA_TAB_PRIVS

角色管理：
    角色：一系列相关权限的集合
    角色分类：系统预定义角色和用户预定义角色
1）系统预定义角色：conn system/tiger;
        select * from DBA_ROLES;(有33个)
2）用户自定义角色：
a:创建角色
create role 角色名称 【not identified】【identified by 密码】
create role middle_tiger_role identified by middle_tiger_role

grant connect,create table,create view to middle_tiger_role
注意：给角色授权时需要注意，一个角色可以被授予另一个角色。但不能授予其本身，不能产生循环授权。
3）修改角色：
    让角色生效/失效
alter role 角色名称【not identified】【identified by 密码】为high_tiger_role 添加口令
eg.alter role high_tiger_role identified by highrole

角色的生效与失效：

SET ROLE 【角色名称【IDENTIFIED BY 密码】】【ALL 【except 角色名称】【NONE】】

设置某一个角色失效

SET ROLE high_tiger_role IDENTIFIED BY highrole

4)删除角色：
   drop role 角色名称

利用角色进行权限管理
1）给用户角色授予角色
grant 角色列表 to 用户列表 角色列表
eg. grant connect,high_tiger_role to user1; (connect是指connect 权限)
2）从用户或角色回收角色
  revoke 角色列表 from 用户列表角色列表
eg.revoke resoruce,connect from middle_tiger_role
3)用户角色的激活或屏蔽
alter user 用户名 default role 【角色名称】【all【except 角色列表】】【none】
eg.屏蔽用户的所有角色
alter user user1 default role none

查询角色信息
  DBA_ROLES
  DBA_ROLE_PRIVS
  USER_ROLE_PRIVS
  ROLE_ROLE_PRIVS
  ROLE_SYS_PRIVS
  ROLE_SYS_PRIVS
  ROLE_TAB_PRIVS
  SESSION_PRIVS
  SESSION_ROLES

概要文件
  概要文件是数据库和系统资源限制的集合。是Oracle数据库的安全策略的重要组成部分。
利用概要文件，可以限制用户对数据库与系统资源的使用，还可以对用户口令进行管理。
  每个数据库用户都必须有一个概要文件，通常DBA将用户分为几种类型，然后每种类型的
用户创建一个概要文件。
  系统资源的限制包括：
1）CPU使用时间
2）每个用户的并发会话数
3）用户连接数据库的时间
4）用户连接数据库的控制时间
5）私有SQL区与PL SQL区的使用

a:创建概要文件：
create profile 概要文件名称 limit 系统资源限制参数，口令管理参数
eg.
create profile pwd_profile limit failed_attempts 4 password_lock_time 10;
eg.创建一个名为res_profile的概要文件，要求每个用户最多可以创建4个并发会话，每个会话的持续时间
最长为60分钟，如果绘画在连续20分钟内空闲，则结束会话。
eg.create profile res_profile limit
session_per_user 4 connect_time 60 idle time 20 private_sga 100k
cpu_per call 1000;

b:将概要文件分配给用户
1.在创建用户的时候指定概要文件
create user user_5 identified by user5 profile res_profile;
2.为用户指定概要文件（替换默认的概要文件）
alter user user5 profile pwd_profile;

c：删除概要文件
drop profile 概要文件名称 cascade;

d:查询概要文件
user_password_limits
user_resource_limits
dba_profiles