1.创建、授权、连接用户。
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 11 05:18:45 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> sho user
USER is "SYS"
SQL> create user blake identified by blake;
User created.
SQL> conn blake/blake
#blake用户还没有任何权限，不能做任何事，连接也不行。
ERROR:
ORA-01045: user BLAKE lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> grant create session to blake;
#给blake用户授予相关权限
Grant succeeded.
SQL> conn blake/blake
Connected.
SQL>
[oracle@localhost ~]$ sqlplus blake/blake
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 11 06:17:43 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show user
USER is "BLAKE"
SQL> create table test (id varchar2(10),age number);
create table test (id varchar2(10),age number)
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> conn / as sysdba
Connected.
SQL> grant create table to blake;
Grant succeeded.
SQL> conn blake/blake
Connected.
SQL> create table test (id varchar2(10),age number);
Table created.
SQL>
2.撤回权限。
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 11 23:11:03 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn blake/blake
Connected.
SQL> create table test2 (id varchar2(10),age number);
Table created.
SQL> conn / as sysdba
Connected.
SQL> revoke create table from blake;
Revoke succeeded.
SQL> conn blake/blake
Connected.
SQL> create table test3 (id varchar2(10),age number);
#blake用户创建表的权限已经被撤回，所以不能再创建表了。
create table test3 (id varchar2(10),age number)
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL>
3.改用户密码。
SQL> conn scott/tiger
#密码错误
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn / as sysdba
Connected.
SQL> alter user scott identified by tiger;
#将scott的密码改为tiger
User altered.
SQL> conn scott/tiger
Connected.
SQL> alter user scott identified by oracle;
#scott用户可以改自己的密码
User altered.
SQL> conn blake/blake
Connected.
SQL> alter user scott identified by scott;
#blake用户不能改scott用户的密码
alter user scott identified by scott
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> conn / as sysdba
Connected.
SQL> alter user scott identified by scott;
#sys用户相当于root，能改任何用户的密码。
User altered.
SQL> conn scott/scott
Connected.
SQL> alter user scott identified by oracle;
User altered.
SQL> password
#可以使用password命令改密码
Changing password for SCOTT
Old password:
New password:
Retype new password:
Password changed
SQL> conn / as sysdba
Connected.
SQL> conn scott/abc
Connected.
SQL> conn / as sysdba
Connected.
SQL> alter user scott identified by oracle;
User altered.
SQL> password blake
Changing password for blake
New password:
Retype new password:
Password changed
SQL> conn blake/oracle
Connected.
SQL>
4.查看用户信息，删除用户。
SQL> select * from user_users;
#查看当前用户信息
USERNAME USER_ID ACCOUNT_STATUS LOCK_DATE EXPIRY_DA
------------------------------ ---------- -------------------------------- --------- ---------
DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED
------------------------------ ------------------------------ ---------
INITIAL_RSRC_CONSUMER_GROUP
------------------------------
EXTERNAL_NAME
----------------------------------------------------------------------------------------------------
SYS 0 OPEN 27-NOV-18
SYSTEM TEMP 17-SEP-11
SYS_GROUP
SQL> select * from all_users;
#查看系统所有用户
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
OPS$TOM 86 11-JUN-18
OPS$ORACLE 85 11-JUN-18
BLAKE 84 11-JUN-18
SCOTT 83 17-SEP-11
OWBSYS 78 17-SEP-11
APEX_030200 77 17-SEP-11
APEX_PUBLIC_USER 75 17-SEP-11
FLOWS_FILES 74 17-SEP-11
MGMT_VIEW 73 17-SEP-11
SYSMAN 71 17-SEP-11
SPATIAL_CSW_ADMIN_USR 69 17-SEP-11
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
SPATIAL_WFS_ADMIN_USR 66 17-SEP-11
MDDATA 64 17-SEP-11
OWBSYS_AUDIT 82 17-SEP-11
OLAPSYS 60 17-SEP-11
MDSYS 57 17-SEP-11
SI_INFORMTN_SCHEMA 56 17-SEP-11
ORDPLUGINS 55 17-SEP-11
ORDDATA 54 17-SEP-11
ORDSYS 53 17-SEP-11
ANONYMOUS 46 17-SEP-11
XDB 45 17-SEP-11
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
CTXSYS 43 17-SEP-11
EXFSYS 42 17-SEP-11
XS$NULL 2147483638 17-SEP-11
WMSYS 32 17-SEP-11
APPQOSSYS 31 17-SEP-11
DBSNMP 30 17-SEP-11
ORACLE_OCM 21 17-SEP-11
DIP 14 17-SEP-11
OUTLN 9 17-SEP-11
SYSTEM 5 17-SEP-11
SYS 0 17-SEP-11
33 rows selected.
SQL> conn blake/oracle
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST TABLE
TEST2 TABLE
TEST3 TABLE
SQL> conn / as sysdba
Connected.
SQL> drop user blake cascade;
#删除用户及关联数据
User dropped.
SQL> conn blake/oracle
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> roll
ERROR:
ORA-01012: not logged on
SQL> rollback;
SP2-0640: Not connected
SQL> show user;
USER is ""
SQL> conn / as sysdba
Connected.
SQL> roll
Rollback complete.
SQL> conn blake/oracle
#删除的用户，回滚不能还原。
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> select * from all_users;
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
OPS$TOM 86 11-JUN-18
OPS$ORACLE 85 11-JUN-18
SCOTT 83 17-SEP-11
OWBSYS 78 17-SEP-11
APEX_030200 77 17-SEP-11
APEX_PUBLIC_USER 75 17-SEP-11
FLOWS_FILES 74 17-SEP-11
MGMT_VIEW 73 17-SEP-11
SYSMAN 71 17-SEP-11
SPATIAL_CSW_ADMIN_USR 69 17-SEP-11
SPATIAL_WFS_ADMIN_USR 66 17-SEP-11
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
MDDATA 64 17-SEP-11
OWBSYS_AUDIT 82 17-SEP-11
OLAPSYS 60 17-SEP-11
MDSYS 57 17-SEP-11
SI_INFORMTN_SCHEMA 56 17-SEP-11
ORDPLUGINS 55 17-SEP-11
ORDDATA 54 17-SEP-11
ORDSYS 53 17-SEP-11
ANONYMOUS 46 17-SEP-11
XDB 45 17-SEP-11
CTXSYS 43 17-SEP-11
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
EXFSYS 42 17-SEP-11
XS$NULL 2147483638 17-SEP-11
WMSYS 32 17-SEP-11
APPQOSSYS 31 17-SEP-11
DBSNMP 30 17-SEP-11
ORACLE_OCM 21 17-SEP-11
DIP 14 17-SEP-11
OUTLN 9 17-SEP-11
SYSTEM 5 17-SEP-11
SYS 0 17-SEP-11
32 rows selected.
SQL>
5.开放和关闭指定表的查询权限
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Jun 12 03:25:16 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create user blake identified by oracle;
User created.
SQL> grant create session to blake;
Grant succeeded.
SQL> grant create table to blake;
Grant succeeded.
SQL> conn blake/oracle
Connected.
SQL> create table test (id varchar2(10),age number);
Table created.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST TABLE
SQL> select * from scott.emp;
#blake用户对scott.emp表没有查询权限
select * from scott.emp
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> conn / as sysdba
Connected.
SQL>
SQL> grant select on scott.emp to public;
#开放scott.emp表的查询权限
Grant succeeded.
SQL> conn blake/oracle
Connected.
SQL> select * from scott.emp;
#scott.emp的查询权限已开放
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> conn / as sysdba
Connected.
SQL> revoke select on scott.emp from public;
Revoke succeeded.
SQL> conn blake/oracle
Connected.
SQL> select * from scott.emp;
#scott.emp的查询权限已关闭
select * from scott.emp
*
ERROR at line 1:
ORA-00942: table or view does not exist
