DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Object Privilege

  1. #1
    Join Date
    Mar 2001
    Posts
    131
    Hi,

    Can any one suggest me a sql query to get the report on
    User Name and all objects privillages the user is having on any schema.

    This query should give the result for all users created on the database.

    Thanks,

    UpeshP

  2. #2
    Join Date
    Nov 2000
    Location
    Israel
    Posts
    268
    Hi,
    Every user can query the ALL_XXX views such as ALL_TABLES, ALL_VIEWS, ALL_OBJECTS etc..
    These views provide for all objects that user has privileges too.
    You can query the DBA_TAB_PRIVS to view all user table privileges:
    select owner,table_name,privilege,grantee
    from DBA_TAB_PRIVS
    where owner not in ('SYS','SYSTEM');

    cheers
    R.

  3. #3
    Join Date
    Mar 2001
    Posts
    131
    Hi,
    Thanks for the reply.

    This query gives the information about tables only. If i want for other objects also then ?

    Can any one suggest ??


  4. #4
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Originally Posted By: Upeshp
    This query gives the information about tables only. If i want for other objects also then ?
    DBA_TAB_PRIVS lists all object_privilege granted to users (excluding column specific privileges) use DBA_COL_PRIVS for column specific privileges.

    Sanjay

  5. #5
    Join Date
    Mar 2001
    Posts
    131
    If it is so...........and when i am executing the above written query ..i am not getting procedures, pakages, functions privileges listed in it.




  6. #6
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    You can use DBA_OBJECTS, grouped by owner...

    So, also USER_OBJECTS, ALL_OBJECTS...

    HTH.

  7. #7
    Join Date
    Mar 2001
    Posts
    131

    DBA_OBJECTS doesn't have any column related to Privileges.


  8. #8
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    Originally posted by Upeshp

    DBA_OBJECTS doesn't have any column related to Privileges.

    No, and what mean for you the column object_type in dba_objects?

    Cheers

    Angel

  9. #9
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Originally Posted by Upeshp:
    If it is so...........and when i am executing the above written query ..i am not getting procedures, pakages, functions privileges listed in it.
    Have a look at this..
    Code:
    sanjay@ORAM> select * from user_tab_privs
      2  where owner not in ('SYS','SYSTEM');
    
    GRANTEE         OWNER           TABLE_NAME      GRANTOR         PRIVILEGE       GRA
    --------------- --------------- --------------- --------------- --------------- ---
    PUBLIC          SANJAY          SANJAY          SANJAY          SELECT          NO
    PUBLIC          SANJAY          V_SANJAY        SANJAY          SELECT          NO
    
    sanjay@ORAM> create procedure my_proc is
      2  begin
      3  null;
      4  end;
      5  /
    
    Procedure created.
    
    sanjay@ORAM> grant execute on my_proc to public;
    
    Grant succeeded.
    
    sanjay@ORAM> select * from user_tab_privs
      2   where owner not in ('SYS','SYSTEM');
    
    GRANTEE         OWNER           TABLE_NAME      GRANTOR         PRIVILEGE       GRA
    --------------- --------------- --------------- --------------- --------------- ---
    PUBLIC          SANJAY          SANJAY          SANJAY          SELECT          NO
    PUBLIC          SANJAY          V_SANJAY        SANJAY          SELECT          NO
    PUBLIC          SANJAY          MY_PROC         SANJAY          EXECUTE         NO
    HTH
    Sanjay

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