Detailed Notes
We continue our series and install our database.
1. Database installation summary 2. Root vs other users 3. Create a user https://dev.mysql.com/doc/refman/8.0/en/create-user.html CREATE USER 'sammy'@'localhost' IDENTIFIED BY 'password'; 4. Grant privileges GRANT ALL PRIVILEGES ON *.* TO 'user1'@localhost IDENTIFIED BY 'password1'; GRANT ALL PRIVILEGES ON 'yourDB'.* TO 'user1'@localhost; flush privileges; 5. Delete a user drop user 'user1'@localhost 6. Create a Database 7. Use Database 8. Drop a Database
Transcript Text
[Music] hello and welcome back we are on day two we're looking at getting started on our sql tutorials now i mentioned yesterday and hopefully or last time we met and hopefully you did some preparing the idea of downloading the database now this could be most likely maybe sql server or mariadb if you go to mariadb.org dot org you can see where the latest versions are and uh you can probably go back all the way to hours our version that we're working with that i'm working with but you shouldn't see too much differences in it now if you want to see how to install it then you can see pretty easily you can do an example of installing my sql on let's say linux and you can see some stuff here we're going to see like what your command lines are going to look like everything like that because depending on what you're doing you've got different some very different installations now whatever you do you should end up as part of the installation you should have some sort of a root user uh it's in my sequel it's called root uh it may be uh dbo or something like that but you've got some primary owner now we are today one of the things we're going to look at is creating a user and granting privileges and so this right away is going to get into database specific things i'm going to show you how it's done in mysql you can follow along if you're using mysql if you're using a different database engine then you may have to do a little you know searching to see how it gets done in your um in your specific environment because it could vary widely and it could be something that um you know this is completely confusing when i start putting some of these commands in they may be completely wrong and broken so if you're on something other than my sql maria db uh take a look through it take a look at what the specifics are now you will probably have you've got a root user so if depending on how you did it you should be able to you would have something like a and this is from my sequel is to log in once you've installed everything and you probably will see this in the commands but just to be sure you're going to do something like mysql it says this is the engine user it's going to be your username and then dash p and then when you do it it's going to say enter a password now mine is not set up i've got it secured so that root user is not available if unless i am root itself so i have to go all the way up and i probably didn't type my password right and in so doing now i can do with without anything and because i am root on this machine it's going to come in and and b i'm root i could also do i think though if i do root with dash p i think i've blocked that out so it's if i give it a password oh okay so it does do it here so i also i have it locked down so you can only install only be root if you're root essentially uh let me make sure who i am okay let me drop back down one more and then um now what i did is i created a user called rob and gave it a password and it has access to everything so we talked about this you don't want your root user to be the one you use most the time so instead what you want to do is create a like a developer user in the my sequel world and i'm going to have some links in this show notes on doing this but it's pretty easy is you're going to do create user and let me call him i'm going to call him tutor at let's say at star so it depends he can log in from anywhere well let's keep him localhost tutor at localhost and then i'm going to tell it identified by which is basically the password and my password is going to be just um let's do tutor one two three ah let's just do test one two three this is like a really simple do not use this yourself unless it's a completely internal uh unavailable database so now when i did this um i've created a user and now i can log in as tutor so if i do this let me quit and if i do my sql dash u tutor dash p test one two three okay so now i'm i'm logged in but now i have this command so i can see what are all the databases that are available practically none that i can see however if i come back out and log in as the rob user which can't i gotta type that right again if i log in as that user and then do show databases i'm gonna be able to see a ton of databases because i can see everything the only one i saw before was the information screen uh schema now i will show you uh in this my sequel world if you ever wonder what users you have uh within the database there's this command called use and that's using a specific database so i'm going to use mysql and that puts me in quote you know that's the context that i'm now in my sql so if i use if i want to look at the tables i do show tables these are the tables here that exist in the my sql database including user so if i do select star from user i'm going to see a bunch of data and it's so here's the actually let me do it this way is i can see all the data that's in that table but let me do this i'm gonna see what are the columns and let me do uh let's do oh let's just do host user and password host user password from user is that right oh shoot yeah those are all cap and i wonder if that'll work i think that'll work so so i'm going to show you this as you can see here that guy just created that password so that's tutor on localhost this password is not test123 because it is encrypted in the database and so you're not going to be the only way you're going to be able to find out what a password is is to either guess it correctly or you can change a password you can update a password we're going to get into those things a little bit later today i just wanted to basically do the let's create a user and then when i'm uh let's see oh i'm i'm who i am so now i can create a user and then i can drop a user which is going to be the same one so it's going to be tutorial at localhost if i do that and now if i look at the users i don't see he doesn't exist anymore so let me go with first let's create a database so you want to create a database and this is something you're going to want to hold on you're going to do and work with for a while so let's do create a date create database and let's call it tutorial because this one we're going to work with and if i do that now i can see okay created a database if i use use tutorial i'm in that database if i do show tables whoops i have to um i have to type it right then there's no tables in here i haven't created a table yet in my database i can also get rid of my database and if i do it gets rid of everything in the database you do not want to do this unless it's basically an empty database so if i drop database tutorial if i do show databases i'm going to see that tutorial does not exist if i go back and create it and then do show databases then here it is now let me do this real quick i want to i do want to jump back to oh i've got to create that user again i apologize so let's look back at creating that user so if i do create where to put create create user there we go create user tutor local host identified by tesla okay so i'm going to create him and now i'm going to go back and log in as him and remember i do show databases if i do create database tutorial or let's do um let's just call it my database my database now i'm going to get access denied because i created this user and have very little permissions granted to them so i'm not able to create a database now i can create a database and then assign those permissions to my user which is basically what i'm going to do here in a minute or i can assign to i can assign them to up to like for example here if i do um and this is called grant privileges and so this is where we're getting a little bit into this getting into permissions so i can do uh if i want i need to grant all privileges if i can spell it right privileges on and i can do star dot star to and then i give it the username so if i do tutor at localhost now i try to do it as me as tutor remember i'm logged into tutor then i'm going to get access denied because i can't grant myself those permissions however if i come in as me now if i want to do grant all privileges then what i need to do is oops i need him i think he needs to be identified by what was it test one two three oh rob is not able to do it i have to be root i did not give rob the ability even to grant these permissions however i can let's see do i have okay so i do have my tutorial database but what i can do so i can't grant privileges to everything but i can do everything on the tutorial database oops maybe i'm not let's make sure i don't have that i may not be able to grant permissions i think i don't have i didn't give myself grant permission let's uh let's see if i can see that from here uh i want to select sir from user so i want to do select star from user where user whoops equals rom and so i can see here now you can see a bunch of privileges up here so i can see there's selects inserts updates deletes reload process grant so if i go look at grant privilege let's do grant proof and let's see if i have any others that i want to give um that'll probably be good for now and i can see that i don't have grant privileges but i can do let's see um and now i'm playing around a little bit because i have not done this very uh where did my other little there there's my other window um so there okay i should get that pretty easy so blank on that to rob at star let's see if that'll work um i wonder if i can do grant proof probably not so i'm just going to cheat so i'm going to do update user set whoops grant proof equal y where user equals oh dang it because rob is a string is that where i blew it up earlier oh it probably is okay so actually if i want to do grant grant to let's see if i do that does that make it let me try one more time okay anyways so now if i go back to my select now i see that i have grant privileges so now if i come back in here oh i guess i wasn't as many anyways i was able to sort of cheat but now i come in here and i want to do grant uh all privileges on tutorial.star to them let's see if i can get away with that nope i can't do it oh because i don't have interesting let me see if i do grant all privileges whoops star 2 rob at star oh let's see where i'm at oh i know what it is oh so it doesn't allow that so now i do have to come in as my super dude so now if i come in here and i grant all privileges and that works and if i want to grant everything to the tutor i can do that now one of the things we've got to remember when we're signing privileges is we need to be able to when you're done you want to flush which is going to update everything crosstalk privileges so now if i go to uh let's see who do i want to be oh i don't want to do let me get back to here so now if i log in as tutor because i granted access to that database i can see tutorial i can use tutorial i'm going to do create table oh this is going to be tough um table one as this thing which is just going to be um name varchar 10 let's see if that'll work i'm trying to get the most simple create but i don't have that right now so i'm going to actually deal with that in another time but i can go in and see something at least within this because i do show tables and there are none in tutorial i'm going to get into this a little bit further next episode right now what i want to do is walk through creating a user granting some privileges deleting a user because what we want to do and this is sort of your homework create a user and probably call him like if you can tutor and then give him a good uh password or go with a very simple one if this is a database that is has no outside access and then create your database which is again that's just going to be create database tutorial and then see it's already created and then you're going to do use tutorial and that is essentially where we're going to be when we pick up on the next episode because what we're going to do is we now have a database so we have sort of a a place to work if you think back to the prior episode where we had the spreadsheet example what we've done at this point is we've opened up our spreadsheet software and we have a blank workbook and you can sort of think of it that way if this workbook back to that prior example i could call this um i'm just going to call it my databases and well actually i'm sorry this would be so these would be tables within it so this would now be if i can if i can drag and drop properly so this would be like the tutorial database which at this point would have actually nothing um so i can take all this and this all does not exist because table one is not really i haven't created it yet so this gave me the creation of table one and we're going to call this um this is going to end up be calling yeah i'm going to call this basic table because that's what we're going to create next time around is we're going to create a table we're going to create some columns and we're going to start doing some very basic stuff within it and we're going to swing back around so we're going to work on our on creating a table first with very simple data we're not going to get too deep into it as we're going to run into a few of these things these first few episodes we're going to as i did even a little bit today we're going to jump ahead and talk about some concepts here and there that it's sort of take our word for it and later we're going to dig deeper into it so at this point if you're set for the next episode then you've got your engine installed you have a user that you're going to be using for this series and you've created a database so we've got a starting point when we start creating tables in that next episode that being said it's time to move on and get prepared for that next episode so go out there have yourself a great day a great week and we will talk to you next time you
Transcript Segments
[Music]
hello and welcome back we are on day two
we're looking at getting started on our
sql tutorials
now
i mentioned yesterday and hopefully or
last time we met
and hopefully you did some preparing
the idea of
downloading the database
now this could be
most likely maybe sql server or mariadb
if you go to
mariadb.org
dot org you can see where the latest
versions are
and
uh you can probably go back all the way
to
hours our version that we're working
with that i'm working with
but you shouldn't see too much
differences in it
now if you want to see how to install it
then you can see pretty easily you can
do an example of
installing my sql
on let's say linux
and
you can see some stuff here we're going
to see like what your command lines are
going to look like everything like that
because depending on what you're doing
you've got different
some very different installations
now whatever you do
you should end up
as part of the installation you should
have some sort of a root user
uh it's in my sequel it's called root uh
it may be
uh dbo or something like that but you've
got some
primary owner
now
we are today one of the things we're
going to look at is creating a user and
granting privileges and so this right
away is going to get into
database specific things
i'm going to show you how it's done in
mysql you can follow along if you're
using mysql if you're using a different
database engine then you may have to do
a little you know searching to see how
it gets done in your
um in your specific environment
because it could vary
widely
and
it could be something that um
you know
this is completely confusing when i
start putting some of these commands in
they may be completely wrong and broken
so if you're on something other than my
sql maria db
uh take a look through it take a look at
what the specifics are
now
you will probably have you've got a
root user so if
depending on how you did it
you should be able to you would have
something like a and this is from my
sequel is to log in once you've
installed everything and you probably
will see this in the commands but just
to be sure you're going to do something
like mysql it says this is the engine
user it's going to be your username and
then dash p and then when you do it it's
going to say enter a password
now mine is not set up i've got it
secured so that root user is not
available if unless i am
root itself so i have to go all the way
up
and i probably didn't type my password
right
and in so doing now i can do with
without anything and because i am root
on this machine
it's going to come in and
and b
i'm root i could also do i think though
if i do
root with dash p i think i've blocked
that out so it's if i give it a password
oh okay so it does do it here so i also
i have it locked down so you can only
install only be root if you're
root essentially uh let me make sure who
i am okay
let me drop back down one more
and then
um
now what i did is i created a user
called rob and gave it a password
and it has access to everything
so we talked about this you don't want
your root user to be the one you use
most the time
so
instead what you want to do is create a
like a developer user
in the my sequel world
and i'm going to have some links in this
show notes on doing this but it's pretty
easy is you're going to do create user
and let me call him
i'm going to call him tutor
at
let's say
at star so it depends he can log in from
anywhere well let's keep him localhost
tutor at localhost and then i'm going to
tell it
identified by which is basically the
password
and my password is going to be just um
let's do tutor one two three ah let's
just do test one two three this is like
a really simple do not use this yourself
unless it's a completely internal uh
unavailable database
so
now when i did this
um
i've created a user and now i can log in
as tutor so if i do this let me quit
and if i do my sql dash u
tutor
dash p
test one two three okay so now i'm i'm
logged in but now
i have this command so i can see what
are all the databases that are available
practically none that i can see however
if i come back out and log in as
the rob user which can't i gotta type
that right again
if i log in as that user
and then do show databases i'm gonna be
able to see a ton of databases because i
can see everything the only one i saw
before was the information screen uh
schema
now i will show you
uh in this my sequel world if you ever
wonder
what
users you have
uh within the database there's this
command called use
and that's using a specific database so
i'm going to use mysql
and that puts me in
quote you know that's the context that
i'm now in my sql so if i use if i want
to look at the tables
i do show tables
these are the tables here that exist in
the my sql database including
user so if i do select star from user
i'm going to see a bunch of data
and it's
so here's the actually let me do it this
way is i can see all the data that's in
that table
but let me do this i'm gonna see what
are the
columns
and let me do uh
let's do oh let's just do host user and
password
host user password
from user
is that right
oh shoot yeah those are all cap
and i wonder if that'll work i think
that'll work
so
so i'm going to show you this as you can
see
here
that guy just created
that password so that's tutor on
localhost
this password is not test123 because it
is encrypted in the database
and so you're not going to be the only
way you're going to be able to find out
what a password is
is to either guess it correctly or
you can change a password you can update
a password
we're going to get into those things a
little bit later today i just wanted to
basically do the let's create a user
and then when i'm uh let's see oh i'm
i'm who i am so now
i can create a user
and then i can drop a user which is
going to be the same one so it's going
to be tutorial
at localhost if i do that
and now if i look at the users i don't
see he doesn't exist anymore
so let me
go with first let's create a database
so you want to create a database and
this is something you're going to want
to hold on you're going to do and work
with for a while
so let's do create a date create
database
and let's call it tutorial
because this one we're going to work
with
and if i do that now i can see okay
created a database if i use use tutorial
i'm in that database if i do
show tables
whoops i have to um
i have to type it right
then there's no tables in here i haven't
created a table yet in my database i can
also get rid of my database and if i do
it gets rid of everything in the
database you do not want to do this
unless it's basically an empty database
so if i drop database tutorial
if i do show databases i'm going to see
that tutorial does not exist if i go
back and create it
and then do show databases
then here it is
now let me do this real quick i want to
i do want to jump back to
oh i've got to create that user again i
apologize
so let's look back at creating that user
so if i do
create where to put create
create
user there we go create user tutor local
host identified by tesla okay so i'm
going to create him
and now
i'm going to go back and log in as him
and remember i do show databases
if i do create database
tutorial or let's do
um
let's just call it my database my
database
now
i'm going to get access denied because i
created this user and have
very little permissions granted to them
so i'm not able to create a database
now
i can create a database and then assign
those permissions to my user
which is
basically what i'm going to do here in a
minute or i can assign to
i can assign them to up to like for
example here if i do
um
and this is called grant privileges and
so this is where we're getting a little
bit into this getting into permissions
so i can do
uh if i want i need to grant all
privileges if i can spell it right
privileges
on and i can do star dot star
to
and then i give it the username so if i
do tutor at localhost
now i try to do it as me
as tutor remember i'm logged into tutor
then
i'm going to get access denied because i
can't grant myself those permissions
however
if i come in as me
now
if i want to do grant all privileges
then what i need to do is
oops i need him i think he needs to be
identified by
what was it test one two three
oh
rob is not able to do it i have to be
root i did not give rob the ability even
to grant these permissions
however i can
let's see do i have
okay so i do have my tutorial database
but what i can do so i can't grant
privileges to everything
but
i can do
everything on the tutorial database
oops
maybe i'm not
let's make sure i don't have that i may
not be able to grant permissions i think
i don't have i didn't give myself grant
permission let's uh
let's see if i can see that from here
uh i want to select
sir
from user
so i want to do select
star from
user
where
user whoops
equals rom
and so i can see here
now you can see a bunch of privileges up
here so i can see there's selects
inserts updates deletes
reload process grant
so if i go look at
grant privilege
let's do grant proof and let's see if i
have any others that i want to give
um
that'll probably be good for now
and i can see that i don't have grant
privileges but
i can do
let's see
um
and now i'm playing around a little bit
because i have not done this very uh
where did my other little there there's
my other window
um
so
there okay
i should get that pretty easy so blank
on that to
rob at star let's see if that'll work
um
i wonder if i can do grant proof
probably not so i'm just going to cheat
so i'm going to do update
user
set whoops
grant
proof equal
y
where
user equals
oh
dang it because rob is a string
is that where i blew it up earlier
oh it probably is okay so actually if i
want to do grant grant to
let's see if i do that does that make it
let me try one more time
okay anyways so now
if i go back to my select
now i see that i have grant privileges
so now
if i come back in
here
oh i guess i wasn't as many anyways i
was able to sort of cheat
but now
i come in here and i want to do grant
uh
all privileges on tutorial.star to them
let's see if i can get away with that
nope i can't do it oh because i don't
have
interesting
let me see
if i do
grant all privileges
whoops
star 2
rob at star
oh
let's see where i'm at oh i know what it
is
oh so it doesn't allow that so now i do
have to come in as my super dude
so now if i come in here
and i grant all privileges
and that works and if i want to grant
everything to the tutor
i can do that now one of the things
we've got to remember when we're signing
privileges is we need to be able to when
you're done you want to flush which is
going to update everything crosstalk
privileges
so now
if i go to
uh let's see who do i want to be oh i
don't want to do let me get back to here
so now if i log in as tutor
because i granted access to that
database
i can see tutorial i can use tutorial
i'm going to do create table
oh this is going to be tough um
table one
as
this thing which is just going to be um
name
varchar 10
let's see if that'll work i'm trying to
get the most simple create
but i don't have that right now so i'm
going to actually deal with that in
another time
but i can go in
and
see something at least within this
because i do show tables
and there are none in tutorial i'm going
to get into this a little bit further
next episode
right now what i want to do is walk
through creating a user granting some
privileges deleting a user because what
we want to do
and this is sort of your homework
create a user and probably call him like
if you can
tutor and then give him a good
uh password or go with a very simple one
if this is a database that is has no
outside access
and then create your database
which is again that's just going to be
create database
tutorial
and then
see it's already created and then you're
going to do use tutorial and
that is essentially where we're going to
be when we pick up on the next episode
because what we're going to do is we now
have a database so we have sort of a
a place to work if you think back to the
prior episode where we had the
spreadsheet example
what we've done at this point is we've
opened up our spreadsheet software and
we have a blank workbook
and you can sort of think of it that way
if
this workbook
back to that prior example
i could call this
um
i'm just going to call it my databases
and well actually i'm sorry this would
be so these would be tables within it so
this would now be
if i can if i can drag and drop properly
so this would be like the tutorial
database
which at this point would have actually
nothing
um so i can take all this and this all
does not exist
because table one is not really i
haven't created it yet so this gave me
the creation of table one
and we're going to call this um
this is going to end up be calling
yeah
i'm going to call this basic table
because that's what we're going to
create next time around
is we're going to create a table we're
going to create some columns
and we're going to start doing some very
basic stuff within it
and we're going to swing back around so
we're going to work on our
on creating a table first with
very simple data we're not going to get
too deep into it as we're going to run
into a few of these things these first
few episodes we're going to
as i did even a little bit today we're
going to jump ahead and talk about some
concepts here and there that
it's sort of take our word for it and
later we're going to dig deeper into it
so at this point
if you're set for the next episode then
you've got your engine installed
you have a user that you're going to be
using for this
series
and you've created a database so we've
got a starting point when we start
creating tables
in that next episode
that being said it's time to move on and
get prepared for that next episode
so go out there have yourself a great
day a great week and we will talk to you
next time
you