6 Replies Latest reply: Apr 12, 2011 10:09 AM by 854765 RSS

    Truncate table in another schema without drop any table privilege

    518838
      Hi,
      I've 2 schemas IS_OWNER and IS_USER in the same database.

      IS_OWNER contains a table named TAB_BASE. IS_USER have synonym for the same table and have select, insert, update and delete privileges on the table

      I want to truncate the table TAB_BASE in schema IS_USER, but system gives the insufficient privileges error.

      While referring previous OTN threads, I found that truncate from another user is possible when IS_USER will be having drop any table privilege(which is obviously not a desirable privilege to give as IS_USER should have minimal privileges)

      I also came to know about below method to do so:
      Create a stored procedure and use Dynamic SQL to truncate the required tables and pass the execute permission to IS_USER. But again we require a privilege to truncate the table directly without using a SP.

      Is there any other way to achieve this goal?

      Thanks
      Deepak
        • 1. Re: Truncate table in another schema without drop any table privilege
          Ora
          Write a procedure in IS_OWNER, that will truncate the table that is in IS_OWNER, and give IS_USER 'EXECUTE' privilege on that procedure.
          Definer's Rights:- A user of a definer's rights procedure requires only the privilege to execute the procedure and no privileges on the underlying objects that the procedure accesses, because a definer's rights procedure operates under the security domain of the user who owns the procedure, regardless of who is executing it.
          By default, there will be Definers' rights, so I think there won't be any problem.
          • 2. Re: Truncate table in another schema without drop any table privilege
            438346
            To use "TRUNCATE TABLE" command, that user should have "drop any table" permission.

            TRUNCATE TABLE command is not a DML Command
            • 3. Re: Truncate table in another schema without drop any table privilege
              518838
              Write a procedure in IS_OWNER, that will truncate the table that is in IS_OWNER, and give IS_USER 'EXECUTE' privilege on that procedure.
              To use "TRUNCATE TABLE" command, that user should have "drop any table" permission.
              I'm aware about these solutions and have already mentioned in my question both the possible scenarios. I've also explained these are not the solutions I'm actually looking for

              Is there any other possible workaround to this problem?

              Thanks
              Deepak
              • 4. Re: Truncate table in another schema without drop any table privilege
                438346
                Hi Deepak,
                While referring previous OTN threads, I found that truncate from another user is possible when IS_USER will be having drop any table privilege(which is obviously not a desirable privilege to give as IS_USER should have minimal privileges)
                I also came to know about below method to do so:
                Create a stored procedure and use Dynamic SQL to truncate the required tables and pass the execute permission to IS_USER. But again we require a privilege to truncate the table directly without using a SP
                If you canot give permission to drop table and also if you canot use alternative procedure to drop table then there is no such way to drop a table on another user directly. Thats oracle default security.
                • 5. Re: Truncate table in another schema without drop any table privilege
                  Nimish Garg
                  i dont think you wud be able to truncate the table of another user.
                  the max u can do is simply delete the records.



                  Nimish Garg
                  http://nimishgarg.blogspot.com/
                  • 6. Re: Truncate table in another schema without drop any table privilege
                    854765
                    I had the same problem, finally found an answer.

                    Try this from IS_OWNER:

                    GRANT DROP ANY TABLE TO IS_OWNER
                    GRANT INSERT ANY TABLE TO IS_OWNER

                    Even though IS_OWNER may have created schema IS_USER, and possibly even created the table(s), you still have to explicitly give rights to drop the table(s) to truncate (truncate table is DDL and needs the same rights as drop table). If you want to insert data as IS_OWNER into schema IS_USER you have to grant yourself the privilege to do so.