No question at this time
DBA Top 10
1 B. Vroman 16200
2 M. Cadot 10100
3 T. Boles 8150
4 J. Schnackenberg 7700
5 A. Kavsek 7000
6 M. Hidayathullah ... 3000
7 G. Lambregts 1100
7 P. Wisse 1100
9 T. P 1000
10 B. Derous 500
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48261
Total active users1615
Act. users last 24h3
Act. users last hour0
Registered user hits last week315
Registered user hits last month1260
Go up

ROLES AND ASSOCIATED PRIVILEGES !
Next thread: Error on datapump import on XMLSCHEMA objects
Prev thread: Large volume of data in inserting in our database.

Message Score Author Date
Dear all , Why roles and associated privileges ...... Jill Salalila Sep 10, 2018, 14:21
Yes there is a way: you proper use export/import...... Michel Cadot Sep 10, 2018, 15:01
Hi Mike, I am NOT getting ! <b> Please elabora...... Jill Salalila Sep 10, 2018, 15:26
Don't you understand <b>YOU MUST SHOW US WHAT YO...... Michel Cadot Sep 10, 2018, 15:30
Check your log file for the import. My guess is t...... Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 Pts Tim Boles Sep 10, 2018, 16:31

Follow up by mail Click here


Subject: ROLES AND ASSOCIATED PRIVILEGES !
Author: Jill Salalila, Philippines
Date: Sep 10, 2018, 14:21, 10 days ago
Os info: RHEL 5.7
Oracle info: 11.2.0.1
Message: Dear all ,

Why roles and associated privileges are NOT imported automatically when we import a schema ?

For Example

 

create user usr1 identified by usr1 quota 100m on users;
create role testrole;
grant create table, create session to testrole;
grant testrole to usr1;
create table usr1.emp as select * from scott.emp;
SQL> conn usr1/usr1
connected.

>> Take export of usr1 Schema

>> Import usr1 schema in another Database

After import,

SQL>conn usr1/usr1
ERROR:
ORA-01045: user USR1 lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.


QUESTION

IS there any way to get roles and associated privileges by default when we do export & import a Schema in 11g or 12c ?






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

Subject: Re: ROLES AND ASSOCIATED PRIVILEGES !
Author: Michel Cadot, France
Date: Sep 10, 2018, 15:01, 10 days ago
Message:
Yes there is a way: you proper use export/import.

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

Subject: Re: ROLES AND ASSOCIATED PRIVILEGES !
Author: Jill Salalila, Philippines
Date: Sep 10, 2018, 15:26, 10 days ago
Message: Hi Mike,

I am NOT getting !
Please elaborate little more ..

I am trying to find answer ..still not
Please update how can i achieve that one ?

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: ROLES AND ASSOCIATED PRIVILEGES !
Author: Michel Cadot, France
Date: Sep 10, 2018, 15:30, 10 days ago
Message:
Don't you understand YOU MUST SHOW US WHAT YOU DO AND GET if you want we can see what's wrong in it?

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

Subject: Re: ROLES AND ASSOCIATED PRIVILEGES !
Author: Tim Boles, United States
Date: Sep 10, 2018, 16:31, 10 days ago
Score:   Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 Pts
Message: Check your log file for the import. My guess is that it tells you something like
Processing object type SCHEMA_EXPORT/ROLE_GRANT

ORA-39083: Object type ROLE_GRANT failed to create with error:
ORA-01919: role 'TESTROLE' does not exist
Failing sql is:
GRANT "TESTROLE" TO "USR1"


It does import "rights" to the role but not the role itself. You either will need to do an export of the roles and import them into the database or precreate the role in the database you are importing into.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here