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 hour1
Registered user hits last week395
Registered user hits last month1204
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, 180 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, 180 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, 180 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, 180 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, 179 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, 179 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, 179 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