sql> conn /as sysdba
Connected to Oracle Database 11g Express Edition Release 11.2.0.2.0
Connected as scott AS SYSDBA
1)创建用户
使用scott,dba 登录
sql> create user ice identified by tiger;
User created
2)显示用户
select username from dba_users;
3)删除
sql> drop user ice;
User dropped
4)修改用户密码
alter user ice identified by 123456;
创建的新用户是没有任何权限的,甚至登录数据库的权限都没有.
5)授权
sql> grant connect to ice;
Grant succeeded
sql> conn ice/123456
Connected to Oracle Database 11g Express Edition Release 11.2.0.2.0
Connected as ice
权限
系统权限:用户对数据库的相关权限.(create session)
对象权限:用户对其他用户的数据对象(表,触发器,视图...)操作的权限. [select,insert,update,delete,all,create index...]
角色:
预定义角色:connect dba resource
自定义角色:
1)权限不足
sql> conn ice/123456
sql> create table test(userId varchar2(30));
ORA-01031: 权限不足
2)表或视图不存在
切换sys登录: 表Owner =SYS
create table emp(userId varchar2(30));
再切换到ice
select * from emp;
ORA-00942: 表或视图不存在
3)grant resource后有建表的权限
dba登录:
sql> grant resource to ice ;
Grant succeeded
sql> conn ice/123456
Connected to Oracle Database 11g Express Edition Release 11.2.0.2.0
Connected as ice
sql> create table test(userId varchar2(30));
Table created
sql> select * from test;
USERID
------------------------------
sql> desc test;
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
USERID VARCHAR2(30) Y
4)ice访问scott创建的emp表
dba登录:
grant select on emp to ice;
sql> select * from emp;
select * from emp
ORA-00942: 表或视图不存在
得使用:
select * from sys.emp;
同理:grant update on emp to ice;--增加修改权限.
grant all on emp to ice;
5)revoke收回权限
dba登录:
sql> revoke select on emp from ice;
Revoke succeeded
ice登录:
sql> select * from sys.emp;
select * from sys.emp
ORA-00942: 表或视图不存在
