預設角色的設定和修改

 

在給某個使用者一個角色後，該角色成為其預設角色（或預設角色的一部分），可以通過以下方式修改。

SQL> create user terry identified by terry;

User created

SQL> conn sys/sys@test as sysdba
Connected to Oracle Database 10g Release 10.2.0.1.0
Connected as SYS

SQL> grant connect ,resource to terry;

Grant succeeded

SQL> create table terry.emp as select * from user_tables;

Table created

SQL> create role test_role;

Role created

SQL> grant select on scott.emp to test_role;

Grant succeeded

SQL> grant test_role to terry;

Grant succeeded

SQL> conn terry/terry@test
Connected to Oracle Database 10g Release 10.2.0.1.0
Connected as terry

SQL> select * from scott.emp;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------

SQL> conn sys/sys@test as sysdba
Connected to Oracle Database 10g Release 10.2.0.1.0
Connected as SYS

SQL> alter user terry default role connect,resource;--修改預設角色

User altered

SQL> conn terry/terry@test
Connected to Oracle Database 10g Release 10.2.0.1.0
Connected as terry

SQL> select * from scott.emp;

select * from scott.emp

ORA-00942: 表或檢視不存在

SQL> set role test_role;

Role set

SQL> select * from scott.emp;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------

SQL> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADMIN_OPTION DEFAULT_ROLE OS_GRANTED
------------------------------ ------------------------------ ------------ ------------ ----------
TERRY                          CONNECT                        NO           YES          NO
TERRY                          RESOURCE                       NO           YES          NO
TERRY                          TEST_ROLE                      NO           NO           NO

通過set role語句可以增加預設角色，通過alter user  xxxx default role xxxx 既可以增加，也可以減少角色。

 

此方法可以作為臨時性的許可權管理方法。從此例可以看到角色的靈活性。

 

 

來自：Oracle觸發器與儲存過程高階程式設計-第3版