No question at this time
DBA Top 10
1 M. Cadot 20800
2 A. Kavsek 14900
3 M. Hidayathullah ... 10800
4 B. Vroman 7900
5 P. Wisse 5300
6 T. Boles 4200
7 J. Schnackenberg 3800
8 K. Van Nieuwenhov... 2100
9 G. Van Cauter 1600
10 A. Hudspith 1500
About
DBA-Village
Download PLATO
The free tool for auditing and tuning your database
Version 55 now available
Sep 02, 2016
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48052
Total active users1733
Act. users last 24h10
Act. users last hour0
Registered user hits last week395
Registered user hits last month1204
Go up

grants
Next thread: datawarehouse
Prev thread: disable frontend on the weblogic domain

Message Score Author Date
Hi Guys I have made a backup of my schema by us...... Tso P May 19, 2017, 14:05
Dear Tso, from dbms_metadata.get_granted_ddl('r...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Mirza Hidayathullah Baig May 19, 2017, 16:01
Hello Tso, what have you done precisely? My gue...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Bruno Vroman May 20, 2017, 11:41
Thanks Bruno Yes, these exactly what I did: ...... Tso P May 22, 2017, 14:29
Hello Tso, this is related to your other post "...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Bruno Vroman May 22, 2017, 18:18
Thanks Bruno I do have another environment wher...... Tso P May 24, 2017, 14:29

Follow up by mail Click here


Subject: grants
Author: Tso P, South Africa
Date: May 19, 2017, 14:05, 150 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, 150 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, 150 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, 147 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, 147 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, 145 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