由於工作原因很長一段時間沒有使用oracle,最近花了點時間學習下,把自己所學的記錄下來以便日後鞏固(以10g為例)。
SQL> conn system/orcl Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as system SQL> create user test identified by test; User created SQL>
SQL> alter user system identified by scott1; User altered
SQL> drop user test; User dropped注意如果想刪除用戶對應的數據對象時(如表)需要用加cascade 如drop user test cascade
SQL> create user test identified by test; User created SQL> grant connect to test; Grant succeeded SQL> conn test/test; Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as test SQL> show user User is "test"
SQL> conn system/orcl; Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as system SQL> grant select on scott.emp to test; Grant succeeded SQL> conn test/test; Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as test SQL> select * from scott.emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH CLERK 7902 1980-12-17 1800.00 20 7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30 7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30 7566 JONES MANAGER 7839 1981-04-02 2975.00 20 7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30 7698 BLAKE MANAGER 7839 1981-05-01 2850.00 30 7782 CLARK MANAGER 7839 1981-06-09 2450.00 10 7788 SCOTT ANALYST 7566 1987-04-19 3000.00 20 7839 KING PRESIDENT 1981-11-17 5000.00 10 7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30 7876 ADAMS CLERK 7788 1987-05-23 1100.00 20 7900 JAMES CLERK 7698 1981-12-03 950.00 30 7902 FORD ANALYST 7566 1981-12-03 3000.00 20 7934 MILLER CLERK 7782 1982-01-23 1300.00 34.56 10 14 rows selected注:(1)grant select可換insert、update、delete、all。 (2)同樣可以授權數據對象(表或視圖)的某列值給指定的用戶,如果是select授權某列值給指定的用記時使用創建視圖然後把視圖授權給指定的用戶 (注:網上有好多使用grant select on emp(ename,sal) to test;授權,這在9i下可以成功,但在9i以後不允許授予select某列的權限,但可以授insert ,update某列的權限)
SQL> conn scott/scott Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as scott SQL> create view v_test as 2 select ename,sal from emp; create view v_test as select ename,sal from emp ORA-01031: 權限不足 SQL> conn system/orcl; Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as system SQL> grant create view to scott; Grant succeeded SQL> conn scott/scott Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as scott SQL> create view v_test as 2 select ename,sal from emp; View created SQL> grant v_test to test; grant v_test to test ORA-01919: 角色 'V_TEST' 不存在 SQL> grant select on v_test to test; Grant succeeded SQL> select * from v_test; ENAME SAL ---------- --------- SMITH 1800.00 ALLEN 1600.00 WARD 1250.00 JONES 2975.00 MARTIN 1250.00 BLAKE 2850.00 CLARK 2450.00 SCOTT 3000.00 KING 5000.00 TURNER 1500.00 ADAMS 1100.00 JAMES 950.00 FORD 3000.00 MILLER 1300.00 14 rows selected授權test用戶只能修改sal字段。
SQL> grant update(sal) on emp to test; Grant succeeded 授權test用戶只能插入empno和empname字段。 SQL> grant insert(empno,ename) on emp to test; Grant succeeded
SQL> CONN SCOTT/SCOTT Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as scott SQL> REVOKE SELECT ON V_TEST FROM TEST; Revoke succeeded SQL> REVOKE UPDATE(SAL) ON EMP FROM TEST; REVOKE UPDATE(SAL) ON EMP FROM TEST <span style="color:#ff0000;">ORA-01750: UPDATE/REFERENCES 只能從整個表而不能按列 REVOKE</span> SQL> REVOKE UPDATE ON EMP FROM TEST; Revoke succeeded SQL> REVOKE INSERT ON EMP FROM TEST; Revoke succeeded注:注意上面的錯誤提示不能對表的某列revoke只能對整個表revoke。
SQL> CONN SCOTT/SCOTT Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as scott SQL> GRANT SELECT ON V_TEST TO TEST WITH GRANT OPTIONS; GRANT SELECT ON V_TEST TO TEST WITH GRANT OPTIONS <span style="color:#ff0000;">ORA-00994: 缺失 OPTION 關鍵字</span> SQL> GRANT SELECT ON V_TEST TO TEST WITH GRANT OPTION; Grant succeeded SQL> CONN TEST/TEST Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as test SQL> GRANT SELECT ON V_TEST TO TEST1; GRANT SELECT ON V_TEST TO TEST1 <span style="color:#ff0000;">ORA-00942: 表或視圖不存在</span> SQL> GRANT SELECT ON SCOTT.V_TEST TO TEST1; Grant succeeded注:(1)權限的傳遞只需要在授權時加上with grant option就能讓被授權的用戶將些權限授予其他用戶。 (2)如果授權的是系統權限(上面操作的都是對象權限)傳遞給其他人時只需要加上with admin option。 (3)此時如果scott用戶把test的查詢v_test權限收回將一同把test1的查詢v_test權限收回。
SQL> conn system/orcl Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as system SQL> create profile lock_account limit failed_login_attempts 2 password_lock_time 3; Profile created SQL> alter user test profile lock_account; User altered <span style="color:#ff0000;">通過下面對用戶強制解鎖</span> SQL> alter user test account unlock; User altered (設定用戶定期修改密碼,每隔10天修改自己的密碼寬限期為3天) SQL> create profile remind_account limit password_life_time 10 password_grace_time 3; Profile created SQL> alter user test profile remind_account; User altered 刪除直接drop profile remind_account;