3 Replies Latest reply: Oct 8, 2008 11:13 PM by 247514 RSS

    How to check grants

    597677
      Hi,
      I am getting an error for some users when they try to access an application.
      ORA-04067

      Now i am sure the packages are present and also other obejects are present, so only thing to check is that they have grants to execute them or not.

      So please let me know how can i check that a particular user has right to access a SCHEMA and also an OBJECT in that SCHEMA.

      Please help for both things.

      Thanks and Regards.
        • 1. Re: How to check grants
          Anand...
          hi..


          A user can be given privilages on the schema objects.To check it Use dba_tab_privs or user_tab_privs views

          link: [http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10755/statviews_2365.htm]


          Anand
          • 2. Re: How to check grants
            448778
            Use the below sql to see all the grants(on objects,system privs,role privileges) on user or role.Replace the 'CONNECT' with your username or role name.

            select 'ROLE' typ,
            grantee grantee,
            granted_role priv,
            admin_option ad,
            '--' tabnm,
            '--' colnm,
            '--' owner
            from dba_role_privs
            where grantee='CONNECT'
            union
            select 'SYSTEM' typ,
            grantee grantee,
            privilege priv,
            admin_option ad,
            '--' tabnm,
            '--' colnm,
            '--' owner
            from dba_sys_privs
            where grantee='CONNECT'
            union
            select 'TABLE' typ,
            grantee grantee,
            privilege priv,
            grantable ad,
            table_name tabnm,
            '--' colnm,
            owner owner
            from dba_tab_privs
            where grantee='CONNECT'
            union
            select 'COLUMN' typ,
            grantee grantee,
            privilege priv,
            grantable ad,
            table_name tabnm,
            column_name colnm,
            owner owner
            from dba_col_privs
            where grantee='CONNECT'
            order by 1;
            • 3. Re: How to check grants
              247514
              user445775 provided a useful query,

              you probably better split the dba_role_privs from others two. Because you need to find out what table privileges and system privileges grant to the role. Then you can find out the privilege user inherited from roles.
              select 'ROLE' typ,
              grantee grantee,
              granted_role priv,
              admin_option ad
              from dba_role_privs
              where grantee='USERNAME'
              find out all ROLE the user has and find out all privileges each role has
              select 'SYSTEM' typ,
              grantee grantee,
              privilege priv,
              admin_option ad,
              '--' tabnm,
              '--' colnm,
              '--' owner
              from dba_sys_privs
              where grantee='CONNECT'
              union
              select 'TABLE' typ,
              grantee grantee,
              privilege priv,
              grantable ad,
              table_name tabnm,
              '--' colnm,
              owner owner
              from dba_tab_privs
              where grantee='CONNECT'
              union
              select 'COLUMN' typ,
              grantee grantee,
              privilege priv,
              grantable ad,
              table_name tabnm,
              column_name colnm,
              owner owner
              from dba_col_privs
              where grantee='CONNECT'
              order by 1;