yesterday I was revoked the dba role from this user but still he had create session privileges on his schema.But today my mate have granted DBA Role again and also granted insert,update,delete,drop,create any table to user.Now apllication user said issue has resolved.

My though: DBA Role is not problem for above error and DBA role didn't resolve the issue since DBA role don't have create privileges as below. Issue has resolved becuase my mate has given "Grant create,drop,delete,insert******* any table to user". I know Grant create**** any table to user' is not good practice for DBA.

1) If you need to grant a privilege containing the word "ANY" to a user to make your application work you have failed. This means people can screw up the data dictionary and kill your whole database. It also means they can to any form of privilege escalation they like.2) If you need to grant DBA privilege to any user to make your application work you have failed. Same problems as above.

Regarding your question, if the code is a stored procedure, function or package the DBA role is unlikely to be used. Privileges granted via a role are not usable by code created with owner rights. They are only used by code created with invoker rights and anonymous blocks. This is more than likely why adding the DBA role did not "fix" your problem.

If you have a requirement in your application to do something with this level of privilege, what you should do is.

1) Create a user with the necessary privileges to perform the task, making sure the only people who have access to that user are the DBAs.2) Create PL/SQL APIs to perform all the necessary tasks, making sure they have coded the APIs carefully to prevent SQL Injection or privilege escalation.3) Grant execute on those APIs to the application users that need them.

This way, you control which tasks the users can do.

If you worked for me and granted application users the DBA role or privileges with "ANY" in then, you would be asked to leave the building and you would never work for me again. It really is that wrong!