Qs about default role

Dear all,

Can I set a role with password as the default role in 9i?

Thanks!

Yes.

Will the user need to enter the password or will it be enabled when a user connects to the database, without requiring the user to specify the roles' passwords.

Thanks

If it's users default role it will be enabled automaticaly uppon loggon, no password will be required. But in case the role will be disabled during the session and the user will want to enable it again, he will need a password to do it.

Code:

---

Connected to:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

SQL> create role test_role identified by blabla;

Role created.

SQL> grant test_role to scott;

Grant succeeded.

SQL> select granted_role, default_role
  2  from dba_role_privs where grantee = 'SCOTT';

GRANTED_ROLE                  DEF
------------------------------ ---
CONNECT                        YES
RESOURCE                      YES
TEST_ROLE                      YES

SQL> connect scott/tiger@o901
Connected.
SQL> select * from session_roles;

ROLE
------------------------------
CONNECT
RESOURCE
TEST_ROLE

SQL> set role none;

Role set.

SQL> select * from session_roles;

no rows selected

SQL> set role test_role;
set role test_role
*
ERROR at line 1:
ORA-01979: missing or invalid password for role 'TEST_ROLE'


SQL> set role test_role identified by blabla;

Role set.

SQL> select * from session_roles;

ROLE
------------------------------
TEST_ROLE

SQL>

---

Thanks Jmodic, and thanks David80 for your questions.