创建用户主要关心以下几个方面:
1 认证方式,如密码,外部认证等。通常为密码
2 权限分配与回收 一般用户赋予connect resource 角色
收回默认的unlimited tablespace 权限 避免无限制的使用默认的用户表空间。
其他应用所需权限
3 空间分配 一般为每个用户建立独立的应用相关的tablespace
建立tablespace后,在tablespace上给与用户unlimited tablespace权限以使用全部空间。
4 分配给用户默认default tablespace 和 temporary tablespace
相关视图:
dba_sys_privs
dba_ts_quotas;
dba_tablespaces;
dba_users;
1 认证方式,如密码,外部认证等。通常为密码
sys:
create user james identified by james
2 权限分配与回收 一般用户赋予connect resource 角色
收回默认的unlimited tablespace 权限 避免无限制的使用默认的用户表空间。
sys@O10G>select * from dba_sys_privs where grantee='CONNECT';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
CONNECT CREATE SESSION NO
sys@O10G>select * from dba_sys_privs where grantee='RESOURCE';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
RESOURCE CREATE TRIGGER NO
RESOURCE CREATE SEQUENCE NO
RESOURCE CREATE TYPE NO
RESOURCE CREATE PROCEDURE NO
RESOURCE CREATE CLUSTER NO
RESOURCE CREATE OPERATOR NO
RESOURCE CREATE INDEXTYPE NO
RESOURCE CREATE TABLE NO
8 rows selected.
Elapsed: 00:00:00.00
sys@O10G>select * from dba_sys_privs where grantee='JAMES';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
JAMES UNLIMITED TABLESPACE NO
grant connect to james;
grant resource to james;
revoke unlimited tablespace from james;
3 空间分配 一般为每个用户建立独立的应用相关的tablespace
建立tablespace后,在tablespace上给与用户unlimited tablespace权限以使用全部空间。
create tablespace james
datafile '/export/home/oracle/oradata/james.dbf'
size 100M
autoextend On
next 10M
maxsize 2048M
extent management local uniform size 128k
segment space management auto;
sys@O10G>alter user james quota unlimited on james;
sys@O10G>select * from dba_ts_quotas where username='JAMES';
TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO
--------------- --------------- ---------- ---------- ---------- ---------- ---
JAMES JAMES 0 -1 0 -1 NO
-1 代表unlimited
或者赋予需要的空间
sys@O10G>alter user james quota 10M on james;
User altered.
sys@O10G>select * from dba_ts_quotas where username='JAMES';
TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO
--------------- --------------- ---------- ---------- ---------- ---------- ---
JAMES JAMES 0 10485760 0 1280 NO
4 分配给用户默认default tablespace 和 temporary tablespace
sys@O10G>alter user james
2 default tablespace james
3 temporary tablespace temp;
sys@O10G>select username,default_tablespace,temporary_tablespace from dba_users where username='JAMES';
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
--------------- ------------------------------ ------------------------------
JAMES JAMES TEMP
创建用户和分配空间的注意事项
推荐阅读
- SQL Server 2008 表变量 临时表
- 上周热点回顾(12.18-12.24)
- 上周热点回顾(1.8-1.14)
- Idea或一切Tomcat运行的软件,解决端口被占用的问题:
- org.hibernate.HibernateException: Could not obtain transaction-synchronized Session for current thread
- idea在处理spring国际化解决中文乱码,properties的格式:native-to-ascii
- BasicNetwork.performRequest: Unexpected response code 404
- ajax传一个数组到springMVC的请求方法中,方法得不到请求的参数
- 安卓自定义属性,Android studio无法提示,只提示3个
- 读取系统通讯录信息的时候出错:.IllegalStateException: Couldn't read row 0, col -1 from CursorWindow