Now that we've added users to our database, it's time to begin strengthening security by adding permissions. Our first step will be to grant appropriate database permissions to our users. We'll accomplish this through the use of the SQL GRANT statement.
Here's the syntax of the statement:
GRANT <permissions>
[ON <table>]
TO <user/role>
[WITH GRANT OPTION]
Now, let's take a look at this statement line-by-line.
The first line, GRANT <permissions>, allows us to specify the specific table permissions we are granting. These can be either table-level permissions (such as SELECT, INSERT, UPDATE and DELETE) or database permissions (such as CREATE TABLE, ALTER DATABASE and GRANT). More than one permission can be granted in a single GRANT statement, but table-level permissions and database-level permissions may not be combined in a single statement.
The second line, ON <table>, is used to specify the affected table for table-level permissions. This line is omitted if we are granting database-level permissions. The third line specifies the user or role that is being granted permissions.
Finally, the fourth line, WITH GRANT OPTION, is optional. If this line is included in the statement, the user affected is also permitted to grant these same permissions to other users. Note that the WITH GRANT OPTION can not be specified when the permissions are assigned to a role.
Let's look at a few examples. In our first scenario, we have recently hired a group of 42 data entry operators who will be adding and maintaining customer records.
They need to be able to access information in the Customers table, modify this information and add new records to the table. They should not be able to entirely delete a record from the database. First, we should create user accounts for each operator and then add them all to a new role, DataEntry. Next, we should use the following SQL statement to grant them the appropriate permissions:
GRANT SELECT, INSERT, UPDATE
ON Customers
TO DataEntry
And that's all there is to it! Now let's examine a case where we're assigning database-level permissions. We want to allow members of the DBA role to add new tables to our database. Furthermore, we want them to be able to grant other users permission to do the same. Here's the SQL statement:
GRANT CREATE TABLE
TO DBA
WITH GRANT OPTION
Notice that we've included the WITH GRANT OPTION line to ensure that our DBAs can assign this permission to other users.
At this point, we've learned how to add users and roles to a database and assign them permissions as necessary. In the next section of this article, we'll look at the methods for removing permissions from users. Read on!
Here's the syntax of the statement:
GRANT <permissions>
[ON <table>]
TO <user/role>
[WITH GRANT OPTION]
Now, let's take a look at this statement line-by-line.
The first line, GRANT <permissions>, allows us to specify the specific table permissions we are granting. These can be either table-level permissions (such as SELECT, INSERT, UPDATE and DELETE) or database permissions (such as CREATE TABLE, ALTER DATABASE and GRANT). More than one permission can be granted in a single GRANT statement, but table-level permissions and database-level permissions may not be combined in a single statement.
The second line, ON <table>, is used to specify the affected table for table-level permissions. This line is omitted if we are granting database-level permissions. The third line specifies the user or role that is being granted permissions.
Finally, the fourth line, WITH GRANT OPTION, is optional. If this line is included in the statement, the user affected is also permitted to grant these same permissions to other users. Note that the WITH GRANT OPTION can not be specified when the permissions are assigned to a role.
Let's look at a few examples. In our first scenario, we have recently hired a group of 42 data entry operators who will be adding and maintaining customer records.
They need to be able to access information in the Customers table, modify this information and add new records to the table. They should not be able to entirely delete a record from the database. First, we should create user accounts for each operator and then add them all to a new role, DataEntry. Next, we should use the following SQL statement to grant them the appropriate permissions:
GRANT SELECT, INSERT, UPDATE
ON Customers
TO DataEntry
And that's all there is to it! Now let's examine a case where we're assigning database-level permissions. We want to allow members of the DBA role to add new tables to our database. Furthermore, we want them to be able to grant other users permission to do the same. Here's the SQL statement:
GRANT CREATE TABLE
TO DBA
WITH GRANT OPTION
Notice that we've included the WITH GRANT OPTION line to ensure that our DBAs can assign this permission to other users.
At this point, we've learned how to add users and roles to a database and assign them permissions as necessary. In the next section of this article, we'll look at the methods for removing permissions from users. Read on!
SHARE
