语法结构:授权
GRANT 角色|权限 TO 用户(角色)
代码演示:授权
SQL> GRANT CONNECT TO jerry;
授权成功。
SQL> GRANT RESOURCE TO jerry;
授权成功。
SQL>
语法结构:其他操作
//回收权限
REVOKE 角色|权限 FROM 用户(角色)
//修改用户的密码
ALTER USER 用户名 IDENTIFIED BY 新密码
//修改用户处于锁定(非锁定)状态
ALTER USER 用户名 ACCOUNT LOCK|UNLOCK
SQL> grant all on emp to XiaoMei;
授权成功。
SQL> conn xiaomei/XiaoMei;
已连接。
SQL> SELECT ENAME,JOB,SAL FROM MyEmp WHERE SAL>2000;
ENAME JOB SAL
---------- --------- ----------
JONES MANAGER 2975
BLAKE MANAGER 2850
CLARK MANAGER 2450
SCOTT ANALYST 3000
KING PRESIDENT 5000
FORD ANALYST 3000
已选择6行。
SQL> update MyEmp set SAL=3001 where EName='FORD' And JOB='ANALYST';
已更新 1 行。
SQL> SELECT ENAME,JOB,SAL FROM MyEmp WHERE SAL>2000;
ENAME JOB SAL
---------- --------- ----------
JONES MANAGER 2975
BLAKE MANAGER 2850
CLARK MANAGER 2450
SCOTT ANALYST 3000
KING PRESIDENT 5000
FORD ANALYST 3001
已选择6行。
SQL> conn scott/tiger;
已连接。
SQL> SELECT ENAME,JOB,SAL FROM MyEmp WHERE SAL>2000;
SELECT ENAME,JOB,SAL FROM MyEmp WHERE SAL>2000
*
第 1 行出现错误:
ORA-00942: 表或视图不存在
SQL> SELECT ENAME,JOB,SAL FROM Emp WHERE SAL>2000;
ENAME JOB SAL
---------- --------- ----------
JONES MANAGER 2975
BLAKE MANAGER 2850
CLARK MANAGER 2450
SCOTT ANALYST 3000
KING PRESIDENT 5000
FORD ANALYST 3001
已选择6行。
SQL> conn XiaoMei/XiaoMei;
已连接。
SQL> Drop synonym myemp;
同义词已删除。
SQL> SELECT ENAME,JOB,SAL FROM myEmp WHERE SAL>2000;
SELECT ENAME,JOB,SAL FROM myEmp WHERE SAL>2000
*
第 1 行出现错误:
ORA-00942: 表或视图不存在
SQL> SELECT ENAME,JOB,SAL FROM scott.Emp WHERE SAL>2000;
ENAME JOB SAL
---------- --------- ----------
JONES MANAGER 2975
BLAKE MANAGER 2850
CLARK MANAGER 2450
SCOTT ANALYST 3000
KING PRESIDENT 5000
FORD ANALYST 3001
已选择6行。
SQL>