Oracle 用户,角色,权限等
权限管理是 Oracle 系统的精华,不同用户登录到同一数据库中,可能看到不同数量的表,拥有不同的权限。Oracle 的权限分为系统权限和数据对象权限,共一百多种,如果单独对用户授权,很囧,有一些用户需要的权限是相同的,就把这些用户归为同一类——某种角色,通过设立一些有预定权限的角色简化和明确授权操作,角色出现的动机也就是为了简化权限管理,它是权限的集合。一般做法是:系统把权限赋给角色,然後把角色赋给用户,当然也可以直接把某权限赋给用户。Oracle 提供细粒度的权限,可以对表的某一列单独设置权限,可以对某用户查询某表自动增添 where 限制条件。
相关查询
Oracle 的角色存放在表 dba_roles 中,某角色包含的系统权限存放在 dba_sys_privs 中,包含的对象权限存放在 dba_tab_privs 中。
下面是“角色”、“用户”和“权限”的互查:
1. 用户
a. 查询用户列表。TODO
b. 查询某用户的角色。如:
select * from dba_role_privs where grantee='SCOTT'
如果是直接执行:
select * from role_sys_privs;
将得到当前用户的所有角色和系统权限。
c. 查询某用户的权限。
select * from dba_tab_privs where grantee='ZHANGSAN'; -- 对表对象的权限
select * from dba_sys_privs where grantee='ZHANGSAN'; -- 系统权限
2. 角色
角色有两类: 预定义角色和自定义角色。
a. 查询角色列表。可用如下 SQL
select * from dba_roles;
select distinct(granted_role) from dba_role_privs; -- 需要帐号 system 登录才能查看到。
b. 查询角色具有的权限。如要查询角色 RESOURCE 具有的全部权限可用如下 SQL:
select privilege, grantee from dba_sys_privs where grantee='RESOURCE'
union
select privilege, grantee from dba_tab_privs where grantee='RESOURCE';
c. 查询具有某角色的用户。TODO
3. 权限
权限分两大类:
- 系统权限:用户对数据库的一些权限:建库,建表,建索引,修改密码等等,
- 对象权限:用户 对其他用户的数据对象操作的权限。
a. 查询权限列表。因为角色 DBA 具有整个数据库系统所有的权限,所以如果要查询整个系统有哪些权限,也可以通过执行如下 SQL 得到:
select * from role_sys_privs where role='DBA'; -- 这个查询只得到 137 条记录,奇怪,和下面的不同。如果是通过普通用户如SCOTT登录执行此查询,将只看到他本人具有的全部系统权限。
select * from system_privilege_map order by name; -- 在 Oracle 9i2 中,是157条记录。
b. 查询具有某权限的角色。TODO
c. 查询具有某权限的用户。可以通过 dba_tab_privs 和 dba_sys_privs 查询。
自定义权限
创建角色
SQL> create role helloworldrole not identified;
授予一个系统权限
SQL> grant create session to helloworldrole with admin option;
授予一个对象权限
SQL> grant select on scott.emp to helloworldrole;
把角色授予给一用户
SQL> grant helloworldrole to zhangsan;
删除权限
SQL> drop role helloworldrole;
(权限删除後,前面授权过的用户 zhangsan 不再具有 create session 和 select on scott.emp 的权限了。删除权限操作,需要具有 delete any role 的权限。)
用户存亡
创建用户。需要dba权限的用户才能操作的。 Oracle 用户的密码必须以字母开头,
create user zhangsan identified by hello; -- 创建一个名为 zhangsan 的用户,密码是 hello
创建好後,还不能直接连接数据库,需要授权:
grant connect to zhangsan;有关授权的具体内容,参看本页下一节【权限分配】。
用户被授予 CONNECT 权限後,就可以连接数据库了:
conn zhangsan/hello;
修改用户密码。如果给自己修改密码可以在控制台执行命令: passw 或 password
如果管理员要修改别人的密码,可以在控制台执行命令: password 用户名
删除用户: drop user XXX 。注意,自己无法删除自己。
删除用户时,如果被删除的用户已经创建的有表,就需要带一个参数 cascade,就是指定删除时将创建的表都删掉。
权限分配
授权。可以一次授予多个角色,如:
grant connect, resource to umims;
一般用户分配 connect, resource 就可以建立连接,创建表并对表进行所有操作了,resource 这个角色可以在任意表空间建表。角色 DBA 拥有所有权限,授予时需谨慎。系统权限一般由 DBA 操作。
对象权限有这么几种:select, insert, update, deleted, all, create index, and etc..
grant select on emp to zhangsan; -- 这句可以在 scott 用户登录後执行。
grant select on scott.emp to zhangsan; -- 这句可以在 system 用户登录後执行,这里的 scott 是方案名。
权限的传递
希望 zhangsan 用户有查询 scott.emp 的权限,还希望张三能把这个权限传递给别人。
-- 如果是对象权限,在授权语句後加上 with grant option,带有 with grant option 的,不能授权给角色。
grant select on emp to zhangsan with grant option -- 可选的权限有 select, update, delete, insert, all
grant select(col1, col2) on emp to zhangsan ... -- 可以更细粒度
grant update(col2) on ... -- 可以更细粒度
-- 如果是系统权限,在授权语句後加上 with admin option
grant connect to zhangsan with admin option
注意,sys/system 两用户可以操作任何用户的对象权限。
回收权限
revoke update on emp from zhangsan;
谁 grant 的,谁收回,否则报错,但似乎 scott 赋给 zhangsan 的权限,system 无法收回,但反之可以(TODO)。 对象权限的回收,是级联的,而系统权限不是。
resource 角色包含了 unlimited table space 权限,dba 角色不具备 sysdaba 和 sysoper 的特权:启动和关闭数据库。