Database Reference
In-Depth Information
As you can see, adding and removing users is very easy. Giving the
appropriate privileges to the appropriate users gets a bit more complex. So
let's now examine privileges.
23.2
Privileges
A privilege gives a user permission to perform certain tasks or access specific
objects in the database. There are two types of privileges:
System Privileges
. These give a user the capability to do something
in the database, such as create tables or create views.
Object Privileges
. These give a user access to the data in an object,
such as the privilege to select or update rows in a specific table.
When you assign (grant) a privilege, you can give a user the ability to
assign the privilege to others. You have to be logged on with a user who has
the privilege and has the right to assign that privilege as well. The SYSTEM
user, being a DBA, has just about every privilege needed to assign privileges
to others.
23.2.1
Granting Privileges
The syntax for granting system and object privileges is very similar. Figure
23.5 shows both system and object privileges.
Roles are discussed later in this chapter. For now, focus on the com-
mands as they are used to grant system and object privileges to users. Some
important differences to note between granting system privileges and grant-
ing object privileges are as follows:
When granting system privileges, the WITH ADMIN OPTION can
be used.
When granting object privileges, the WITH GRANT OPTION can
be used.
Only object privileges name a specific object, such as a table, with the
ON clause.
Now let's demonstrate executing granting of privileges. Once again, we
begin by connecting as the SYSTEM user.
