五、方案-用户-角色和权限

1、方案和用户关系

1）当在创建一个用户之后，oracle会自动给你创建一个方案，

备注：oracle是以方案的形式来管理你的数据项的，而方案的名字和你所创建的用户名一模一样

2）方案里面有很多的数据对象，表、视图、触发器、存储过程，是以方案的形式来管理数据对象的

sys用户--对应-->sys方案（包含基表、动态视图）拥有dba（数据库管理员角色）

sysdba（系统管理员角色），sysoper（系统操作员角色）角色或者说权限是oracle权限最高的用户

system用户--对应-->system方案（包含次级数据）拥有dab(数据库管理员角色)，sysdba（系统管理员角色） 这两个角色，但是没有sysoper（系统操作员角色）

 

2、权限概念：

权限分为两种：一个叫做对象权限（针对数据对象而言的），一个叫做系统权限（针对数据库而言的）。

系统权限：是指用户对数据库的相关权限，建库、建表、建索引、存储、修改密码

对象权限：用户对其他用户的数据对象，操作的权限

 

 

3、|用户、权限操作实例|

创建用户、赋予权限

--创建用户(一般是具有dba数据库管理员的权限才能创建)

conn system/manager;

create user xiaoming identified by m123;

 

--删除用户

drop user zhangjunbo cascade;

 

--查看当前用户

show user;

 

--授予权限：通过角色进行批量授权

grant  connect to zhangjunbo;

connect 是一种角色（角色是权限的批量授权）

dba角色（几乎拥有数据库的所有权限）

resource角色（可以使该用户在任何一个表空间中建表）

 

--赋予用户的系统权限：(用户创建之后,DBA会赋予用户一些系统权限)

开发者一般具有如下系统权限：

create session(创建会话)

create table(创建表)

create sequence(创建序列)

create view(创建视图)

create procedure(创建过程)

 

--授予另外一个用户对本用户对象相关的操作的权限：

grant select on emp to xiaoming

grant update on emp to xiaoming

grant all on emp to xiaoming

 

--收回另外一个用户对本用户表的操作的权限：

revoke select on scoot.emp from xiaoming 

 

--授予系统相关的权限,并允许其将权限继续给其他人：

grant connect to xiaoming with admin option;

 

--修改密码

alter user zhangjunbo identified by zjb123;给另一用户或者当前用户修改密码

password zhangjunbo;给当前用户修改密码

 

--显示所有数据库用户的详细信息

select * from dba_users;

备注：在建立用户时，oracle会把用户的信息存放到数据字典中，当给用户授予权限或是角色时，

oracle会将权限和角色的信息存放到数据字典，通过查询dba_users 可以显示所有数据库用户的详细信息；

 

--查询oracle中所有的系统权限，一般是dba

select * from system_privilege_map order by name;--不常用

 

--查看系统权限信息（GRANTEE、PRIVILEGE、ADMIN_OPTION）

select * from  dba_sys_privs;

select * from dba_sys_privs where grantee='ZHANGJUNBO';查看用户被授予那些权限

select * from dba_sys_privs where privilege='CREATE SESSION';查看那些用户被授予了create session的权限

 

--显示用户具有的对象权限（系统用户或者dba用户才有权限进行查询）

select * from  dba_tab_privs;

 

--显示用户所具有的角色

select * from dba_role_privs;

 

--查询oracle中的所有角色，一般是dba

select * from dba_roles;

 

--查询数据库的表空间

select tablespace_name from dba_tablespaces;

 

--查看某个用户具有怎样的角色

select * form dba_role_privs where grantee='用户名';

 

--查看某个角色包含哪些系统权限

select * from dba_sys_privs where grantee='DBA';

或则是select * from role_sys_privs where role='DBA';

 

--查看某个角色包含的对象权限

select * from dba_tab_privs where grantee='角色名';

 

--显示当前用户可以访问的所有数据字典视图

select * from dict where comments like '%grant%'

 

--显示当前数据库的全称

select * from global_name；   --其实global_name 也是个视图；

 

--连接

conn zhangjunbo/zjb123;

conn system/zjb123 as sysdba;

备注：system无作为sysoper进行登录的权限，否则会报：insufficient privileges

 

--断开连接

disc zhangjunbo;断开与数据库的连接，但不会退出sql*plus

exit zhangjunbo;断开与数据库的连接，同时会退出sql*plus

 

--文件编辑命令

edit d:\zjb.sql;

 

--运行本地文件

方法一：@d:\zjb.sql;

方法二：start d:\zjb.sql;

 

--将sql*plus屏的内容输出到指定的文件中去

spool d:zjb_student.sql

select * from student

spool off;

 

--设置页面

show linesize;设置显示行的宽度

show pagesize;设置每页显示的行数

 

4、|系统权限和对象权限的创建、删除|

常用的系统权限有：

create session     连接数据库                                    create table 建表

create view         建视图                                          create public synonym     建同义词

create procedure 建过程、函数、包                            create trigger  建触发器

create cluster      建族

 

如何显示系统权限：

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

select * from system_privilege_map order by name;

1）创建两个用户ken，tom 初始阶段他们没有任何权限，如果登录就会给出错误的信息；

create user ken identified by m123; --m123是密码

create user ten identified by m123; --m123是密码

conn system/manager as sysdba;

 

2）给用户ken授权

A、grant create session,create table to ken with admin option;

如果后面加有with admin option;表名允许ken用户继续将create session和create table这两个权限继续往下分；

 

B、grant create view to ken;

conn ken/m123;--现在肯定可以登录了；

 

3)给用户tom授权

show user --结果显示为ken,然后用ken 给tom授予权

grant create session,create table to tom with admin option;

--用ken给tom授予create session、create table 这两个权限会成功；

 

grant create view to tom;

--用ken 给tom授予create view 会授权失败，因为在给ken授予create view权限是后面并没有加with admin option;

 

4）回收系统权限：

回收系统权限是dba来完成的，如果其它的用户来回收系统权限，要求该用户必须具有相应系统权限及转授系统权限的选项；

回收系统权限使用revoke来完成；--revoke 撤回；

当回收了系统权限后，用户就不能执行响应的操作了

system-------------->ken--------------->tom

create session   create session   create session

 

用system执行如下操作：

revoke create session from ken;请思考tom 还能够登录吗？还可以登录，说明系统权限不是级联回收的；

 

步骤1、先切换到dba用户

conn system/manager as sysdba;

步骤2、撤销ken的create session权限

revoke create session from ken;

备注：虽然撤销了ken的连接数据库的权限，但是tom仍然还是可以登录的conn tom/m123;

 

对象权限：

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

比如Smith 用户要访问 scott.emp表（scott:方案，emp:表）就必须在scott,emp表上具有对象的权限；

scott.emp表示 scott方案的emp表；

注意在不同的表空间上面可以建相同的表明的表；

 

--对象权限有哪些：

常用的有：

alter 修改                          delete 删除                       select 查询

insert 添加                        update 修改                      index  索引

references 引用               execute执行

--显示对象权限：

通过数据字段视图可以显示用户或是角色所具有的对象权限，视图为 dba_tab_privs

conn system/manger

select distinct privilege from dba_tab_privs;

select grantor,owner,table_name,privilege from dba_tab_privs where grantee='BLAKE'

select grantor,owner,table_name,privilege from dba_tab_privs where grantee='SYS';

 

 

/*如果是系统权限往下面派发用的是 with admin option;

如果是对象权限往下面派发用的是 with grant option;*/

 

怎么赋值给对象权限：

在oracle9i之前，授予对象权限是由对象的所有者来完成的，如果用其他的用户来操作，则需要用户

具有相应的（with grant option）权限，从oracle9i开始dba用户（sys,system）可以将任何对象上的

对象对象权限授予其他用户，授予对象权限用grant命令来完成。

 

对象权限可以授予用户，角色和public,在授予对象权限时，如果带有with grant option 选项，

则可以将该权限转授给其他用户，但是要注意的是with grant option 选项不能被授予角色；

 

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

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

首先要用dba用户进行登录

conn system/manager as sysdba;

create user monkey identified by m123;   --identified 鉴定、识别

grant create session to monkey;

conn monkey/m123;

 

grant select on scott.emp to blake with grant option;

 

方法一：用scott本人对monkey进行查询的授权

conn scott/m123;

grant select on emp to monkey;

select *　from emp;--是查询不到结果的；

select * from scott.emp;--可以查询出来；

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

grant update on emp to monkey;

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

grant delete on emp to monkey;

4）有没有更简单的方法，一次把所有的权限赋给monkey？

grant all on emp to monkey;

 

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

1）希望monkey 只可以修改scott.emp标的sal字段，怎样操作？

grant update on emp(sal) to monkey;

 

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

grant select on emp(ename,sal) to monkey

 

3、授予alter权限：

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

conn scott/tiger

grant alter on emp to blake;

当然也可以用system,sys来完成这件事；

 

4、授予execute权限

如果用户想要执行其他方案的包/过程/函数，则须有execut 权限，比如为了让ken

可以执行包dbms_transactions,可以授权execute 权限

conn system/manager

grant execute on dbms_transaction to ken;

 

5、授予index权限：

如果想在别的方案的表上建立索引，则必须具有index对象权限，

如果为了让black可以在scott.emp上建立索引，就给其index的对象权限

conn scott/tiger;

grant index on scott.emp to blake;