Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2006
    Posts
    20

    Unanswered: find privileges for a user

    Ho do I find privileges for a user, need to know who has DBA/system privileges.

    TIA.

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    SELECT /*+ RULE */ GRANTEE
    FROM SYS.DBA_ROLE_PRIVS A
    WHERE GRANTED_ROLE='DBA'
    AND EXISTS (SELECT NULL FROM ALL_USERS WHERE USERNAME =A.GRANTEE)


    The exists is to make sure that we are looking at users instead of another role.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Jun 2006
    Posts
    20

    what's the diff. bet'n DBA with admin

    First thansks a bunch for the SQL.
    what's the diff. bet'n DBA with admin and dba with 'not admin '

    DBA with 'not admin' can do what ?

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    For example.
    Code:
    $ sqlplus '/ as sysdba'
    
    SQL*Plus: Release 10.2.0.2.0 - Production on Mon Jul 17 10:34:46 2006
    
    Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.
    
    
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
    With the Partitioning, Real Application Clusters and Data Mining options
    
    SQL> create user a identified by a;
    
    User created.
    
    SQL> create user b identified by b;
    
    User created.
    
    SQL> grant connect to a;
    
    Grant succeeded.
    
    SQL> conn a/a
    Connected.
    SQL> grant connect to b;
    grant connect to b
    *
    ERROR at line 1:
    ORA-01932: ADMIN option not granted for role 'CONNECT'
    
    
    SQL> conn / as sysdba
    Connected.
    SQL>
    SQL> grant connect to a with admin option;
    
    Grant succeeded.
    
    SQL> conn a/a
    Connected.
    SQL> grant connect to b;
    
    Grant succeeded.
    
    SQL>
    The WITH ADMIN OPTION clause let you pass this privilege to others users as well.

  5. #5
    Join Date
    Jun 2006
    Posts
    20

    default 'yes'

    Ok, first thanks for being really quick and nice explanation.

    I understood that 'with admin' but my confusion was that if a user does not have 'with admin' option, can that user still do with 'DBA' as default role everything a DBA do except passing this privilege to others users ?
    We just want to make sure that wrong users should not have DBA privilege that can be used to harm the database.

  6. #6
    Join Date
    May 2006
    Posts
    132
    Yes. If a user has the DBA with OUT the ADMIN OPTION, then the user can just grant DBA role to himself with the ADMIN OPTION.

Posting Permissions

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