Oracle对象权限

对象权限指访问其他方案的权利，用户直接访问自己的方案的对象，但是如果要访问别的方案的 对象，则必须具有对象的权限。

比如smith用户要访问scott.emp表（scott：方案，emp：表），则不需再scott.emp 表上具有对象的权限。

常用的对象权限：

Alter --修改(修改表结构) delete --删除

Select --查询 insert --添加

Update --修改(更新数据) index --索引

References --引用 execute --执行

显示对象权限

通过数据字典视图可以显示用户或角色所具有的对象权限：dba_tab_privs;

授予对象权限

在oracle9i前，授予对象权限是由对象的所有者来完成的，如果用其他的用户来操 作，则需要用户具有相应的(with grant option)权限，从oracle9i开始，dba 用户(sys,system)可以将任何对象上的对象权限授予其他用户，授予对象权限是用 grant命令来完成的。

对象权限可以授予用户，角色，和public。在授予权限时，如果带有with grant option选项，则可以将该权限转授给其他用户，但是要注意with grant option 选项不能被授予角色。

小案例

1.monkey用户要操作scott.emp表，则必须授予相应的对象权限

a) 希望monkey可以查询scott.emp的表数据，怎样操作？

首先建立一个monkey用户

SQL> create user monkey identified by m123;

User created

给monkey用户授权

SQL> grant create session to monkey;

Grant succeeded

可以查看monkey用户具有连接数据库的权限

SQL> conn monkey/m123;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as monkey

用monkey用户查看scott方案的emp表，得到了失败的提示

SQL> select * from scott.emp;

select * from scott.emp

ORA-00942: 表或视图不存在

连接到scott用户，让scott用户为monkey用户授权

SQL> conn scott/tiger;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as scott

SQL> grant select on emp to monkey;

Grant succeeded

下面就是我们想要的结果：

SQL> select * from scott.emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

8881 test用户 MANAGER 7782 2010-12-21 23.00 23.00 10

......

15 rows selected

b) 希望monkey可以修改scott.emp表的数据，怎样操作？

Sql> Grant update on emp to monkey;

c) 希望monkey可以删除scott.emp表的数据，怎样操作？

Sql> Grant delete on emp to monkey;

d) 一次性将所有对scott.emp表的数据操作的权限授予monkey

Sql> Grant all on mep to monkey;

2.能否对monkey访问权限更加精细控制（授予列权限）

a) 希望monkey只可以修改scott.emp表的sal字段，怎样操作？

Grant update on emp(sal) to monkey;

b) 希望monkey只可以查询scott.emp表的ename，sal数据，怎样操作？

Grant select on emp(ename,sal) to monkey;

3.授予alter权限

如果monkey用户要修改scott.emp表的结构，则必须授予alter对象权限

Sql> conn scott/tiger;

Sql> grant alter on emp to monkey;

当然也可以由sys,system来完成此事。

4.授予execute权限

如果用户想要执行其他方案的包/过程/函数，则必须有execute权限。

比如为了让monkey用户可以执行dbms_transaction，可以授execute权限

Sql> conn system/manger;

Sql> grant execute on dbms_transaction to monkey;

5.授予index权限

如果想在别的方案的表上建立索引，则必须具有index对象权限，如：为了让monkey 用户可以子scott.emp上建立索引，就给其index的对象权限

Sql> conn scott/tiger;

Sql> grant index on scott.emp to monkey with grant option

6.使用with grant option选项

该选项用于转授对象权限，但是该选项只能被授予用户，而不能授予角色

Sql> conn scott/tiger;

Sql> grant select on emp to monkey with grant option;

Sql> conn monkey/m123;

Sql> grant select on scott.emp to anybody;

回收对象权限

在oracle9i中，收回对象的权限可由对象的所有者来完成，也可以由dba用户 (sys,system)来完成。

注意：收回对象权限后，用户就不能执行相应的sql命令，对象权限可以级联回收。

语法：revoke 对象权限 on 对象 from 用户；

1.定义

2.对象权限有哪些

如何赋给对象权限

系统权限

系统权限：用于控制用户可以执行的一个或者一组数据库操作。比如当用户具有create table权限时，可以在其他方案中建表；当用户具有create any table权限时，可以在任何方案中建表。Oracle提供了100多种系统权限。

常用的有：

Create session --连接数据库

Create view --建立视图

Create table --建表

Create procedure --建过程、函数、包

Create trigger --键触发器

Create cluster --键簇

Create public synonym --键同义词

显示系统权限

可以通过查询数据字典视图system_privilege_map;可以显示所有的系统权限：

Select * from system_privilege_map order by name;

授予系统权限

一般情况下，授予系统权限是由dba完成的，如果由其他用户来授予系统权限，则要 求该用户必须具有grant any privilege的系统权限；在授予时可以带with admin option选项，这样被授予系统权限的用户或角色可以将该系统权限授予其他用户户角 色。

SQL> create user ken identified by m123;

User created

SQL> grant create session,create table to ken with admin option;

Grant succeeded

SQL> grant create view to ken;

Grant succeeded

SQL> conn ken/m123;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as ken

回收系统权限

一般情况下，回收系统权限是dba来完成的，如果其他用户来回收系统权限，要求该 用户必须具有相应的系统权限以及转授系统权限的选项(with admin option)。回 收系统权限使用revoke命令来完成。系统权限是不能级联回收。

SQL> revoke create session from ken;

Revoke succeeded

商店售货系统设计案例：

现有一个商店的数据库，记录客户及其购物情况，有下面三个表组成：

商品goods（商品号goodsId，商品名goodsName，单价unitprice，商品类别category，供应商provider）；

客户表customer（客户号customerId，姓名name，住址address，电邮email，性别sex，省份证cardID）;

购买purchase（客户号customerId，商品号goodsId，购买数量nums）；

用sql语言完成下列功能:

1.建表，在定义中要求声明：

a) 每个表的主键

b) 客户的姓名不能为空

c) 单价必须大于0，购买数量必须在1~30之间

d) 电邮不能重复

e) 客户的性别必须是男或者女，默认为男

Goods表

SQL> create table goods(goodsId char(8) primary key,

2 goodsName varchar2(30),

3 unitprice number(10,2) check(unitprice >0),

4 category varchar2(8),

5 provider varchar2(30));

Table created

Customer表

SQL> create table customer(customerId char(8) primary key,

2 name varchar2(50) not null,

3 address varchar2(50),

4 email varchar2(50) unique,

5 sex char(2) default ‘男‘ check(sex in(‘男‘,‘女‘)),

6 cardId char(18) );

Table created

Purchase表

SQL> create table purchase( customerId char(8) references customer(customerId),

2 goodsId char(8) references goods(goodsId),

3 nums number(10) check(nums between 1 and 30) );

Table created

如果在建立表时忘记建立必要的约束，则可以在建表后使用alter table命令为表增加约束。但是注意，增加not null约束时，需要使用modify选项，而增加其他四种约束使用add选项。

2.修改表

a)每个表的主外码

b)客户的姓名不能为空；增加商品名也不能为空

SQL> alter table goods modify goodsName not null;

Table altered

c)单价必须大于0,购买数量必须在1~30之间

d)电邮不能重复；增加省份证不能重复

SQL> alter table customer add constraint cardUnique unique(cardId);

Table altered

e)客户的性别必须是男或者女，默认为男

f)增加客户的住址只能是海淀、朝阳、东城、西城、通州、崇文。

SQL> alter table customer add constraint addressCheck check(address in(‘东城‘,‘西城‘,‘海淀‘,‘朝阳‘,‘通州‘,‘崇文‘));

Table altered