Results 1 to 4 of 4

Thread: Grant

  1. #1
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    Unanswered: Grant

    hI,

    How can I generate a script from the Oracle data dictionary, to grant privileges to all users, to all tables in a specific schema ?

  2. #2
    Join Date
    Jan 2003
    Location
    Woking
    Posts
    107

    Re: Grant

    Originally posted by Alexxx12
    hI,

    How can I generate a script from the Oracle data dictionary, to grant privileges to all users, to all tables in a specific schema ?
    Hi,
    One of the methods that i can think of is that
    create public synonym for all the tables and give all grants to public.
    That way all users will have all access to all tables in a particular
    schema.
    The SQL to generate this script is as follows...

    SELECT 'CREATE PUBLIC SYNONYM '||OBJECT_NAME||' FOR '||OBJECT_NAME||';'
    FROM USER_OBJECTS
    WHERE OBJECT_TYPE = 'TABLE'
    UNION ALL
    SELECT 'GRANT ALL ON '||OBJECT_NAME||' TO PUBLIC;'
    FROM USER_OBJECTS
    WHERE OBJECT_TYPE = 'TABLE'
    ORDER BY 1
    /

    You can change this SQL to suit yourself. That if you want grant a
    specific permission or role, you can do it. using this script.
    nn

  3. #3
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139
    hi,

    thanks for this response.
    this looks more like a query rather than the actual sql that is going to grant privileges to all users, to all tables in a specific schema. Am I correct or is this going to actually grant privileges to all users?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by Alexxx12
    hi,

    thanks for this response.
    this looks more like a query rather than the actual sql that is going to grant privileges to all users, to all tables in a specific schema. Am I correct or is this going to actually grant privileges to all users?
    It is SQL that generates SQL. You run that SQL and the output is something like:

    GRANT ALL ON aaa TO PUBLIC;
    GRANT ALL ON bbb TO PUBLIC;
    ...

    If you spooled that output to a file, you could then run that file to do the creates and grants:

    SQL> @myfile.txt

    An alternative that does it all in one go would be:

    BEGIN
    FOR r IN (SELECT table_name FROM user_tables)
    LOOP
    EXECUTE IMMEDIATE 'GRANT ALL ON '||r.table_name||' TO PUBLIC';
    END LOOP;
    END;
    /

Posting Permissions

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