oracle设置默认角色,默认角色的设置和修改

在给某个用户一个角色后，该角色成为其默认角色(或默认角色的一部分)，可以通过以下方式修改。

SQL> create user terry identified by terry;

User created

SQL> conn 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

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 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

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版