Qs about default role
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Qs about default role

  1. #1
    Join Date
    Sep 2002
    Posts
    30
    Dear all,

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

    Thanks!

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Yes.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Oct 2002
    Location
    Pune, India
    Posts
    1

    default role

    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

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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>
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Sep 2002
    Posts
    30
    Thanks Jmodic, and thanks David80 for your questions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width