ORACLE创建表空间、创建用户、以及授权、权限
- SELECT * FROM DBA_SYS_PRIVS;
- SELECT * FROM USER_SYS_PRIVS;
创建临时表空间:
- 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;
- SELECT * FROM DBA_ROLE_PRIVS;
- SELECT * FROM USER_ROLE_PRIVS;