Permissions and Security
The basic mechanism for controlling user access is through the GRANT
command. As part of setting up MySQL, as root (DBA), I did the following
create database plddb;
grant all on plddb.* to 'pld';
This in effect grants user pld full control over the plddb database.
For Postgres, the privileges are
The general syntax is
GRANT privilege ON object TO user
[REQUIRE require_option] [WITH with_option]
ON *.*: any database, any table
ON *: all privileges on the currently selected database, if one is
selected; dangerous otherwise
ON database.*: all privileges on the named database; safer
ON database.table: all privileges on the specific table
Note that ON database.* grants privileges for all tables in the database,
either now or created in the future. The "*" thus does not act
like a wildcard, matching all existing tables. It also "matches" tables to
be created in the future.
The specific MySQL object privileges are as
follows (the Postgres object privileges are below)
||Alter tables and indexes
||Alter stored procedures
||create database, create table
||Create a stored procedure
|Create Temporary Table
||These are created via the TEMPORARY command
||A separate form of Create for views
||delete rows from an existing table
||drop tables and databases
||create or drop indexes
||insert rows into an existing table
||special table-locking command
||run Select queries
||update rows of an existing table
If you assign someone privileges to a specific set of tables, they will not
be able to do normalization, as that requires creating new tables. On the
other hand, if you assign someone * privileges, then there is sometimes a
concern that you can't really tell just what it is you are giving them. If
they have permissions to DROP a table and then re-CREATE it, they can
replace an existing table with one of their own.
The most common require_option, besides NONE, is to require the
use of SSL to connect to the database over the network.
The most common with_option above is GRANT OPTION, allowing the
user to GRANT his or her privileges to others. In general, it is a good idea
to REVOKE this privilege. Only the DBA should be giving away privileges. The
only other common form of with_option is to add a constraint on
the rate of queries, eg
WITH MAX_QUERIES_PER_HOUR 100
If one has Insert privileges on only some columns of a table, then one can
insert records only if the remaining columns have default values. The Insert
privilege is really a privilege for determining which columns a user can
assign non-default values in an insert operation.
To revoke permission to select e.ssn the DBA can use
revoke select (ssn) on plddb.employee to
This will also disallow queries that use the ssn in a join:
select e.lname from employee e, department d
where e.ssn = d.mgr_ssn and d.dnumber = 5;
A very commonly used alternative is to construct the necessary join and to
create a view for it. Then the user can be granted
permission to use the view (presumably not containing any SSN columns).
Views often contain all the information needed so no further joins may be
necessary; if the view later needs to be updated to include new columns that
is easily done.
Role-based Access Control
In MySQL, the DBA can use the GRANT command to grant a set of specific
privileges to a given user. If multiple users need similar privileges, the
same sequence of GRANTs must be applied to each.
Oracle and Postgres allow the creation of privilege sets known as roles.
A user can then be granted a role, and thus inherit (unless inheritance is
disabled!) the privileges of that role. This kind of authentication
mechanism is sometimes called role-based access control,
or RBAC. A common set of roles might be
After a user has been assigned a role, the role can be edited later; the
user then automatically has the newer privileges.
A role granted to a user can be temporarily disabled, as needed.
The syntax for granting roles is GRANT rolename TO username.
The GRANT command can list multiple roles (comma-separated) and can also
list system privileges as well as roles.
Generally speaking, roles involving create table, or drop
table, or create view, are restricted to DBAs.
DB Security Guide 11g Release 1 has a list of predefined roles
starting on page 111 (4-11).
Note that this is very different from the use of shared "role accounts": a
single account with established permissions that is then shared by multiple
users. Shared accounts are a serious security problem: it is difficult to
manage password changes and next to impossible to trace actions taken back
to individual users.
Postgres Roles and Grant Privileges
In Postgres, all accounts (including individual-user
accounts) are considered to be role accounts. User accounts are role
accounts with the LOGIN right. Group accounts are, by convention, roles
without this right.
This raises some potential confusion. If A and B are roles, what happens if
we put A into group B and B into group A?
create role A;
create role B;
grant B to A; -- put A in group B
grant A to B; -- try to put B in group A
Postgres disallows this. (How do you think it implements this?)
Postgres roles normally inherit all rights of other roles of which they are
members. If a role is created with NOINHERIT, though, then such inheritance
from parent roles is blocked:
CREATE ROLE joe LOGIN INHERIT;
CREATE ROLE admin NOINHERIT;
CREATE ROLE wheel NOINHERIT;
GRANT admin TO joe;
-- make joe a member of group admin
GRANT wheel TO admin;
-- make admin a member of group wheel
Now joe does inherit from admin. But joe does not
inherit from wheel. Group admin does not inherit from wheel. This is due to
the NOINHERIT on admin, which applies going up but not going down.
Tables and schemas have owners. To change the owner of a
table or schema, use
alter table works_on owner to alice;
alter schema ignatius owner to alice
Roles have attributes (and rights). Here are the basic
- login privilege (CREATE ROLE pld LOGIN)
- superuser status (CREATE ROLE pld SUPERUSER)
- database creation (CREATE ROLE pld CREATEDB)
- role creation (CREATE ROLE pld CREATEROLE)
- password (CREATE ROLE pld PASSWORD 'rambl3rs'
To change a user's password, for example, we use
alter user alice password 'wonderland';
A useful role to have available is one with CREATEDB and CREATEROLE
attributes, allowing most user and database management without needing
full SUPERUSER authority.
Database objects (ie tables) also have privileges.
Sometimes the attributes above are informally called privileges, but they
are conceptually separate (and are usually set with the ALTER command).
The "true" privileges are those set with the GRANT command:
grant select, insert, update, delete,
truncate, references, trigger on employee to alice;
grant all on employee to alice;
revoke all on employee from alice;
grant select on all tables in schema ignatius to alice;
-- this is how you create "read-only" access
The truncate privilege allows one-step deletion of all rows of a table.
The references privilege is needed to create foreign-key constraints; it
must be present for both tables involved.
Use \dp (or \dp tablename) to view privileges. Here is a
sample, from \dp employee:
Schema | Name | Type | Access
privileges | Column privileges | Policies
public | employee | table | pld=arwdDxt/pld
The privileges use the following codes:
r select ("read")
w update ("write")
a insert ("append")
/pld: the user or role granting the privilege
It is possible to grant privileges just to selected columns:
grant select, update (lname,
salary, address) on employee to alice;
If you grant insert on selected columns, then the remaining columns must
have default values.
There are also privileges that apply to databases: connect (allows
connection to that database), create (allows creation of new schemas),
temp (temporary table creation).
Many new Postgres administrators find it surprising that new users need
no special permission to create tables. It is true that new users have
default permission to create tables in the public
schema. The following grant, in other words, is in place:
grant create, usage on schema public to public
where the second "public", here, to quote from the manual, "can be thought
of as an implicitly defined group that always includes all roles." It can
(and should, in production environments) be revoked:
revoke create, usage on schema public from
The "usage" privilege on schemas is supposedly needed to access schema
objects (tables and views). It is separate from the select privilege.
If you create a table, you are the table's owner, and thus
can do anything with it.
MySQL user/host rules
(See Paul DuBois MySQL,
13.2.3, "A Privilege Puzzle")
In MySQL, user accounts also come with a hostname. These are found in the
mysql.user table, normally accessible only by privileged
users. When accounts are created, they include a host specification ('%' by
default; recall that this is the match-everything pattern):
create user 'pld' @ '%' identified by 'password';
A common complaint on support sites is that a user cannot log in, or at
least cannot log in from localhost, with the given password. The most common
culprit is the presence of an anonymous account, with localhost as the only
create user '' @ 'localhost';
The user table at this point contains
| localhost |
(The query is select host, user from user;)
When MySQL searches this table, it searches first by host, and then by user,
with explicit hosts (eg localhost) coming before patterns
such as % (this is the reverse of the above). The blank
username is allowed to match 'pld'; it is not clear why MySQL does this, but
User value is
blank, it matches any user name. (MySQL
5.5 manual section 6.2.4)
And so the login matches the second record above. The user-supplied
password, however, intended for the first record, likely does not
One fix is to add 'pld' @ 'localhost' with GRANT, this time with the localhost
GRANT ALL ON plddb.* to 'pld' @ 'localhost' IDENTIFIED BY
Strictly speaking, this means that 'pld' @ '' and 'pld' @ 'localhost' can
have different privileges (though I have not tested this, and this may have
An easier strategy is to drop the anonymous account, which you do not really
drop user '' @ 'localhost';
Searching the user table will identify other blank or
wildcard account names.