Results 1 to 6 of 6

Thread: Is_dba

  1. #1
    Join Date
    Feb 2007
    Posts
    4

    Unanswered: Is_dba

    Hallo!!!
    i've got a really strange problem with the privilege in Oracle users.

    The query that validates if its' dba or not it's:

    SELECT CASE WHEN SYS_CONTEXT('USERENV', 'ISDBA') = 'TRUE' THEN 1 ELSE 0 END isdba FROM DUAL

    But this allways give me FALSE!!! even if the user it's show in this select:

    select * from dba_users.

    What's wrong in the select that need's anything else more to know if the user it's DBA????

    Thks..

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    ISDBA does not detect whether you are a DBA or not. It returns TRUE if you are logged on as 'SYS'.

  3. #3
    Join Date
    Feb 2007
    Posts
    4

    then how to know???

    Then how to know if i'm dba or not???

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Ask yourself: "Am I a DBA or not?"

    It seems not, because - if you were, you'd know the answer.

    I'm also not a DBA, but I'd query DBA_ROLE_PRIVS and search for grantees who have DBA role granted.

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    We go with session_roles to do the same
    Code:
      function user_is_dba_fn return boolean
      is
        l_is_dba number;
      begin
    
        select count(*)
        into l_is_dba
        from session_roles
        where role = 'DBA';
    
        if l_is_dba = 0 then
          return false;
        else
          return true;
        end if;
    
      end user_is_dba_fn;
    -cf

  6. #6
    Join Date
    Feb 2007
    Posts
    4

    Thks for the sudgestions is_dba

    Finally looks like the concept was wrong...
    the users can log in as 3 forms
    (1)Normal
    (2)sysoper
    (3)sysdba

    but only if the user log-in as sysdba the query finally wone give me TRUE.

    SQL>connect id/password as sysdba

    SELECT userenv('ISDBA') FROM dual;

    USEREN
    ------
    TRUE

Posting Permissions

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