Database Reference
In-Depth Information
user account in a database speciied by the login. he following SQL statement creates a user with
the login created in the previous SQL statement:
create user
user_name
from login
login_name
After the user is created, the user can be granted some permission with the following SQL
statement:
GRANT SELECT
ON BUILDING
TO
user_name
In the above code, the user is granted the SELECT permission on the table BUILGING. To
remove a permission granted to a user, the REVOKE SQL statement can be used to accomplish
the task.
Schemas can also be used to grant permissions to users. A schema is a set of database objects
assigned to one or more users. For the users who are allowed to access the same set of database
objects, you can create a schema to include these database objects. hen, assign the schema to these
users. You can grant some permission to the schema instead of granting the permission to each user.
Using strong passwords or keys is another way to make your database safer. When using SQL
authentication, a strong password is required. A user name should not include symbols like @.
Also, SQL authentication does not support Remote Desktop Connection.
ACTIVITY 12.1 WINDOWS AZURE SQL DATABASE USER MANAGEMENT
For our Class_Registration database created on Windows Azure, only the database owner
can access the database. It is recommended that we should design separate database roles for
the applications connected to the database and then add the application accounts to those
database roles. he following activity demonstrates how to accomplish this task:
1. To follow the recommendation, log on to the
master
database with the URL
https://qmcooqcanr.database.windows.net/?langid
=
en-US#$database
=
master
where qmcooqcanr is the SQL Database server name. Your SQL Database server name
should be diferent. hen, log on to the database as shown in Figure 12.2.
2. In the
New Query
pane, run the following SQL statement. he password has to be
strong enough to create the login.
create login Smith with password
=
'PWF@rCa$$&/-7'
go
3. After the Smith login is created. Click
Log of
to exit the SQL Database Management
Portal.
4. Enter the database
Class_Registration
as shown in Figure 12.3 and log in again with
your user name and password.
5. In the
New Query
pane, run the following SQL statement:
create user Smith from login Smith
go
