dump without "security definer" for views?
law...@gmail.com
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)
Peter H. Coffin
> 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
Give yourself the SUPER priv. Or try it with a user ID that has it.
> 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
Wild guess that there isn't any such user as 'user'@'ip_address/netmask'
on that system. Create them.
> 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)
>
--
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
law...@gmail.com
> On Mon, 28 Jul 2008 13:57:33 -0700 (PDT), lawp...@gmail.com wrote:
> > 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
>
> Give yourself the SUPER priv. Or try it with a user ID that has it.
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.
>
> > 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
>
> Wild guess that there isn't any such user as 'user'@'ip_address/netmask'
> on that system. Create them.
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.
Gordon Burditt
>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?
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?
law...@gmail.com
>
> 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?
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.
Gordon Burditt
>> 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?
>
>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.
law...@gmail.com
>
> 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.
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.
Gordon Burditt
>> default. Look at the syntax for CREATE VIEW. The DEFINER is taken from
>> how you logged in to MySQL, unless you have administrative privileges.
>
>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?
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.
>I tried creating a view from the command-line interface, without
>specifying a security definer.
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.
>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?
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.
law...@gmail.com
> >> 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.
>
> >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?
>
> 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).
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?
>
> 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.) )
So how does this intersect with the permissions?
>
> DON'T CREATE A VIEW WITHOUT SPECIFYING SQL SECURITY INVOKER.
OK, I get the idea.
> >I tried creating a view from the command-line interface, without
> >specifying a security definer.
>
> DON'T CREATE A VIEW WITHOUT SPECIFYING SQL SECURITY INVOKER
....
Well, it didn't work anyway.
>
> 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.
>
> 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.
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.