OCA-UNIT6-USERS

OCA 第六章主要实验

一：外部验证：
SQL> show parameter os_authent_prefix
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix string ops$
SQL> create user ops$oracle identified externally;
SQL> conn /
Connected.
SQL> show user
USER is "OPS$ORACLE"
二：权限
（1）
开启ISQLPLUS
[root@ocpdb ~]# su - oracle
[oracle@ocpdb ~]$ isqlplusctl start
iSQL*Plus 10.2.0.1.0
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Starting iSQL*Plus ...
iSQL*Plus started.
（2）
查询与权限相关的视图
select * from dict d where d.table_name like '%PRIV%';
TABLE_NAME COMMENTS
USER_COL_PRIVS Grants on columns for which the user is the owner, grantor or grantee
ALL_COL_PRIVS Grants on columns for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee
DBA_COL_PRIVS All grants on columns in the database
USER_COL_PRIVS_MADE All grants on columns of objects owned by the user
ALL_COL_PRIVS_MADE Grants on columns for which the user is owner or grantor
USER_COL_PRIVS_RECD Grants on columns for which the user is the grantee
ALL_COL_PRIVS_RECD Grants on columns for which the user, PUBLIC or enabled role is the grantee
USER_ROLE_PRIVS Roles granted to current user
…….
（3）
列出系统权限
select * from dba_sys_privs;
GRANTEE PRIVILEGE ADMIN_OPT
DBA CREATE SESSION YES
DBA ALTER SESSION YES
DBA DROP TABLESPACE YES
DBA BECOME USER YES
DBA DROP ROLLBACK SEGMENT YES
DBA SELECT ANY TABLE YES
DBA INSERT ANY TABLE YES
DBA UPDATE ANY TABLE YES
DBA DROP ANY INDEX YES
DBA SELECT ANY SEQUENCE YES
…….
（4）
列出对象权限
select * from dba_tab_privs;
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY
CTXSYS SYS ICOL$ SYS SELECT YES NO
OLAPSYS SYS CON$ SYS SELECT YES NO
CTXSYS SYS CON$ SYS SELECT YES NO
CTXSYS SYS IND$ SYS SELECT YES NO
OLAPSYS SYS COL$ SYS SELECT YES NO
CTXSYS SYS COL$ SYS SELECT YES NO
CTXSYS SYS TS$ SYS SELECT YES NO
OLAPSYS SYS CCOL$ SYS SELECT YES NO
CTXSYS SYS CCOL$ SYS SELECT YES NO
OLAPSYS SYS USER$ SYS SELECT YES NO
XDB SYS USER$ SYS SELECT NO NO
CTXSYS SYS USER$ SYS SELECT YES NO
…….
（5）
验证系统权限是否级联
SQL> conn / as sysdba
Connected.
SQL> create user jeff identified by jeff account unlock;
User created.
SQL> create user emi identified by emi account unlock;
User created.
SQL> conn jeff/jeff
ERROR:
ORA-01045: user JEFF lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn emi/emi
ERROR:
ORA-01045: user EMI lacks CREATE SESSION privilege; logon denied
SQL> conn / as sysdba
Connected.
SQL> grant create session to jeff with admin option;
Grant succeeded.
SQL> conn jeff/jeff
Connected.
SQL> conn jeff/jeff
Connected.
SQL> show user
USER is "JEFF"
SQL> grant create session to emi;
Grant succeeded.
SQL> conn emi/emi
Connected.
SQL> conn / as sysdba
Connected.
SQL> revoke create session from jeff;
Revoke succeeded.
SQL> conn jeff/jeff
ERROR:
ORA-01045: user JEFF lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn emi/emi
Connected.
总结： 系统权限不级联
（6）
验证对象权限是否级联
先让JEFF再次拥有CREATE SESSION的权限
SQL> conn / as sysdba
Connected.
SQL> grant create session to jeff;
Grant succeeded.
SQL> conn jeff/jeff
Connected.
SQL> show user
USER is "JEFF"
SQL> select * from hr.employees;
select * from hr.employees
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> conn emi/emi
Connected.
SQL> select * from hr.employees;
select * from hr.employees
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> conn / as sysdba
Connected.
SQL> grant select on hr.employees to jeff with grant option;
Grant succeeded.
SQL> conn jeff/jeff
Connected.
SQL> select count(*) from hr.employees;
COUNT(*)
----------
107
SQL> grant select on hr.employees to emi;
Grant succeeded.
SQL> conn emi/emi
Connected.
SQL> select count(*) from hr.employees;
COUNT(*)
----------
107
SQL> conn / as sysdba
Connected.
SQL> revoke select on hr.employees from jeff;
Revoke succeeded.
SQL> conn jeff/jeff
Connected.
SQL> select count(*) from hr.employees;
select count(*) from hr.employees
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> conn emi/emi
Connected.
SQL> select count(*) from hr.employees;
select count(*) from hr.employees
*
ERROR at line 1:
ORA-00942: table or view does not exist
三： 角色
（1）
验证角色（内仅包含系统权限）是否级联
SQL> conn / as sysdba
Connected.
SQL> drop role role1;
Role dropped.
SQL> create role role1;
Role created.
SQL> grant create session to role1;
Grant succeeded.
SQL> revoke create session from jeff;
Revoke succeeded.
SQL> revoke create session from emi;
Revoke succeeded.
SQL> conn jeff/jeff
ERROR:
ORA-01045: user JEFF lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn emi/emi
ERROR:
ORA-01045: user EMI lacks CREATE SESSION privilege; logon denied
SQL> conn / as sysdba
Connected.
SQL> grant role1 to jeff with admin option;
Grant succeeded.
SQL> conn jeff/jeff
Connected.
SQL> show user
USER is "JEFF"
SQL> grant role1 to emi;
Grant succeeded.
SQL> conn emi/emi
Connected.
SQL> revoke role1 from jeff;
Revoke succeeded.
SQL> conn jeff/jeff
ERROR:
ORA-01045: user JEFF lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn emi/emi
Connected.
总结： 当角色里面包含的是系统权限，它是不级联的。
（2）
验证角色（内仅包含系统权限与对象权限）是否级联
先恢复JEFF的CREATE SESSION 权限
SQL> conn / as sysdba
Connected.
SQL> grant role1 to jeff with admin option;
Grant succeeded.
SQL> conn jeff/jeff
Connected.
SQL> show user
USER is "JEFF"
SQL> select count(*) from hr.employees;
select count(*) from hr.employees
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> conn emi/emi
Connected.
SQL> select count(*) from hr.employees;
select count(*) from hr.employees
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> conn / as sysdba
Connected.
SQL> grant select on hr.employees to role1;
Grant succeeded.
重新给JEFF角色ROLE1(这里只是为了让你更清楚)
SQL> conn / as sysdba
Connected.
SQL> revoke role1 from jeff;
Revoke succeeded.
SQL> grant role1 to jeff with admin option;
Grant succeeded.
SQL> conn jeff/jeff
Connected.
SQL> select count(*) from hr.employees;
COUNT(*)
----------
107
SQL> show user
USER is "JEFF"
SQL> grant role1 to emi;
Grant succeeded.
SQL> conn emi/emi
Connected.
SQL> select count(*) from hr.employees;
COUNT(*)
----------
107
SQL> conn / as sysdba
Connected.
SQL> revoke role1 from jeff;
Revoke succeeded.
SQL> conn jeff/jeff
ERROR:
ORA-01045: user JEFF lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn emi/emi
Connected.
SQL> select count(*) from hr.employees;
COUNT(*)
----------
107
总结： 角色ROLE与系统权限一样，不会级联，哪怕它里面包含了会级联的对象权限。
四： 概要文件PROFILE（可以用EM图形界面去点）
（1） PROFILE之FAILED_LOGIN_ATTEMPTS
CREATE PROFILE "PROFILE1" LIMIT CPU_PER_SESSION DEFAULT
CPU_PER_CALL DEFAULT
CONNECT_TIME DEFAULT
IDLE_TIME DEFAULT
SESSIONS_PER_USER DEFAULT
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL DEFAULT
PRIVATE_SGA DEFAULT
COMPOSITE_LIMIT DEFAULT
PASSWORD_LIFE_TIME DEFAULT
PASSWORD_GRACE_TIME DEFAULT
PASSWORD_REUSE_MAX DEFAULT
PASSWORD_REUSE_TIME DEFAULT
PASSWORD_LOCK_TIME DEFAULT
FAILED_LOGIN_ATTEMPTS 3（密码输错3次自动锁住）
PASSWORD_VERIFY_FUNCTION DEFAULT；
将新建出来的PROFILE1“安”在HR上
ALTER USER "HR" PROFILE "PROFILE1"；
SQL> conn hr/hr
Connected.
SQL> conn hr/he
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn hr/he
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn hr/he
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn hr/hr
ERROR:
ORA-28000: the account is locked（三次后被锁住了）
（3）
PROFILE之口令验证函数（VERIFY_FUNCTION）
[oracle@ocpdb admin]$ ls -l *utlpwd*
-rw-r----- 1 oracle oinstall 5737 Sep 11 2000 utlpwdmg.sql
[oracle@ocpdb admin]$ pwd
/home/oracle/app/oracle/product/10.2.0/db_1/rdbms/admin
[oracle@ocpdb admin]$
[oracle@ocpdb admin]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 27 07:12:48 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> @/home/oracle/app/oracle/product/10.2.0/db_1/rdbms/admin/utlpwdmg.sql
Function created.
Profile altered.
ATTENTION:使用提供的口令验证函数可强制设置以下口令限制：
•
最小长度是四个字符。
•
口令不能与用户名相同。
•
口令必须至少包含一个字母、一个数字和一个特殊字符。
•
口令与前一口令必须有三个字母不相同。
到EM里面，点进去PROFILE，就能看到
Complexity
Complexity function VERIFY_FUNCTION
SQL> conn / as sysdba
Connected.
SQL> alter user test identified by test;
alter user test identified by test
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20001: Password same as or similar to user（看到没）
SQL> alter user test identified by x$345;
User altered.（成功，因为满足了要求）
五： 限额 QUOTA
SQL> conn / as sysdba
Connected.
SQL> drop user test;
User dropped.
SQL> create user test identified by test default tablespace test;
User created.
SQL> conn test/test
ERROR:
ORA-01045: user TEST lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> grant create session to test;
Grant succeeded.
SQL> conn test/test
Connected.
SQL> show user
USER is "TEST"
SQL> create table test1a(a number) tablespace test;
create table test1a(a number) tablespace test
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> conn / as sysdba
Connected.
SQL> grant create table to test;
Grant succeeded.
SQL> conn test/test
Connected.
SQL> create table test1a(a number) tablespace test;
create table test1a(a number) tablespace test
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'TEST'
SQL> conn / as sysdba
Connected.
SQL> alter user test quota 10m on test;
User altered.
SQL> conn test/test
Connected.
SQL> create table test1a(a number) tablespace test;
Table created.(成功创建)