Oracle 用户管理篇
1）用户查询：
select * from dba_user;
describes all users of the database, have more columns than select * from all_users;
select * from all_users;
lists all users of the database visible to the current user,only 3 columns
select * from user_users;
describes the current user
2）查看用户系统权限
select * from dba_sys_privs;
describes system privileges granted to users and roles
select * from user_sys_privs;
describes system privileges granted to the current user
3)查看用户对象权限：
select * from dba_tab_privs;
describes all object grants in the database
select * from all_tab_privs;
describes the following types of grants: Object grants for which the current user is the object owner,
grantor, or grantee OR Object grants for which an enabled role or PUBLIC is the grantee
select * from user_tab_privs;
describes the object grants for which the current user is the object owner, grantor, or grantee
4）查看所有角色：
select * from dba_roles;
lists all roles that exist in the database.
5）查看用户所拥有的角色：
select * from dba_role_privs;
describes the roles granted to all users and roles in the database.
select * from user_role_privs;
describes the roles granted to the current user.
6)新建用户（授权并查询权限）：
SQL> create user rachel identified by pass;
SQL> grant connect,resource, create session to rachel;
Grant succeeded.
SQL> commit;
这样用户就可能通过sqlplus 连接DB了。
[oracle@test ~]$ sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 28 04:01:47 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> show user;
USER is "RACHEL"
SQL> select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
RACHEL CREATE SESSION NO
RACHEL UNLIMITED TABLESPACE NO
7）新建角色(授权并查询权限)：
SQL> create role test;
Role created.
SQL> grant connect, resource, create session, create any index,create procedure , create any table, insert any table,drop any table,create
database link,alter any table,update any table,create public synonym to test;
Grant succeeded.
SQL> commit;
Commit complete.
SQL> select * from dba_sys_privs where GRANTEE='TEST';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
TEST CREATE PROCEDURE NO
TEST CREATE PUBLIC SYNONYM NO
TEST CREATE ANY TABLE NO
TEST INSERT ANY TABLE NO
TEST DROP ANY TABLE NO
TEST CREATE DATABASE LINK NO
TEST ALTER ANY TABLE NO
TEST CREATE SESSION NO
TEST CREATE ANY INDEX NO
TEST UPDATE ANY TABLE NO
10 rows selected.
8）把角色赋予用户：
SQL> grant test to rachel;
Grant succeeded.
SQL> commit;
Commit complete.
SQL> select * from dba_role_privs where GRANTEE='RACHEL';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
RACHEL TEST NO YES
RACHEL RESOURCE NO YES
RACHEL CONNECT NO YES
9)修改用户密码：
SQL> alter user rachel identified by rawu;
User altered.
SQL> commit;
Commit complete.
修改用户的其它参数同修改密码的方法一样.(refer alter user syntax as below link)
10)删除用户：
SQL> drop user haha;
User dropped.
SQL> drop user haha cascade;
Note: Specify CASCADE to drop all objects in the user's schema before dropping the user. You must specify this clause to drop a user
whose schema contains any objects.
11) 用户profile:
user profile 主要是对用户的可用资源及用户密码设一些限定，以免因某个用户的超额使用系统资源以影响其它用户。
以下是oracle的解释：
Resource limitation and user profile mechanisms prevent "run-away" queries, or more deliberate and malicious manipulation of system
resources by a particular user. A user profile is a set of administrator-defined resource limits assigned to a username; through the use of
user profiles, Oracle enables the database administrator to define and limit the amount of certain system resource available to a user.
从 以下SQL 可以得知有profile文件定义了哪些资源。
SQL> select * from dba_profiles;
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
DEFAULT CPU_PER_CALL KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
DEFAULT IDLE_TIME KERNEL UNLIMITED
DEFAULT CONNECT_TIME KERNEL UNLIMITED
DEFAULT PRIVATE_SGA KERNEL UNLIMITED
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10
DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
DEFAULT PASSWORD_LOCK_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_GRACE_TIME PASSWORD UNLIMITED
MONITORING_PROFILE COMPOSITE_LIMIT KERNEL DEFAULT
MONITORING_PROFILE SESSIONS_PER_USER KERNEL DEFAULT
MONITORING_PROFILE CPU_PER_SESSION KERNEL DEFAULT
MONITORING_PROFILE CPU_PER_CALL KERNEL DEFAULT
MONITORING_PROFILE LOGICAL_READS_PER_SESSION KERNEL DEFAULT
MONITORING_PROFILE LOGICAL_READS_PER_CALL KERNEL DEFAULT
MONITORING_PROFILE IDLE_TIME KERNEL DEFAULT
MONITORING_PROFILE CONNECT_TIME KERNEL DEFAULT
MONITORING_PROFILE PRIVATE_SGA KERNEL DEFAULT
MONITORING_PROFILE FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED
MONITORING_PROFILE PASSWORD_LIFE_TIME PASSWORD DEFAULT
MONITORING_PROFILE PASSWORD_REUSE_TIME PASSWORD DEFAULT
MONITORING_PROFILE PASSWORD_REUSE_MAX PASSWORD DEFAULT
MONITORING_PROFILE PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
MONITORING_PROFILE PASSWORD_LOCK_TIME PASSWORD DEFAULT
MONITORING_PROFILE PASSWORD_GRACE_TIME PASSWORD DEFAULT
32 rows selected.
我们也可以自已create or alter user profile.
user profile 是一个具体的文件，还是放在数据字典中的？？？
