---查看命令：用户默认表空间
SYS@ACE >select username,default_tablespace,temporary_tablespace,created from dba_users where username in('WEI','HR');
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED
------------------------------ ------------------------------ ------------------------------
HR USERS TEMP 01-APR-17
WEI USERS TEMP 12-MAY-17
--001:用户创建第一个能连接用户，有三种情况无法连接用户
（1）账户被锁定无法被连接：
SYS@ACE >alter user yang account lock; User altered.
SYS@ACE >conn yang/y；
ERROR:ORA-28000: the account is locked Warning: You are no longer connected to ORACLE.
Conn / as sysdba
?如何查看账户是否被锁定呢？
SQL> select username,account_status from dba_users where username='YANG';
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
YANG LOCKED
有个列：account_status =’UNLOCK’ 锁定状态、或者，open
Alter user yang account unlock;
如果account_status 的状态是expored： 则需要重置密码，identified by 密码；
（2）密码过期
SYS@ACE >select username,profile from dba_users where username='Q';查看对应profile文件
USERNAME PROFILE
------------------------------ ------------------------------
Q DEFAULT
Alter profile default q password_life_time unlimted;(无限制--格式默认是天，最低1,0报错）
-
无连接权限
Grant connect to q
--002-账户使用，经常操作的对象是表，表储存的位置在默认表空间的数据文件中。
有时因为没有使用表空间的权限或者没有对表操作的权限无法达到需求
YANG@ACE >select * from user_ts_quotas;
TABLESPACE_NAME 字节 BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO
------------------------------ ---------- ---------- ---------- ---------- ---
USERS 默认表空间 196608 2097152 块 24 256 NO动态
-
alter user yang quota 2M on users
-
Alter user yang quota unlimited on users;
--002：联级权限授予之 管理选择权：with admin option
测试一，特性查看：
SYS@ACE >create user yang identified by zhuo quota 10m on users;
SYS@ACE >create user xxf identified by xxf quota unlimited on users ;
SYS@ACE >create user hao identified by hao quota unlimited on users;
SYS@ACE >grant connect to yang,xxf,hao;
SYS@ACE >grant select any table to yang with grant option;
grant select any table to yang with grant option （select any table 不能通过此方式授予）
*
ERROR at line 1:
ORA-01939: only the ADMIN OPTION can be specified
SYS@ACE >grant select any table to yang with admin option;
YANG@ACE >select count(*) from hr.employees;
YANG@ACE >select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
SELECT ANY TABLE
YANG@ACE >grant select on scott.dept to xxf;
grant select on scott.dept to xxf
*
ERROR at line 1:
ORA-01031: insufficient privileges
YANG@ACE >grant select any table to xxf;
Grant succeeded
XXF@ACE >select count(*) from hr.employees;
XXF@ACE >select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
SELECT ANY TABLE 联机查询权限，不能指定对象授予权限，做人不能太小气--特性一
SYS@ACE >revoke select any table from yang;
select * from session_privs; 公安干掉老大，小弟的钱还在---特性二
测试二、测试其它的权限,去别人家偷菜
SYS@ACE >grant create any table to yang with admin option;
YANG@ACE >grant create table to hao;
grant create table to hao
*
ERROR at line 1:
ORA-01031: insufficient privileges
YANG@ACE >grant create any table to hao; 还是特性一，此权限授予他人很死板，有啥给啥
Grant succeeded. 不多给，也不能少给----
YANG@ACE >create table hr.xxx(id int);
Table created.
因为前面已经收回权限，需要再次授予
SYS》 grant select any table to yang;
YNG@ACE >select * from user_tables;
no rows selected
YANG@ACE >select count(*) from hr.xxx;
--003联机权限授予之 授予选择： with grant option
SYS@ACE >grant select any table to yang with grant option;
grant select any table to yang with grant option
*
ERROR at line 1: 具体对象权限严格采用此方式===
ORA-01939: only the ADMIN OPTION can be specified
SYS》 grant select on hr.employees to yang;
YANG> select count(*) from hr.employees;
YANG> grant select on hr.emplyees to hao;
HAO@ACE >select * from user_tab_privs;
必须通过用户拥有的对象权限才能查看，会话权限视图无法查看
项目经理 业主 表名 投资者 特权
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE
------------------------------ ------------------------------ ------------------------------ ------------------------------
HAO HR EMPLOYEES YANG SELECT NO NO
SYS@ACE >revoke select on hr.employees from yang;
HAO@ACE >select * from user_tab_privs; 特性：投资人失败，连带整个工程失败
no rows selected
总结：基最好使用admin权限大，grant权限小不说，还能被联级干死，各有好处。
3：用户改名
用户改名：yang 改为xxf
select username from dba_users where username in('YANG','XXF'); --查询用户相关名称
USERNAME
------------------------------
YANG
conn yang/y --连接
SQL> select count(*) from user_objects; --查询数据对象数量
COUNT(*)
----------
466
select user#,name from user$ where name in('YANG','XXF');
USER# NAME
---------- ------------------------------
93 YANG
update user$ set name='XXF' where user#=93; --更改基表操作
commit;
select user#,name from user$ where name in('YANG','XXF'); --查询发现，原对象被干掉了
USER# NAME
---------- ------------------------------
93 XXF
SELECT USERNAME FROM DBA_USERS WHERE USERNAME IN('YANG','XXF'); 视图查询
USERNAME
------------------------------
XXF
conn yang/y ---连接还是可以连接的
select * from cat;
select count(*) from user_objects;
conn xxf/y
SQL> shutdown immediate;
SQL> conn yang/y
ERROR:
ORA-01017: invalid username/password; logon denied
---不重启也行：执行操作：alter system flush shared_pool;
Warning: You are no longer connected to ORACLE.
SQL> conn xxf/y
SQL> select count(*) from user_objects;
COUNT(*)
----------
466
---
查询用户密码： 操作失败
select name,password from user$ where name='XXF';
XXF 9D3C147312506C5B
create user a identified by values'9D3C147312506C5B';