|
Home > Archive > Oracle Server > July 2005 > Newbie: Simple User question
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Newbie: Simple User question
|
|
| bbreukelen@gmail.com 2005-07-25, 1:26 pm |
| I'm new to oracle and am used to Mysql.
I now use oracle because our Remedy application works best with oracle.
I want to create a useraccount in oracle to access the database tables
but I'm not sure how to restrict it.
The application created an oracle user ARAdmin and a tablespace
ARSystem.
I wish to create an oracle user accessible by the webserver only
limited to localhost and with access to insert, update, select and
delete in all tables within the tablespace from the arsystem
tablespace.
That's the only thing I'm interested in and frankly I'm not very much
into learning all about oracle.
Thanks a lot,
Boudewijn
| |
| fitzjarrell@cox.net 2005-07-25, 8:25 pm |
|
Comments embedded.
bbreukelen@gmail.com wrote:
> I'm new to oracle and am used to Mysql.
> I now use oracle because our Remedy application works best with oracle.
> I want to create a useraccount in oracle to access the database tables
> but I'm not sure how to restrict it.
>
YOu HAVE a user to access the database tables. Why do you need
another?
> The application created an oracle user ARAdmin and a tablespace
> ARSystem.
>
And that is the user you need. Creating a second user with such access
is asking for trouble in more ways than one, as your data integrity
will have flown straight out the proverbial window, along with any good
common sense you may have had prior to this request.
> I wish to create an oracle user accessible by the webserver only
> limited to localhost and with access to insert, update, select and
> delete in all tables within the tablespace from the arsystem
> tablespace.
>
Unless you create an on-logon trigger to kill any session not
originating from the localhost you can't restrict client connections to
a specific server. Also, you can't turn off the listener as Remedy
needs to access the database. And you can't create such a trigger
unless you also restrict the program used to sqlplus.exe, sqlplusw.exe
or sqlplus (from a UNIX system) as Remedy would also fail to connect
using that criteria, making Remedy unusable.
Oracle is NOT MySQL.
> That's the only thing I'm interested in and frankly I'm not very much
> into learning all about oracle.
>
Obvioiusly, as your question clearly indicates a lack of knowledge on
how Oracle operates. Simply put, what you want cannot be done, since,
as I stated above, Oracle IS NOT MySQL. You need to STOP thinking
there's any similarity outside the syntax (which was, in my opinion,
heavily 'lifted' from Oracle) and decide to actually LEARN the DBMS
you're using. If you won't do that you should go BACK to MySQL since
you obviously took the time to learn IT before you decided upon its
use.
So, frankly, you HAVE a user account clearly designed for the access
you desire. There is no need for another, and you cannot, using simple
means a la MySQL, restrict the machine from which a user connects, a
fact you would have soon realised had you taken the time and the effort
to actually learn the mechanism of user access Oracle employs.
And, frankly, if you're going to be this lazy, you don't deserve the
explanation you just received.
> Thanks a lot,
>
> Boudewijn
David FItzjarrell
| |
|
|
bbreukelen@gmail.com wrote:
>That's the only thing I'm interested in and frankly I'm not very much
>into learning all about oracle.
With a lazy shit attitude like that, you're going to get the lazy shit
response, "JFGI(*) you lazy shit".
I found what you were looking for after 35 seconds, but seeing as
you're lazy, I'm not going to give you the search key.
Paul...
(*) Just F..... Google It.
>Boudewijn
--
plinehan __at__ yahoo __dot__ __com__
XP Pro, SP 2,
Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.1.0;
When asking database related questions, please give other posters
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work!").
Thanks.
Furthermore, as a courtesy to those who spend
time analysing and attempting to help, please
do not top post.
| |
|
|
fitzjarrell@cox.net wrote:
>Unless you create an on-logon trigger to kill any session not
>originating from the localhost you can't restrict client connections to
>a specific server.
Ahem... what about restricting the IP address of the *_database_*
client to 127.0.0.1 - i.e. the web server, which *_it_* does the
serving of pages to the outside world? Obviously a web site that can
only be accessed by one machine is pretty pointless, though given the
laziness of the programmer, who knows?
At least that's what I thought the original question was?
Paul...
>David FItzjarrell
--
plinehan __at__ yahoo __dot__ __com__
XP Pro, SP 2,
Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.1.0;
When asking database related questions, please give other posters
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work!").
Thanks.
Furthermore, as a courtesy to those who spend
time analysing and attempting to help, please
do not top post.
| |
| fitzjarrell@cox.net 2005-07-25, 8:25 pm |
|
Paul wrote:
> fitzjarrell@cox.net wrote:
>
>
>
>
>
> Ahem... what about restricting the IP address of the *_database_*
> client to 127.0.0.1 - i.e. the web server, which *_it_* does the
> serving of pages to the outside world? Obviously a web site that can
> only be accessed by one machine is pretty pointless, though given the
> laziness of the programmer, who knows?
>
>
> At least that's what I thought the original question was?
>
>
> Paul...
>
>
> --
>
> plinehan __at__ yahoo __dot__ __com__
>
> XP Pro, SP 2,
>
> Oracle, 9.2.0.1.0 (Enterprise Ed.)
> Interbase 6.0.1.0;
>
> When asking database related questions, please give other posters
> some clues, like operating system, version of db being used and DDL.
> The exact text and/or number of error messages is useful (!= "it didn't work!").
> Thanks.
>
> Furthermore, as a courtesy to those who spend
> time analysing and attempting to help, please
> do not top post.
With MySQL it's possible to restrict a user account to a specific
server, regardless of how other user accounts can connect. This is, I
believe, what the OP wants. And, as far as I know, the only way to get
that is with an on-logon trigger.
David Fitzjarrell
| |
| bbreukelen@gmail.com 2005-07-26, 7:23 am |
| First of all thank you all for the responses.
The reason I want the new user too access the tablespace is security. I
don't want to use the originat user because a have to embed the
username/password into the PHP code and other people are able to see
the code that are not allowed to enter the oracle dbase.
The Remedy dbase user has far to many privaliges to be out on the
street.
I wanted to restrict the user to the dbase only coming from localhost
so people cannot use the account to connect to the dbase from remote
computers but this is not very important if the restrictions are ok.
I already created a user with the role connect and I found out that I
can give the user permissions using eg. -GRANT SELECT ON
"ARADMIN"."ARSCHEMA" TO "Webserver"-
But I'd like to use something like -GRANT SELECT ON "ARADMIN"."%" TO
"Webserver"- but this doesn't work.
About the last part, I'm not lazy. It's just that I looked up a lot of
responses on similar questions and the answer always is 'Pickup a book
and learn all about the oracle'.
I can understand that but the project does not allow me to and this is
the only thing I need to setup. The rest will simply be handled by the
Remedy application so I feel like it's a waste of time for me.
I can imagine that some of you don't want to answer my question because
of this reason. That's not a problem at all. Just don't waste you time
by giving a response without an answer.
Thanks again,
Boudewijn van Breukelen
| |
| IANAL_VISTA 2005-07-26, 9:23 am |
| bbreukelen@gmail.com wrote in
news:1122374256.385501.320080@g43g2000cwa.googlegroups.com:
> First of all thank you all for the responses.
>
> The reason I want the new user too access the tablespace is security. I
> don't want to use the originat user because a have to embed the
> username/password into the PHP code and other people are able to see
> the code that are not allowed to enter the oracle dbase.
> The Remedy dbase user has far to many privaliges to be out on the
> street.
>
> I wanted to restrict the user to the dbase only coming from localhost
> so people cannot use the account to connect to the dbase from remote
> computers but this is not very important if the restrictions are ok.
>
> I already created a user with the role connect and I found out that I
> can give the user permissions using eg. -GRANT SELECT ON
> "ARADMIN"."ARSCHEMA" TO "Webserver"-
>
> But I'd like to use something like -GRANT SELECT ON "ARADMIN"."%" TO
> "Webserver"- but this doesn't work.
In cases like this I write SQL which generates SQL like:
SPOOL MYNEW.SQL
SELECT 'GRANT SELECT ON ' || TABLE_NAME || ' TO WEBSERVER;' FROM
USER_TABLES;
SPOOL OFF
@MYNEW.SQL
| |
| fitzjarrell@cox.net 2005-07-26, 11:23 am |
|
bbreukelen@gmail.com wrote:
> First of all thank you all for the responses.
>
> The reason I want the new user too access the tablespace is security. I
> don't want to use the originat user because a have to embed the
> username/password into the PHP code and other people are able to see
> the code that are not allowed to enter the oracle dbase.
> The Remedy dbase user has far to many privaliges to be out on the
> street.
>
> I wanted to restrict the user to the dbase only coming from localhost
> so people cannot use the account to connect to the dbase from remote
> computers but this is not very important if the restrictions are ok.
>
> I already created a user with the role connect and I found out that I
> can give the user permissions using eg. -GRANT SELECT ON
> "ARADMIN"."ARSCHEMA" TO "Webserver"-
>
> But I'd like to use something like -GRANT SELECT ON "ARADMIN"."%" TO
> "Webserver"- but this doesn't work.
>
> About the last part, I'm not lazy. It's just that I looked up a lot of
> responses on similar questions and the answer always is 'Pickup a book
> and learn all about the oracle'.
>
> I can understand that but the project does not allow me to and this is
> the only thing I need to setup. The rest will simply be handled by the
> Remedy application so I feel like it's a waste of time for me.
>
> I can imagine that some of you don't want to answer my question because
> of this reason. That's not a problem at all. Just don't waste you time
> by giving a response without an answer.
>
> Thanks again,
>
> Boudewijn van Breukelen
Your original post indicated the following:
"I wish to create an oracle user accessible by the webserver only
limited to localhost and with access to insert, update, select and
delete in all tables within the tablespace from the arsystem
tablespace."
Given that criteria what good does it do you to have a second, possibly
publicly accessable user account which can insert, delete and update
data for this application? Where is the security in that thought? I
fail to understand how that makes your system safe, as this new user
has the same access rights as the schema owner except the ability to
create any objects. It also provides any number of ways to use sql
injection to damage/destroy your data. I can undestand having a user
account which can VIEW data in another schema, but to grant all
possible data manipulation options to a second account provides, in my
mind, no security at all.
Of course, I guess I should expect this, given your desire to forego
undestanding Oracle in deference to a 'quick fix'.
David Fitzjarrell
| |
| xhoster@gmail.com 2005-07-26, 1:23 pm |
| bbreukelen@gmail.com wrote:
> First of all thank you all for the responses.
>
> The reason I want the new user too access the tablespace is security.
....
>
> I can understand that but the project does not allow me to and this is
> the only thing I need to setup. The rest will simply be handled by the
> Remedy application so I feel like it's a waste of time for me.
>
> I can imagine that some of you don't want to answer my question because
> of this reason. That's not a problem at all. Just don't waste you time
> by giving a response without an answer.
Well, I'll waste my time anyway. If you were just mucking around with
Oracle as a hobby or something and wanted some help making an unsecure
by-the-seat-of-the-pants system, I would be inclined to help you (to the
limited extent I could). But you say you are interested in security. If
you are truly interested in security, then either learn what you are doing,
or hire someone who knows what they are doing. End of story.
Xho
--
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service $9.95/Month 30GB
| |
| Frank van Bortel 2005-07-26, 8:23 pm |
| bbreukelen@gmail.com wrote:
> First of all thank you all for the responses.
>
Hm
> The reason I want the new user too access the tablespace is security. I
You cannot. You can restrict (rather: grant) access to Oracle objects,
not the underlying physical structures. If a table is once created by
user A in table space B, you cannot restrict user A from accessing this
table.
> don't want to use the originat user because a have to embed the
> username/password into the PHP code and other people are able to see
> the code that are not allowed to enter the oracle dbase.
Then encrypt it.
> The Remedy dbase user has far to many privaliges to be out on the
> street.
The redesign it. Or lock the account.
>
> I wanted to restrict the user to the dbase only coming from localhost
Then don't start a listener.
> so people cannot use the account to connect to the dbase from remote
> computers but this is not very important if the restrictions are ok.
>
> I already created a user with the role connect and I found out that I
> can give the user permissions using eg. -GRANT SELECT ON
> "ARADMIN"."ARSCHEMA" TO "Webserver"-
>
> But I'd like to use something like -GRANT SELECT ON "ARADMIN"."%" TO
> "Webserver"- but this doesn't work.
Well, it does, but slightly different syntax... As you do not wish
to learn anything about Oracle, I will not be a partypooper and explain.
>
> About the last part, I'm not lazy. It's just that I looked up a lot of
> responses on similar questions and the answer always is 'Pickup a book
> and learn all about the oracle'.
>
> I can understand that but the project does not allow me to and this is
> the only thing I need to setup. The rest will simply be handled by the
> Remedy application so I feel like it's a waste of time for me.
Remedy, as in BMCRemedy? Or OGIT/WebPharao?
>
> I can imagine that some of you don't want to answer my question because
> of this reason. That's not a problem at all. Just don't waste you time
> by giving a response without an answer.
>
> Thanks again,
>
> Boudewijn van Breukelen
>
--
Regards,
Frank van Bortel
| |
|
|
|
|
|