Oracle创建表空间、创建用户以及授权、查看权限

CREATE?TEMPORARY?TABLESPACE?test_temp
TEMPFILE?'C:\oracle\product\10.1.0\oradata\orcl\test_temp01.dbf'
SIZE?32M
AUTOEXTEND?ON
NEXT?32M?MAXSIZE?2048M
EXTENT?MANAGEMENT?LOCAL;

创建用户表空间

CREATE?TABLESPACE?test_data
LOGGING
DATAFILE?'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\TEST_DATA01.DBF'?
SIZE?32M?
AUTOEXTEND?ON?
NEXT?32M?MAXSIZE?2048M
EXTENT?MANAGEMENT?LOCAL;

创建用户并制定表空间

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

给用户授予权限

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;

将role这个角色授与username,也就是说，使username这个用户可以管理和使用role所拥有的资源

GRANT?role?TO?username;

-----------------------------------------------查看用户权限---------------------------------------------------------

查看所有用户

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

查看用户系统权限

SELECT?*?FROM?DBA_SYS_PRIVS;
SELECT?*?FROM?USER_SYS_PRIVS;

查看用户对象或角色权限

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

查看所有角色

SELECT?*?FROM?DBA_ROLES;

查看用户或角色所拥有的角色

SELECT?*?FROM?DBA_ROLE_PRIVS;
SELECT?*?FROM?USER_ROLE_PRIVS;

?

-------遇到no privileges on tablespace 'tablespace?'

?

alter?user?userquota?10M[unlimited]?on?tablespace;