How to Revoke Unwanted Privileges From Seeded ASG Schema (Doc ID 1144969.1)

Last updated on SEPTEMBER 06, 2016

Applies to:

Oracle Mobile Field Service - Version 11.5.8 and later
Information in this document applies to any platform.
***Checked for relevance on 09-Jul-2013***


Goal

Find that the ASG schema has several privileges assigned that are normally only intended for 'DBA' like accounts.

e.g.

select granted_role, grantee
from dba_role_privs where grantee = 'ASG'

GRANTED_ROLE                   GRANTEE                       
------------------------------ ------------------------------
SELECT_CATALOG_ROLE            ASG                          



select grantee, privilege, admin_option
from dba_sys_privs where grantee = 'ASG'
and privilege like '%ANY%'

GRANTEE                        PRIVILEGE                                ADMIN_OPTION
------------------------------ ---------------------------------------- ------------
ASG                            ALTER ANY PROCEDURE                      NO         
ASG                            ALTER ANY TABLE                          NO         
ASG                            ALTER ANY TRIGGER                        NO         
ASG                            DELETE ANY TABLE                         NO         
ASG                            DROP ANY PROCEDURE                       NO         
ASG                            DROP ANY TABLE                           NO         
ASG                            DROP ANY TRIGGER                         NO         
ASG                            INSERT ANY TABLE                         NO         
ASG                            SELECT ANY TABLE                         NO         
ASG                            UPDATE ANY TABLE                         NO       

select grantee, privilege, admin_option
from dba_sys_privs where grantee = 'ASG'
and (privilege = 'DROP PUBLIC SYNONYM'
   or privilege = 'UNLIMITED TABLESPACE'
   or privilege = 'CREATE PUBLIC SYNONYM')
  
GRANTEE                        PRIVILEGE                                ADMIN_OPTION
------------------------------ ---------------------------------------- ------------
ASG                            DROP PUBLIC SYNONYM                      NO         
ASG                            UNLIMITED TABLESPACE                     NO         
ASG                            CREATE PUBLIC SYNONYM                    NO


The architecture for mobile in 11.5.7 and earlier required a separate database between the mobile devices and the enterprise EBS database. Data from EBS was replicated to this database. For the purposes of replication the ASG schema was used as a replication propagator and any schema that was used as a propagator automatically obtained the list of privileges mentioned.

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms