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: 497
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: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
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: 497
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: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
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: 497
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: 21823
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: 497
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: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
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: 497
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: 21823
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: 497
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: 21823
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: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
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: 497
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: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
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 messageGo to next message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
Eyes are more sufficient than ears to read the answers... Smile

Report message to a moderator

Re: Use of DBA role [message #600199 is a reply to message #578739] Sat, 02 November 2013 19:44 Go to previous messageGo to next message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
Hi Michel,

Quote:
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).


May i know who has to decide in granting these privileges to application users in database .. is has to be decided by DBA or application team ?

If it is application team , can you explain why ?

Note:--

I know i have open this thread a long time back but had a doubt related to this thread , so wanted to continue in this same thread but with different question.

With Regards,

Report message to a moderator

Re: Use of DBA role [message #600200 is a reply to message #600199] Sat, 02 November 2013 20:08 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>May i know who has to decide in granting these privileges to application users in database .. is has to be decided by DBA or application team ?
The application team can requests privileges; but the privileges should NEVER include the keyword "ANY".
Who gets fired when the application has data go missing due to user with privileges does wrong action?
Who has to recover data that goes missing due to user with privileges does wrong action?
I say that the DBA has the final say over privileges; but then again I am biased since I am a DBA.

Report message to a moderator

Re: Use of DBA role [message #600201 is a reply to message #600200] Sat, 02 November 2013 21:18 Go to previous messageGo to next message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
Hi Blackswan,

I do not completely agree with your answer that DBA has to determine the privileges required for application users.Because DBA is not the one who will create procedure,functions,packages etc.. as per requirement.So it should be them i guess since i am also not clear on this.

Any other answers from senior folks ?

Report message to a moderator

Re: Use of DBA role [message #600202 is a reply to message #600201] Sat, 02 November 2013 22:07 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Why did you not answer the 2 questions that I ask you?

>I do not completely agree with your answer that DBA has to determine the privileges required for application users.

I did NOT say that the DBA determines the required privileges.
The application team should make the determination for the required privileges.

>Because DBA is not the one who will create procedure,functions,packages etc.. as per requirement.
I agree with above.

If/when the DBA agrees with the application team, then no problem exists regarding who gets which privileges.

With Oracle RDBMS everything is forbidden; except that which is explicitly GRANTED.
The requirements & how Oracle manages access to objects should dictate who gets which privileges.
Privileges granted should NEVER exceed the requirements.

Report message to a moderator

Re: Use of DBA role [message #600203 is a reply to message #600202] Sun, 03 November 2013 01:29 Go to previous messageGo to next message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
May be wrongly understood and thanks for the explanations.

Report message to a moderator

Re: Use of DBA role [message #600218 is a reply to message #600203] Sun, 03 November 2013 08:35 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
why should answer your questions when you refuse to answer our questions to you?
Who gets fired when the application has data go missing due to user with privileges does wrong action?
Who has to recover data that goes missing due to user with privileges does wrong action?

Report message to a moderator

Re: Use of DBA role [message #600225 is a reply to message #600218] Sun, 03 November 2013 10:41 Go to previous message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
Hi Blackswan,

Sorry for not answering your question.Here is the answer for your question.

Quote:
Who gets fired when the application has data go missing due to user with privileges does wrong action -- DBA?
Who has to recover data that goes missing due to user with privileges does wrong action -- DBA?


I appreciate Littlefoot/Michel/Blackswan for answering my question.

Thank you everyone...

Report message to a moderator

Previous Topic: Package body,triggers are not appearing on "Connect" user
Next Topic: security policy - vpd vs ols
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Feb 01 21:29:46 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.