创建表空间:
create tablespace gouchao
datafile 'f:\xyc01.dbf' size 50m;
用户:
创建用户:
create user xuyongchao3 identified by xyc
default tablespace gouchao
temporary tablespace temp;
修改用户密码:
alter user xuyongchao identified by gouchao;
设置密码过期:
alter user gouchao password expire;
锁定用户:
alter user xuyongchao account lock;
为用户解锁:
alter user xuyongchao account unlock;
为用户授权:(系统权限)
grant connect ,resource ,create table ,create session ,create procedure to xuyongchao;、
为用户授数据对象权限:
grant select on scott.emp to gouchao;
为用户撤销权限:
revoke connect from gouchao;
创建角色:
create role student;
为角色授权:
grant select any table ,connect ,resource to student with admin option;
为用户授角色:
grant student to xuyongchao;
为用户撤销角色
revoke teacher from xuyongchao;
删除角色:
drop role teacher;
在数据字典中查询用户信息,授权情况。角色信息
查询用户信息:
select * from dba_users;
select * from dba_users where username='XUYONGCHAO';
授权情况:
conn xuyongchao/xyc
select * from user_sys_privs;
角色信息:
conn xuyongchao/xyc
select * from user_role_privs;
序列
查看当前用户所创建的序列:
select * from user_sequences;
创建序列:
create sequence S_userno
start with 50 increment by 10
MAXvalue 99 cache 10;
修改序列 :
alter sequence S_userno
maxvalue 200 cache 20 ;
删除序列:
drop sequence S_userno
使用序列:
nextVal:用于返回下一个序列号
Curral:用于返回当前序列号
insert into user(userno ,username) values(S_userno.Nextval,'33');
同义词:
同义词是方案对象的别名,作用:1)简化对象访问2)提高对象访问的安全性
建立公共同义词:
create public synonym public_emp for scott.emp;
使用同义词
select * from public_emp;
建立私有同义词:
create synonym private_emp for scott.emp;
删除公共同义词:
drop public synonym public_emp;
删除私有同义词
drop synonym private_emp;