How to provide grant to specific user role automatically for frequently drop and create table.

Question

0.00/5 (No votes)

See more:

I Have 2 schema in my database. One of the schema has a package which is using a another schema's table. The Table from another schema, which is frequently drop and create with same name after a time span.

So, when the table has drop at the same moment the package from another schema will be invalid due the table.

Is there any way to provide a grant access will remain as it is after the table drop or any way to recompile automatically that package. Please help me on this.

Thanks in Advance.

What I have tried:

I am still finding a way. I am looking the way which will provide a permanent grant. :(

Posted 24-Sep-16 23:06pm

Bittu14

Updated 24-Sep-16 23:13pm

Add a Solution

Comments

Jörgen Andersson 25-Sep-16 15:23pm

Why do you drop and create instead of simply truncating the table?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2016-09-24T23:12:00

When you drop the object the privileges are also removed. This happens always.

On the other other hand dropping and re-creating an object should be a rare, administrative task and not to happen often. Especially if the object is used by other objects, for example packages, this should happen only if the structure of the database changes.

So based on your description, I'd try to find a completely different approach than altering the permanent objects.