12 Replies Latest reply: Feb 18, 2014 11:14 PM by onkar.nath RSS

    Get grant details on synonymsin 11gR2

    907035

      Hi,

       

      I want to get the grants data, on the synonyms and on Objects, I tried look into dictinary views but I couldnt get it.

       

      For Example Select * from user_tab_privs; will give the grants on table, but I want the grants  given to View,Procs,Synonyms.

       

      Im on 11gR2

       

      Please help.

       

       

      Kind Regards,

        • 1. Re: Get grant details on synonymsin 11gR2
          GregV

          Hi,

           

          Though the view is called user_tab_privs, it doesn't show only privileges on tables. It will show you privileges on other objects as well. What do you mean by grant on synonyms?

          • 2. Re: Get grant details on synonymsin 11gR2
            907035

            I want to know What ever the grants given to synonym's/Procs/Views on my schema to others.

            • 3. Re: Get grant details on synonymsin 11gR2
              onkar.nath

              as Greg has already mentioned use USER_TAB_PRIVS to get this information. Use this:

               

              select * from user_tab_privs where owner=user and grantee in ('<whatever users you wanted to check having access to your objects);

               

              Onkar

              • 4. Re: Get grant details on synonymsin 11gR2
                KarK

                Hi,

                 

                As suggested by others you can query the "USER_TAB_PRIVS" to see the privileges granted on the object to other user.

                 

                You can also query the "DBA_TAB_PRIVS" view.

                 

                 

                You can join with the USER_OBJECTS table to see the type of the object as below:

                 

                select a.GRANTEE,a.OWNER,a.TABLE_NAME,a.GRANTOR,a.PRIVILEGE,b.OBJECT_TYPE

                from user_TAB_PRIVS a,

                user_objects b

                where a.table_name=b.OBJECT_NAME;

                • 5. Re: Get grant details on synonymsin 11gR2
                  907035

                  Hi,

                   

                   

                  For Tables, I can query USER_TAB_PRIVS.  But I want Grants Information on Synonyms/Procedure.  Is there any dictionary view.

                   

                  When I grant a Select Access on Synonym to other User, This grant Information where could I get?

                  • 6. Re: Get grant details on synonymsin 11gR2
                    sb92075

                    907035 wrote:

                     

                    Hi,

                     

                     

                    For Tables, I can query USER_TAB_PRIVS.  But I want Grants Information on Synonyms/Procedure.  Is there any dictionary view.

                     

                    When I grant a Select Access on Synonym to other User, This grant Information where could I get?

                     

                    you have already be given the answer;  which is DBA_TAB_PRIVS

                    (see below)

                     

                     

                      1* select object_type, count(*) from dba_objects where object_name in (select table_name from dba_tab_privs) group by object_type order by 1

                    SQL> /

                     

                     

                    OBJECT_TYPE           COUNT(*)

                    ------------------- ----------

                    CONSUMER GROUP               3

                    DATABASE LINK                1

                    DIRECTORY                    9

                    EDITION                      2

                    EVALUATION CONTEXT           1

                    FUNCTION                   213

                    INDEX                        1

                    INDEXTYPE                    9

                    JAVA CLASS               22828

                    JAVA RESOURCE              839

                    JOB CLASS                    2

                     

                     

                    OBJECT_TYPE           COUNT(*)

                    ------------------- ----------

                    LIBRARY                      1

                    MATERIALIZED VIEW            2

                    OPERATOR                    55

                    PACKAGE                    678

                    PACKAGE BODY               647

                    PROCEDURE                   44

                    PROGRAM                     11

                    QUEUE                        5

                    SEQUENCE                    37

                    SYNONYM                  26143

                    TABLE                      477

                     

                     

                    OBJECT_TYPE           COUNT(*)

                    ------------------- ----------

                    TABLE PARTITION             56

                    TYPE                      1561

                    TYPE BODY                  143

                    VIEW                      4725

                     

                     

                    26 rows selected.

                     

                     

                    SQL>

                    • 7. Re: Get grant details on synonymsin 11gR2
                      rp0428

                      Kark already showed you where you can get it. Use the XXX_TAB_PRIVS views.

                       

                      The 'TABLE_NAME, as the docs say can be:

                      Name of the object. The object can be any object, including tables, packages, indexes, sequences, and so on.

                      http://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_5046.htm#i1627646

                      • 8. Re: Get grant details on synonymsin 11gR2
                        sb92075

                        You told but I showed.

                        • 9. Re: Get grant details on synonymsin 11gR2
                          KarK

                          Hi,

                           

                          TABLE_NAME in user_tab_privs refers to all objects, so you can query the USER_TAB_PRIVS or DBA_TAB_PRIVS views, and if you want to know the object type, then you can join with USER_OBJECTS or DBA_OBJECTS as shown below (same as given above but changed the order of column name):

                           

                           

                          SELECT a.TABLE_NAME OBJECT_NAME,

                            b.OBJECT_TYPE                 ,

                            a.OWNER                       ,

                            a.GRANTOR                     ,

                            a.GRANTEE                     ,

                            a.PRIVILEGE

                            FROM user_TAB_PRIVS a,

                            user_objects b

                            WHERE a.TABLE_NAME=b.OBJECT_NAME;

                          • 10. Re: Get grant details on synonymsin 11gR2
                            sb92075

                            KarK wrote:

                             

                            Hi,

                             

                            TABLE_NAME in user_tab_privs refers to all objects, so you can query the USER_TAB_PRIVS or DBA_TAB_PRIVS views, and if you want to know the object type, then you can join with USER_OBJECTS or DBA_OBJECTS as shown below (same as given above but changed the order of column name):

                             

                             

                            SELECT a.TABLE_NAME OBJECT_NAME,

                              b.OBJECT_TYPE                 ,

                              a.OWNER                       ,

                              a.GRANTOR                     ,

                              a.GRANTEE                     ,

                              a.PRIVILEGE

                              FROM user_TAB_PRIVS a,

                              user_objects b

                              WHERE a.TABLE_NAME=b.OBJECT_NAME;

                            KarK wrote:

                             

                            Hi,

                             

                            TABLE_NAME in user_tab_privs refers to all objects, so you can query the USER_TAB_PRIVS or DBA_TAB_PRIVS views, and if you want to know the object type, then you can join with USER_OBJECTS or DBA_OBJECTS as shown below (same as given above but changed the order of column name):

                             

                             

                            SELECT a.TABLE_NAME OBJECT_NAME,

                              b.OBJECT_TYPE                 ,

                              a.OWNER                       ,

                              a.GRANTOR                     ,

                              a.GRANTEE                     ,

                              a.PRIVILEGE

                              FROM user_TAB_PRIVS a,

                              user_objects b

                              WHERE a.TABLE_NAME=b.OBJECT_NAME;

                             

                            please quantify to closest order of magnitude how many rows are returned by SQL above & how long it takes to complete.

                            • 11. Re: Get grant details on synonymsin 11gR2
                              marcusafs

                              When you grant privs to synonyms it is actually granted to the object, not the synonym.

                              • 12. Re: Get grant details on synonymsin 11gR2
                                onkar.nath

                                Marcusafs is correct. When you grant select on synonym, base table select inherited by the grantee automatically. I did a small test:

                                 

                                SQL:SCOTT@tp11g>select grantee,table_name from user_tab_privs;
                                
                                GRANTEE                        TABLE_NAME
                                ============================== ==============================
                                SCOTT                          LOGMNRC_GSBA
                                SCOTT                          DBMS_FLASHBACK
                                TEST                           EMP_MV
                                SCOTT                          HR_EMP
                                SCOTT                          T
                                SCOTT                          HR_EMP
                                SCOTT                          FLASHBACK_TRANSACTION_QUERY
                                
                                7 rows selected.
                                
                                SQL:SCOTT@tp11g>grant select on emp1 to test;
                                
                                Grant succeeded.
                                
                                SQL:SCOTT@tp11g>select grantee,table_name from user_tab_privs;
                                
                                GRANTEE                        TABLE_NAME
                                ============================== ==============================
                                SCOTT                          LOGMNRC_GSBA
                                SCOTT                          DBMS_FLASHBACK
                                TEST                           EMP
                                TEST                           EMP_MV
                                SCOTT                          HR_EMP
                                SCOTT                          T
                                SCOTT                          HR_EMP
                                SCOTT                          FLASHBACK_TRANSACTION_QUERY
                                
                                8 rows selected.
                                
                                
                                SQL:SCOTT@tp11g>
                                
                                

                                 

                                So all you need to check is how many users have access to base tables and out of those base tables, how many tables are participating in synonyms. You should have you answer.

                                 

                                Onkar