search

qwazer / scheme2ddl

Command line util for export oracle schema to set of ddl scripts
82 stars 47 forks source link

Enhancement - Export grants and received grants #37

Open arno82 opened 9 years ago

arno82 commented 9 years ago

I added a functionality that allows us to export all grants into a separate directory. The old behaviour of putting the grants to the parent DDL object is not affected by my change. Additionally I added a function that exports all grants I receive from other schemes!

So I get every grant that my scheme/application needs and everything i grant to others as well. I named the new objects/folders "GRANTS" and "RECEIVED_GRANTS".

If someone is interested I could commit my changes, or simply provide a patch file here.

Let me know.

andyluu85 commented 8 years ago

That would be a useful function.

Will you be able to execute the RECEIVED_GRANTS? I assume not?

qwazer commented 8 years ago

Arno, can you provide patch or pull request? Does this functionality need additional user privilege like sys_dba? It can be disabled by default?

arno82 commented 8 years ago

Not without the appropriate permissions, which you normally don't have in your schema.

But the filename has the foreign schemaname as prefix so you see clearly where to execute those received grants.

E.g. the file: SCOTT_emp.sql contains GRANT SELECT ON "SCOTT"."EMP" TO MY_SCHEMA_NAME

arno82 commented 8 years ago

Answering qwazer question: It can be disabled by the _-tfm exclude 'RECEIVEDGRANT' mechanism, but that would not be default.

Is this a hard requirement for you?

qwazer commented 8 years ago

Yes. I think it must be optional behavior, because

  1. It's confusing
  2. it's really expert option
  3. as i understand, it need some special rights in oracle db.
arno82 commented 8 years ago

Anyway, I attach the patch here for you to see if it's ok. I could then commit it directly to the project if you want. Sadly it is mixed with issue 39 which is a bugfix for a different problem. patch_issue37_issue39.txt

arno82 commented 8 years ago

No you don't need special rights. Only if you want to execute those received grant file to gain the grants, you have to do it in the schema that granted it to you. You understand what I mean and what the features does? Otherwise we could go into detail per email if you want. I think it's very useful.

arno82 commented 8 years ago

I'll try to give an simple explanation of the feature:

My schema is SCHEMA_ARNO with a table TABLE_ARNO inside. If I give SELECT grants to SCOTT schema I will now receive a file in the GRANTS folder which contains the grant.

If SCOTT grants me SELECT to EMP table, I receive a file in the RECEIVED_GRANTS folder which contains the grant that he provided me to this table.

At the end I see what I grant to others and what others grant to me, and I can recreate those grants if needed. This could be the case if the SCOTT schema gets dropped and restored from backup. Then all my grants to SCOTT are gone and needs to be recreated by me.

qwazer commented 8 years ago

I agree, that it is useful feature. But I want to keep backward compatibility. So replacing old grants in parent DDL with new file it's not a good idea from this point of view. Adding new files with RECEIVED_GRANTS is allowed. I quickly looked at your patch. it need some polishing.

  1. For example, following java naming conventions in public String findReceivedGrantDDL(String name, String **G**rantSchema) {
  2. Changing of DDLFormatter is not desirable for backward compatibility reasons.
qwazer commented 8 years ago

Just for your info. I rarely work with Oracle now. My main database now is IBM DB2. And it has different permission mechanism without schemes-as-user based grants I need some time to switch back to Oracle-based thinking.

arno82 commented 8 years ago

Regarding backward compat: I'm not replacing the grants from the parentDDL with the ones from this new GRANTS dir! The new dir is additionally. The old behaviour is untouched!

Naming: You're right. This is possibly not following your conventions. Please change it as needed.

DDLFormatter: It's not necessary to take this change. I added it because it's better when importing the files to have the SET DEFINE OFF clause at the beginning.

And don't worry about the Oracle part of scheme2ddl. I'm having an eye on it because it is my main focus. I try to stay in sync between my own scheme2ddl development (outside of GIT) and this GIT, but it is not absolutely necessary of course.

xorader commented 7 years ago

I was do it (export all USER and SYSTEM grants) in my fork here https://github.com/xorader/scheme2ddl/tree/3.x_patches_from_xorader