Home » RDBMS Server » Server Administration » Creating default roles (oracle 9.2.0.4.0, windows xp.)
Creating default roles [message #341687] Tue, 19 August 2008 23:58 Go to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

Hi all,
Today while checking database, i came accorss the point that there are only two dba roles in database.
SQL>select * from dba_roles;

ROLE                          |PASSWORD
------------------------------|--------
CONNECT                       |NO
RESOURCE                      |NO


I have created one user specifically for full database export. But in database no other roles are there(not even the EXP_FULL_DATABASE role).

Please tell me, is there any utility or way to create all missing default roles in database?
Re: Creating default roles [message #341689 is a reply to message #341687] Wed, 20 August 2008 00:03 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>Please tell me, is there any utility or way to create all missing default roles in database?
notepad and/or sqlplus can create any ROLE desired.
Re: Creating default roles [message #341693 is a reply to message #341689] Wed, 20 August 2008 00:23 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

I know, I can create roles using "create role" statements, using notepad and/or sqlplus.

But EXP_FULL_DATABASE, DBA, IMP_FULL_DATABASE, SELECT_CATALOG_ROLE, RECOVERY_CATALOG_OWNER, GATHER_SYSTEM_STATISTICS etc are not simple roles, right? These roles contains authority for adminstration related work. How can i creat them..?

Dear anacedent,
You are one of the respected seniors of our fourms. I hadn't not expecting this kind of answers from you dear.

Regards,
Dipali..
Re: Creating default roles [message #341696 is a reply to message #341687] Wed, 20 August 2008 00:30 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
ROLEs do not exist automagically. They are created & in your situation eliminated.
V9 is nolonger supported.
If you have a functioning V9 instance with the desired ROLEs, then extract them & apply to the instance which has been abused.
Re: Creating default roles [message #341707 is a reply to message #341687] Wed, 20 August 2008 01:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think the safest way is to recreate the database.
Otherwise you can reverse engineer sql.bsq file and then rerun catalog.sql, catproc.sql, catexp.sql and all scripts that have been executed.

Regards
Michel
Re: Creating default roles [message #341709 is a reply to message #341707] Wed, 20 August 2008 01:33 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

Thanks Ana and Michel,

From other 9i database (working properly), i got the list of all the missing roles and all the privilages assigned to those roles. I have created script to create those roles and grant priviledges to them (create role and grant statements).

Is it okay if i run that script as a SYS user to create missing roles and assign associated priviladges to them? Will this damage my database?
Re: Creating default roles [message #341736 is a reply to message #341709] Wed, 20 August 2008 02:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is it okay if i run that script as a SYS user to create missing roles and assign associated priviladges to them?

Maybe yes or not, noone can know.

Quote:
Will this damage my database?

Less then not having the roles but once more it depends if you have all what is needed for the features you have installed in this database.

Regards
Michel
Re: Creating default roles [message #341774 is a reply to message #341736] Wed, 20 August 2008 04:49 Go to previous message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

Hi,

Running catproc utility solved the problem..

Thanks.. Smile

Regards,
Dipali..
Previous Topic: Shrink UNDO tablespace
Next Topic: set up an Oracle 10g database from existing ones
Goto Forum:
  


Current Time: Fri Dec 02 21:06:42 CST 2016

Total time taken to generate the page: 0.14818 seconds