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

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

[b]How to create a new user?[/b] (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.


[b]How to create locked users? [/b]

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.

[b]如何赋予用户权限或角色?[/b]

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

注意前面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会有所区别.

[b]如何删除user(schema)?[/b]

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