1 2 Previous Next 16 Replies Latest reply on Jun 8, 2012 5:12 PM by 942364

    Creating table in schema A with user B and granting permissions to user C

    942364
      Hello, I've got an amusing problem -- we have a large table that requires a batch process to do millions of updates, and it needs to finish faster. This takes several hours as an update, but a create table as select does the same thing in about a minute. Yay! Then we just drop the old table and rename the new table to the old, and rebuild all the index, and grant select/insert/update/delete permissions to another user that needs access... and there's the problem, Oracle says insufficient privs.

      We have schema A that owns the table, userid B that does all the application batch stuff, and userid C that does inserts for a middleware process. None of these are actual users, of course. We want user B to do the CTAS, rename, index rebuild, and permission re-granting. But user B apparently cannot grant permissions on objects in schema A to user C, even though B has created the table and has the DBA role (not ideal I know)!

      What's really crazy is that there is one way user B can grant the permissions to user C, which is to:

      grant select any table to USER C;
      grant insert any table to USER C;
      grant update any table to USER C;
      grant delete any table to USER C;

      It seems really perverse that user B can create tables in schema A, and even grant C "any table" privs, but can't grant privs specifically on this object in schema A, even with DBA privs. I must be missing something... right?
        • 1. Re: Creating table in schema A with user B and granting permissions to user C
          clcarter
          B apparently cannot grant permissions on objects in schema A ...
          Unless schema A does a grant to B ' ... with admin option;' then B can then grant those rights to other user(s). The tables belong to a user, someone has to own the objects. The owner can grant/revoke rights on his/her objects as appropriate for the other database users.
          DBA role (not ideal I know)!
          Right, DBA role has quite a bit of power. If the security setup requires granting anyone that role, its time to rethink the design and privilege grants.
          • 2. Re: Creating table in schema A with user B and granting permissions to user C
            Frank Kulash
            Hi,

            Welcome to the forum!

            It is funny that B can create the tables, but can't grant privileges on them.

            Why can't A grant the privileges (or create the table)?

            Be careful about granting privileges like SELECT ANY TABLE to C. That will give C the power to use any table in any schema, including schemas and tables not yet created.

            You can write a stored procedure (in schema A) that grants the specific privileges you need. Use EXECUTE IMMEDIATE in the stored procedure to issue the GRANT commands.
            • 3. Re: Creating table in schema A with user B and granting permissions to user C
              AlbertoFaenza
              user10714848 wrote:
              Hello, I've got an amusing problem -- we have a large table that requires a batch process to do millions of updates, and it needs to finish faster. This takes several hours as an update, but a create table as select does the same thing in about a minute. Yay! Then we just drop the old table and rename the new table to the old, and rebuild all the index, and grant select/insert/update/delete permissions to another user that needs access..
              Well, I'm replying to pose another question. Could this solution be a correct approach?
              Creating a new table, drop and rename instead of update the original table?

              I personally prefer to avoid dropping a table in a production environment. Too many impacts (recompiling packages, procedure, validating constraints, etc.).
              But I'm waiting for some expert opinion about this approach.

              Regards.
              Al
              • 4. Re: Creating table in schema A with user B and granting permissions to user C
                942364
                Thanks for the responses, but unfortunately everything has to be done by user B so having user A grant permissions to B isn't an option.

                Sorry, I probably didn't make this clear enough -- this job runs in batch mode on a regular basis. The table owned by user A is dropped and recreated by user B with the CTAS. User A is not set up to run in batch and setting user A up in batch is what we're trying to avoid if at all possible (currently we have hundreds of batch processes and they all run under User B).

                Fortunately B and C are both batch userids (no actual person can access them) so probably we'll end up going with "any table" if there's no way to do this.
                • 5. Re: Creating table in schema A with user B and granting permissions to user C
                  942364
                  Alberto,

                  I researched this and apparently CTAS is considered best practice when you are doing millions of updates. The difference in run time is staggering -- five hours versus one minute.

                  http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:6407993912330

                  Our process needs to finish faster, as it prevents users from using the application. It used to run on weekends where this wasn't a problem, but recent changes have resulted in it needing to run on weekdays.
                  • 6. Re: Creating table in schema A with user B and granting permissions to user C
                    JustinCave
                    To Frank's earlier point, are you sure that A can't own a procedure that does the grants and that is called by B? That would be the simplest and cleanest approach (of course, the easiest and cleanest way to recreate the table would also be for A to own a stored procedure that did it and have B call the stored procedure-- otherwise, B would need the CREATE ANY TABLE privilege which should virtually never be granted). It sounds like you're saying that's not an option but it's not clear from your explanation why that's not an option-- it doesn't seem like it requires A to be "set up in batch" whatever that means to you.

                    Justin
                    1 person found this helpful
                    • 7. Re: Creating table in schema A with user B and granting permissions to user C
                      Stew Ashton
                      Tables and other objects in the data dictionary should not be dropped and created in production on a regular basis.

                      The batch job should TRUNCATE the table and then do INSERT /*+ APPEND */. This is basically equivalent to DROP + CTAS except that the objects and their grants remain.

                      A user that actually executes in production should not have such powerful privileges; there is a security danger to the system if the user is compromised.

                      Now, there is another problem in that a TRUNCATE requires DROP privileges on the table ! This is unfortunate, but that's the way Oracle works.

                      It would be more secure to create a stored procedure in User A that accepts to truncate User A tables. Then you grant user B the privilege to execute the stored procedure.

                      Finally, one way or another you have to deal with indexes and constraints. How are you doing that right now?
                      • 8. Re: Creating table in schema A with user B and granting permissions to user C
                        942364
                        Justin -- maybe I'm misunderstanding, are you saying User B inherits the grant permissions of User A when running User A's package, and thus would be able to grant the permissions to C that way? That would be great, if true!

                        I'll give that a shot, thanks.
                        • 9. Re: Creating table in schema A with user B and granting permissions to user C
                          Stew Ashton
                          If you read the link you gave down farther, you will see references to TRUNCATE + INSERT /*+ APPEND */ as being equivalent to DROP + CTAS.

                          Tom Kyte also insists on not dropping and creating tables dynamically in production. See

                          http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:227413938857#22995104275369

                          "Doing DDL in a stored procedure is not a good idea at all."

                          So go for TRUNCATE + INSERT /*+ APPEND */
                          1 person found this helpful
                          • 10. Re: Creating table in schema A with user B and granting permissions to user C
                            JustinCave
                            Yes. By default, stored procedures are definer's rights stored procedures. If A owns a stored procedure, that stored procedure can do whatever A has been granted privileges to do directly. If A grants B EXECUTE privileges on that stored procedure, when B invokes the stored procedure, the procedure runs with A's privileges.

                            I'd echo Stew's point, though, that re-creating objects in Oracle is generally a bad idea and that a TRUNCATE with a direct-path insert, possibly combined with disabling and rebuilding the indexes would be more appropriate than a CTAS. If you're going to stick with the CTAS, however, that really needs to be done in a stored procedure owned by A so that B doesn't need CREATE ANY TABLE.

                            Justin
                            • 11. Re: Creating table in schema A with user B and granting permissions to user C
                              942364
                              Stew -- I just rebuild the indexes and constraints after the CTAS. That takes about 15 minutes, but is still much faster than the 5 hour update.

                              TRUNCATE + INSERT might be worth a shot, but the source for the data is another system so I'd first have to copy the table somewhere else, and I suspect this whole operation is much slower than CTAS since CTAS is DDL. But I'll try it and see, it would certainly be better not to have to drop tables and rebuild everything.

                              The setup I inherited is a bit of a mess already, way too many users seem to have DBA role for some reason.
                              • 12. Re: Creating table in schema A with user B and granting permissions to user C
                                942364
                                Thanks Justin, will do. Still learning a lot about PL/SQL, really appreciate the advice.
                                • 13. Re: Creating table in schema A with user B and granting permissions to user C
                                  Stew Ashton
                                  (Sorry, we're all talking at once.)

                                  Justin, thank you. I would just repeat that even with the TRUNCATE + INSERT approach you need a stored procedure in A, at least for the TRUNCATE part, otherwise B would require the DROP ANY TABLE privilege.
                                  1 person found this helpful
                                  • 14. Re: Creating table in schema A with user B and granting permissions to user C
                                    Frank Kulash
                                    Hi,
                                    user10714848 wrote:
                                    Justin -- maybe I'm misunderstanding, are you saying User B inherits the grant permissions of User A when running User A's package, and thus would be able to grant the permissions to C that way? That would be great, if true!
                                    That's more or less how it works.

                                    More precisely, if A owns a procedure, created with AUTHID DEFINER (which is the default), then anything done inside that procedure is done with A's privileges, even if B calls it. No matter what the procedure does, the only privilege B needs is the EXECUTE privilege on the procedure.
                                    A will have to log on once (either to create the procedure, or after the procedure is created) to grant the EXECUTE privilege to B, but after that, A will not need to log on any more. The procedure, and the privileges on the procedure, will not go away when any tables are dropped.
                                    1 person found this helpful
                                    1 2 Previous Next