9 Replies Latest reply: Nov 2, 2007 12:00 PM by APC RSS

    grant all priveleges on a schema to a user or role

    555356
      It failed when I tried to complete the task described in the title. I got 'ORA-00905: missing keyword' error.
      What's wrong with the statement?

      grant all privileges on schema ace to test_role
        • 1. Re: grant all priveleges on a schema to a user or role
          Sentinel
          I believe the syntax is
          grant all privileges on <schema1>.<object> to <schema2>;

          note that it is schema1<dot>object e.g.
          grant all privileges on hr.emp to scott;
          • 2. Re: grant all priveleges on a schema to a user or role
            555356
            thanks for the reply. However, can we grant privileges on all objects in a schema at once, instead of granting on one object at a time?
            • 3. Re: grant all priveleges on a schema to a user or role
              247514
              First of all, your syntax is wrong. Should be
              grant ALL PRIVILEGES to test_role;
              secondly, it will not grant object privilege of schema to test_role, all privileges only include system privileges.

              http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9013.htm#i2155015

              Oracle Database provides the ALL PRIVILEGES shortcut for granting all the system privileges listed in Table 18-1, except the SELECT ANY DICTIONARY privilege.

              You can only grant all privileges of one object at a time, not whole schema, for example you can grant all privileges on table bonus to user HR,
              GRANT ALL ON bonuses TO hr 
              • 4. Re: grant all priveleges on a schema to a user or role
                555356
                thanks for pointing out. But, my question is how to grant privileges (one or many)on all the projects on a shema to a user?
                • 5. Re: grant all priveleges on a schema to a user or role
                  555356
                  typo:

                  thanks for pointing out. But, my question is how to grant privileges (one or many)on all the ojects on a shema to a user?
                  • 6. Re: grant all priveleges on a schema to a user or role
                    ddevienne
                    See "Grant SELECT On All Tables In A Schema" at the bottom of http://www.psoug.org/reference/roles.html. Not quite what you are asking for, but may get you in the right direction. --DD                                                                                                                                                                                                                                                                                                                                                                               
                    • 7. Re: grant all priveleges on a schema to a user or role
                      247514
                      No, you can't, have to be by object, however you could spool the output of this query and run,
                      set pagesize 100
                      set linesize 200
                      SQ> select 'grant all on '||object_name||' to test_role;' from user_objects
                      where object_type not in ('INDEX','LOB','SYNONYM','TRIGGER','TABLE PARTITION');
                      'GRANTALLON'||OBJECT_NAME||'TOTEST_ROLE;'
                      --------------------------------------------------------------------------------
                      grant all on MEVIEW to test_role;
                      grant all on TEST2 to test_role;
                      grant all on B to test_role;
                      grant all on BS to test_role;
                      ............
                      • 8. Re: grant all priveleges on a schema to a user or role
                        555356
                        then why do I get this syntax from Oracle documentation?

                        GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
                        ON SCHEMA schemaname [, ...]
                        TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
                        • 9. Re: grant all priveleges on a schema to a user or role
                          APC
                          then why do I get this syntax from Oracle documentation?
                          selective editing??

                          Cheers, APC

                          Blog : http://radiofreetooting.blogspot.com/