如何创建oracle新用户(新schema)

oracle中创建新的user, 也就相应地创建了一个新的schema, 用来区别其他目录的同时, 还保存所有相关user的数据对象以及user的表.

How to create a new user? (create a new db)

首先用system(默认密码为manager)用户登陆连接数据库.

The statement create user creates a user.
In the most simple form, the create user statement is one of the following three:

create user alfredo identified by alfredos_secret;
create user alfredo identified externally;
create user alfredo identified globally as 'external_name';

The first one creates a local user, the second one creates an external user while the last one creates global user.


How to create locked users?

A user can be created locked, that is, the user cannot connect to the database.

SQL> create user alfredo identified by passw0rd account lock;

The user is now created, he can be granted some rights, for example the right to connect to the database:

SQL> grant connect to alfredo;

Now, if the user tries to connect to the database, he will get ORA-28000:

SQL> connect alfredo/passw0rd
ERROR:
ORA-28000: the account is locked

The user can now be unlocked with an alter user statement:

SQL> alter user alfredo account unlock;

Which allows Alfredo to log on to the database now:

SQL> connect alfredo/passw0rd
Connected.

如何赋予用户权限或角色?

可以用grant直接给用户赋予权限, 也可以赋予用户角色, 角色可以看做是权限的集合.
这里我们只讨论角色, 权限可以参考http://www.techonthenet.com/oracle/grant_revoke.php

注意前面grant仅仅赋予了用户connect role, 这个role仅仅是给予用户的一些常见权利，最基本的, 不包括创建表等privileges  
   
   ALTER    SESSION    --修改会话  
   CREATE    CLUSTER    --建立聚簇  
   CREATE    DATABASE    LINK    --建立数据库链接  
   CREATE    SEQUENCE    --建立序列  
   CREATE    SESSION    --建立会话  
   CREATE    SYNONYM    --建立同义词  
   CREATE    VIEW    --建立视图  

如需要可以再赋予resource角色：    --是授予开发人员的  
   
   CREATE    CLUSTER    --建立聚簇  
   CREATE    PROCEDURE    --建立过程  
   CREATE    SEQUENCE    --建立序列  
   CREATE    TABLE    --建表  
   CREATE    TRIGGER    --建立触发器  
   CREATE    TYPE    --建立类型

SQL> grant connect, resource to alfredo;

但是实际查找oracle 10 2.0.1.0 系统时，发现两个role的privileges和上诉有所不同:

用sys登陆到oracle中，执行以下两条语句:

         select * from role_sys_privs WHERE ROLE LIKE 'CONNECT';  --查询connect角色的权限

         select * from role_sys_privs WHERE ROLE LIKE 'RESOURCE';  --查询 resource角色的权限


CONNECT角色：    --是授予终端用户的典型权利，最基本的      
   CREATE    SESSION    --建立会话  
       
RESOURCE角色：    --是授予开发人员的     
   CREATE    CLUSTER         --建立聚簇  
   CREATE    PROCEDURE       --建立过程  
   CREATE    SEQUENCE        --建立序列  
   CREATE    TABLE           --建表  
   CREATE    TRIGGER         --建立触发器  
   CREATE    TYPE            --建立类型
   CREATE    OPERATOR        --创建操作者
   CREATE    INDEXTYPE       --创建索引类型
   CREATE    TABLE           --创建表

也就是说不同oracle版本两个role的privilege会有所区别.

如何删除user(schema)?

如果想要快速的删除一个Schema下的所有数据库对象，您会使用什么样的手段来完成呢？
如果您是DBA，可能更倾向于先删除用户(Drop User)然后再重新创建用户。
优点：删除的彻底，不留任何痕迹（这是DBA伟大和危险的真实体现）。
缺点：要求的操作权限比较高，往往需要DBA亲力亲为；有一定误操作的风险；不便于书写到自动化脚本中，因为在当前用户有session连接的情况下是无法实现用户删除的。

如果drop user 用户名:  时出现下边的错误:
情况 1.
12:19:13  [DROP - 0 row(s), 0.000 secs]  [Error Code: 1922, SQL State: 42000]  ORA-01922: CASCADE must be specified to drop 'MIKE'

drop user 用户名  cascade;
注意:
drop user xx ：只是删除用户
drop user xx cascade ：会删除此用户名下的所有表和视图

情况 2.

SQL〉DROP USER mike01 CASCADE

ERROR：ORA-01940: cannot drop a user that is currently connected

由于当前用户正连接到数据库，所以无法删除

解决办法：

1) 查询此用户的会话进程，

SQL〉SELECT SID,SERIAL# FROM V$SESSION WHERE USERNAME='mike01';

       SID    SERIAL#
---------- ----------
        24      25341
        86      18117

2) 结束此用户的所有会话

SQL>ALTER  SYSTEM  KILL SESSION '24,25341';

System altered.

SQL>ALTER SYSTEM KILL SESSION '86,18117';

System altered.

3) 删除用户

SQL〉DROP USER USER1 CASCADE；

如果您是一名开发人员，可能会倾向于使用开发工具（如Toad、PL/SQL Developer等）点选的方式完成清理工作。
优点：要求权限不高，如果工具功能掌握熟练可以较快的完成删除任务。
缺点：无法实现自动化和批量操作的目的，人工成本太高，效率极低；存在工具软件异常假死的现象，不推荐使用。