Why Role is not enabled be default ?

Why Role is not enabled be default ?

SYS@NSMSP> select * from dba_role_privs where grantee='LIQY';

no rows selected

SYS@NSMSP> grant SMS_SEL_ROLE to liqy;

Grant succeeded.

SYS@NSMSP> grant SMS_FULL_ROLE to liqy;

Grant succeeded.

SYS@NSMSP> select * from dba_role_privs where grantee='LIQY';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
LIQY                           SMS_SEL_ROLE                   NO  YES
LIQY                           SMS_FULL_ROLE                  NO  YES

SYS@NSMSP> revoke SMS_SEL_ROLE from liqy;

Revoke succeeded.

SYS@NSMSP> revoke SMS_FULL_ROLE from liqy;

Revoke succeeded.

SYS@NSMSP> grant SMS_SEL_ROLE to liqy;

Grant succeeded.

SYS@NSMSP> alter user liqy default role SMS_SEL_ROLE;

User altered.

SYS@NSMSP> grant SMS_FULL_ROLE to liqy;

Grant succeeded.

SYS@NSMSP> select * from dba_role_privs where grantee='LIQY';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
LIQY                           SMS_SEL_ROLE                   NO  YES
LIQY                           SMS_FULL_ROLE                  NO  NO

# note that here SMS_FULL_ROLE   is not activated by default.


--rectify the issue 

dbsvr21:NSMSP:/software/oranSMS/admin/NSMSP/create/setup/role> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Apr 3 15:53:29 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning option

SYS@NSMSP> spool alter_default_role_mnaccess.log
SYS@NSMSP> select * from dba_role_privs where grantee='MNACCESS';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
MNACCESS                       MNACCESS_ROLE                  NO  YES
MNACCESS                       SMS_FULL_ROLE                  NO  NO

SYS@NSMSP> prompt in DEF column SMS_FULL_ROLE is not activated by default
in DEF column SMS_FULL_ROLE is not activated by default
SYS@NSMSP> alter user MNACCESS default role none;

User altered.

SYS@NSMSP> select * from dba_role_privs where grantee='MNACCESS';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
MNACCESS                       MNACCESS_ROLE                  NO  NO
MNACCESS                       SMS_FULL_ROLE                  NO  NO

SYS@NSMSP> revoke MNACCESS_ROLE from  MNACCESS;

Revoke succeeded.

SYS@NSMSP> grant MNACCESS_ROLE to  MNACCESS;

Grant succeeded.

SYS@NSMSP> revoke SMS_FULL_ROLE from  MNACCESS;

Revoke succeeded.

SYS@NSMSP> grant SMS_FULL_ROLE to  MNACCESS;

Grant succeeded.

SYS@NSMSP> select * from dba_role_privs where grantee='MNACCESS';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
MNACCESS                       MNACCESS_ROLE                  NO  NO
MNACCESS                       SMS_FULL_ROLE                  NO  NO

SYS@NSMSP> alter user MNACCESS default role none;

User altered.

SYS@NSMSP> select * from dba_role_privs where grantee='MNACCESS';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
MNACCESS                       MNACCESS_ROLE                  NO  NO
MNACCESS                       SMS_FULL_ROLE                  NO  NO

SYS@NSMSP> revoke SMS_FULL_ROLE, MNACCESS_ROLE from MNACCESS;

Revoke succeeded.

SYS@NSMSP> select * from dba_role_privs where grantee='MNACCESS';

no rows selected

SYS@NSMSP> grant SMS_FULL_ROLE, MNACCESS_ROLE to  MNACCESS;

Grant succeeded.

SYS@NSMSP> select * from dba_role_privs where grantee='MNACCESS';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
MNACCESS                       MNACCESS_ROLE                  NO  NO
MNACCESS                       SMS_FULL_ROLE                  NO  NO


MNACCESS@NSMSP> conn / as sysdba
Connected.
SYS@NSMSP> select * from dba_role_privs where grantee='MNACCESS';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
MNACCESS                       MNACCESS_ROLE                  NO  NO
MNACCESS                       SMS_FULL_ROLE                  NO  NO

SYS@NSMSP> conn mnaccess/mnaccess
Connected.
MNACCESS@NSMSP> insert into super_conf.cust_roam_cap(mobileno, accountid, starttime, indicator)values(97485258,51494367,20120403121541,'IN');
insert into super_conf.cust_roam_cap(mobileno, accountid, starttime, indicator)values(97485258,51494367,20120403121541,'IN')
                       *
ERROR at line 1:
ORA-01031: insufficient privileges


MNACCESS@NSMSP> set role all
  2  ;

Role set.

MNACCESS@NSMSP> insert into super_conf.cust_roam_cap(mobileno, accountid, starttime, indicator)values(97485258,51494367,20120403121541,'IN');

1 row created.

MNACCESS@NSMSP> rollback;

Rollback complete.

MNACCESS@NSMSP> conn / as sysdba
Connected.

SYS@NSMSP> ALTER USER mnaccess DEFAULT ROLE mnaccess_role, SMS_FULL_ROLE;

User altered.

SYS@NSMSP> select * from dba_role_privs where grantee='MNACCESS';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
MNACCESS                       MNACCESS_ROLE                  NO  YES
MNACCESS                       SMS_FULL_ROLE                  NO  YES

SYS@NSMSP> revoke create session from MNACCESS;

Revoke succeeded.

SYS@NSMSP> conn mnaccess/mnaccess
Connected.
MNACCESS@NSMSP> insert into super_conf.cust_roam_cap(mobileno, accountid, starttime, indicator)values(97485258,51494367,20120403121541,'IN');

1 row created.

MNACCESS@NSMSP> rollback;

Rollback complete.


  COUNT(*)
----------
         0