[Oracle XE Series II] Oracle XE creates tablespaces, creates users, and grants and views permissions.

1. Create a tablespace

1. Temporary tablespace

CREATE TEMPORARY TABLESPACE zhunian_tempTEMPFILE 'D:\OracleXE\app\oracle\oradata\XE\zhunian_temp.dbf 'SIZE 32MAUTOEXTEND ONNEXT 32M MAXSIZE 2048MEXTENT MANAGEMENT LOCAL;

2. Create a user tablespace

CREATE TABLESPACE zhunian_dataLOGGINGDATAFILE 'D:\OracleXE\app\oracle\oradata\XE\zhunian_data.dbf '  SIZE 32M AUTOEXTEND ONNEXT 32M MAXSIZE 2048MEXTENT MANAGEMENT LOCAL;

2. Create a user

1. Create a user and specify a tablespace

CREATE USER zhunian IDENTIFIED BY <PASSWORD>DEFAULT TABLESPACE ZHUNIAN_DATATEMPORARY TABLESPACE ZHUNIAN_TEMP; 

2. grant permissions to users

GRANT CREATE SESSION,DBA TO zhunian;

I am lazy and give dba permissions directly. The actual authorization can be more detailed, for example:

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 TABLETO  zhunian; 

Of course, the role can also be granted to the user zhunian. That is to say, the user zhunian can manage and use the resources owned by the role. The syntax is as follows:

GRANT  role  TO  zhunian;

3. view User Permissions

1> View All Users

SELECT * FROM DBA_USERS;SELECT * FROM ALL_USERS;SELECT * FROM USER_USERS; 

2> View user system Permissions

SELECT * FROM DBA_SYS_PRIVS;SELECT * FROM USER_SYS_PRIVS; 

3> View user objects or role Permissions

SELECT * FROM DBA_TAB_PRIVS;SELECT * FROM ALL_TAB_PRIVS;SELECT * FROM USER_TAB_PRIVS; 

4> View All roles

SELECT * FROM DBA_ROLES; 

5> View roles owned by a user or role

SELECT * FROM DBA_ROLE_PRIVS;SELECT * FROM USER_ROLE_PRIVS; 

 

Table space-related extensions see: http://www.cnblogs.com/renjie08317/archive/2012/11/02/2750849.html

 

Reprinted please indicate the source

Http://www.cnblogs.com/zhunian/archive/2012/10/31/2748723.html

Http://www.zhunian.iego.cn