No question at this time
DBA Top 10
1 A. Kavsek 12400
2 M. Cadot 7300
3 B. Vroman 6400
4 P. Wisse 5600
5 J. PĂ©ran 1900
6 . Lauri 1500
7 T. P 500
7 R. Pattyn 500
9 A. Chavan 300
9 D. Johnson 300
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48458
Total active users1487
Act. users last 24h2
Act. users last hour0
Registered user hits last week190
Registered user hits last month829
Go up

schema permissions
Next thread: data load slow
Prev thread: java version

Message Score Author Date
Hi Guys I have a schema that I dropped and recr...... Tso P Apr 20, 2017, 09:14
Dear Tso, you will not get all grants, using th...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Mirza Hidayathullah Baig Apr 20, 2017, 09:44
Thanks Mirza Are you saying I won't get all per...... Tso P Apr 20, 2017, 09:58
Hi, Once you generate sqlfile, you can view all...... Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts Mirza Hidayathullah Baig Apr 20, 2017, 10:56
Hmmm Mirza, what will be in the sql file that h...... Bruno Vroman Apr 20, 2017, 15:46
Dear Bruno, i mean generate the sql file to kno...... Mirza Hidayathullah Baig Apr 20, 2017, 15:54
Hi Mirza, indeed I had not read your posts corr...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Bruno Vroman Apr 21, 2017, 10:03

Follow up by mail Click here


Subject: schema permissions
Author: Tso P, South Africa
Date: Apr 20, 2017, 09:14, 962 days ago
Os info: rhel5
Oracle info: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
Message: Hi Guys

I have a schema that I dropped and recreated with data pump...I have a full export and a schema export.

Now, after dropping and importing the schema some of the permissions seems to be lost.

Like the directory permissions and the dbms_java.grant_permission...

Is it possible that I can get these grants from the full export, more especially the dbms_java.grant_permission?

Please help...

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

Subject: Re: schema permissions
Author: Mirza Hidayathullah Baig, United Kingdom
Date: Apr 20, 2017, 09:44, 962 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Dear Tso,

you will not get all grants, using that dumpfile extract the grants by using SQLFILE parameter.

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: schema permissions
Author: Tso P, South Africa
Date: Apr 20, 2017, 09:58, 962 days ago
Message: Thanks Mirza

Are you saying I won't get all permissions?

I am more interested in the dbms_java_permission.

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

Subject: Re: schema permissions
Author: Mirza Hidayathullah Baig, United Kingdom
Date: Apr 20, 2017, 10:56, 962 days ago
Score:   Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts
Message: Hi,

Once you generate sqlfile, you can view all ddls, including grants

for eg.:

impdp username/password directory=<directory_name> dumpfile=<dumpfile name> sqlfile=script.sql

view script.sql for ddl.

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: schema permissions
Author: Bruno Vroman, Belgium
Date: Apr 20, 2017, 15:46, 961 days ago
Message: Hmmm Mirza,

what will be in the sql file that has not been applied during the first impdp session?

@Tso: if there are missing privileges (for example on SYS objects), you might have to SELECT them from the source database to apply them in the target.

Best regards,

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

Subject: Re: schema permissions
Author: Mirza Hidayathullah Baig, United Kingdom
Date: Apr 20, 2017, 15:54, 961 days ago
Message: Dear Bruno,

i mean generate the sql file to know what are grants present in export full dumpfile.

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: schema permissions
Author: Bruno Vroman, Belgium
Date: Apr 21, 2017, 10:03, 961 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hi Mirza,

indeed I had not read your posts correctly.
But knowing what is in the dmp file doesn't help to know what is missing.
I would rather go to the source database to run things like
SELECT privilege, grantor || '.' || table_name trgt

FROM dba_tab_privs
WHERE grantee = 'SCOTT'
ORDER BY 2, 1;

SELECT * FROM dba_sys_privs WHERE grantee = 'SCOTT';
to know what has to be granted (or not, for example this might be the opportunity to make some "cleansing" and remove some privileges that are not necessary).

Tso: in addition to the SELECT I've shown you can also generate things like "SELECT 'grant ' || privilege || ' on ' || grantor... SCOTT;'" to have a script that actually grants the privileges.
Note that there is no harm if a privilege is granted several times (so what is in the dmp file is not critical)

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