oracleday15(授予对象权限级联回收 角色 pl/sql基本概念)

授予对象权限 基本语法

grant 对象权限 on 方案.数据对象  to 用户[角色] [with grant option]  角色 不能转发 不是人 带with grant option 没什么意义

grant 对象权限 on 方案.数据对象  to 角色]

可以把权限直接赋给用户 或角色 

如果要操作scott.emp 表

授予查询权限 使用scott 或者system/sys用户来操作

grant select on scott.emp to monkey

授予修改权限

grant update on scott.emp to monkey

授予删除权限给monkey

grant delete on scott.emp to monkey

一次把所有权限 crud 授予

grant all on scott.emp to monkey

alter 权限 修改scott.emp的结构

grant alter on scott.emp to black;

授予execute权限

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

授予index权限

如果想要在别的方案的标上建立权限 则必须有index对象权限

grant index on scott.emp to black;

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

回收对象权限

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

收回对象权限后 用户就不能执行相应的sql命令 但是要注意的是对象的权限 会被级联回收

比如借一个人东西 那个人再借给别人  主人找回 物品后 别的人都不能再用了

基本语法 

revoke 对象权限 on 方案.数据对象 from 用户

对象的权限是级联回收

scott ——>black ——>jones

system用户操作

create user black identified by m123;

create user jones identified by m123;

grant create session to black; (能登录)

grant create session to jones;

scott 登录 完成如下操作

把对emp的操作权力给blake 

grant select on emp to black with grant option;

使用black 把权限给jones;

grant select on scott.emp to jones; 不带用户名就把自己的给jones了

现在使用scott 将查询权限从 black上回收

revoke select on scott.emp from black;

此时black 和jones  都不能再对scott.emp进行查询

说明 对象权限是级联回收的

角色  

角色就是相关权限的命令集合 使用角色的主要 目的就是为了简化权限的管理

角色是一组权限的 集合 目的是为了简化对权限的管理 从而达到对用户管理的简化

为什么需要角色

假设要让1.2.3用户都拥有权限

1、连接数据库

2、在scott.emp 表上 select,insert,update 

使用自定义角色完成

create role myrole not identified

给角色赋予权限(角色可以有系统和对象权限)

grant create session to myrole;

grant select on scott.emp to myrole;

grant insert on scott.emp to myrole;

grant update on scott.emp to myrole;

这时我们可以把myrole 这个自定义角色赋给指定的某个用户 比方说 

crate user  aa identified by m123;

grant myrole to aa;

预定义角色

select * from dba_roles; 查看有多少预定义角色

select * from dba_role_privs where grantee='AAA';  查看AAA用户 所具有的角色 用户名要大写

预定义角色是指oracle所提供的角色 每种角色都用于执行一些特定的管理任务 下面 我们介绍常用的预定义角色 connect resource dba

connect 角色具有一般应用开发人员需要的大部分权限 只要给用户授予connect 和resource 角色就够了connect角色具有哪些系统权限？

create session

resource 角色 具有建立存储过程 、触发器等 需要注意的是resouce角色隐含unlimited tablespace 系统权限 包含下面权限

create cluster create indextype create table create sequence create type create procedure create trigger 

dba角色 具有所有的系统权限 及 with admin option 选项 默认的dba用户为sys和system 他们可以将任何系统权限授予其他用户 

但是要注意的是dba角色不具备(启动和关闭数据库)

把某个角色赋给某个用户的基本语法  多个角色 可以逗号隔开

grant 角色 to 用户[with admin option]

创建一个用户 然后赋给CONNECT 角色

create user  aas identified by m123;

grant CONNECT to aas;

角色的分类 

1、预定义角色

oracle 提供了33中预定义角色 常用的是 (connect resource dba)

查看所有角色 select * from dba_roles;

角色可以包含系统权限 也可以包含对象权限 

如何知道某个角色具有怎样的权限

select * from dba_sys_privs where grantee='DBA' 角色名称要大写 不然查询不到(grantee 被授权) 查看角色拥有的权限

CONNECT 就一个 create session

2、自定义角色

oracle设计者 认为33种 预定义角色 可能不能满足所有的需求 所以可以使用自定义角色来解决问题  这样可以让管理员更加灵活

顾名思义就是自己定义的角色 根据自己的需要来定义 一般是dba来建立 如果用别的用户来建立 则需要具有create role 的系统权限 

在建立角色时可以指定验证方式(不验证，数据库验证等)

建立角色(不验证)(常用)

如果角色是公用的角色 可以采用不验证的方式建立角色  

create role 角色名 not identified

建立角色(数据库验证)

采用这样的方式时 角色名、口令存放在数据库中当激活该角色时 必须提供口令 建立这种角色时需要为其提供口令

create role 角色名 identified by xxx;

删除角色 

使用 drop role 一般由dba 来执行 如果其他用户则要求具有drop any role 系统权限

drop role 角色名  角色 授予的权限都消失了  预定义角色也能删除

显示角色信息

 显示所有角色 

select * from dba_roles;

显示角色具有的系统权限

select privilege,admin_option form role_sys where role = '角色名';

显示角色具有的对象权限

select * from dba_tab_privs where grantee='用户名';

角色名要大写

显示用户具有的角色 及默认角色

select granted_role,default_role from dba_role_privs where grantee ='用户名';

精细访问控制

是指用户可以使用函数、策略实现更加细微的安全访问控制 如果使用精细访问控制 则当在客户端发出sql语句(select insert update delete)

oracle 会自动在sql语句后追加调用(where子句) 并执行新的sql语句 通过这样的控制 可以使得不同的数据库在访问相同表时

返回不同的数据信息 

用户 scott blake jones

策略 emp_access

数据库表emp

如上图所示 通过策略emp_access 用户scott，black，jones 在执行相同的sql语句时 可以返回不同的结果 当执行select ename from emp;时

根据实际情况可以返回不同的结果 

oracle 的pl/sql 编程

基本概念  :pl/sql 是在标准sql语句基础上扩展的一种对oracle 数据库进行编程的语言 可以定义常量和变量 而且可以使用条件语句和循环语句

为什么需要pl/sql 

因为使用纯的sql 来操作数据库有技术缺陷

技术缺陷

技术缺陷
1、不能模块化编程 为了完成订单 可能要发出几条sql
2、执行速度
3、安全性问题
4、浪费带宽

解决方式 

使用pl/sql 来编写 过程 以提高效率

pl/sql (procedural language /sql) 是oracle 在标准的sql语言上的扩展 pl/sql不仅允许嵌入sql 语言 还可以定义变量和常量 

允许使用条件语句和循环语句 允许使用列外处理各种错误 这样使得它的功能变得更加强大

学习必要性

1、提高应用程序的运行性能

2、模块化的设计思想(分页的过程 订单的过程 转账的过程)

3、减少网络传输量

4、提高安全性

缺点 

移植性不好

sqlplus 开发工具

sqlplus 是oracle 公司提供的一个工具 

一个简单例子开发一个简单的存储过程 可以完成向某表添加一条记录

create procedure pro1 is 

begin insert into emp (empno,ename) values(44,'44');

end;

/

如何调用过程

1、控制台调用

exec pro1;

exec 过程名 (参数1 , 参数2)

2.java程序

下次说

pl/sql devloper 开发工具

pl/sql developer 是用于开发pl/sql 块的集成开发环境(ide) 他是一个独立的产品 而不是oracle 的附带品

例子

编写一个存储过程 该过程可以删除某条记录  可以接受输入参数的简单过程

create procedure pro2(in_empno number) is   //先变量名称后变量类型

begin delete from emp where empno=in_empno;

end;

/

pl/sql 基础知识 介绍 

开发人员使用pl/sql 编写应用模块时 不仅需要掌握 sql语句的编写方法 还要掌握pl/sql 语句及语法规则 pl/sql编程 可以使用变量和逻辑控制语句

从而可以编写非常有用的功能模块

比如:分页存储过程模块 订单处理存储过程模块、转账存储过程模块 而且如果使用pl/sql编程 可以轻松完成非常复杂的查询要求

块 (编程):1、 过程(存储过程) 2、函数 3、触发器4、 包

利用pl/sql 可以开发过程、函数、包(包体) 、触发器

它们的基本编程单元是块

编写规范

1、注释

单行注释 - - 两个- 合在一起为了明显点 分开

多行注释

/*...*/ 来划分

2、标识符号耳朵命名规范

  1、当定义变量时 建议用v_ 作为前缀 v_sal

2、当定义常量时 建议用c_作为前缀i c_rate  constant

3、当定义游标时 建议用 _cursor 作为后缀 emp_cursor

  4、当定义例外时 建议用e_作为前缀 e_error

块结构是示意图

pl/sql 块由三个部分构成 定义部分、执行部分、例外处理部分 如下所示

declare

定义部分 定义常量、变量、游标、例外、复杂数据类型  其他地方不能定义 比较严格

begin 

执行部分 要执行的pl/sql 语句和sql语句 

exception

例外处理部分 处理运行的各种错误 捕获例外

end;

定义部分是从declare开始 该部分是可选的执行部分是从begin开始的 该部分是必须的 例外处理部分是从exception开始的 该部分是可选的

dbms_output  是 oracle 所提供的包 (类似java的开发包) 该包包含一些过程 put_line 就是dbms_output 包的一个过程

只包含执行部分的案例

 begin 

dbms_output.put_line('hello,world'); //默认是不输出的

end;

/

set serveroutput on //打开输出

定义变量的类型 

declare

v_name varchar2(8); //要定义大点不然放不进去

begin

select ename into v_ename from emp where empno=&empno;  把查询到ename值 放到v_ename zhong 

dbms_output.put_line('雇员名是'|| v_ename)

end;

/块 

改为过程

create procedure pro4(in_empno number) is

--定义变量的格式是 变量名称 变量的类型

v_ename varchar(8)

begin

--把查询到的ename值放入 v_ename 变量 

select ename into v_enmae from emp where empno=in_empno;

--输出v_ename

dms_output.put_line('雇员名是'||v_ename);

end;

/