数据库创建表空间及用户

一、数据库工具端

  1、 创建临时表空间和用户表空间 

CREATE TEMPORARY TABLESPACE test_temp
TEMPFILE 'C:oracleproduct10.1.0oradataorcl	est_temp01.dbf'
SIZE 32M
AUTOEXTEND ON
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;

CREATE TABLESPACE test_data
LOGGING
DATAFILE 'C:ORACLEPRODUCT10.1.0ORADATAORCLTEST_DATA01.DBF' 
SIZE 32M 
AUTOEXTEND ON 
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;

  2、创建用户并指定表空间

CREATE USER username IDENTIFIED BY password
DEFAULT TABLESPACE TEST_DATA
TEMPORARY TABLESPACE TEST_TEMP;

  3、用户授权

GRANT 
CREATE SESSION, CREATE ANY TABLE, CREATE ANY VIEW ,CREATE ANY INDEX, CREATE ANY PROCEDURE,
ALTER ANY TABLE, ALTER ANY PROCEDURE,
DROP ANY TABLE, DROP ANY VIEW, DROP ANY INDEX, DROP ANY PROCEDURE,
SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE
TO username;

  角色即权限的集合，可以把一个角色授予给用户

//创建角色
create role myrole;

//将创建、删除等权限授予myrole
GRANT CREATE SESSION, CREATE ANY TABLE, CREATE ANY VIEW ,CREATE ANY INDEX, CREATE ANY PROCEDURE,
　　ALTER ANY TABLE, ALTER ANY PROCEDURE,
　　DROP ANY TABLE, DROP ANY VIEW, DROP ANY INDEX, DROP ANY PROCEDURE,
　　SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE to myrole;

//授予username用户myrole的角色
grant myrole to username;

//删除角色
drop role myrole;

  4、去权限

//grant是赋权，revoke则是除权

CREATE SESSION, CREATE ANY TABLE, CREATE ANY VIEW ,CREATE ANY INDEX, CREATE ANY PROCEDURE,
ALTER ANY TABLE, ALTER ANY PROCEDURE,DROP ANY TABLE, DROP ANY VIEW, DROP ANY INDEX, DROP ANY PROCEDURE,SELECT ANY TABLE, 
INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE
from username;

//删除username用户的myrole角色
revoke myrole from username;

二 linux服务器端

linux服务器端用户和工具端类似，只是需要先登录oracle

1、使用sqlplus登录

[oracle@j2eedev05 ~]$sqlplus /nolog
SQL> conn / as sysdba;
SQL>create user username identified by password
SQL> grant dba to username;

2、

alert user scott identified by tiger;//修改密码

授予权限
grant create session to zhangsan;//授予zhangsan用户创建session的权限，即登陆权限
grant unlimited tablespace to zhangsan;//授予zhangsan用户使用表空间的权限
grant create table to zhangsan;//授予创建表的权限
grante drop table to zhangsan;//授予删除表的权限
grant insert table to zhangsan;//插入表的权限
grant update table to zhangsan;//修改表的权限
grant all to public;//这条比较重要，授予所有权限(all)给所有用户(public)