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

彦红7744

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等）点选的方式完成清理工作。
优点：要求权限不高，如果工具功能掌握熟练可以较快的完成删除任务。
缺点：无法实现自动化和批量操作的目的，人工成本太高，效率极低；存在工具软件异常假死的现象，不推荐使用。