linux mysql创建表空间_数据库创建表空间及用户

一、数据库工具端

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

CREATE TEMPORARY TABLESPACE test_tempTEMPFILE 'C:\oracle\product\10.1.0\oradata\orcl\test_temp01.dbf'

SIZE 32MAUTOEXTEND ONNEXT 32M MAXSIZE 2048MEXTENT MANAGEMENT LOCAL;

创建临时表空间

CREATE TABLESPACE test_dataLOGGINGDATAFILE 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\TEST_DATA01.DBF'

SIZE 32MAUTOEXTEND ONNEXT 32M MAXSIZE 2048MEXTENT MANAGEMENT LOCAL;

创建用户表空间

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

CREATE USER username IDENTIFIED BY passwordDEFAULT TABLESPACE TEST_DATATEMPORARY TABLESPACE TEST_TEMP;

3、用户授权

GRANTCREATE 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 TABLETO 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 TABLEfrom 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)