Chapter 15 – SQL Authorizationid¶
Note
You are reading a digital copy of SQL-99 Complete, Really, a book that documents the SQL-99 standard.
The book and the standard does not reflect the features of CrateDB, we are just publishing it as a service for the community and for reference purposes.
On the one hand, CrateDB does not implement the SQL-99 standard thoroughly, on the other hand, it extends the standard for implementing cluster features and others.
For more information specific to CrateDB, check out the CrateDB Reference documentation.
In this chapter, we’ll describe SQL <AuthorizationID>s in detail, and show you the syntax to use to create, alter and destroy them.
Table of Contents
<AuthorizationID>¶
We use the non-standart term <AuthorizationID> to mean a named bundle of Privileges, which is either a User or a Role. Users and Roles can be distinguished as follows.
A user is either an actual person or an application program that has access to SQL-data. Users are outside Schemas. If an <AuthorizationID> is a User, the methods used to create, drop, and map it to actual persons/application programs are implementation-defined.
A Role generally refers to a title or a set of duties within an organization. Roles are inside Schemas. If an <AuthorizationID> is a Role, it is created and dropped using standard SQL statements.
All <AuthorizationID>s must be unique within the SQL-environment.
The Objects that may belong to a User or a Tole are called Privileges; they depend on some <AuthorizationID>. An <AuthorizationID> may have the use of zero or more Privileges.
User¶
A User is either an actual person or an application program that has access to SQL-data.
[NON-PORTABLE] SQL does not include any CREATE USER, MAP USER, or DROP USER
statements. The methods you´ll use with your DBMS to create and drop Users and
to identify the real-life user an <AuthorizationID> represents are
non-standart because the SQL Standart requires implementors to define how a
User comes into being, how it may be destroyed, how it maps to users of
SQL-data, and what constitutes a valid <AuthorizationID>. [OCELOT
Implementation] The OCELOT DBMS that comes with this book “creates” a User
automaticallywhen its identifier is usedin a CONNECT
statement, a CREATE
SCHEMA
statement, a MODULE
statement, a SET SESSION AUTHORIZATION
statement, or a GRANT
statement. Users are “dropped” if they own no Onjects
and all their Privileges have been revoked.
Role¶
Large organization may have hordes of users with the same Privileges on the same Objects. For instance, circulation assistants are all allowed to do the circulation process. In SQL-92, the solutions were unsatisfactory:
If each circulation assistant had a different <AuthorizationID>, then there were too many Objects in the Catalog and granting/revoking was a major chore.
If all circulation assistants had the same <AuthorizationID>, some other method had to be devised so that one could distinguish which assistant really did what.
In SQL3 there is a major improvement: Roles. An SQL Role is a named bundle of zero or more Privileges. Granting a Role to an <AuthorizationID> allows that <AuthorizationID> to use every Privilege granted to that Role. There is a many-to-many relationship between user <AuthorizationID>s and Roles: a user may be granted the use of many Roles and the use of the same Role may be granted to many user <AuthorizationID>s. The use of a Role may also be granted to another Role.
Types of <AuthorizationID>¶
An <AuthorizationID> is defined by a descriptor that contains three pieces of information:
The <identifier> that is the name of the <AuthorizationID>.
Whether the <AuthorizationID> identifies a User or a Role.
A Privilege descriptor for every Privilege granted to the <AuthorizationID>. At any point in time, an <AuthorizationID> has the use of every Privilege granted to PUBLIC, every Privilege directly granted to that <AuthorizationID> and every Privilege granted to every Role that has been granted to that <AuthorizationID>. (Since a Role may be granted to another Role, all dependent Role Privileges are also available to the <AuthorizationID>.
The SQL-session <AuthorizationID> is the user <AuthorizationID> for an SQL-session. Initially, it is the user <AuthorizationID> that started a given SQL-session and is determined from the
USER
clause of theCONNECT
statement. The SQL-session <AuthorizationID> may never beNULL
– if theCONNECT ... USER
clause names a Role instead of a user <AuthorizationID>, the SQL-session <AuthorizationID> is set to a default value by your DBMS. You can change the SQL-session <AuthorizationID> with theSET SESSION AUTHORIZATION
statement. The user functionSESSION_USER
returns the value of the current SQL-session <AuthorizationID>.The context of an SQL-session includes its current User and its current Role: together, they determine the Privileges available to execute SQL statements in the SQL-session and one of them is always the “current <AuthorizationID>”. Either one of current user or current Role may be
NULL
at any time, but they may not both beNULL
at the same time – the non-null identifier is the SQL-session’s current <AuthorizationID>. At the beginning of every SQL-session, the current user is set to the value of the SQL-session <AuthorizationID> and the current Role is set to the Role that started the SQL-session – it is determined from theCONNECT
statement: ifCONNECT ... USER
specifies a <Role name>, the Role identified by that <Role name> becomes the current Role; otherwise, the current Role isNULL
. You can change the current role with theSET ROLE
statement. The equivalent user functionsCURRENT_USER
andUSER
return the value of the current user <AuthorizationID>. The user functionCURRENT_ROLE
returns the value of the current Role <AuthorizationID>.The Module <AuthorizationID> is the owner of a given Module and is the current <AuthorizationID> when the Module’s SQL procedures are executed. The Module <AuthorizationID> may either be specifically identified (in the
MODULE
statement) or it will default to the current SQL-session <AuthorizationID>.The Schema <AuthorizationID> is the owner of a given Schema and may either be specifically identified (in the
CREATE SCHEMA
statement) or it will default to the current Module <AuthorizationID>, if there is one. If the Module you’re running doesn’t have an explicit owner either, the Schema <AuthorizationID> defaults to the current SQL-session <AuthorizationID>.The current <AuthorizationID> is the <AuthorizationID> whose Privileges are checked prior to the execution of an SQL statement. If the current <AuthorizationID> doesn’t have the required Privilege to perform an operation on SQL-data, the SQL statement will fail. For direct SQL, the SQL-session <AuthorizationID> is always the current <AuthorizationID>.
To create a Role and grant its use to an initial <AuthorizationID>, use the
CREATE ROLE
statement. To destroy a Role, use the DROP ROLE
statement.
To grant Privileges to a user or a Role, or to grant the use of a Role to an
<AuthorizationID>, use the GRANT
statement. To revoke Privileges from a
user or a Role, or to revoke the use of a Role from an <AuthorizationID>, use
the REVOKE
statement or the DROP ROLE
statement. To change the current
SQL-session <AuthorizationID>, use the SET SESSION AUTHORIZATION
statement.
And to change the current Role, use the SET ROLE
statement.
If you want to restrict your code to Core SQL, don’t use Roles at all.
<AuthorizationID> Names¶
An <AuthorizationID> name is eiter a <User name> or a <Role name>. The required syntax for an <AuthorizationID> name is as follows:
<AuthorizationID> ::=
user name | <Role name>
An <AuthorizationID> is a <regular identifier> or a <delimited identifier>, no
more than 128 octets long, that is unique (for all <AuthorizationID>s and
Roles) within the Cluster it belongs to. Typically, an <AuthorizationID> is a
Christian name (e.g., BOB
or SAM
, identifying a user) or a department
name (e.g.: DOCUMENTATION, identifying a Role) and, most often, has a
one-to-one relationship to some user (i.e.: each user has only one
<AuthorizationID>).
The <regular identifier> – PUBLIC
– is a valid <AuthorizationID> only in
the GRANT
statement and the REVOKE
statement. PUBLIC
is the SQL
special grantee, used as an “all-purpose” grantee, to enable system-wide
Privileges to be granted to and/or revoked from all current and future
<AuthorizationID>s with a single SQL statement. Every <AuthorizationID> always
has the use of every Privilege granted to PUBLIC
. The <delimited
identifier> – "PUBLIC"
– is never a valid <AuthorizationID>.
The <delimited identifier> – "_SYSTEM"
– is a valid <AuthorizationID>
only when you’re looking at INFORMATION_SCHEMA. "_SYSTEM"
is the SQL
special grantor, used (by your DBMS) as an “all-purpose” grantor, to enable
Object-wide Privileges to be granted to an <AuthorizationID> that creates an
Object, or to revoke cascading, system-wide Privileges from an
<AuthorizationID> that drops an Object. (In the case of a DROP, "_SYSTEM"
is also the grantee that revokes all related Privileges from all other
<AuthorizationID>s.)
[NON-PORTABLE] Except for the cases (PUBLIC
, "PUBLIC"
, "_SYSTEM"
)
noted earlier, what your DBMS will recognize as a valid <AuthorizationID> is
non- standard because the SQL Standard requires implementors to define what a
valid <AuthorizationID> is. [OCELOT Implementation] The OCELOT DBMS that comes
with this book will accept any SQL_TEXT
<identifier> as an
<AuthorizationID>.
CREATE ROLE Statement¶
The CREATE ROLE
statement defines new Role. The required syntax for the
CREATE ROLE
statement is:
CREATE ROLE <Role name> [ WITH ADMIN <grantor> ]
<grantor> ::= CURRENT_USER | CURRENT_ROLE
The CREATE ROLE
statement specifically defines an <AuthorizationID> to be a
Role, rather than a user: this is necessary since otherwise your DBMS could
mistake <Role name> for a user name which appears in similar contexts. <Role
name> is an <AuthorizationID> that is unique within your SQL-environment and
CREATE ROLE
automatically grants the use of the new Role to the current
<AuthorizationID> WITH ADMIN OPTION
(that is, the current <AuthorizationID>
gets the use of the new Role and may pass this use on to others).
[NON-PORTABLE] Whether you can use CREATE ROLE
is non-standard because the
SQL Standard requires implementors to define what Privilege (if any) is needed
to execute CREATE ROLE
.
The optional WITH ADMIN
clause decides which <AuthorizationID> (either the
current user or the current Role) gets the use of this Role: CURRENT_USER
is the <AuthorizationID> of the current user and CURRENT_ROLE
is the
<AuthorizationID> of the current Role. If you omit the clause, it defaults to
WITH ADMIN CURRENT_USER
– but if CURRENT_USER
is NULL
, the clause
defaults to WITH ADMIN CURRENT_ROLE
. If you specify WITH ADMIN
CURRENT_USER
and the current <AuthorizationID> is a <Role name>, or if you
specify WITH ADMIN CURRENT_ROLE
and the current <Role name> is NULL
,
the GRANT
statement will fail: your DBMS will return the SQLSTATE error
0L000 "invalid grantor"
. Here are two examples:
CREATE ROLE assistants_role WITH ADMIN CURRENT_USER;
-- creates a Role, identified by the <AuthorizationID> ASSISTANTS_ROLE, for the current user
CREATE ROLE assistants_role WITH ADMIN CURRENT_ROLE;
-- creates a Role, identified by the <AuthorizationID> ASSISTANTS_ROLE, for the current Role
If you want to restrict your code to Core SQL, don’t use the CREATE ROLE
statement.
Privilege¶
An SQL Privilege allows an <AuthorizationID> to perform a given action on a
specific Table, Column, Domain, Character set, Collation, Translation, Trigger,
SQL-invoked routine or UDT. Privileges are dependent on some <AuthorizationID>
or Role, are created and granted with the GRANT
statement and are destroyed
with the REVOKE
statement and the DROP ROLE
statement. A Privilege is
defined by a descriptor that contains five pieces of information:
The name of the Object that the Privilege acts on.
The <AuthorizationID> that granted the Privilege.
The <AuthorizationID> that may use the Privilege.
The action (either
INSERT
,UPDATE
,DELETE
,SELECT
,REFERENCES
,USAGE
,UNDER
,TRIGGER
orEXECUTE
) that may be taken on the specified Object.Whether the Privilege is grantable by the grantee.
Whether the Privilege also applies to subtables in a hierarchy (for
SELECT
only).
<privileges>¶
The required syntax for a Privilege specification is as follows.
<privileges> ::=
<object privileges> ON <Object name>
<Object name> ::=
[ TABLE ] <Table name> |
DOMAIN <Domain name> |
CHARACTER SET <Character set name> |
COLLATION <Collation name> |
TRANSLATION <Translation name> |
TYPE <UDT name> |
<specific routine designator>
<object privileges> ::=
ALL PRIVILEGES |
<action> [ {,<action>}... ]
<action> ::=
DELETE |
SELECT [ (<Column name> [ {,<Column name>} ... ]) ] |
INSERT [ (<Column name> [ {,<Column name>} ... ]) ] |
UPDATE [ (<Column name> [ {,<Column name>} ... ]) ] |
REFERENCES [ (<Column name> [ {,<Column name>} ... ]) ] |
TRIGGER |
UNDER |
USAGE |
EXECUTE
The Privilege specification specifies one or more Privileges for a specific Object. Not all Privileges are valid for every Object.
ALL PRIVILEGES
is a shorthand: it means every Privilege the grantor has on
“<Object name>”. If “<Object name>” identifies a temporary Table, ALL
PRIVILEGES
is the only valid Privilege specification. Here’s an example:
ALL PRIVILEGES ON TABLE Table_1
In theory, you could substitute ALL PRIVILEGES
for USAGE
or
EXECUTE
, but there’s never a good reason to do so. Traditionally, ALL
PRIVILEGES
substitutes for the combination of Table Privileges: SELECT
,
INSERT
, UPDATE
, DELETE
and perhaps TRIGGER
or REFERENCES
.
The closest analogous construct is the *in "SELECT * ..."
, then. And
similar objections to using it apply:
You can’t tell by reading the SQL code what Privileges are in fact being granted.
Because
ALL PRIVILEGES
is a shorthand, it might mean something different the next time you use it (because somebody might have in the interim granted additional Privileges to the current user).If the current user has only one Privilege, you will not be warned that all the other potentially applicable Privileges are not being granted.
You should prefer to be explicit about what you’re granting: if you depend on a
complex system of defaults you’ll miss something. These considerations don’t
apply to temporary Tables since, for them, ALL PRIVILEGES
really does mean
all of: SELECT
, INSERT
, UPDATE
, DELETE
, TRIGGER
,
REFERENCES
.
A SELECT
, INSERT
, UPDATE
, REFERENCES
, DELETE
or TRIGGER
Privilege is called a Table Privilege: it allows the specified action on the
entire Table named in the Privilege’s descriptor – including on any Columns
that are subsequently added to that Table. You may not grant the TRIGGER
Table Privilege on anything but a Base table. You may not grant the other Table
Privileges on a declared LOCAL TEMPORARY
Table, or on any other Object.
Note that the <keyword> TABLE
is noise; it may be omitted from the
Privilege specification. Here are two equivalent examples:
DELETE ON TABLE Table_1
DELETE ON Table_1
Both of these Privilege specifications define a Privilege that deletes rows
from TABLE_1
. Here’s an example of a Privilege specification that defines a
Privilege that selects values from every Column of TABLE_1
:
SELECT ON TABLE Table_1
And here’s an example of a Privilege specification that defines a Privilege
that enables a Trigger to operate on every Column of TABLE_1
:
TRIGGER ON TABLE Table_1
A SELECT
(<Column name> list), INSERT
(<Column name> list), UPDATE
(<Column name> list) or REFERENCES
(<Column name> list) is called a Column
Privilege: it allows the specified action only on the Columns actually named
when the Privilege is granted. You may not grant Column Privileges on the
Columns of a declared LOCAL TEMPORARY
Table or on any other Object. Again,
the <keyword> TABLE is noise; it may be omitted from the Privilege
specification. Here are two equivalent examples:
UPDATE(column_1,column_3) ON TABLE Table_1
UPDATE(column_1,column_3) ON Table_1
Both of these Privilege specifications define a Privilege that updates the
values of TABLE_1.COLUMN_1
and TABLE_1.COLUMN_3
. Here’s an example of a
Privilege specification that defines a Privilege that inserts values into
TABLE_1.COLUMN_2
:
INSERT(column_2) ON TABLE Table_1
And here’s an example of a Privilege specification that defines a Privilege
that enables TABLE_1.COLUMN_3
to be named in a Constraint or Assertion
definition:
REFERENCES(column_3) ON TABLE Table_1
Every Privilege you have on a Table that is the basis for an updatable View is
also granted to you on that View. For example, if you have the INSERT
and
SELECT
Privileges on a Table and create an updatable View based on that
Table, you will also have the INSERT
and SELECT
Privileges on the View.
If your Table Privileges are grantable Privileges, your Privileges on the View
will also be grantable.
An UNDER
Privilege may either apply to a structured type or it may apply to
a typed Table. An UNDER
Privilege on a UDT allows the creation of subtypes;
an UNDER
Privilege on a typed Table allows the creation of subtables. YOu
may not grant an UNDER
Privilege on any other Object. Here’s an example:
UNDER ON TYPE bob.bob_udt
A USAGE
Privilege allows the use of the Domain, UDT, Character set,
Collation or Translation named in the Privilege’s descriptor. You may not grant
a USAGE
Privilege on any other Object. Here’s two examples:
USAGE ON DOMAIN domain_1
USAGE ON CHARACTER SET bob.charset_1
When a Catalog is “created”, your DBMS grants the USAGE
Privilege on all
INFORMATION_SCHEMA
Character sets, Collations and Translations to PUBLIC
WITH GRANT OPTION
, so you always have the use of those Objects. If for some
reason you should ever create a new character-related Object, do the same –
there is no point being secretive here.
An EXECUTE
Privilege allows the SQL-invoked routine named in the
Privilege’s descriptor to be executed. You may not grant an EXECUTE
Privilege on any other Object. Here’s an example:
EXECUTE ON SPECIFIC ROUTINE some_routine
(We define <specific routine designator> in our chapter on UDTs.)
Note that if two Privileges are identical except that one is a grantable
Privilege and the other is not, the grantable Privilege takes precedence and
both Privileges are set to grantable Privileges. Similarly, if two otherwise
identical Privileges indicate one has WITH HIERARCHY OPTION
and the other
does not, the first takes precedence. Your DBMS will then eliminate the
redundant duplicate Privilege.
If you want to restrict your code to Core SQL, don’t specify the UNDER
Privilege, don’t specify the SELECT
Privilege as a Column Privilege (that
is, with a <Column name> list) and don’t specify the INSERT
Privilege as a
Column Privilege.
GRANT statement¶
The GRANT
statement defines both Privilege grants and Role authorizations
and so has two different syntaxes. The first is identified as the <grant
privilege statement> and the second as the <grant role statement>.
<grant privilege statement>¶
The required syntax for the <grant privilege statement> form of the GRANT
statement is:
<grant privilege statement> ::=
GRANT <privileges> TO <grantee> [ {,<grantee>}... ] [ WITH GRANT OPTION ]
[ FROM <grantor> ]
<grantee> ::= PUBLIC | <AuthorizationID>
<grantor> ::= CURRENT_USER | CURRENT_ROLE
The <grant privilege statement> grants one or more Privileges on a given
Object to one or more grantees, including (possibly) PUBLIC
. The grantor of
the Privileges must, of course, hold those Privileges as grantable Privileges.
We’ve already shown you the syntax for the <privileges> Privilege
specification; it’s used exactly that way in this form of the GRANT
statement. Here are some examples:
GRANT SELECT ON TABLE Table_1 TO PUBLIC;
GRANT INSERT(column_1,column_5) ON Table_1 TO sam;
GRANT ALL PRIVILEGES ON TABLE Table_1 TO PUBLIC, bob, sam;
GRANT USAGE ON DOMAIN domain_1 TO bob;
GRANT EXECUTE ON SPECIFIC ROUTINE some_routine TO sam;
If your <grantee> is PUBLIC
, you’re granting the Privilege to a list of
<grantee>s that contains all of the <AuthorizationID>s in the Cluster – now
and in the future. If your <grantee> is one or more <AuthorizationID>s, you’re
granting the Privilege only to those <AuthorizationID>s. (Remember that an
<AuthorizationID> may identify either a user or a Role.)
When you grant a SELECT, UPDATE or REFERENCES Table Privilege on a Base table that has subtables, the effect is two-fold:
The optional WITH HIERARCHY OPTION
clause may apply only to a SELECT
TABLEPrivilege that is granted on a typed Table. When you use this option, you
are granting the SELECT
Privilege not only on the Table named, but also on
all of that Table´s substables.
The optional FROM
clause names the grantor of the Privileges:
CURRENT_USER
is the <AuthorizationID> of the current user and
CURRENT_ROLE
is the <AuthorizationID> of the current Role. If you omit the
clause, it defaults to FROM CURRENT_USER
– but if CURRENT_USER
is
NULL
, the clause defaults to FROM CURRENT_ROLE
. If you specify FROM
CURRENT_USER
and the current <AuthorizationID> is a <Role name>, or if you
specify FROM CURRENT_ROLE
and the current <Role name> is NULL
, the
GRANT
statement will fail: your DBMS will return the SQLSTATE error 0L000
"invalid grantor". Here are two examples
:
GRANT UPDATE(column_1,column_5) ON Table_1 TO sam FROM CURRENT_USER;
GRANT DELETE ON Table_1 TO PUBLIC FROM CURRENT_ROLE;
The optional WITH GRANT OPTION
clause defines a grantable Privilege: one
that the grantee may, in turn, grant to other <AuthorizationID>s. If you omit
the clause, the grantee will not be able to pass the Privilege on to others.
Here are two examples:
GRANT REFERENCES(column_4,column_5) ON Table_1 WITH GRANT OPTION
TO bob,sam FROM CURRENT_USER;
GRANT TRIGGER Table_1 WITH GRANT OPTION
TO PUBLIC FROM CURRENT_ROLE;
If the GRANT
statement isn’t able to successfully create a Privilege
descriptor for every one of its Privilege specifications, your DBMS will return
the SQLSTATE warning 01007 "warning-privilege not granted"
.
If you want to restrict your code to Core SQL, don’t use the FROM
<grantor>
clause or the WITH HIERARCHY OPTION
with the GRANT
statement.
<grant role statement>¶
The required syntax for the <grant role statement> form of the GRANT
statement is:
<grant role statement> ::=
GRANT <Role name> [ {,<Role name>}... ] TO <grantee> [ {,<grantee>}... ]
[ WITH ADMIN OPTION ]
[ FROM <grantor> ]
<grantee> ::= PUBLIC | <AuthorizationID>
<grantor> ::= CURRENT_USER | CURRENT_ROLE
The <grant role statement> grants the use of one or more Roles to one or more
grantees, including (possibly) PUBLIC
. The grantor of the Roles must, of
course, hold those Roles as grantable Roles (that is, WITH ADMIN OPTION
).
If your <grantee> is PUBLIC
, you’re granting the use of <Role name> to a
list of <grantee>s that contains all of the <AuthorizationID>s in the Cluster
– now and in the future. If your <grantee> is one or more <AuthorizationID>s,
you’re granting the use of the Role only to those <AuthorizationID>s. Keep in
mind that an <AuthorizationID> may identify either a user or a Role – this
means you can grant the use of a Role not only to a user, but to another Role.
Be careful with this last option: SQL doesn’t allow you to grant the use of a
Role to a Role that already has the use of that Role – that is, no cycles of
Role grants are allowed. Note that if two Role grants are identical except that
one is a grantable Role and the other is not, the grantable Role takes
precedence and both Roles are set to grantable Roles. Your DBMS will then
eliminate the redundant duplicate Role grant.
The optional FROM clause names the grantor of the Roles: CURRENT_USER
is
the <AuthorizationID> of the current user and CURRENT_ROLE
is the
<AuthorizationID> of the current Role. If you omit the clause, it defaults to
FROM CURRENT_USER
– but if CURRENT_USER
is NULL
, the clause
defaults to FROM CURRENT_ROLE
. If you specify FROM CURRENT_USER
and the
current <AuthorizationID> is a <Role name>, or if you specify FROM
CURRENT_ROLE
and the current <Role name> is NULL
, the GRANT
statement
will fail: your DBMS will return the SQLSTATE error 0L000 "invalid
grantor"
. For example, in this SQL statement, the current user is granting
the use of the ASSISTANTS_ROLE
Role to every <AuthorizationID>:
GRANT assistants_role TO PUBLIC FROM CURRENT_USER;
In this following SQL statement, the current Role is granting the use of the
ASSISTANTS_ROLE
and the BOSSES_ROLE
Role to the bob
and joe
<AuthorizationID>s:
GRANT assistants_role, bosses_role TO bob, joe FROM CURRENT_ROLE;
The optional WITH ADMIN OPTION
clause defines a grantable Role: one that
the grantee may, in turn, grant the use of to other <AuthorizationID>s. If you
omit the clause, the grantee will not be able to pass the use of the Role on to
others. Here are two examples:
GRANT assistants_role TO PUBLIC FROM CURRENT_USER WITH ADMIN OPTION;
GRANT assistants_role, bosses_role TO bob, joe
FROM CURRENT_ROLE WITH ADMIN OPTION;
If you want to restrict your code to Core SQL, don’t use the <grant role
statement> form of the GRANT
statement and don’t grant any grantable
Privileges on your Objects to others users – Core SQL only allows the owner of
an Object to hold a grantable Privilege.
Data Control¶
You need explicit permission to perform any action on any SQL Object. SQL’s
security system is discretionary (meaning that your rights vary depending on
the action and on the Object). If you try to violate security, what usually
happens is an error message. It’s not a sophisticated system, but there is some
complexity when we get into details and definitions. Here’s a simple GRANT
statement:
GRANT INSERT -- action
ON Books -- Object
TO joe; -- user
After this SQL statement is executed, the user named joe
will be able to
use the INSERT
statement on the Table named Books
. SQL’s security
system boils down to this: you can let people access data with GRANT
statements, or you can refuse to do so. The combination {action plus Object} is
a Privilege. The action is usually a verb, the Object is usually a Table and
the user is usually a person.
Action and Object¶
What action is possible depends on what the Object is. Here is a chart of the
Object types which can be in GRANT
statements, and the applicable actions
for them:
OBJECT |
ACTION(S) |
Base table
|
SELECT , INSERT , UPDATE , DELETE , TRIGGER , |
REFERENCES |
|
View |
|
Column |
|
Domain |
|
Character Set |
|
Collation |
|
Translation |
|
SQL-invoked routine |
|
UDT |
|
Except for USAGE
, the GRANT
actions correspond to verbs that are used
in SQL statements, for example:
UPDATE Integer_Tables SET
integer_1 = CAST('1' AS INFORMATION_SCHEMA.CARDINAL_NUMBER);
For this SQL statement to be possible, the current <AuthorizationID> must have the following Privileges:
The
UPDATE
Privilege on TableINTEGER_TABLES
and/or theUPDATE
Privilege on ColumnINTEGER_TABLES.INTEGER_1
.The
USAGE
Privilege on DomainINFORMATION_SCHEMA.CARDINAL_NUMBER
.
User/Role¶
A user is an <AuthorizationID>. It’s important to remember that users are
outside the “database” – the effect is that <AuthorizationID>s are
unqualified names, that there are no CREATE USER / DROP user statements in
standard SQL and that some important questions depend on your operating
system. An easy way to appreciate this is to remember that joe
is still
joe
even if you switch to a different Schema in a different Catalog. Other points about users:
All DBMS operations are done on behalf of some user.
All Objects are owned by some user.
<AuthorizationID>s refer to real-world entities like people (
Sam Brown
/Joe
) or jobs (VP Marketing
/Tellers
) or departments (Acquisitions
/Front Office
) or robots (Consolidate Report program
,Internet spider
). DBMSs can’t distinguish between these categories.
In a security context, we’re concerned with the user’s authorization to ask for
certain acts to be performed. That’s where the GRANT
statement comes in.
Owners¶
Consider this SQL statement:
CREATE SCHEMA Sally_Schema AUTHORIZATION Sally
CREATE Table Sally_Dates (date_1 DATE, date_2 DATE);
(The question “who may execute a CREATE SCHEMA
statement?” is a tough one.
It’s implementor-defined, which might mean “anybody at all”, but more likely
means “some special database administrator who exists when you first install
the DBMS”. Though the question is important, it rarely is going to come up.)
In this example, a Role named Sally is the owner of SALLY_SCHEMA
. Therefore,
by definition, Sally is the owner of all Objects within that Schema – right
now, this includes Table SALLY_DATES
and the Columns DATE_1
and DATE_2
.
Caution
You’ll often hear that the “owner of a Table” is “the user who created the Table”. Technically that’s wrong. Suppose another user, Joe, was able to create a Table and store it in Sally’s schema:
CREATE Table Sally_Schema.Joe_Dates (
date_1 DATE, date_2 DATE);
Joe is the creator of Table JOE_DATES
– but the owner is Sally, because
Sally owns the Schema. The distinction between creators and owners is trivial
for SQL-92 users because it is illegal to create Objects in a Schema that you
don’t own. It is legal in SQL3, though – so don’t let old definitions mix
you up.
As the Schema owner, Sally now has the power to CREATE
, ALTER
or
DROP
all Objects in the Schema. Sally alone has this power – she cannot
delegate it – so a Schema owner’s CREATE
/ALTER
/DROP
power is a
right, not a Privilege. This power exists, but we will not trouble with it
further because it does not involve the GRANT
statement.
Sally also has a full set of all Privileges that apply for each Object in her
Schema. For example, Sally has the USAGE
Privilege for all of her Schema’s
Domains, Character sets, Collation and Translations and she has SELECT
,
UPDATE
, INSERT
, DELETE
, REFERENCES
and TRIGGER
Table
Privileges for all of her Schema’s Base Tables. Sally’s set of Privileges stems
automatically from her ownership of the Schema. Technically it is considered
that she was “granted” these powers by a pseudo-user named "_SYSTEM"
, but
for practical purposes she is the ultimate authority. She cannot lose her right
to do these operations. She can, however, pass on any or all of her Privileges
to any other user. This ability is called the GRANT OPTION
, because Sally
has the option of granting the Privileges she holds. Sally also may pass on the
the Privileges she holds. Sally also may pass on the grant option itself.
grant option itself.
GRANTs on Tables¶
Let’s follow what a DBMS does with a GRANT
statement, using a series of
examples based on SALLY_SCHEMA
. Here’s a GRANT
statement for that
Schema (assume Sally is the current <AuthorizationID>):
GRANT SELECT, INSERT ON Sally_Dates TO joe WITH GRANT OPTION;
Given this SQL statement, a DBMS acts as follows:
It determines that Sally owns the Schema, therefore she has a full set of Privileges on Table
SALLY_DATES
. There is no need to find a Privilege descriptor to see whether she may grant Privileges.It determines that Joe does not exist, so it silently creates a user named Joe. Even if Joe has not made his existence known before, that would be no reason to believe he does not exist. (We’re making some assumptions here for the sake of the example. This would be the usual case, but your DBMS might require you to execute some form of (non-standard) CREATE USER statement or it may have some operating-system dependent check on whether Joe exists.)
It creates these six new Privilege descriptors:
GRANTOR |
GRANTEE |
OBJECT |
NAME |
ACTION |
GRANT_OPTION |
Sally |
Joe |
Table |
|
|
YES |
Sally |
Joe |
Table |
|
|
YES |
Sally |
Joe |
Column |
|
|
YES |
Sally |
Joe |
Column |
|
|
YES |
Sally |
Joe |
Column |
|
|
YES |
Sally |
Joe |
Column |
|
|
YES |
The four “Column Privilege” descriptors may surprise you a bit since the GRANT
statement syntax doesn’t include COLUMN
as an Object. But since it does
allow Column Privileges to be defined, the effect is that a GRANT
on a
Table creates, not only a Privilege descriptor for that Table, but also a
Privilege descriptor for every applicable Column of that Table.
Now, suppose that time passes and the current <AuthorizationID> becomes Joe,
who executes this GRANT
statement:
GRANT INSERT, UPDATE ON Sally_Dates TO Sam;
Given this SQL statement, a DBMS acts as follows:
It determines that Joe does not own the Schema, so it looks through the Privilege descriptors to see if there are any where grantee = ‘Joe’, Object = ‘Table’, Name = ‘Sally_Dates’ and grant_option = YES. It finds
INSERT
Privileges but it does not findUPDATE
Privileges, so it returns theSQLSTATE warning 01007 "warning-privilege not granted"
and it creates these three new Privilege descriptors:
GRANTOR |
GRANTEE |
OBJECT |
NAME |
ACTION |
GRANT_OPTION |
Joe |
Sam |
Table |
|
|
NO |
Joe |
Sam |
Column |
|
|
NO |
Joe |
Sam |
Column |
|
|
NO |
More time passes and Sally is once again the current <AuthorizationID>. She does the following:
GRANT INSERT ON Sally_Dates TO Sam;
GRANT INSERT(date_1) ON Sally_Dates TO bob;
Given this SQL statement, a DBMS acts as follows:
Once again it sees that Sally owns the Schema and so has full Privileges, all grantable.
It creates these five new Privilege descriptors:
GRANTOR |
GRANTEE |
OBJECT |
NAME |
ACTION |
GRANT_OPTION |
Sally |
Sam |
Table |
|
|
NO |
Sally |
Sam |
Column |
|
|
NO |
Sally |
Sam |
Column |
|
|
NO |
Sally |
Bob |
Table |
|
|
NO |
Sally |
Bob |
Column |
|
|
NO |
These Privilege descriptors for Same are not duplicates of the ones that were
created earlier, when Joe granted Sam the same Privileges, therefore the DBMS
makes new Privilege descriptors. Later, however, Sally repeats this GRANT
:
GRANT INSERT ON Sally_Dates TO Sam;
In this final case, nothing happens: there are already Privilege descriptors
with the same {grantor, grantee, Object, name, action} and duplicates will not
be added to INFORMATION_SCHEMA
. Your DBMS will return “okay” after creating
zero new rows.
A related example – trivial but worth making because older SQL books describe
it incorrectly – concerns the question: what if the last example had contained
the WITH GRANT OPTION
clause? In that case, the grant_option field for the
last two Privilege descriptors would have been changed from NO
to YES
.
In the end, we have fourteen new Privilege descriptors. Sally has the same
powers of ownership as before, Joe can INSERT INTO Sally_Dates
or SELECT
FROM Sally_Dates
, Sam can INSERT INTO Sally_Dates
and Bob can INSERT
INTO Sally_Dates.date_1
. Joe’s Privileges are WITH GRANT OPTION
, Sam’s
and Bob’s are not. We have been at some pains to show that the result is both
Table and Column Privilege descriptors, and no two Privilege descriptors can be
exact duplicates. There can, however, be considerable overlap, as is seen by
the fact that Sam has two sets of INSERT
Privileges on the same Object –
one with grantee=Sally and the other with grantee=Joe.
Caution
it is a delusion to believe that Bob may now execute this SQL statement:
INSERT INTO Sally_Dates (date_1) VALUES (CURRENT_DATE);
because to do this, Bob needs the INSERT
Privilege for both Column
DATE_1
and Column DATE_2 -- INSERT
statements create whole rows. So
it’s useless to "GRANT INSERT(Column-list)"
unless (a) you expect that
somebody else will grant access on the other Columns or (b) your
(Column-list) contains the names of all Columns in the Table, but you don’t
want to just "GRANT INSERT ON <Table name>"
because that would give
access not only to all Columns currently in the Table, but to all Columns
which will ever be in the Table (including Columns added by future ALTER
TABLE
statements).
If Sally does the following:
GRANT UPDATE(date_1) ON Sally_Dates TO Sam;
the effect is that Sam can do this:
UPDATE Sally_Dates SET date_1 = 'DATE '1994-07-15';
but Sam can’t do this:
UPDATE Sally_Dates SET date_2 = 'DATE '1994-07-15';
There are times when such fine-tuning of controls is desirable. If Sally does the follwoing:
GRANT SELECT(date_1) ON Sally_Dates TO Sam;
the effect is that Sam can do this:
SELECT date_1 FROM Sally_Dates;
SELECT COUNT(*) FROM Sally_Dates;
but he can’t specifically SELECT
the second Column.
Caution
If SELECT
(Column-list) Privileges are granted, the recipients should
avoid using "SELECT * ..."
. The "SELECT * ..."
statement will
suddenly cease to work for them if new Columns are added to the Table.
We would never recommend by-Column granting, without first looking at the alternative – granting on a View.
GRANTs on Views¶
By definition, a View is a Table. So the considerations specific to
“GRANT
s on Views” have nothing to do with syntax – the syntax is the same
as for any Table – but with the effect. Specifically, let’s see what would
occur if Joe owns a View of Table SALLY_DATES
.
To make the example possible we have to start by giving Joe his own Schema. It’s convenient to make Joe’s View at the same time. Here’s the SQL statement to do it:
CREATE Schema Joe_Schema AUTHORIZATION Joe
CREATE View Joe_Views AS
SELECT date_1,date_2 FROM Sally_Schema.Sally_Dates;
To do this, Joe will need the SELECT
Privilege on the Columns of Table
SALLY_DATES
in SALLY_SCHEMA
– we gave these to him earlier. Naturally,
the rules don’t let Joe gain new Privileges by owning a View. He can DROP
this View (that’s his power as an owner and he can SELECT FROM
or INSERT
INTO
this View (those are the grantable Privileges he holds on
SALLY_DATES
) – and that’s all. He cannot do this:
UPDATE Joe_Views SET date_1 = CURRENT_DATE;
because UPDATE
is not a Privilege he holds on SALLY_DATES
, and
ultimately, an UPDATE
on the View will UPDATE
the Table. Now assume
that Joe creates another View:
CREATE View Joe_Views_2 AS
SELECT date_1 FROM Joe_Views WHERE date_1 > DATE '1994-01-03';
and then does a GRANT
to Sam:
GRANT INSERT, SELECT ON Joe_Views_2 TO Sam;
… a beautiful example of why Views are useful for security systems. Here, Joe has restricted Sam’s access not only to a particular Column of a Table, but to particular rows of that Table. He has done more than is possible with by-Column granting – and he has done it more cleanly.
GRANTs on Procedures¶
The sad news is that the most useful GRANT
of all is one that hardly anyone
can use yet. It depends on the existence of procedures, which are an SQL3
Object. Only GRANT
s on procedures can bring us real-word examples like the
following one.
Think of a bank. Tellers in the bank do not have Privileges on Tables, or on
Columns within Tables. If we give them UPDATE
Privileges on customer
accounts, they’ll be able to do anything at all to the accounts (too
dangerous); if we don’t, they won’t be able to do anything at all (too
restrictive). What everyone – managers, tellers and customers – wants is a
Privilege that allows a specific combination of operations. For example, let
Joe transfer money – that is, let him withdraw from one account and deposit to
another account, provided that the total of the transaction balances out to
$0.00. You could define this situation in a program using syntax like this:
CREATE PROCEDURE Transfer_Procedure
... INSERT
... UPDATE
... CHECK
... CASE
...;
And then you could allow Joe to use the procedure with:
GRANT EXECUTE ON Transfer_Procedure TO Joe;
Almost always, restricting based on an SQL verb (INSERT
/ UPDATE
/
DELETE
) is vague. Administrators of non-imaginary institutions would prefer
finer tuning: they want to restrict actions based on entire SQL statements or,
more often, on combinations of SQL statements. Since procedure granting will
give them that, eventually they’ll throw away the old grants on Tables /
Columns / Views, and GRANT EXECUTE
will become the norm.
Constraints¶
If a user names a Column within a Constraint or Assertion definition, that
means he/she/it REFERENCES
the Column. (Don’t be misled by the appearance
of the <keyword> REFERENCES
in FOREIGN KEY
Constraint definitions; in
this context we’re talking about any kind of Constraint. And don’t be misled by
the appearance of the <keyword> SELECT
inside some complex CHECK
Constraints; the action here is REFERENCES
so the SELECT
Privilege is
irrelevant for Constraints.) Here’s an example – Sally (who owns the Table
SALLY_DATES
) may issue this GRANT
statement:
GRANT REFERENCES ON Sally_Dates TO Joe;
This means that Joe can now create a Constraint or Assertion that uses the
Columns of SALLY_DATES
– for example:
CREATE ASSERTION joe_constraint CHECK (
Joe_Views.date_1 <> DATE '1999-12-31');
Not only does this Assertion limit the values of JOE_VIEWS
, it also limits
the values of SALLY_DATES
, since that is the Table that the View is based
on. Obviously, Joe should not be able to set limits on Sally’s data unless she
specifically allows him to – thus, the REFERENCES
Privilege.
REVOKE Statement¶
By the time we finish setting up a Catalog and granting appropriate Privileges
to our users, we probably have several thousand Privilege descriptors in
INFORMATION_SCHEMA
– more than the count for all other Schema Objects
combined. Maintaining them is made easier by the fact that when an Object is
dropped, the DBMS will silently destroy all associated Privilege descriptors.
That leaves only the problem: how do we adjust for the occasional necessity to
remove a Privilege descriptor due to a change in status of a particular user
(or Role)? The problem does not occur frequently, but can be mightily
cumbersome: the SQL Standard devotes about 40 pages to it. We have managed to
simplify the description somewhat, by focussing on the two “essentials” of the
process:
What we are trying to do is reverse the effects of a
GRANT
statement, using aREVOKE
statement – the clauses of which have almost the same syntax asGRANT
’s clauses.What we are really doing is deleting Privilege descriptor rows from
INFORMATION_SCHEMA
.
The REVOKE
statement destroys both Privilege descriptors and Role
authorizations and so has two different syntaxes. The first is identified as
the <revoke privilege statement> and the second as the <revoke role statement>.
The required syntax for the REVOKE
statement is as follows.
<revoke privilege statement> ::=
REVOKE [ GRANT OPTION FOR ] <privileges> FROM <grantee> [ {,<grantee>}... ]
[ FROM {CURRENT_USER | CURRENT_ROLE} ] {RESTRICT | CASCADE}
<revoke role statement> ::=
REVOKE [ ADMIN OPTION FOR ] <Role name> [ {,<Role name>}... ]
FROM <grantee> [ {,<grantee>}... ]
[ FROM {CURRENT_USER | CURRENT_ROLE} ]
{RESTRICT | CASCADE}
<grantee> ::= PUBLIC | <AuthorizationID>
The <revoke privilege statement> revokes one or more Privileges on a given
Object from one or more grantees, including (possibly) PUBLIC
, while the
<revoke role statement> revokes the use of one or more Roles from one or more
grantees. Only the grantor of the Privileges (or the Roles) may revoke them.
We’ve already shown you the syntax for the <privileges> Privilege
specification; it’s used exactly that way in the <revoke privilege statement>
form of the REVOKE
statement. Here are some examples:
REVOKE SELECT ON TABLE Table_1 FROM PUBLIC CASCADE;
REVOKE INSERT(column_1,column_5) ON Table_1 FROM sam CASCADE;
REVOKE ALL PRIVILEGES ON TABLE Table_1 FROM PUBLIC CASCADE;
REVOKE USAGE ON DOMAIN domain_1 FROM bob CASCADE;
REVOKE EXECUTE ON SPECIFIC ROUTINE some_routine FROM sam CASCADE;
And here’s an example of the <revoke role statement> form of REVOKE
:
REVOKE assistants_role FROM PUBLIC CASCADE;
In both cases, if your <grantee> is PUBLIC
, you’re revoking the Privilege
(or the use of the Role) from a list of <grantee>s that contains all of the
<AuthorizationID>s in the Cluster. If your <grantee> is one or more
<AuthorizationID>s, you’re revoking the Privilege (or the use of the Role) only
from those <AuthorizationID>s. (Remember that an <AuthorizationID> may identify
either a user or a Role.)
Remember that, for Tables, GRANT
creates Privilege descriptors for both the
Table and its Columns. Well, when you REVOKE
a Table Privilege, all
by-Column Privileges for that Table disappear too. The effect is a bit strange:
when you revoke a Table Privilege, you lose the Column Privilege (even if it
was granted separately) and when you revoke a Column Privilege, you lose that
Column Privilege – even if it resulted from a Table Privilege GRANT
.
The optional FROM
clause names the grantor of the Privileges or the Role
you’re revoking: CURRENT_USER
is the <AuthorizationID> of the current user
and CURRENT_ROLE
is the <AuthorizationID> of the current Role. If you omit
the clause, it defaults to FROM CURRENT_USER
– but if CURRENT_USER
is
NULL
, the clause defaults to FROM CURRENT_ROLE
. If you specify FROM
CURRENT_USER
and the current <AuthorizationID> is a <Role name>, or if you
specify FROM CURRENT_ROLE
and the current <Role name> is NULL
, the
REVOKE
statement will fail: your DBMS will return the SQLSTATE error
0L000 "invalid grantor"
. Here are two examples:
REVOKE UPDATE ON Table_1 FROM sam FROM CURRENT_USER CASCADE;
-- revokes the UPDATE Privilege on TABLE_1 from Sam only if the current user granted that Privilege in the first place
REVOKE assistants_role FROM PUBLIC FROM CURRENT_ROLE CASCADE;
-- revokes the use of the ASSISTANTS_ROLE Role from PUBLIC only if the current Role granted the use of that Role in the first place
The optional HIERARCHY OPTION FOR
clause (applicable only to the <revoke
privilege statement>) allows you to revoke only the WITH HIERARCHY OPTION
from the specified SELECT
Privilege.
The optional GRANT OPTION FOR
(<revoke privilege statement>) and ADMIN
OPTION FOR
(<revoke role statement>) clauses allow you to revoke only the
grantability of a Privilege or a Role. For example, consider these SQL
statements:
GRANT UPDATE ON TABLE Table_1 TO sam WITH GRANT OPTION;
REVOKE GRANT OPTION FOR UPDATE ON Table_1 FROM sam CASCADE;
The first SQL statement allows Sam to update TABLE_1
, and to pass this
Privilege on to others. The second SQL statement revokes the latter ability:
Sam can still update TABLE_1
, but may no longer pass the Privilege on.
Here’s another example:
GRANT assistants_role TO bob WITH ADMIN OPTION;
REVOKE WITH ADMIN OPTION FOR assistants_role FROM bob CASCADE;
The first SQL statement allows Bob to use all of the Privileges belonging to
the ASSISTANTS_ROLE
Role, and to pass the use of this Role on to others.
The second SQL statement revokes the latter ability: Bob can still use the
Role’s Privileges, but may no longer pass that use on.
The GRANT
/ADMIN
option clauses have another effect. Suppose that a user
holds a Privilege on a Table WITH GRANT OPTION
, and does so, also with
GRANT OPTION
. The second user can now do the same for a third user, and so
on – for example:
GRANT DELETE ON TABLE Sally_Dates TO joe WITH GRANT OPTION;
-- assume Sally does this
GRANT DELETE ON TABLE Sally_Dates TO sam WITH GRANT OPTION;
-- assume Joe does this
GRANT DELETE ON TABLE Sally_Dates TO bob WITH GRANT OPTION;
-- assume Sam does this
What should happen if Sally now does the following?
REVOKE DELETE ON TABLE Sally_Dates FROM joe;
Here, we’ve deliberately left off RESTRICT
/CASCADE
for the sake of the
example, so let’s assume that the SQL statement works: Joe no longer has the
DELETE
Privilege on SALLY_DATES
. The Privileges handed down from Joe to
Sam, and from Sam to Bob, are now called “abandoned Privileges”: they are
dependent on Joe’s DELETE
Privilege – and Joe doesn’t have it any more.
This is where the RESTRICT
/CASCADE
<keyword>s come in.
If your
REVOKE
statement specifiesCASCADE
, theREVOKE
succeeds – and it cascades down to revoke any Privileges that would otherwise be abandoned. In our example, that means both Sam and Bob would no longer have theDELETE
Privilege onSALLY_DATES
either.If your
REVOKE
statement specifiesRESTRICT
, theREVOKE
succeeds only if the Privilege being revoked has no dependent Privileges. In our example, that means theREVOKE
statement would fail.
The same holds true for revoking the use of a Role.
Objects can also become “abandoned” when a Privilege or the use of a Role is
revoked. For example, remember that Joe holds the SELECT
Privilege on
SALLY_DATES
and, with this, was able to create his View, JOE_VIEWS
. Now
suppose Sally does this:
REVOKE SELECT ON Sally_Dates FROM joe CASCADE;
The effect is that, not only does Joe lose his ability to SELECT
from
SALLY_DATES
, but that JOE_VIEWS
is dropped! The reason is that, in
effect, JOE_VIEWS
is nothing but a SELECT
that Joe does from
SALLY_DATES
, and since such SELECT
s are no longer allowed, the View
may no longer exist. If, on the other hand, Sally does the following:
REVOKE SELECT ON Sally_Dates FROM joe RESTRICT;
the effect is that the REVOKE
statement fails: Sally may not revoke Joe’s
ability to SELECT
from SALLY_DATES
because this would mean that
JOE_VIEWS
would be abandoned – and this is not allowed. The same holds
true for any Object that anyone was able to create only because they held some
required Privilege (or were able to use a Role that held that Privilege): if
REVOKE ... RESTRICT
is used, the statement will fail but if REVOKE ...
CASCADE
is used, the statement will not only revoke but drop all Objects that
would otherwise be abandoned.
If the REVOKE
statement isn’t able to find a Privilege descriptor for every
one of its Privilege specifications, your DBMS will return the SQLSTATE
warning 01006 "warning-privilege not revoked"
.
If you want to restrict your code to Core SQL, don’t use the <revoke role
statement> form of the REVOKE
statement and don’t use REVOKE ...
CASCADE
or the GRANT OPTION FOR
or HIERARCHY OPTION FOR
clause. Also,
when revoking, make sure that your current <AuthorizationID> is the owner of
the Schema that owns the Object you’re revoking Privileges for.
DROP ROLE Statement¶
The DROP ROLE
statement destroys a Role. The required syntax for the DROP
ROLE
statement is:
DROP ROLE <Role name>
The <Role name> must identify an existing Role for which an enabled
<AuthorizationID> has the WITH ADMIN OPTION
. That is, only an
<AuthorizationID> with the WITH ADMIN OPTION
on a Role may drop it. The
effect of DROP ROLE <Role name>, e.g.,
DROP ROLE assistants_role;
is that the Role named ASSISTANTS_ROLE
will be destroyed and that your DBMS
will also do this for every <AuthorizationID> that was granted use of the Role:
REVOKE assistants_role FROM <AuthorizationID> RESTRICT;
If you want to restrict your code to Core SQL, don’t use the DROP ROLE
statement.
What privileges do I have?¶
Earlier in this chapter, we said that every Privilege is defined by a
descriptor stored in INFORMATION_SCHEMA
. You can thus find out what Privileges
you have by querying the appropriate Views therein (see our chapter on SQL
Catalogs). Each “Privilege descriptor” View includes Columns for grantor,
grantee, Object, <Object name> and is_grantable – the basic Privilege
description stuff. You might think that this makes things fairly
straightforward – but take note! There is deep trickiness implied by
simple-sounding questions like: may Joe may SELECT
from SALLY_DATES
?
The first trickiness is that it is not enough to look for “Grantee = ‘Joe’”.
Joe may have a Privilege due to his ROLE
, and at any rate, Joe is a member
of the PUBLIC
. So when searching for Joe’s Privileges, the conditions
should include “Grantee = 'PUBLIC'
and Grantee = ‘whatever Role Joe may be
able to use’”.
The second trickiness is that it is not enough to look for “Object =
'Sally_Dates'
in the TABLE_PRIVILEGES
View. Joe may have a Privilege on
only one Column, so you have to search the COLUMN_PRIVILEGES
View too. And
even if you do find that Joe does have the SELECT
Privilege, that might not
be enough – he might also need separate USAGE
Privileges for the UDT, the
Character set and the Collation associated with each Column in the Table. And
even if you find that Joe doesn’t have the SELECT
Privilege, the answer may
be insufficient – he might have the SELECT
Privilege on a View of the
Table, or he might be able to get all the data he needs using a procedure.
Tip
Explain these trickinesses to other users at your site, then ask them to observe these limits:
Number of by-Column
GRANT
s = 0.Number of Roles = 1 (not including
PUBLIC
).Number of Views = 1 per Table.
Number of
USAGE GRANT
s = 1 (that is, oneGRANT
only, toPUBLIC
).
Violating the security system¶
Each of the following depends on a “hole” in the Standard SQL security wall. Perhaps the vendor of your particular DBMS hasn’t plugged the hole. What fun it would be to find out …
If you have the SELECT
Privilege WITH GRANT OPTION
on a
single Table: Suppose the grantor of your Privilege is Sally. Why not
reciprocate and GRANT
your Privilege back to her? The advantage is that
later, if Sally tries to revoke your Privilege and forgets to say CASCADE
,
she will fail because there is now a chain of Privilege descriptors,
terminating with her.
If your database is stored on a local microcomputer: The DBMS can’t block your access to the file – usually its only line of defense is encryption or proprietary formatting (Microsoft Access and dBASE IV use variations of this defense). If you know what the Table and Column names are, you can go to another computer and make a database with the same structure but different data. Then copy your file over the legitimate one. This is a cuckoo’s egg violation.
If your database is stored on a distant server: There’s still a chance that the client receives complete row copies for local filtering. Start a long query, then turn your computer off. Start it up again and look for temporary unclosed files, probably in the windowstemp directory.
Here is a security hole that is opened up by the REFERENCES Privilege: Sally creates a Table –
CREATE TABLE Sally_1 ( column1 SMALLINT);
Sally lets Joe reference her Table –
GRANT REFERENCES ON Sally_1 TO joe;
Joe creates a Table –
CREATE TABLE Joe_1 (column_1 INT REFERENCES Sally_1);
Joe lets Sam update his Table –
GRANT UPDATE ON Joe_1 to Sam;
Now, even though Sam has no Privileges at all on Sally’s Table, he can find out what’s in it! All he has to do is:
UPDATE Joe_Schema.Joe_1 SET
column_1 = :x;
in a loop, for “x” = all possible SMALLINT
values. If, and only if,
UPDATE
succeeds, then the value of “x” is in Sally’s Table.
User Functions¶
The SQL Standard defines four types of <AuthorizationID> whose values can be
obtained through the use of the scalar user functions USER
,
CURRENT_USER
, SESSION_USER
, SYSTEM_USER
and CURRENT_ROLE
: each
returns an SQL_TEXT
character string whose value represents an
<AuthorizationID>.
[NON-PORTABLE] The result of a user function is non-standard because the SQL
Standard requires implementors to define whether the result string is fixed
length or variable length and the result string’s fixed length or maximum
length (as applicable). [OCELOT Implementation] The OCELOT DBMS that comes with
this book has each user function return a variable length SQL_TEXT
string.
The result has a maximum length of 128 octets.
The required syntax for a user function is as follows.
user function ::=
USER |
CURRENT_USER |
SESSION_USER |
SYSTEM_USER |
CURRENT_ROLE
A user function returns an SQL_TEXT
character string with the COERCIBLE
coercibility attribute.
CURRENT_USER
returns the <AuthorizationID> of the current user. This is
either the user specified in the CONNECT
statement that began the
SQL-session (assuming it was a user, and not a Role that began the session) or
a default <AuthorizationID> set by your DBMS. CURRENT_USER
will be NULL
if the current <AuthorizationID> is a <Role name>. USER
is a synonym for
CURRENT_USER
that may be used in Core SQL.
CURRENT_ROLE
returns the <AuthorizationID> of the current Role. This is
either the Role specified in the CONNECT
statement that began the
SQL-session (assuming it was a Role, and not a user that began the session;
otherwise NULL
) or the Role specified in the most recent SET ROLE
statement issued for the SQL-session. CURRENT_ROLE
will be NULL
if the
current <AuthorizationID> is a user, rather than a Role.
SESSION_USER
returns the <AuthorizationID> of the SQL-session user. This is
either the user specified in the CONNECT
statement that began the SQL-session
(assuming it was a user, and not a Role that began the session; otherwise a
default <AuthorizationID> set by your DBMS) or the user specified in the most
recent SET SESSION AUTHORIZATION
statement issued for the SQL-session. In most
cases, the SQL-session <AuthorizationID> is the same as the current <AuthorizationID>.
[NON-PORTABLE] SYSTEM_USER
returns an <AuthorizationID> that represents the
operating system user who executed the SQL-client Module that contains the
SYSTEM_USER function call and is thus non-standard because the SQL Standard
requires implementors to define their system user. [OCELOT Implementation] The
OCELOT DBMS that comes with this book has a SYSTEM_USER
called OCELOT.
The current <AuthorizationID> is the user or Role against which Privilege checking is done to see whether an SQL operation is allowable. For example, prior to executing this SQL statement:
SELECT * FROM Table_1;
your DBMS will check to see that the current <AuthorizationID> has the
SELECT
Privilege on that Table. If the current <AuthorizationID> has the
Privilege, the SELECT
is performed. If the current <AuthorizationID> does
not have the Privilege, the SELECT is disallowed.
The SQL-session <AuthorizationID> is the <AuthorizationID> that is used as the default <AuthorizationID> whenever a SQL operation which requires an <AuthorizationID> is not provided with an explicit <AuthorizationID> specification. For example, when executing this SQL statement:
CREATE SCHEMA bob;
your DBMS effectively executes this SQL statement:
CREATE SCHEMA bob AUTHORIZATION SESSION_USER;
If you want to restrict your code to Core SQL, don’t use CURRENT_USER
,
SESSION_USER
, SYSTEM_USER
or CURRENT_ROLE
.
Dialects¶
Many vendors have added their own Privileges to the SQL Standard’s set. For example:
IBM’s DB2 has a SYSADM (system administrator) who can
CREATE
andDROP
in any Schema, and who can create Schemas (one of the implementor-defined areas in the Standard). Several other vendors also have similar “super user” Privileges which are not associated with any particular Object.Sybase has a
CONNECT
Privilege; other vendors allow connection by anyone, but new users can’t do anything without further Privileges.The most popular non-standard Privilege is GRANT ALTER (which sometimes allows users to both alter and drop Tables).
DBMSs which allow non-standard Objects allow non-standard Privileges to go with them; thus GRANT INDEX is common, and DB2 also has access controls for tablespaces and indexspaces (which are somewhat like “files”).
On the other hand, older DBMSs may fail to support the REFERENCES
Privilege. They allow Constraints to be defined by anyone who has the
appropriate SELECT
Privileges instead.
A few SQL3 features are already fairly common. Sybase, Microsoft SQL Server and
Informix allow “GRANT SELECT
(Column-name list) …”. Oracle has had a
CREATE ROLE
statement since version 7.0; Sybase and Microsoft SQL Server
also allow Roles but they use non-standard terms and syntax.
As for syntax – the majority of DBMSs will let you specify more than just one
“Object” in both GRANT
and REVOKE
statements. This leads to ambiguities
– what is "GRANT UPDATE(column1) ON Tables_1, Tables_2"
supposed to do?
And is it proper if the different Objects are in different Catalogs? And what
if different Privileges apply for different Objects? You’re on safer ground if
you just ignore this sort of enhancement.
The biggest variations involve the ways that the DBMS can identify and
acknowledge users. If your operating system has a list of valid users and
passwords (something you’ll notice if you have to “log in” to your computer),
then your DBMS can get the user by querying the OS (Ingres, Informix and DB2 do
this). Alternatively, or in addition, your DBMS might require you to supply
both a user ID and a password when you CONNECT
(Oracle and Sybase do this).
In the former case, the user’s name is probably limited to as few as eight
characters because the OS has more restrictions than the DBMS does. In the
latter case, there is probably a non-standard CREATE USER statement and a
“user” Object stored inside the database.
Finally, you will see some DBMSs skimping on their obligation to store
Privilege descriptors for owners. A quick glance at the
INFORMATION_SCHEMA.COLUMN_PRIVILEGES
View should tell you whether your DBMS
is one of these.