ANBOB™

通过grant 授于用户的角色，在用户登录时默认是全部继承的，除非是用alter user 显示的修改，指定用户连接后默认的角色有哪些，但是用户也可以使用后使用set role再附加已授权的但没启用的角色用于当前session

下面看我的例子一看就明白了

SQL> create role tabview ;

Role created.

SQL> grant create table ,create view to tabview;

Grant succeeded.

SQL> create role con;

Role created.


SQL> grant create session to con;

Grant succeeded.


SQL> create user anbob identified by anbob;

User created.

SQL> grant con,tabview to anbob;

Grant succeeded.

SQL> conn anbob/anbob;
Connected.
SQL> create table test1(id int);
create table test1(id int)
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'


SQL> conn system/oracle
Connected.

SQL> alter user anbob quota 10m on  users;

User altered.

SQL> conn anbob/anbob
Connected.
SQL> create table test1(id int);

Table created.

SQL> conn system/oracle
Connected.
SQL> alter user anbob default role none;

User altered.

SQL> conn anbob/anbob;
ERROR:
ORA-01045: user ANBOB lacks CREATE SESSION privilege; logon denied


Warning: You are no longer connected to ORACLE.
SQL> alter user anbob default role con;
SP2-0640: Not connected
SQL> conn system/oracle
Connected.
SQL> alter user anbob default role con;

User altered.

SQL> conn anbob/anbob;
Connected.
SQL> create table test2(id int);
create table test2(id int)
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> set role tabview;

Role set.

SQL> create table test2(id int);

Table created.

SQL> conn system/oracle
Connected.
SQL> alter user anbob default role all;

User altered.

SQL> conn anbob/anbob;
Connected.
SQL> create table test3(id int);

Table created.

SQL> conn system/oracle
Connected.
SQL> alter user anbob default role all except tabview;

User altered.

SQL> conn anbob/anbob;
Connected.
SQL> create table test4(id int);
create table test4(id int)
*
ERROR at line 1:
ORA-01031: insufficient privileges

DEFAULT ROLE Clause

Specify the roles granted by default to the user at logon. This clause can contain only roles that have been granted directly to the user with a GRANT statement. You cannot use the DEFAULT ROLE clause to enable:

•Roles not granted to the user

•Roles granted through other roles

•Roles managed by an external service (such as the operating system), or by the Oracle Internet Directory

Oracle Database enables default roles at logon without requiring the user to specify their passwords or otherwise be authenticated. If you have granted an application role to the user, you should use the DEFAULT ROLE ALL EXCEPT role clause to ensure that, in subsequent logons by the user, the role will not be enabled except by applications using the authorized package.

note: 这与revoke是不一样的，区别是revoke是夺回用户的角色，而deault 只是有角色默认不使用

打赏

微信扫一扫，打赏作者吧～