Wednesday 27 January 2016

How to create a schema with existing schema permissions in oracle database

In my case existing schema is APPS and need to create a schema like CMSRC.

Step 1:

SQL> Set long 100000000
SQL> select dbms_metadata.get_ddl('USER','APPS') from dual;

DBMS_METADATA.GET_DDL('USER','APPS')
--------------------------------------------------------------------------------

   CREATE USER "APPS" IDENTIFIED BY VALUES '*******************'
      DEFAULT TABLESPACE "ODSDATA_TBS"
      TEMPORARY TABLESPACE "TEMP"

Step 2:

SQL> select DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','APPS') from dual;

DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','APPS')
--------------------------------------------------------------------------------

   GRANT "RESOURCE" TO "APPS"
   GRANT "SELECT_CATALOG_ROLE" TO "APPS"
   GRANT "AQ_ADMINISTRATOR_ROLE" TO "APPS" WITH ADMIN OPTION
   GRANT "JAVASYSPRIV" TO "APPS"
   GRANT "CTXAPP" TO "APPS"
   GRANT "EBS_APPS_ROLE" TO "APPS"


Step 3: Create CMSRC schema

CREATE USER CMSRC IDENTIFIED BY cmsrc DEFAULT TABLESPACE ODSDATA_TBS  TEMPORARY TABLESPACE TEMP;

Step 4:   Granting permissions to CMSRC schema as like APPS schema

   GRANT "RESOURCE" TO "CMSRC"
   GRANT "SELECT_CATALOG_ROLE" TO "CMSRC"
   GRANT "AQ_ADMINISTRATOR_ROLE" TO "CMSRC" WITH ADMIN OPTION
   GRANT "JAVASYSPRIV" TO "CMSRC"
   GRANT "CTXAPP" TO "CMSRC"
   GRANT "EBS_APPS_ROLE" TO "CMSRC"

Hope this post helps :)

No comments:

Post a Comment