Startseite

Oracle User anlegen und Schema Rechte kopieren

Mo, 04/19/2010 - 09:22 – brngor

 

  • User anlegen über script
    vi create_user.sql

    CREATE USER P03
      IDENTIFIED BY "berlin"
      DEFAULT TABLESPACE USERS
      TEMPORARY TABLESPACE TEMP
      PROFILE DEFAULT
      ACCOUNT UNLOCK;
      GRANT CONNECT TO P03;
      ALTER USER P03 DEFAULT ROLE ALL;


  • Script ausführen
    SQL> @create_user.sql
    User created.
    Grant succeeded.
    User altered.

     

  • Rechte von Schema "SCC" auf einen neuen Oracle Datenbank User Kopieren.
    vi create_grant.sql

    set feedback off
     set heading off
     set pagesize 1000
     spool grant_select-P03.sql
     SELECT 'grant select on '||owner||'.'||object_name||' to P03;'     
          FROM dba_objects
         WHERE object_type IN ('TABLE', 'VIEW')
           AND owner       IN ('SCC', 'CTXSYS');
    spool off 


     

  • Ausführen des Script.
    SQL> @create_grant.sql
    grant select on CTXSYS.DR$PARAMETER to P03;
    grant select on CTXSYS.DR$CLASS to P03;
    grant select on CTXSYS.DR$OBJECT to P03;
    grant select on CTXSYS.DR$OBJECT_ATTRIBUTE to P03;
    grant select on CTXSYS.DR$OBJECT_ATTRIBUTE_LOV to P03;
    [...]

 

 

  • Neu erstellte SQL-Datei ausführen.
    SQL> ! ls -lrt
    -rw-r--r--  1 agisora  oinstall  33859 Apr 19 09:19  grant_select-P03.sql

    SQL> @grant_select-P03.sql