Home » RDBMS Server » Security » User of DBA role (Oracle 11.2.0.3.0 , Linux 5)
User of DBA role [message #578489] Thu, 28 February 2013 12:56 Go to next message
Jack14
Messages: 333
Registered: December 2011
Location: INDIA
Senior Member
Hi,

Can we grant DBA role to users in database ? ( users like application users and users who are not part of DBA )

Regards,
Jack

Report message to a moderator

Re: User of DBA role [message #578493 is a reply to message #578489] Thu, 28 February 2013 13:34 Go to previous messageGo to next message
Michel Cadot
Messages: 54719
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No.
Not even to dba.
ALWAYS create your own roles, do NOT use Oracle predefined ones.

Regards
Michel

Report message to a moderator

Re: User of DBA role [message #578494 is a reply to message #578493] Thu, 28 February 2013 13:58 Go to previous messageGo to next message
Jack14
Messages: 333
Registered: December 2011
Location: INDIA
Senior Member
Michel,

May i know the reason why we should not give DBA role to other user ?

Thank you

[Updated on: Thu, 28 February 2013 13:59]

Report message to a moderator

Re: User of DBA role [message #578495 is a reply to message #578494] Thu, 28 February 2013 14:05 Go to previous messageGo to next message
Michel Cadot
Messages: 54719
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you want any user can drop your database?

Regards
Michel

Report message to a moderator

Re: User of DBA role [message #578496 is a reply to message #578495] Thu, 28 February 2013 14:24 Go to previous messageGo to next message
Jack14
Messages: 333
Registered: December 2011
Location: INDIA
Senior Member
Michel,

But only user with SYSDBA privilege has the privilege to drop the database.

DBA role has following privileges.

SELECT statement result is right here

Could you clarify ?

Regards,
Jack

[EDITED by LF: applied [spoiler] tags to cut a long story short]

[Updated on: Mon, 04 March 2013 14:13] by Moderator

Report message to a moderator

Re: User of DBA role [message #578497 is a reply to message #578496] Thu, 28 February 2013 14:44 Go to previous messageGo to next message
Littlefoot
Messages: 17256
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Could YOU (Jack14) clarify why do you think that any user (like application users) should have all (or part or most of) these privileges?

Report message to a moderator

Re: User of DBA role [message #578498 is a reply to message #578497] Thu, 28 February 2013 14:57 Go to previous messageGo to next message
Jack14
Messages: 333
Registered: December 2011
Location: INDIA
Senior Member
Littlefoot

Quote:
Could you (Jack14) clarify why do you think that any user (like application users) should have all (or part or most of) these privileges?

I think you have misunderstood my question.Please reread my question.
Regards,

Report message to a moderator

Re: User of DBA role [message #578501 is a reply to message #578498] Thu, 28 February 2013 15:44 Go to previous messageGo to next message
Michel Cadot
Messages: 54719
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think you misunderstood Littlefoot question which is the most important one.

And to answer your question, I said "drop the database" in the meaning of "drop all application data and accounts". If it rest an empty database, do you think (as an application user) you still have a database?

Regards
Michel

[Updated on: Thu, 28 February 2013 15:45]

Report message to a moderator

Re: Use of DBA role [message #578508 is a reply to message #578501] Thu, 28 February 2013 17:24 Go to previous messageGo to next message
Jack14
Messages: 333
Registered: December 2011
Location: INDIA
Senior Member
Quote:
I said "drop the database" in the meaning of "drop all application data and accounts". If it rest an empty database, do you think (as an application user) you still have a database?

Michel,
No .Obviously there wont be any record inside the database and there is no meaning to have the database then.

Regards,
Jack

Report message to a moderator

Re: Use of DBA role [message #578523 is a reply to message #578508] Fri, 01 March 2013 00:18 Go to previous messageGo to next message
Littlefoot
Messages: 17256
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Jack14, you listed 202 privileges. An ordinary user certainly doesn't need ALL of them. Moreover, he most probably doesn't need more than 5-6 of them, such as
- create session
- create table
- create procedure
- create sequence
- create view
- create trigger

That's just enough to get him started. If he needs additional privileges, it is easy to grant them later. But - hey, granting DBA to that kind of a user you described is a HUGE overkill.

As Michel said: if you grant DBA to your user, he'd be able to do a lot of things I'm quite sure you wouldn't be happy with. One of them is to drop any user. Or drop any table (which means: drop tables that don't belong to him, but any table in anyone's schema in that database).

So, if you are sure you want to do that, go ahead - it is your database, after all. Just don't come back in a week or two asking "how to restore tables someone dropped. Please note that I don't do backup. Help plz."

Report message to a moderator

Re: Use of DBA role [message #578723 is a reply to message #578523] Mon, 04 March 2013 07:30 Go to previous messageGo to next message
Jack14
Messages: 333
Registered: December 2011
Location: INDIA
Senior Member
Littlefoot,

Thanks for the explanations.

I have some more clarifications.I see some users having DBA,RESOURCE,CONNECT roles.The same user
has role based privilege on objects means he does not have direct privileges on objects that he owns whereas
he is able to access the objects through roles. ( Can we allow the users to access objects like that ? )

I am also scared revoking DBA,RESOURCE,CONNECT roles from users which will have any impact to database and application.

Could you clarify my doubts ?

Regards,
Jack

[Updated on: Mon, 04 March 2013 07:31]

Report message to a moderator

Re: Use of DBA role [message #578727 is a reply to message #578723] Mon, 04 March 2013 07:45 Go to previous messageGo to next message
Littlefoot
Messages: 17256
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Jack14
The same user has role based privilege on objects means he does not have direct privileges on objects that he owns

That is wrong, I'm afraid. The owner simply OWNS his objects and has ALL privileges on those objects. It seems that someone set it wrong there, in your database.

Michel told you ages ago:Michel

ALWAYS create your own roles, do NOT use Oracle predefined ones.

So, in your test database, create a few of your own roles (depending on user profile), revoke DBA, resource and connect roles from all users, grant CREATE SESSION and let them work. They won't be able to do much, but will report everything they can't do. Make notes, and then grant privileges (or roles) they really need, not the ones they have now.

Once you have it sorted, simply apply the same in your production database.

Report message to a moderator

Re: Use of DBA role [message #578729 is a reply to message #578723] Mon, 04 March 2013 07:49 Go to previous messageGo to next message
Michel Cadot
Messages: 54719
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
We can't say for YOUR application.
If you think a little bit, I think you will know the answer to your questions.

Quote:
Can we allow the users to access objects like that


Application users should get privileges ONLY through roles; no direct privileges (unless there is some special need that can't be achieved with roles; and for the moment I don't see any for application user).

Regards
Michel

Report message to a moderator

Re: Use of DBA role [message #578735 is a reply to message #578729] Mon, 04 March 2013 08:40 Go to previous messageGo to next message
Jack14
Messages: 333
Registered: December 2011
Location: INDIA
Senior Member
Michel,

Thank you.but i see DBA,RESOURCE role is granted to the user .Shall i revoke those roles from users and
would there be impact to database/application if i revoke it.

Regards,

Report message to a moderator

Re: Use of DBA role [message #578737 is a reply to message #578735] Mon, 04 March 2013 08:45 Go to previous messageGo to next message
Michel Cadot
Messages: 54719
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Already answered.
If you activate what is between your ears you should know the answer.

Regards
Michel

[Updated on: Mon, 04 March 2013 08:45]

Report message to a moderator

Re: Use of DBA role [message #578739 is a reply to message #578737] Mon, 04 March 2013 08:52 Go to previous message
Jack14
Messages: 333
Registered: December 2011
Location: INDIA
Senior Member
Eyes are more sufficient than ears to read the answers... Smile

Report message to a moderator

Previous Topic: How to rebuild AUD$
Next Topic: Given Grants doesn't reflect on Oracle forms 6i
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Jun 20 04:02:54 CDT 2013

Total time taken to generate the page: 0.24234 seconds
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.