1.1 创建和管理数据库用户账户
sys 查看数据库里有多少用户?
SQL> select distinct username from dba_users;
1.1.1 用户的缺省值表空间
每个用户账户都可以指定默认的表空间,用户创建的任何对象(如表或者索引)将缺省保存在此表空间中,如果创建用户时没有指定默认表空间,那么属于数据库级的默认表空间(DBCA创建数据库时默认是users表空间)将应用于该账户。
SQL> select * from database_properties; //查看数据库级的默认表空间
SQL> alter database default tablespace tablespace_name; //可以更改
1.1.2 空间配额的概念:
配额(quota)是表空间中为用户的对象使用的空间量,dba建立用户时就应该考虑限制用户的磁盘空间配额,否则无限制配额用户可能把表空间撑爆(甚至损坏SYSTEM表空间)。
ALTER USER tim QUOTA 10m ON test_tbs; --设置限额=10m
ALTER USER tim QUOTA --不受限制
ALTER USER tim QUOTA 0 ON test_tbs; --收回限额
1.2 管理概要文件(profile)
作用是对用户访问数据库做一些限制。有几个要点:
1) 概要文件(profile)具有两个功能,一个是实施口令限制,另一个是限制会话可以占用的资源。
2) 始终要实施口令控制,而对于资源限制,则只有实例参数RESOURE_LIMIT为TRUE时(默认是FALSE)才会实施。
3) 系统自动使用概要文件,有一个默认的default profile,限制很松,作用较小。
4)可以使用create profile 为用户创建它的概要文件,没有说明的参数就default profile 的当前版本中提取。
Password_parameter 部分:
Failed_login_attempts:指定在帐户被锁定之前所允许尝试登陆的的最大次数。
Password_lock_time:在到达 Failed_login_attempts 后锁定账户的天数。
Password_life_time:口令过期前的天数,口令在过期后仍可以使用,具体取决于
Password_grace_time
Password_grace_time:口令过期(将生成更改口令的提示)后第一次成功登录后的 天数,在此期间,旧口令仍然可用。 Password_reuse_time:可以重新使用口令前的天数
password_reuse_max:可以重新使用口令的次数
Password_verify_function:更改口令时运行的函数名,此函数一般用于检查新口令 所需的复杂程度。
Resource_parameter 部分
Session_per_user:对同一个用户账户执行的并发登录数。
Cpu_per_session:在强制终止会话前,允许会话的服务器进程使用的 CPU 时间(厘 秒)。
Cpu_per_call: 在强制终止某条 SQL 语句前,允许会话的服务器进程用于执行此 语句的 CPU 时间(厘秒)。 Connect_time: 在强制终止会话前,会话的最长持续时间(分钟)。
Idle_time: 在强制终止会话前,允许会话处于闲置状态的最长时间(分钟)。
Logical_reads_per_session: 在强制终止会话前,会话可读取的块数(无论块在数 据缓冲区还是磁盘)。 Logical_read_per_call: 在强制终止单个语句前,此语句可读取的块数(无论块 在数据缓冲区还是磁盘)。
Private_sga: 对于通过共享服务器体系结构连接的会话,允许会话的会话数据 在 SGA 中占用的字节数(考点)。 Composite_limit:前面几个参数的加权和。这是一个高级功能,其需要的配置不在 OCP 考试范围。
1)创建一个概要文件,如果出现两次口令失误,那么将账户锁定。
SYS@orcl> create profile two_error limit failed_login_attempts 2;
2)将概要文件分配给 psmax 用户
SYS@orcl> alter user psmax profile two_error;
SYS@orcl> select username,profile from dba_users where username = 'PSMAX';
3) psmax 尝试两次登录使用错误密码,系统报错ORA-28000错误。
4) sys 为psmax 解锁
SYS@orcl> conn / as sysdba
Connected.
SYS@orcl> alter user psmax account unlock;
5) sys删掉了two_error概要文件。
SYS@orcl> drop profile two_error cascade;
6) 删除two_error后,psmax用户又绑定到default profile 上。
//profile 这部分操作参数较多,使用命令有些啰嗦,可以使用 OEM 方式来管理,比较方便。
1.3 更改密码
sys 建立用户时给用户一个密码,用户的密码保存在数据字典中,用户登录再更改密码。这些密码是加密形式存在的,DBA也无法知晓。
SYS@orcl> select name,password from user$ where name = 'SCOTT';
sys 不知道用户密码,又想临时登录用户账户,但不想打扰用户。怎样做:
SYS@orcl> alter user scott identified by tiger;
1.4 系统权限,对象权限,角色
1.4.1 权限的引入:
数据库安排分为系统安全和数据安全
系统安全:用户名和口令,分配给用户的磁盘空间及用户的系统操作,如 profile 等等
数据库安全:对数据库对象的访问及操作
用户具备系统权限才能访问数据库。
具备对象权限才能访问数据库中的对象。
简而言之: 权限(privilege): system privilege and object privilege
1) system privilege : 针对于database 的相关权限。
系统权限通常由DBA授予(11g 有200多种,select distinct privilege from dba_sys_privs; 也可被其他用户或角色授予。)
典型DBA 权限
CREATE USER
DROP USER
BACKUP ANY TABLE
SELECT ANY TABLE
CREATE ANY TABLE
典型用户需要的系统权限
CREATE SESSION
CREATE TABLE
CREATE SEQUENCE
CREATE VIEW
CREATE PROCEDURE
2) object privilege : 针对于schema 用户的object
对象权限有8种: ALTER ,DELETE,EXECUTE,INDEX,INSERT,REFERENCES,SELECT,UPDATE
对象权限 表 视图 序列 过程
----------------------------------------------
ALTER * *
DELETE * *
EXECUTE *
INDEX *
INSERT * *
REFERENCES *
SELECT * * *
UPDATE * *
1.4.2 权限的授权
授予系统权限语法:
GRANT sys_privs,[role] TO user|role|PUBLIC [WITH ADMIN OPTION]
//授予角色与系统权限的语法格式是一样的,所以可以并列在一个句子里赋权
授予对象权限语法
GRANT object_privs ON object TO user|role|PUBLIC [WITH GRANT OPTION]
创建和删除角色 CREATE role myrole; DROP role myrole;
1.4.3角色的引入:
系统权限太繁杂,将系统权限打包成角色,Oracle 建议通过角色授权权限,目的就是为了简化用户访问管理。
sys:
SYS@orcl> create user psmax identified by psmax;
SYS@orcl> conn psmax/psmax
Connected.
PSMAX@orcl> select * from tab;
no rows selected
Elapsed: 00:00:00.02
PSMAX@orcl> create table a(id int);
create table a(id int)
*
ERROR at line 1:
ORA-01031: insufficient privileges
Elapsed: 00:00:00.13
PSMAX@orcl>
PSMAX@orcl> conn / as sysdba
Connected.
SYS@orcl> grant create table to psmax;
Grant succeeded.
Elapsed: 00:00:00.01
SYS@orcl> create table a(id int);
Table created.
Elapsed: 00:00:01.03
SYS@orcl>
sys: SQL> grant unlimited tablespace to psmax; // 授予 psmax系 统 权 限 unlimited tablespace,可以无限制的使用任何表空间
SQL> alter user psmaxquota 5m on users; //仅对于使用 users 表空间加上了磁盘 限额。
psmax:
SQL> create table a (id int);
表已创建。
SQL> select * from session_privs; //这个语句最常用,但其中不包括该用户的对 象权限(考点)
列出Oracle 所有系统权限:
SYS@orcl> drop user tim cascade;
User dropped.
Elapsed: 00:00:04.34
SYS@orcl> create user tim identified by tim;
User created.
Elapsed: 00:00:02.37
SYS@orcl> grant connect,resource to tim;
Grant succeeded.
Elapsed: 00:00:00.05
SYS@orcl> select * from session_privs;
PRIVILEGE
----------------------------------------
ALTER SYSTEM
AUDIT SYSTEM
CREATE SESSION
ALTER SESSION
RESTRICTED SESSION
CREATE TABLESPACE
ALTER TABLESPACE
MANAGE TABLESPACE
DROP TABLESPACE
UNLIMITED TABLESPACE
CREATE USER
BECOME USER
ALTER USER
DROP USER
需要注意两点:
1) 在resource 角色里包含了ulimited tablespace 系统权限,意思是不限制用户使用任何表空间,此权限太大,它包括可以访问system 表空间,在实际中一般要将此权限收回。
2) sys将resource 角色授权给用户,其中有9个系统权限,只有一个ulimited tablespace 可以单独收回(从角色收回系统权限的特列)
SYS@orcl> create tablespace test_tbs datafile '/home/oracle/app/oradata/orcl/test01.dbf'size 10m;
SYS@orcl> drop user tim;
User dropped.
Elapsed: 00:00:23.45
SYS@orcl> drop uder tim cascade;
drop uder tim cascade
*
ERROR at line 1:
ORA-00950: invalid DROP option
Elapsed: 00:00:00.01
SYS@orcl> create user tim identified by tim default tablespace test_tbs;
User created.
Elapsed: 00:00:00.59
SYS@orcl> grant connect,resource to tim;
Grant succeeded.
Elapsed: 00:00:00.03
SYS@orcl> revoke unlimited tablespace from tim;
Revoke succeeded.
Elapsed: 00:00:00.03
SYS@orcl> alter user time quota 10m on test_tbs;
alter user time quota 10m on test_tbs
*
ERROR at line 1:
ORA-01918: user 'TIME' does not exist
Elapsed: 00:00:00.02
SYS@orcl> alter user tim quota 10m on test_tbs;
User altered.
Elapsed: 00:00:00.05
SYS@orcl> select tablespace_name,username,max_bytes from dba_ts_quotas where username = 'TIM';
TABLESPACE_NAME USERNAME MAX_BYTES
------------------------------ ------------------------------ ----------
TEST_TBS TIM 10485760
Elapsed: 00:00:00.31
SYS@orcl>
1.4.4 几个有关权限的考点。
1) sys,system拥有普通用户的所有对象权限,并有代理授权资格。
2) 系统权限里的any含义:
sys:
SQL> grant create any table to tim;
tim:
SQL> create table scott.t100 (id int);
1.4.5 几个有关权限的考点
1)sys, system 拥有普通用户的所有对象权限,并有代理授权资格。
2)系统权限里的 any 含义: sys: SQL> grant create any table to tim;
tim: SQL> create table scott.t100 (id int);
3)对象权限一般由对象拥有者授予,也可以由 sys 或 system 代理授予。
sys: grant select on scott.emp to tim;
可以使 update 对象权限精确到列:
scott: SQL> grant select, update(sal) on emp to tim;
SQL> revoke update(sal) on emp from tim; revoke update(sal) on emp from tim
* 第 1 行出现错误: ORA-01750: UPDATE/REFERENCES 只能从整个表而不能按列 REVOKE
SQL> revoke update on emp from tim;
授予 Tim 对 scott.emp 的所有对象权限
SQL> create all on scott.emp to tim;
几个需要注意的地方:
系统权限和对象权限语法格式不同,不能混合使用 grant create table,select on emp to tim 错(考点) 系统权限和角色语法相同可以并列授权 grant connect,create table to tim 对 可以一条语句并列授予多个用户 grant connect to tim,ran 对 可以通过授权建立用户,如 ran 用户不存在, grant connect,resource to ran identified by ran; 对
4)系统权限的传递与回收:WITH ADMIN OPTION 选项
sys: 先建立两个测试用户 tim 和 ran
CREATE USER tim IDENTIFIED BY tim;
CREATE USER ran IDENTIFIED BY ran;
GRANT create session TO tim WITH ADMIN OPTION;
tim: GRANT create session TO ran 收回 tim 系统权限,ran 的系统权限没有收回!
sys: REVOKE create session FROM tim;
conn tim/tim 可以成功
5)对象权限的传递与回收:WITH GRANT OPTION 选项
scott:为 tim 用户授权
GRANT SELECT ON emp TO tim WITH GRANT OPTION; tim: GRANT SELECT ON scott.emp TO ran;
检查 tim 和 ran 都能否访问 scott.emp ....
scott:回收 tim 对象权限 REVOKE SELECT ON emp FROM tim;
检查 tim 和 ran 都不能访问 scott.emp 了, ran 的对象权限也收回!
1.4.6 对象权限在存储过程中的使用
SCOTT 将存储过程proc1的execute 权限赋给tim,proc1中包含了一些tim没有权限的DML操作,那么tim能成功地执行存储过程proc1吗?这个问题涉及到了create procedure时invoker_rights_clause的两个选项:
1,AUTHID CURRENT_USER // 执行存储过程时,要检查用户是否有DML操作的对象权限。
2,AUTHID DEFINER(默认)
测试:
SYS@orcl> drop table scott.a;
Table dropped.
Elapsed: 00:00:00.21
SYS@orcl> create table scott.a(d1 date);
Table created.
Elapsed: 00:00:00.05
SYS@orcl> grant connect,resource to tim identified by tim;
Grant succeeded.
Elapsed: 00:00:00.04
SYS@orcl>
SCOTT@orcl> conn tim/tim;
Connected.
TIM@orcl> show user
USER is "TIM"
TIM@orcl> conn scott/tiger;
Connected.
SCOTT@orcl> create or replace procedure proc1 as
2 begin
3 insert into scott.a values(sysdate);
4 end;
5 /
Procedure created.
Elapsed: 00:00:00.99
SCOTT@orcl> grant execute on proc1 to tim;
Grant succeeded.
Elapsed: 00:00:00.08
SCOTT@orcl> conn tim/tim
Connected.
TIM@orcl> exec scott.proc1;
PL/SQL procedure successfully completed. //有执行存储过程的权限,但没有 insert 对象权 限,竟然也能执行成功。
Elapsed: 00:00:00.22
TIM@orcl>
scott: //使用 invoker_rights_clause 加入 AUTHID CURRENT_USER 参数再试试。
TIM@orcl> conn scott/tiger
Connected.
SCOTT@orcl> create or replace procedure proc1 AUTHID CURRENT_USER
2 as
3 begin
4 insert into scott.a values(sysdate);
5 commit;
6 end;
7 /
Procedure created.
Elapsed: 00:00:00.31
SCOTT@orcl> conn tim/tim
Connected.
TIM@orcl> exec scott.proc1;
BEGIN scott.proc1; END;
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SCOTT.PROC1", line 4
ORA-06512: at line 1
Elapsed: 00:00:00.04
TIM@orcl> conn scott/tiger
Connected.
SCOTT@orcl> grant all on a to tim;
Grant succeeded.
Elapsed: 00:00:00.01
SCOTT@orcl> conn tim/tim
Connected.
TIM@orcl> exec scott.proc1;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.05
TIM@orcl>
1.4.7 与权限有关的数据字典。
SESSION_PRIVS //用户当前会话拥有的系统权限
USER_ROLE_PRIVS //用户被授予的角色
ROLE_SYS_PRIVS //用户当前拥有的角色的系统权限
USER_SYS_PRIVS //直接授予用户的系统权限
USER_TAB_PRIVS //授予用户的对象权限
ROLE_TAB_PRIVS //授予角色的表的权限
练习: 要掌握权限与角色的关系,以及如何查看信息,
数据字典 dba_xxx_privs all_xxx_privs user_xxx_privs
其中 xxx:role 表示角色,sys 表示系统权限,tab 表示对象权限。
从哪个角度看,非常重要! 我们举个例子:三个用户,分别是 sys,scott,和 tim, sys: 1)建立 myrole 角色,把 connect 角色和 create table 系统权限以及 update on scott.emp 对象权限放进 myrole。 2)把 myrole 角色授给 tim。 3)把 create table 系统权限授给 tim。
scott: 把 select on emp 表的对象权限授给 tim
如此 tim 用户有了如下角色和权限:
myrole(connect,create table,update on scott.emp) create table select on emp
我们从三个角度分析一下,如何里数据字典里查看 tim 拥有的角色和权限信息。
从 dba 角度看:
看用户 tim 所拥有的系统权限 select * from dba_sys_privs where grantee='TIM';
看用户 tim 所拥有的对象权限 select * from dba_tab_privs where grantee='TIM';
看用户 tim 所拥有的角色(不包含角色里的角色)
select * from dba_role_privs where grantee='TIM';
查看这个角色里包含的角色 select * from dba_role_privs where grantee='MYROLE';
查看这个角色里包含的系统权限 select * from dba_sys_privs where grantee='MYROLE';
查看这个角色里包含的对象权限 select * from dba_tab_privs where grantee='MYROLE';
从当前用户角度看:
查看和自己有关的角色(不含角色中含有的角色) select * from user_role_privs;
查看和自己有关的系统权限(不含角色中的系统权限) select * from user_sys_privs;
查看和自己有关的对象权限(不含角色中的对象权限)
select * from user_tab_privs;
角色里包含的角色 select * from role_role_privs;
角色里包括的系统权限 select * from role_sys_privs;
角色里包括的对象权限 select * from role_tab_privs;
查看和自己有关的系统权限(包括角色里的权限) select * from session_privs;
从 scott 用户看是个什么情况
select * from all_tab_privs where grantee='TIM';
select * from all_tab_privs where table_name='EMP';