I have a MySQL database where I'm using mysqldump to make daily back-
ups of the files. I have some views defined. A problem that I'm
running into is that when I tyr to run these dumps on a development
machine, the views don't get created because each line for the view
has a DEFINER= part. The definer is a user that doesn't exist on the
test system, and the ip address is another system altogether.

 CREATE ALGORITHM=UNDEFINED DEFINER=`user`@`ip_address/netmask` SQL
SECURITY DEFINER VIEW...

And I get the error
ERROR 1227 (42000): Access denied; you need the SUPER privilege for
this operation

I tried it on another system, and the views did get created, but when
I try a select from one of them,
ERROR 1449 (HY000): There is no 'user'@'ip_address/netmask' registered

Is it a problem with how I created the view originally? I did it using
phpMyAdmin, so to test, I created the same view using CREATE VIEW...,
but I still get a DEFINER statement on the export.

So how can I do a dump and just get CREATE VIEW...?

I'm using version 10.10 of mysqldump.
mysqldump  Ver 10.10 Distrib 5.0.16, for pc-linux-gnu (i386)

Give yourself the SUPER priv. Or try it with a user ID that has it.

Wild guess that there isn't any such user as 'user'@'ip_address/netmask'
on that system. Create them.

--
The pluses in my current job include laughing in the face of Nobel
laureates who have just lost the only copy of their data.  (Hey,
I'm still a BOFH).
              -- Bob Dowling

On Jul 30, 1:18 pm, "Peter H. Coffin" <hell...@ninehells.com> wrote:

Is there any way around this? I'm on a shared web host, and I can't
give myself ( nor will they give me ) those privileges.

Well, duh.

What you're saying is that I need to synchronize the username and
passwords between my in-host development site, and my production
webhost?

Why do views require a particular usename and ip address, when other
things, such as tables, do not? Why do views have this extra security
or restriction, where an area where a user have more potential to do
damage, such as access to basic tables, do not?

It seems kind of problematic; if I wanted to develop a web project and
distribute it, I would have to either hand-edit the CREATE sqls, or
tell people to add a particular user/netmask on the system they're
deploying on.

Since you are running with SQL SECURITY DEFINER, the view runs with
the privileges of the DEFINER.  (That is something I wouldn't give
out lightly; it's like a set-uid UNIX program.)  Since in your case
that user doesn't exist, this is a problem.

Does this problem also occur with an unknown user and SQL SECURITY INVOKER?

On Aug 4, 11:00 am, gordonb.qt...@burditt.org (Gordon Burditt) wrote:

Are these security definers something set up on the MySQL server? It's
on a shared web host, so it's not something I have complete control
over.

It's something you set when you define a view, either explicitly or by
default.  Look at the syntax for CREATE VIEW.  The DEFINER is taken from
how you logged in to MySQL, unless you have administrative privileges.

On Aug 5, 12:55 pm, gordonb.tw...@burditt.org (Gordon Burditt) wrote:

Gordon, thanks for that information. I couldn't find it anywhere.

I guess what's still a question to me, is what's the design reason on
MySQL's part for doing this for, say, views, but not for table create
statements?

I tried creating a view from the command-line interface, without
specifying a security definer. When I ask for the CREATE TABLE
statement back, it has a security definer. This is because of how the
hosting company has access to mysql set up, right? So there's nothing
I can do about it, really?

---------------------------
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 54998 to server version: 5.0.67-log

mysql> use database;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CREATE TABLE `tTest` (`sort_order` INT NOT NULL) ENGINE =
InnoDB ;

mysql> CREATE ALGORITHM = UNDEFINED VIEW vTest AS SELECT * FROM tTest
ORDER BY sort_order;
SHOW CREATE TABLE vTest;Query OK, 0 rows affected (0.03 sec)

mysql> SHOW CREATE TABLE vTest;
+-------
+-------------------------------------------------------------------------- --------------------------------------------------------------------------- -------------------------------------------
+
| View  | Create
View
|
+-------
+-------------------------------------------------------------------------- --------------------------------------------------------------------------- -------------------------------------------
+
| vTest | CREATE ALGORITHM=UNDEFINED
DEFINER=`user`@`share.webhost.com` SQL SECURITY DEFINER VIEW `vTest`
AS select `tTest`.`sort_order` AS `sort_order` from `tTest` order by
`tTest`.`sort_order` |
+-------
+-------------------------------------------------------------------------- --------------------------------------------------------------------------- -------------------------------------------
+
1 row in set (0.01 sec)
---------------------------------------

I didn't have a security definer statement when I created the view,
but I got it when I asked for the CREATE TABLE back. That in itself
doesn't really concern me; All I need is to have more portable
sqldumps. That's where I don't want the security definer ; I'd like to
be able to run my dumps on any system without editing the text at
all.

Tables do not contain code you can EXECUTE.  Views do.  Code needs
to run with the privileges of a specific, and existing, user.  You
get to choose the privileges of the user using the view (SQL SECURITY
INVOKER) or the privileges of a named user (SQL SECURITY DEFINER,
with DEFINER set to that user).

Do you understand what set-uid root under UNIX means?  It's much
the same idea.  You can create tables accessible only by the DBA,
and then create views for each department that show only what that
department needs.  They can use the views, but they CANNOT go
directly to the table itself.) )

DON'T CREATE A VIEW WITHOUT SPECIFYING SQL SECURITY INVOKER.
DON'T CREATE A VIEW WITHOUT SPECIFYING SQL SECURITY INVOKER.
DON'T CREATE A VIEW WITHOUT SPECIFYING SQL SECURITY INVOKER.
DON'T CREATE A VIEW WITHOUT SPECIFYING SQL SECURITY INVOKER.
DON'T CREATE A VIEW WITHOUT SPECIFYING SQL SECURITY INVOKER.

DON'T CREATE A VIEW WITHOUT SPECIFYING SQL SECURITY INVOKER.
DON'T CREATE A VIEW WITHOUT SPECIFYING SQL SECURITY INVOKER.
DON'T CREATE A VIEW WITHOUT SPECIFYING SQL SECURITY INVOKER.
DON'T CREATE A VIEW WITHOUT SPECIFYING SQL SECURITY INVOKER.
DON'T CREATE A VIEW WITHOUT SPECIFYING SQL SECURITY INVOKER.

If you don't care who the definer is, you should specify SQL SECURITY
INVOKER.  The code has to run under *someone*'s privileges.  If you
don't understand why you need a DEFINER with SQL SECURITY DEFINER,
read the manual on the SQL SECURITY clause.

DON'T CREATE A VIEW WITHOUT SPECIFYING SQL SECURITY INVOKER.
DON'T CREATE A VIEW WITHOUT SPECIFYING SQL SECURITY INVOKER.
DON'T CREATE A VIEW WITHOUT SPECIFYING SQL SECURITY INVOKER.
DON'T CREATE A VIEW WITHOUT SPECIFYING SQL SECURITY INVOKER.
DON'T CREATE A VIEW WITHOUT SPECIFYING SQL SECURITY INVOKER.

Do you understand that if you send an email, you need to specify a
destination address (or more than one), and they need to be valid?
Do you understand that if you specify that code should be run under
a specific user's privileges (SQL SECURITY INVOKER), that user
must exist?

DON'T CREATE A VIEW WITHOUT SPECIFYING SQL SECURITY INVOKER.
DON'T CREATE A VIEW WITHOUT SPECIFYING SQL SECURITY INVOKER.
DON'T CREATE A VIEW WITHOUT SPECIFYING SQL SECURITY INVOKER.
DON'T CREATE A VIEW WITHOUT SPECIFYING SQL SECURITY INVOKER.
DON'T CREATE A VIEW WITHOUT SPECIFYING SQL SECURITY INVOKER.

If you created a view with SQL SECURITY DEFINER, delete it and
create it correctly with SQL SECURITY INVOKER.  My version of
MySQL will let me create views with *any* DEFINER, including
nonexistent ones, with SQL SECURITY INVOKER, but not with SQL
SECURITY DEFINER.

Don't define a view without SQL SECURITY INVOKER without a darn
good excuse, and a darn good idea who the DEFINER should be, and
why.

DON'T CREATE A VIEW WITHOUT SPECIFYING SQL SECURITY INVOKER.
DON'T CREATE A VIEW WITHOUT SPECIFYING SQL SECURITY INVOKER.
DON'T CREATE A VIEW WITHOUT SPECIFYING SQL SECURITY INVOKER.
DON'T CREATE A VIEW WITHOUT SPECIFYING SQL SECURITY INVOKER.
DON'T CREATE A VIEW WITHOUT SPECIFYING SQL SECURITY INVOKER.
DON'T CREATE A VIEW WITHOUT SPECIFYING SQL SECURITY INVOKER.
DON'T CREATE A VIEW WITHOUT SPECIFYING SQL SECURITY INVOKER.
DON'T CREATE A VIEW WITHOUT SPECIFYING SQL SECURITY INVOKER.
DON'T CREATE A VIEW WITHOUT SPECIFYING SQL SECURITY INVOKER.
DON'T CREATE A VIEW WITHOUT SPECIFYING SQL SECURITY INVOKER.
DON'T CREATE A VIEW WITHOUT SPECIFYING SQL SECURITY INVOKER.
DON'T CREATE A VIEW WITHOUT SPECIFYING SQL SECURITY INVOKER.
DON'T CREATE A VIEW WITHOUT SPECIFYING SQL SECURITY INVOKER.
DON'T CREATE A VIEW WITHOUT SPECIFYING SQL SECURITY INVOKER.
DON'T CREATE A VIEW WITHOUT SPECIFYING SQL SECURITY INVOKER.
DON'T CREATE A VIEW WITHOUT SPECIFYING SQL SECURITY INVOKER.
DON'T CREATE A VIEW WITHOUT SPECIFYING SQL SECURITY INVOKER.
DON'T CREATE A VIEW WITHOUT SPECIFYING SQL SECURITY INVOKER.
DON'T CREATE A VIEW WITHOUT SPECIFYING SQL SECURITY INVOKER.
DON'T CREATE A VIEW WITHOUT SPECIFYING SQL SECURITY INVOKER.
that is, unless you actually know what SQL SECURITY DEFINER is and
have a darn good reason for selecting a specific DEFINER.

On Aug 14, 7:19 pm, gordonb.nf...@burditt.org (Gordon Burditt) wrote:

When you say 'code', do you mean SQL statements, or prorgamming
langauge code, stuff that could leak out to the system?
Because if by 'code', you mean SQL statements, then why don't DROP
TABLE statements, or INSERT statements in the MySQL dumps have similar
security restrictions? Isn't that 'code' you can execute?

So how does this intersect with the permissions?

OK, I get the idea.

....

Well, it didn't work anyway.

Listen, all this ranting isn't helping me understand the concepts. In
fact, it's making me wonder if your head's on tight. When someone
starts ranting like this, it makes me suspect that they have an
unwarranted emotional attachment to their ideas. They try to use
showmanship and drama instead of reason to convince. I don't buy it.
When they can show me that they understand the system, and can
rationally explain to me what they think is best and the reasons for
it, so long as those reasons are valid, they've probably won me over.