9 Replies Latest reply: May 5, 2012 7:07 PM by rp0428 RSS

    Granting  entire Schema access ?

    johnpau2013
      - We have database user "A" with several tables,views...etc (thousands of objects)

      - We created a new database user "B". Is there a simple way of granting all ON user A objects to B ?
      without writing some scripts ?

      We want user B able to access everything which user A owns.
        • 1. Re: Granting  entire Schema access ?
          sb92075
          johnpau2013 wrote:
          - We have database user "A" with several tables,views...etc (thousands of objects)
          while possible it is not practical.

          >
          - We created a new database user "B". Is there a simple way of granting all ON user A objects to B ?
          without writing some scripts ?
          many SQL statements would be required; in script or not.

          >
          We want user B able to access everything which user A owns.
          • 2. Re: Granting  entire Schema access ?
            Aman....
            johnpau2013 wrote:
            - We have database user "A" with several tables,views...etc (thousands of objects)

            - We created a new database user "B". Is there a simple way of granting all ON user A objects to B ?
            without writing some scripts ?

            We want user B able to access everything which user A owns.
            If all that what user B is going to do is to access those thousands of objects of user A, what's the point of having the 2nd user? Why did you create it?

            As for your question, no there is no other way except writing explicit grants on all those objects of user A given to user B.

            Aman....
            • 3. Re: Granting  entire Schema access ?
              krramnik
              What kind of access you want to grant? SELECT permission alone or more?

              Better to create a role and grant all the access on user A to that role as per your requirement.

              Then Grant that role to User B.

              The advantages are
              1. In future if you have similar requirement of granting access on user A objects to any number of users, you can do that by simply granting that role.

              2. The access to User A can be revoked from User B or any other user just by revoking this role.
              • 4. Re: Granting  entire Schema access ?
                sb92075
                krramnik wrote:
                What kind of access you want to grant? SELECT permission alone or more?

                Better to create a role and grant all the access on user A to that role as per your requirement.
                privileges acquired via ROLE do NOT apply within named PL/SQL procedures.
                • 5. Re: Granting  entire Schema access ?
                  rp0428
                  >
                  Is there a simple way of granting all ON user A objects to B ?
                  without writing some scripts ?
                  >
                  Sure - that's called a 'proxy' user.

                  See ALTER USER in the SQL Reference and look for the word 'proxy'.
                  http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_4003.htm#i2115788
                  >
                  proxy_clause

                  The proxy_clause lets you control the ability of an enterprise user (a user outside the database) or a database proxy (another database user) to connect as the database user being altered.

                  The ENTERPRISE USER clause lets you expose user to proxy use by enterprise users. The administrator working in Oracle Internet Directory must then grant privileges for appropriate enterprise users to act on behalf of user.

                  The db_user_proxy clause let you expose user to proxy use by database user db_user_proxy, activate all, some, or none of the roles of user, and specify whether authentication is required. For information on proxy authentication of application users, see Oracle Database Application Developer's Guide - Fundamentals.
                  >
                  There is an example and more explanation as to how you can specify what roles you want used by the proxy.

                  For your case
                  SQL>ALTER USER A GRANT CONNECT THROUGH B;
                  Now you can login using the proxy and don't need A's password to act as A
                  SQL>connect b[a]@myDatabase;
                  • 6. Re: Granting  entire Schema access ?
                    ajallen
                    This is a pretty common means of separating the data user from the object owner. Here is one way that works pretty well and is dead easy.
                    CREATE ROLE schem_b_user;
                    
                    BEGIN
                      dbms_output.enable(1000000);
                    
                        FOR x IN ( SELECT owner, 
                                          object_name,
                                          DECODE(object_type, 'TABLE' ,   'select, insert, update, delete',
                                                              'SEQUENCE', 'select',
                                                              'VIEW',     'select',
                                                                          'execute') AS privs,
                                          DECODE (owner, 'USERA', 'SCHEMA_B_USER' ) AS app_user
                                     FROM dba_objects
                                    WHERE object_type IN ('TABLE',    'PACKAGE', 'PROCEDURE',
                                                          'FUNCTION', 'SEQUENCE', 'VIEW')
                                      AND owner       IN ('USERA' ))
                        LOOP
                          BEGIN
                             EXECUTE IMMEDIATE 'grant ' || x.privs       || ' on ' || x.owner ||
                                               '.'      || x.object_name || ' to ' || x.app_user   ;
                    
                    --        EXECUTE IMMEDIATE 'create or replace synonym '|| x.app_user||'.'||x.object_name||
                    --                           ' for ' ||x.owner||'.'||x.object_name ;
                    
                          EXCEPTION
                            WHEN others THEN
                              dbms_output.put_line('Bad owner = '||x.owner||';  Bad app_user='||x.app_user||
                                                   ';  Bad object_name='||x.object_name);
                          END;
                        END LOOP;
                    END;
                    /
                    GRANT schema_b_user TO userb;
                    Save the block in a file and you can run it any time you like to refresh the privileges, like when creating new objects.

                    Also, remember that for procs created by userb, he will need to have privileges granted directly to him, and not through a role.

                    Good Luck
                    • 7. Re: Granting  entire Schema access ?
                      rp0428
                      >
                      Here is one way that works pretty well and is dead easy.
                      >
                      Not as easy as using the 'proxy' method that was designed for this purpose.
                      • 8. Re: Granting  entire Schema access ?
                        ajallen
                        Proxy logins allow the user b to become user a for the duration of the session. This is not usually a very good idea when user a owns the objects. It will allow user b to execute ddl against user a's objects. Instead we use privileges, commonly granted through roles. That allows user b to maintain the data and execute the procs necessary to user b's job.
                        • 9. Re: Granting  entire Schema access ?
                          rp0428
                          Then you should have answered 'NO' to OP's question.
                          >
                          Is there a simple way of granting all ON user A objects to B ?
                          without writing some scripts ?
                          >
                          You are writing scripts.

                          I, instead, answere 'YES' - using the 'proxy' method does what OP ask and does not use scripts.

                          You can certainly argue 'This is not usually a very good idea' if you choose but the 'when user a owns the objects' part is misplaced since user a MUST own the objects per OP's requirement and also must always own the objects when using proxies.
                          >
                          It will allow user b to execute ddl against user a's objects.
                          >
                          Correct - and OP needs to consider this possibility
                          >
                          Instead we use privileges, commonly granted through roles. That allows user b to maintain the data and execute the procs necessary to user b's job.
                          >
                          A proxy can be created that does this also. That is why I suggested that OP read the entire section about proxies and why I included this
                          >
                          The db_user_proxy clause let you expose user to proxy use by database user db_user_proxy, activate all, some, or none of the roles of user, and specify whether authentication is required

                          There is an example and more explanation as to how you can specify what roles you want used by the proxy.