Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2003
    Location
    South Africa
    Posts
    41

    Unanswered: Urgent - Plz Help -Spooling File

    Hi PPL

    I need help urgent, I have roughly about 90 DBs user and about 1000 tables. Now I need to run a script that spools out the grants for these users.

    My User Security is stored in my old DB and when I am importing, I want to import with grants, although there is a catch, I have added a further 42 tables after the import and I dont know whats the user names excpet that there are stored in table Oa0p0990....

    The script runs about 1/2 way and then complain about overflowing and that the buufer is too small....

    This is my script
    ----------------------------------------
    set serveroutput on size 1000000
    set linesize 200
    set pagesize 5000
    spool c:\grants.sql

    DECLARE
    A NUMBER;
    A_TEXT VARCHAR2(100);
    A_DUMMY NUMBER;
    A_ROW NUMBER;

    A_COUNT NUMBER;

    CURSOR C1 IS
    SELECT USERNAME
    FROM DBA_USERS
    WHERE USERNAME in ('ADMIN','REPORTER','CARLTON')
    OR USERNAME IN(SELECT USERID FROM SYSADM.OA0P0990);

    CURSOR C2 IS
    SELECT OBJECT_NAME
    FROM DBA_OBJECTS
    WHERE OBJECT_TYPE IN ('TABLE','VIEW','SEQUENCE')
    and owner = 'SYSADM';

    CURSOR C3 IS
    SELECT OBJECT_NAME
    FROM DBA_OBJECTS
    WHERE OBJECT_TYPE IN ('PROCEDURE','PACKAGE','FUNCTION')
    and owner = 'SYSADM';

    BEGIN
    FOR REC IN C1
    LOOP
    FOR REC2 IN C2
    LOOP
    A_TEXT := 'GRANT ALL ON SYSADM.'||REC2.OBJECT_NAME||' TO '||REC.USERNAME||';';
    DBMS_OUTPUT.PUT_LINE(A_TEXT);
    END LOOP;

    FOR REC3 IN C3
    LOOP
    A_TEXT := 'GRANT ALL ON SYSADM.'||REC3.OBJECT_NAME||' TO '||REC.USERNAME||';';
    DBMS_OUTPUT.PUT_LINE(A_TEXT);
    END LOOP;

    END LOOP;
    END;
    /
    spool off

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

    PLZ HELP

  2. #2
    Join Date
    Feb 2003
    Location
    South Africa
    Posts
    41
    This is the error that I get....

    ERROR at line 1:
    ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
    ORA-06512: at "SYS.DBMS_OUTPUT", line 91
    ORA-06512: at "SYS.DBMS_OUTPUT", line 58

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by Deshin13
    This is the error that I get....

    ERROR at line 1:
    ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
    ORA-06512: at "SYS.DBMS_OUTPUT", line 91
    ORA-06512: at "SYS.DBMS_OUTPUT", line 58
    Yes, DBMS_OUTPUT is limited to 1000000 bytes of output, which you are exceeding. So you'll have to adopt a different approach. One way would be:

    Code:
    set pagesize 0 linesize 200
    
    spool grants.sql
    
    select 'grant all on sysadm.' || o.object_name || ' to ' || u.username || ';'
    from dba_users u, dba_objects o
    where ...;
    
    spool off
    
    @grants.sql
    But do you really want to grant ALL on every object to every individual user? Why not assign appropriate priviliges to a role and then grant that role to the users?

  4. #4
    Join Date
    Feb 2003
    Location
    South Africa
    Posts
    41
    Sorry, I dont understand this thing very well....
    -----------------------------------
    Here is the script after I changed it....

    set pagesize 5000 linesize 200

    spool c:\grants1.sql

    select 'grant all on sysadm.' || o.object_name || ' to ' || u.username || ';'
    from dba_users u, dba_objects o
    where dba_users.USERNAME in ('ADMIN','REPORTER','CARLTON') OR dba_users.USERNAME IN(SELECT USERID FROM SYSADM.OA0P0990);

    spool off

    @c:\grants1.sql
    ------------------------------------------------------------
    Then this is the error

    SQL> start c:\new.sql
    where dba_users.USERNAME in ('ADMIN','REPORTER','CARLTON') OR dba_users.USERNAME IN(SELECT USERID FR
    *
    ERROR at line 3:
    ORA-00904: invalid column name


    SP2-0734: unknown command beginning "where dba_..." - rest of line ignored.
    SP2-0226: Invalid line number
    SP2-0734: unknown command beginning "ERROR at l..." - rest of line ignored.
    SP2-0734: unknown command beginning "ORA-00904:..." - rest of line ignored.
    ---------------------------------

    What I dont understand is that all the columns are valid

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by Deshin13
    Sorry, I dont understand this thing very well....
    -----------------------------------
    Here is the script after I changed it....

    set pagesize 5000 linesize 200

    spool c:\grants1.sql

    select 'grant all on sysadm.' || o.object_name || ' to ' || u.username || ';'
    from dba_users u, dba_objects o
    where dba_users.USERNAME in ('ADMIN','REPORTER','CARLTON') OR dba_users.USERNAME IN(SELECT USERID FROM SYSADM.OA0P0990);

    spool off

    @c:\grants1.sql
    ------------------------------------------------------------
    Then this is the error

    SQL> start c:\new.sql
    where dba_users.USERNAME in ('ADMIN','REPORTER','CARLTON') OR dba_users.USERNAME IN(SELECT USERID FR
    *
    ERROR at line 3:
    ORA-00904: invalid column name


    SP2-0734: unknown command beginning "where dba_..." - rest of line ignored.
    SP2-0226: Invalid line number
    SP2-0734: unknown command beginning "ERROR at l..." - rest of line ignored.
    SP2-0734: unknown command beginning "ORA-00904:..." - rest of line ignored.
    ---------------------------------

    What I dont understand is that all the columns are valid
    My table aliases have confused you:

    from dba_users u, dba_objects o

    You must either remove the aliases, or use them in all your where clauses:

    where u.USERNAME in ('ADMIN','REPORTER','CARLTON') OR ...

  6. #6
    Join Date
    Feb 2003
    Location
    South Africa
    Posts
    41
    This is the new script
    -------------------------------------
    set pagesize 5000 linesize 200

    spool c:\grants1.sql

    select 'grant all on sysadm.' || dba_objects.object_name || ' to ' || dba_users.username || ';'
    from sys.dba_users , sys.dba_objects
    where (sys.dba_users.USERNAME in ('ADMIN','REPORTER','CARLTON')
    OR dba_users.USERNAME IN(SELECT USERID FROM SYSADM.OA0P0990))
    AND dba_objects.OWNER = 'SYSADM'AND dba_objects.OBJECT_TYPE IN ('TABLE','VIEW','SEQUENCE','PROCEDURE','PACKAGE',' FUNCTION');

    spool off

    @c:\grants1.sql
    -----------------------------------------

    It works like the bomb!!!

    Thanks for the help....

  7. #7
    Join Date
    Jul 2003
    Location
    Near Paris France
    Posts
    60
    Hi,

    I don't know this schema and tables
    "SYSADM.OA0P0990"

    are they your tables?

  8. #8
    Join Date
    Feb 2003
    Location
    South Africa
    Posts
    41
    Yip, Table Oa0p0990 is where is store all my user security...

    Just glad that this script work although it take quite awhile to run, the file spooled out if 96 mbs and its still running at the moment...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •