Re: Row level security requirements, can I still use MySQL?

2004-11-18 Thread Luke Venediger 
Hi,

I think Peter's suggesting you implement that security model in your
application, rather than in the database server. Apart from probably
not supporting such a security model, it's easier to audit user
activity if you are controlling the security model.

Cheers,
Luke Venediger.


On Thu, 18 Nov 2004 11:46:15 +0100, Jonas Ladenfors
[EMAIL PROTECTED] wrote:
 Ok, jupp if I could use groups each group could have a shared key. How do
 you create groups and then add users to them in MySQL? Are you refering to
 the Linux systems user and groups? This idea should work but I am not
 familiar with how groups work in mysql. I need to be able to audit logs on a
 per user level, is this possible in this solution?
 
 /Jonas
 
 
 
 Hi
 
 I use a system based on linux securuty model
 
 create groups - this will define access to the data, so you need to group
 the data - and encrypt data the group can access using the password
 belonging to that group.
 
 make users members of any number of groups, as required.
 
 Users can then access any data they are untitled to, but  cannot read data
 encrytped with a password they do not have access to.
 
 You will need to use  software (php, C++, asp, whatever) to manage the
 user/group system.
 
 HTH
 
 Peter
 
  -Original Message-
  From: Jonas Ladenfors [mailto:[EMAIL PROTECTED]
  Sent: 18 November 2004 10:19
  To: 'Peter Lovatt'; 'Mysql (E-mail)'
  Subject: RE: Row level security requirements, can I still use MySQL?
 
 
  Yeah you are correct locking is something else I actually meant was
  restricted access.
 
  If I understand you correctly I would then encrypt all information in the
  table I was interested in restricting access to. But if two or more users
  was to share a row in the table they would need a shared key? and then
  several user collaborations would result in a lot of different
  keys. I have
  actually been thinking about this solution earlier, my problem with it is
  where to store the different keys that are needed. Forcing the user to
  manually keep track of 5 - 10 keys is to much to hope for sadly;)
 
  What I have been thinking about is some low-level way where you as an
  administrator can control users and groups and place restrictions on each
  row by tagging the row in some way? Or the user could tag his rows in
  someway.
 
  Is this how other RDBMS enforce access restrictions?
 
  Regards
  /Jonas
 
  -Original Message-
  From: Peter Lovatt [mailto:[EMAIL PROTECTED]
  Sent: den 18 november 2004 11:03
  To: Jonas Ladenfors; Mysql (E-mail)
  Subject: RE: Row level security requirements, can I still use MySQL?
 
 
  Hi
 
  What about encrypting the data using a password that is specific to the
  user. That way only those that know the password for that row can
  access it.
 
  Locking is really to stop two users editing the same record at the same
  time, rather than controlling access.
 
  HTH
 
  Peter
 
 
 
   -Original Message-
   From: Jonas Ladenfors [mailto:[EMAIL PROTECTED]
   Sent: 18 November 2004 09:46
   To: Mysql (E-mail)
   Subject: Row level security requirements, can I still use MySQL?
  
  
   Hello, I am in the position where I need row level user access, this is
   crucial in my current project. I know this has been discussed
   before and the
   answer has been use views when they become availble. But views
   would still
   allow the root user access to the complete table, wouldnt it? I
   would like
   to lock rows to certain user and not let anyone else see them,
   not even the
   root user.
  
   I have been thinking about using heap tables or trying to supply each
   user/group with their own dynamically created tables. But I
  always come to
   the conclusion that I am hacking away at something I do not fully
   understand
   and that I cannot guaranty that the end result will have the security I
   claim.
  
   Is this possible in MySQL?
   Does anyone know if it cab be performed with other RDBMS?
  
   Regards
   /Jonas
  
  
  
  
  
   --
   MySQL General Mailing List
   For list archives: http://lists.mysql.com/mysql
   To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
  
 
 
 
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 
 


-- 
Get Firefox Browser! Reclaim the web. http://getfirefox.com/

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Select with an IF statements

2004-10-27 Thread Luke Venediger 
Hi,

I'm trying to do the following:

SELECT
  IF((ProductStatus IS NOT NULL), Available, Not Available) as ProductStatus
FROM
  tb_Product
WHERE
  ProductName = MyProduct;

It works fine if the ProductName MyProduct works, and returns
Available. However, if the product name doesn't work the query
doesn't return any rows. I would like it to return Not Available if
the product isn't found.

Is there a better way to do this?

Thanks,
Luke Venediger.
-- 
Get Firefox Browser! Reclaim the web. http://getfirefox.com/

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Select with an IF statements

2004-10-27 Thread Luke Venediger 
Thanks Paul!

Luke Venediger.


On Wed, 27 Oct 2004 16:17:51 -0500, Paul DuBois [EMAIL PROTECTED] wrote:
 At 22:58 +0200 10/27/04, Luke Venediger wrote:
 Hi,
 
 I'm trying to do the following:
 
 SELECT
IF((ProductStatus IS NOT NULL), Available, Not Available) as
 ProductStatus
 FROM
tb_Product
 WHERE
ProductName = MyProduct;
 
 It works fine if the ProductName MyProduct works, and returns
 Available. However, if the product name doesn't work the query
 doesn't return any rows. I would like it to return Not Available if
 the product isn't found.
 
 Is there a better way to do this?
 
 SELECT IF(COUNT(*),Available,Not Available) As ProductStatus
 FROM
tb_Product
 WHERE
ProductName = MyProduct;
 
 
 --
 Paul DuBois, MySQL Documentation Team
 Madison, Wisconsin, USA
 MySQL AB, www.mysql.com
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 
 


-- 
Get Firefox Browser! Reclaim the web. http://getfirefox.com/

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Select with an IF statements

2004-10-27 Thread Luke Venediger 
Hi Rhino,

I don't think that answer solves the problem. I do use a programming
language, and doing a query like this means I can make use of the
database engine and not add overhead to my application.

Cheers,
Luke Venediger.

On Wed, 27 Oct 2004 17:26:56 -0400, Rhino [EMAIL PROTECTED] wrote:
 
 - Original Message -
 From: Luke Venediger [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 Sent: Wednesday, October 27, 2004 4:58 PM
 Subject: Select with an IF statements
 
  Hi,
 
  I'm trying to do the following:
 
  SELECT
IF((ProductStatus IS NOT NULL), Available, Not Available) as
 ProductStatus
  FROM
tb_Product
  WHERE
ProductName = MyProduct;
 
  It works fine if the ProductName MyProduct works, and returns
  Available. However, if the product name doesn't work the query
  doesn't return any rows. I would like it to return Not Available if
  the product isn't found.
 
  Is there a better way to do this?
 
 Yes; use a programming language like Java, Perl or PHP.
 
 Rhino
 
 


-- 
Get Firefox Browser! Reclaim the web. http://getfirefox.com/

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Conditional statement: IF

2004-10-22 Thread Luke Venediger 
Hi,

I'm trying to write a conditional statement in my query. Is the
following possible in MySQL?

IF(@SomeValue = 1)
THEN
 SELECT The value is 1;
ELSE
 SELECT The value is not 1;
END IF

I've tried different variations and nothing seems to be working (I'm
getting syntax errors). I'm using MySQL 4.0.18. Any help would be
greatly appreciated.

Thanks,
Luke Venediger.

-- 
Get Firefox Browser! Reclaim the web. http://getfirefox.com/

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Conditional statement: IF

2004-10-22 Thread Luke Venediger 
Hi Anders,

Thanks for that. Yes, I have come from an MSSQL environment, and I'm
using .Net 1.1 with the MySQL Connector/Net.  The idea behind using
conditional statements was to assign a query to a business task. For
example, I could write a query to handle adding items to a shopping
cart. In the query, before I add the item, I need to check if the item
is in stock. If it's not in stock, I need to return a result set that
indicates there has been an error, i.e. There is no stock of the
requested item.

Putting this logic in my code means having to execute a number of
statements with code checks in-between, where I would rather only
execute one query.

Cheers,
Luke Venediger.


On Fri, 22 Oct 2004 18:49:54 +0200, Anders Karlsson [EMAIL PROTECTED] wrote:
 I don't know why you want to do this, but looking at your sybtax, it
 seems like you come
 from a Sybase or SQL Server environment. What you are trying to achieve
 is the
 way it is done in Transact SQL, where the conditional statements and
 stuff like that which
 are typically used in stored procedures, may also be used outside stored
 procedures.
 
 As someone else suggested, a real programming environment might be more
 appropriate,
 with a MySQL connection. Like Perl or so.
 
 Depite this, the simple example you may well be done with reasonably
 normal SQL in MySQL
 like this:
 
 select IF(@val = 1,'It is 1', 'It is not one');
 
 But I suspect you want something more advanced than this, as this is not
 the most useful program the
 world has seen. (But not the least useful either).
 
 /Karlsson
 Luke Venediger wrote:
 
 Hi,
 
 I'm trying to write a conditional statement in my query. Is the
 following possible in MySQL?
 
 IF(@SomeValue = 1)
 THEN
  SELECT The value is 1;
 ELSE
  SELECT The value is not 1;
 END IF
 
 I've tried different variations and nothing seems to be working (I'm
 getting syntax errors). I'm using MySQL 4.0.18. Any help would be
 greatly appreciated.
 
 Thanks,
 Luke Venediger.
 
 
 
 
 -- 
 __  ___ ___   __
/  |/  /_ __/ __/ __ \/ /  Anders Karlsson ([EMAIL PROTECTED])
   / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer
  /_/  /_/\_, /___/\___\_\___/ Stockholm
 ___/   www.mysql.com Cellphone: +46 708 608121
 
 


-- 
Get Firefox Browser! Reclaim the web. http://getfirefox.com/

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: **[SPAM]** Conditional statement: IF

2004-10-22 Thread Luke Venediger 
Hi, 

Apologies, yes I have RTFM, and should've clarified my question. I
actually need to do something more like this:

IF(@SomeValue = 1)
BEGIN
THEN
  SELECT Data as Column1,
MoreData as Column2
ELSE
  SELECT SomeValue is not = 1 as Error
END IF

Thanks,
Luke Venediger.



On Fri, 22 Oct 2004 09:39:21 -0500, Jay Blanchard
[EMAIL PROTECTED] wrote:
 [snip]
 I'm trying to write a conditional statement in my query. Is the
 following possible in MySQL?
 
 IF(@SomeValue = 1)
 THEN
  SELECT The value is 1;
 ELSE
  SELECT The value is not 1;
 END IF
 
 I've tried different variations and nothing seems to be working (I'm
 getting syntax errors). I'm using MySQL 4.0.18. Any help would be
 greatly appreciated.
 [/snip]
 
 Have you RTFM?
 
 SELECT IF(@SomeValue = 1, 'The value is 1', 'The value is not 1')
 
 http://dev.mysql.com/doc/mysql/en/Control_flow_functions.html
 


-- 
Get Firefox Browser! Reclaim the web. http://getfirefox.com/

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Raiserror in MySQL? Raising a user exception in a script

2004-10-21 Thread Luke Venediger 
Hi,

I'm trying to raise an error in a MySql query, in the same way that
would be done in an MSSQL script:

RAISERROR('My error message', 16, -1)

Is there a way to do this in MySQL?

Thanks,
Luke Venediger.
-- 
Get Firefox Browser! Reclaim the web. http://getfirefox.com/

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Raiserror in MySQL? Raising a user exception in a script

2004-10-21 Thread Luke Venediger 
Hi Paul,

Thanks for the information. Is there an alternative? Are there plans
to add this in the future?

Cheers,
Luke Venediger.


On Thu, 21 Oct 2004 08:46:04 -0500, Paul DuBois [EMAIL PROTECTED] wrote:
 At 15:29 +0200 10/21/04, Luke Venediger wrote:
 Hi,
 
 I'm trying to raise an error in a MySql query, in the same way that
 would be done in an MSSQL script:
 
 RAISERROR('My error message', 16, -1)
 
 Is there a way to do this in MySQL?
 
 No.
 
 --
 Paul DuBois, MySQL Documentation Team
 Madison, Wisconsin, USA
 MySQL AB, www.mysql.com
 


-- 
Get Firefox Browser! Reclaim the web. http://getfirefox.com/

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]