Grant privileges to truncate a table

dear club members

I want to grant "truncate table privileges" to a  non owner user.

If any body know please let me know

THanks
saleemlhrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable.

0
MikeOM_DBACommented:

Unfortunately the only way to grant TRUNCATE TABLE permissions to no-owner is ver dangerous:

GRANT DROP ANY TABLE TO TheUser;

0
MikeOM_DBACommented:

PS: Another way is to create a stored procedure in the table owners schema that truncates the table and then: GRANT EXECUTE to non-owner.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

csachdevaCommented:
You cannot grant or revoke permissions for update statistics at the column level. You must have the sso_role to run update statistics or delete statistics on sysroles, syssrvroles, and sysloginroles security tables.

By default, users with the sa_role have permission to run update statistics and delete statistics on system tables other then sysroles, syssrvroles and sysloginroles, and can transfer this privilege to other users.

The partial syntax for grant and revoke is:

grant [truncate table | update statistics | delete statistics] on table_name to {user_name | role_name}

revoke [truncate table | update statistics | delete statistics] on table_name from {user_name | role_name}

You can also issue grant all to grant permissions on update statistics, delete statistics, and truncate table.

For example, the following allows user “harry” to use truncate table and updates statistics on the authors table:

grant truncate table on authors to harry
grant update statistics on authors to harry

The following revokes truncate table and update statistics privileges from “harry” on the authors table:

revoke truncate table on authors from harry
revoke update statistics on authors from harry

The following allows user “billy” to use the delete statistics command on the authors table:

grant delete statistics on authors to billy

The following revokes the delete statistics privileges from user “billy” on the authors table:

revoke delete statistics on authors from billy

The following grants truncate table and update and delete statistics privileges to all users with the oper_role (if users “billy” and “harry” possess the oper_role, they can now run these commands on authors):

grant truncate table on authors to oper_role
grant update statistics on authors to oper_role
grant delete statistics on authors to oper_role

The following revokes truncate table and update and delete statistics privileges from all users with the oper_role:

revoke truncate table on authors from oper_role
revoke update statistics on authors from oper_role
revoke delete statistics on authors from oper_role

Users “billy” and “harry” can no longer run these commands on authors.

You can also implicitly grant permissions for truncate table, delete statistics, and update statistics through a stored procedure. For example, assuming “billy” owns the authors table, he can run the following to grant “harry” privileges to run truncate table and update statistics on authors:

create procedure sproc1
as
truncate table authors
update statistics authors
go
grant execute on sproc1 to harry
go

You can also implicitly grant permissions at the column level for update statistics and delete statistics through stored procedures.

Hope this will satisfy your query.

Regards,
Chetan Sachdeva
0
Anthony PerkinsCommented:
MikeOM_DBA,

>>PS: Another way is to create a stored procedure in the table owners schema that truncates the table and then: GRANT EXECUTE to non-owner.<<
Nope, that will not help.  As aneeshattingal has stated from BOL:
"TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable."
0
Anthony PerkinsCommented:
Actually if you have SQL Server 2005 "you can incorporate the TRUNCATE TABLE statement within a module, such as a stored procedure, and grant appropriate permissions to the module using the EXECUTE AS clause. "
0
Anthony PerkinsCommented:
Here is an example from BOL using SQL Server 2005:

CREATE PROCEDURE TruncateMyTable
WITH EXECUTE AS SELF
AS TRUNCATE TABLE MyDB..MyTable;
0
MikeOM_DBACommented:

Well, you never said it was WinDoze M$ SQL Server -- I posted the Oracle way.
0
Anthony PerkinsCommented:
>>I posted the Oracle way.<<
You are absolutely right, I stand corrected:  The questioner is in fact using Oracle.
0
saleemlhrAuthor Commented:
Thanks all for their comments. I think solution of  csachdeva will  not work. I personnaly tried to grant truncate table  permissions but failed.

I solved this problem by granting drop any table permissions however it is not good ideas as pointed out by MikeOM_DBA.

meanwhile I want this question  to remian open after more R&D points will be awarded. I also tried to create dbProcedure as mentioned by  csachdeva but truncate command does not work in dbprocedure

thanks
0
saleemlhrAuthor Commented:
We use Oracle 10G. this for your knowledge thanks
0
MikeOM_DBACommented:

Try something like this:

CREATE PROCEDURE TABOWNER.TRUNC_MYTAB
IS
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE MYTAB';
END;
/

GRANT EXECUTE ON TABOWNER.TRUNC_MYTAB TO NONOWNER
/
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.