grants
Follow up by mail Click here


Subject: grants
Author: Tso P, South Africa
Date: May 19, 2017, 14:05, 153 days ago
Os info: rhel5
Oracle info: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
Message: Hi Guys

I have made a backup of my schema by using schema export via datapump before application upgrade now I want to revert back. I want to drop the current schema and import the dump.

Now, I need to get the grants that I might not have in the dump...

How do I get them?

Please help!!!

Thanks in advance...
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: grants
Author: Mirza Hidayathullah Baig, United Kingdom
Date: May 19, 2017, 16:01, 153 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Dear Tso,

from dbms_metadata.get_granted_ddl('role_grant' username) we can extract the grants, please check following links.
and mos notes.

http://www.oracle-scripts.net/generate-user-ddl/


https://oracle-base.com/dba/script?category=script_creation&file=user_ddl.sql

How To Create Users Like Another User In Oracle Database At SQL Command Line, Not From OEM (Doc ID 1352212.1)

The dbms_metadata.get_granted_ddl Does Not Extract all System Privileges (Doc ID 1163383.1)

How To Retrieve The Full Object Definition (DDL) From The Database (Doc ID 1460969.1)

Regards
Mirza
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: grants
Author: Bruno Vroman, Belgium
Date: May 20, 2017, 11:41, 152 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hello Tso,

what have you done precisely? My guess:
- expdp schema "SCOTT"
- run application upgrade scripts that have modified the schema but maybe also the privileges of SCOTT
- now you want to go back to "pre-upgrade"

How do you intend to do it? My guess:
- drop user scott cascade
- impdp your backup dmp
- your fear: maybe some privileges are not in the dmp

I think that they are, but anyway here is another scenario that will keep system privileges and privileges against objects in other schemas as they are now (note that maybe this is not the same as the situation "pre-upgrade")
- keep user scott, but delete all its objects (you can easily count the number of objects "group by object_type", you can generate scripts to drop them, repeating the operation in case of failure due to FK for example, if you delete in the correct order things should be OK in at most a few passes)
- impdp your dmp, this will cause 1 "expected error" that you can ignore: <<user scott already exists>>

Best regards,

Bruno Vroman.

- drop user scott cascade;
- create user scott ...
- run scripts to
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: grants
Author: Tso P, South Africa
Date: May 22, 2017, 14:29, 150 days ago
Message: Thanks Bruno

Yes, these exactly what I did:

- expdp schema "SCOTT"
- run application upgrade scripts that have modified the schema but maybe also the privileges of SCOTT
- now you want to go back to "pre-upgrade"

How do you intend to do it? My guess:
- drop user scott cascade
- impdp your backup dmp
- your fear: maybe some privileges are not in the dmp

I found out the sys privileges, like I missed grant execute on sys.dbms_aq to scott, I had to grant that after the impdp as some packages were compiling with errors.

It would be nice if I can get those sys privleges granted to scott against sys objects.

Thanks a lot...
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: grants
Author: Bruno Vroman, Belgium
Date: May 22, 2017, 18:18, 149 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hello Tso,

this is related to your other post "schema permissions" of 20-APR-17. Indeed some privileges on SYS objects are not in the "export schema" dmp. In the other post I mentioned that you could find them by looking at the source of the export, but...

... but I understand now that it is too late to find what was in the database before the upgrade, you can only see what is in it "today".

Or you might restore a backup (real backup, not dmp) of the database "before upgrade" (in another location) to recreate the original source (in which you will be able to identify the privileges).

If you have no backup to restore, then the "do not drop SCOTT but simply drop its objects" will keep the privileges to the SYS objects -although of course you can't be sure that they are the same after upgrade than what they were before upgrade.

Advice: after the import carefully check if there are invalid objects, this might indicate what is missing (example: if a procedure of SCOTT uses "SELECT ... FROM v$session ..." and if SCOTT has not the direct privilege "GRANT SELECT ON v_$session TO SCOTT", the procedure will be invalid with "ORA-0904 table or view does not exist"; the compiler will tell you "line xxx column yyy" so it will be easy to identify the missing privilege by looking at the source code)

Best regards,

Bruno.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: grants
Author: Tso P, South Africa
Date: May 24, 2017, 14:29, 148 days ago
Message: Thanks Bruno

I do have another environment where I can check the privileges.

I will have to test other methods like Flashback technology, etc.

Thanks a lot for the replies...
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here