Permissions and Security

GRANT

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	Alter tables and indexes
Alter Routine	Alter stored procedures
Create	create database, create table
Create Routine	Create a stored procedure
Create Temporary Table	These are created via the TEMPORARY command
Create View	A separate form of Create for views
Delete	delete rows from an existing table
Drop	drop tables and databases
Event
Execute
Index	create or drop indexes
Insert	insert rows into an existing table
Lock Tables	special table-locking command
Select	run Select queries
Show View
Trigger	create triggers
Update	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 'pld'@localhost;

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

DBA
manager
hr_clerk
order_clerk
project_update_application_role

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.

The Oracle 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 attributes:

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 can 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:

The privileges use the following codes:

r    select ("read")
w   update ("write")
a    insert ("append")
d    delete
D    truncate
x    references
t    trigger
/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 public;

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 allowed host:

    create user '' @ 'localhost';

The user table at this point contains

   +-------------------+--------------------+
   | Host             | User              |
   +-------------------+--------------------+
   | %           | pld               |
   | 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 it does:

     If the 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 match.

One fix is to add 'pld' @ 'localhost' with GRANT, this time with the localhost spelled out

    GRANT ALL ON plddb.* to 'pld' @ 'localhost' IDENTIFIED BY 'password';

Strictly speaking, this means that 'pld' @ '' and 'pld' @ 'localhost' can have different privileges (though I have not tested this, and this may have been fixed).

An easier strategy is to drop the anonymous account, which you do not really need:

    drop user '' @ 'localhost';

Searching the user table will identify other blank or wildcard account names.