oracle学习笔记之用户管理

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as SYSTEM@ORCL
 
SQL> show user;
User is "SYSTEM"
 




 
SQL> drop user xiao;//删除用户，只有SYSTEM和SYS用户才有这个权限。
 
User dropped
 
SQL> create user xiao identified by xiao;//创建用户，只有SYSTEM和SYS用户才有这个权限。
 
User created
 
SQL> conn xiao/xiao;//切换用户。
SQL> show user;//显示当前用户。
User is "SYSTEM"
 
SQL> grant connect to xiao;//给xiao这个用户授于登录数据库的角色权限。
 
Grant succeeded
 
SQL> conn xiao/xiao;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as xiao
 
SQL> show user;
User is "xiao"
 
SQL> conn system/system
SQL> conn SYSTEM/SYSTEM;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as SYSTEM
 
SQL> CONN xiao/xiao;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as xiao
 
SQL> show user;
User is "xiao"
 


 
SQL> conn SYSTEM/SYSTEM;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as SYSTEM


 
SQL> grant resource to xiao;//给xiao用户授予创建表空间的权限。
 
Grant succeeded
 
SQL> conn xiao/xiao;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as xiao
 
SQL> create table table3(userId varchar2(30),userName varchar2(40));//创建一个表。
 
Table created
 
SQL> select * from table3
  2  
SQL> select * from table3;
 
USERID                         USERNAME
------------------------------ ----------------------------------------
 
SQL> desc table3;
Name     Type         Nullable Default Comments 
-------- ------------ -------- ------- -------- 
USERID   VARCHAR2(30) Y                         
USERNAME VARCHAR2(40) Y                         
 
SQL> drop table table3;//删除表。
 
Table dropped


 
SQL> conn SYSTEM/SYSTEM;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as SYSTEM
 
SQL> create user lihui identified by lihui;//创建用户。
 
User created
 
SQL> grant resource to lihui;//给lihui用户授予创建表空间的权限。
 
Grant succeeded
 
SQL> conn lihui/lihui;
SQL> grant connect to lihui;//给lihui用户授予登录数据库的权限。
 
Grant succeeded
 
SQL> conn lihui/lihui;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as lihui


SQL> show user;
User is "lihui"
 
SQL> create table tableLihui(Id number,uName varchar2(30));
 
Table created


SQL> insert into tableLihui values(1,'李慧');//插入数据。
 
1 row inserted
 
SQL> select * from tableLihui;
 
        ID UNAME
---------- ------------------------------
         1 李慧
 
SQL> conn SYSTEM/SYSTEM;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as SYSTEM


 
SQL> show user;
User is "SYSTEM"


SQL> conn lihui/lihui;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as lihui
 
SQL> grant select on tableLihui to xiao;//给xiao授予查询tableLihui的权限。
 
Grant succeeded
 
SQL> conn xiao/xiao;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as xiao


SQL> select * from lihui.tableLihui;//xiao查询lihui下的tableLihui表。
 
        ID UNAME
---------- ------------------------------
         1 李慧
 
SQL> conn SYSTEM/SYSTEM;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as SYSTEM
 
SQL> grant select on lihui.tableLihui to xiao;//用SYSTEM用户给xiao授予查询lihui用户的tableLihui表的权限。
 
Grant succeeded
 
SQL> conn xiao/xiao;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as xiao


SQL> conn lihui/lihui;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as lihui
 
SQL> grant all on tableLihui to xiao;//给xiao授予查询、新增、修改、删除tableLihui表数据的权限。
 
Grant succeeded
 
SQL> conn xiao/xiao;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as xiao
 
SQL> show user;
User is "xiao"
 
SQL> insert into lihui.tableLihui values(2,'周日红');
 
1 row inserted
 
SQL> select * from lihui.tableLihui;
 
        ID UNAME
---------- ------------------------------
         1 李慧
 
SQL> conn lihui/lihui;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as lihui
 
SQL> revoke delete on tableLihui from xiao;//回收xiao对tableLihui表的删除数据权限。
 
Revoke succeeded
 
SQL> grant all on lihui.tablelihui to xiao with grant option;//授权，加上with grant option代表被授权的用户还能把这种权限分配给其他用户。
 
Grant succeeded
 
SQL> drop profile lock_account;
 
Profile dropped
 
SQL> create profile lock_account limit failed_login_attempts 3 password_lock_time 2;//创建一个profile文件，当用户密码输入三次错误时密码将被锁定2天。
 
Profile created
 
SQL> alter user lihui profile lock_account;//将lock_account这个profile文件应用于lihui用户，lihui必须遵循lock_account的规则。
 
User altered


SQL> alter user lihui account unlock;//给被锁定的用户解锁。