| <html> |
| <head> |
| <META http-equiv="Content-Type" content="text/html; charset=UTF-8"> |
| <title>Chapter 6. Access Control</title> |
| <link href="../docbook.css" rel="stylesheet" type="text/css"> |
| <meta content="DocBook XSL-NS Stylesheets V1.74.0" name="generator"> |
| <meta name="keywords" content="Hsqldb, HyperSQL, SQL"> |
| <meta name="keywords" content="Hsqldb, HyperSQL, Database, JDBC, Java"> |
| <link rel="home" href="index.html" title="HyperSQL User Guide"> |
| <link rel="up" href="index.html" title="HyperSQL User Guide"> |
| <link rel="prev" href="texttables-chapt.html" title="Chapter 5. Text Tables"> |
| <link rel="next" href="dataaccess-chapt.html" title="Chapter 7. Data Access and Change"> |
| </head> |
| <body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"> |
| <div class="navheader"> |
| <table summary="Navigation header" width="100%"> |
| <tr> |
| <td align="left" width="30%"><a accesskey="p" href="texttables-chapt.html"><img src="../images/db/prev.png" alt="Prev"></a> </td><td align="center" width="40%" style="font-weight:bold;">Chapter 6. Access Control</td><td align="right" width="30%"> <a accesskey="n" href="dataaccess-chapt.html"><img src="../images/db/next.png" alt="Next"></a></td> |
| </tr> |
| <tr> |
| <td valign="top" align="left" width="30%">Chapter 5. Text Tables </td><td align="center" width="40%"><a accesskey="h" href="index.html"><img src="../images/db/home.png" alt="Home"></a></td><td valign="top" align="right" width="30%"> Chapter 7. Data Access and Change</td> |
| </tr> |
| </table> |
| </div> |
| <HR> |
| <div class="chapter" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title"> |
| <a name="accesscontrol-chapt"></a>Chapter 6. Access Control</h2> |
| </div> |
| <div> |
| <div class="authorgroup"> |
| <div class="author"> |
| <h3 class="author"> |
| <span class="firstname">Fred</span> <span class="surname">Toussi</span> |
| </h3> |
| <div class="affiliation"> |
| <span class="orgname">The HSQL Development Group<br> |
| </span> |
| </div> |
| </div> |
| </div> |
| </div> |
| <div> |
| <p class="releaseinfo">$Revision: 3096 $</p> |
| </div> |
| <div> |
| <div class="legalnotice"> |
| <a name="N1170F"></a> |
| <p>Copyright 2010 Fred Toussi. Permission is granted to distribute |
| this document without any alteration under the terms of the HSQLDB |
| license. Additional permission is granted to the HSQL Development Group |
| to distribute this document with or without alterations under the terms |
| of the HSQLDB license.</p> |
| </div> |
| </div> |
| <div> |
| <p class="pubdate">$Date: 2009-08-09 17:50:39 +0100 (Sun, 09 Aug 2009) $</p> |
| </div> |
| </div> |
| </div> |
| <div class="toc"> |
| <p> |
| <b>Table of Contents</b> |
| </p> |
| <dl> |
| <dt> |
| <span class="section"><a href="accesscontrol-chapt.html#N11712">Overview</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="accesscontrol-chapt.html#N11752">Authorizations and Access Control</a></span> |
| </dt> |
| <dd> |
| <dl> |
| <dt> |
| <span class="section"><a href="accesscontrol-chapt.html#N11775">Built-In Roles and Users</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="accesscontrol-chapt.html#N117CE">Access Rights</a></span> |
| </dt> |
| </dl> |
| </dd> |
| <dt> |
| <span class="section"><a href="accesscontrol-chapt.html#N11810">Statements for |
| Authorization and Access Control</a></span> |
| </dt> |
| </dl> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="N11712"></a>Overview</h2> |
| </div> |
| </div> |
| </div> |
| <p>Apart from schemas and their object, each HyperSQL catalog has USER |
| and ROLE objects. These objects are collectively called |
| <span class="emphasis"><em>authorizations</em></span>. Each AUTHORIZATION has some access |
| rights on some of the schemas or the objects they contain. The persistent |
| elements of an SQL environment are database objects</p> |
| <p>Each database object has a name. A name is an identifier and is |
| unique within its name-space. Authorizations names follow the rules |
| described below and the case-normal form is stored in the database. When |
| connecting to a database, the user name and password must match the case |
| of the case-normal form.</p> |
| <a name="N1171C" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>identifier</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>definition of identifier</em></span> |
| </p> |
| <p> |
| <code class="literal"><identifier> ::= <regular identifier> | |
| <delimited identifier> | <SQL language identifier> |
| </code> |
| </p> |
| <p> |
| <code class="literal"><delimited identifier> ::= <double quote> |
| <character sequence> <double quote></code> |
| </p> |
| <p> |
| <code class="literal"><regular identifier> ::= <special character |
| sequence></code> |
| </p> |
| <p> |
| <code class="literal"><SQL language identifier> ::= <special |
| character sequence></code> |
| </p> |
| <p>A <code class="literal"><delimited identifier></code> is a sequence |
| of characters enclosed with double-quote symbols. All characters are |
| allowed in the character sequence.</p> |
| <p>A <code class="literal"><regular identifier></code> is a special |
| sequence of characters. It consists of letters, digits and the underscore |
| characters. It must begin with a letter.</p> |
| <p>A <code class="literal"><SQL language identifier></code> is similar |
| to <code class="literal"><regular identifier></code> but the letters can range |
| only from A-Z in the ASCII character set. This type of identifier is used |
| for names of CHARACTER SET objects.</p> |
| <p>If the character sequence of a delimited identifier is the same |
| as an undelimited identifier, it represents the same identifier. For |
| example "JOHN" is the same identifier as JOHN. In a <code class="literal"><regular |
| identifier></code> the case-normal form is considered for |
| comparison. This form consists of the upper-case of equivalent of all the |
| letters.</p> |
| <p>The character sequence length of all identifiers must be between |
| 1 and 128 characters.</p> |
| <p>A reserved word is one that is used by the SQL Standard for |
| special purposes. It is similar to a <code class="literal"><regular |
| identifier></code> but it cannot be used as an identifier for user |
| objects. If a reserved word is enclosed in double quote characters, it |
| becomes a quoted identifier and can be used for database |
| objects.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="N11752"></a>Authorizations and Access Control</h2> |
| </div> |
| </div> |
| </div> |
| <p>In general, ROLE and USER objects simply control access to schema |
| objects. This is the scope of the SQL Standard. However, there are special |
| roles that allow the creation of USER and ROLE objects and also allow some |
| special operations on the database as a whole. These roles are not defined |
| by the Standard, which has left it to implementors to define such roles as |
| they are needed for the particular SQL implementation.</p> |
| <p>A ROLE has a name a collection of zero or more other roles, plus |
| some privileges (access rights). A USER has a name and a password. It |
| similarly has a collection of zero or more roles plus some |
| privileges.</p> |
| <p>USER objects existed in the SQL-92, but ROLE objects were introduced |
| in SQL:1999. Originally it was intended that USER objects would normally |
| be the same as the operating system USER objects and their authentication |
| would be handled outside the SQL environment. The co-existence of ROLE and |
| USER objects results in complexity. With the addition of ROLE objects, |
| there is no rationale, other than legacy support, for granting privileges |
| to USER objects directly. It is better to create roles and grant |
| privileges to them, then grant the roles to USER objects.</p> |
| <p>The Standard effectively defines a special ROLE, named PUBLIC. All |
| authorization have the PUBLIC role, which cannot be removed from them. |
| Therefore any access right assigned to the PUBLIC role applies to all |
| authorizations in the database. For many simple databases, it is adequate |
| to create a single, non-admin user, then assign access rights to the |
| pre-existing PUBLIC role. Access to INFORMATION_SCHEMA views is granted to |
| PUBLIC, therefore these views are accessible to all. However, the contents |
| of each view depends on the ROLE or USER (AUTHORIZATION) that is in force |
| while accessing the view.</p> |
| <p>Each schema has a single AUTHORIZATION. This is commonly known as |
| the <span class="emphasis"><em>owner</em></span> of the schema. All the objects in the |
| schema inherit the schema owner. The schema owner can add objects to the |
| schema, drop them or alter them.</p> |
| <p>By default, the objects in a schema can only be accessed by the |
| schema owner. The schema owner can grant access rights on the objects to |
| other users or roles.</p> |
| <a name="N11764" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>authorization |
| identifier</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>authorization identifier</em></span> |
| </p> |
| <p> |
| <code class="literal"><authorization identifier> ::= <role name> | |
| <user name></code> |
| </p> |
| <p>Authorization identifiers share the same name-space within the |
| database. The same name cannot be used for a USER and a ROLE.</p> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N11775"></a>Built-In Roles and Users</h3> |
| </div> |
| </div> |
| </div> |
| <p>There are some pre-defined roles in each database; some defined by |
| the SQL Standard, some by HyperSQL. These roles can be assigned to users |
| (directly or via other, user-defined roles). In addition, there is the |
| default initial user, SA, created with each new database.</p> |
| <a name="N1177A" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>PUBLIC</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>the PUBLIC role</em></span> |
| </p> |
| <p>The role that is assigned to all authorizations (roles and |
| users) in the database. This role has access rights to all objects in |
| the INFORMATION_SCHEMA. Any roles or rights granted to this role, are in |
| effect granted to all users of the database.</p> |
| <a name="N11788" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>_SYSTEM</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>the _SYSTEM role</em></span> |
| </p> |
| <p>This role is the authorization for the pre-defined (system) |
| objects in the database, including the INFORMATION_SCHEMA. This role |
| cannot be assigned to any authorization.</p> |
| <a name="N11796" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>DBA</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>the DBA role (HyperSQL-specific)</em></span> |
| </p> |
| <p>This is a special role in HyperSQL. A user that has this role |
| can perform all possible administrative tasks on the database. The DBA |
| role can also act as a proxy for all the roles and users in the |
| database. This means it can do everything the authorization for a schema |
| can do, including dropping the schema or its objects, or granting rights |
| on the schema objects to a grantee.</p> |
| <a name="N117A4" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>CREATE_SCHEMA</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>the CREATE_SCHEMA role |
| (HyperSQL-specific)</em></span> |
| </p> |
| <p>An authorization that has this role, can create schemas. The |
| DBA authorization has this role and can grant it to other |
| authorizations.</p> |
| <a name="N117B2" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>CHANGE_AUTHORIZATION</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>the CHANGE_AUTHORIZATION role |
| (HyperSQL-specific)</em></span> |
| </p> |
| <p>A user that has this role, can change the authorization for the |
| current session to another user. The DBA authorization has this role and |
| can grant it to other authorizations.</p> |
| <a name="N117C0" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>SA</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>the SA user (HyperSQL-specific)</em></span> |
| </p> |
| <p>This user is automatically created with a new database and has |
| the DBA role. Initially, the password for this user is an empty string. |
| After connecting to the new database as this user, it is possible to |
| change the password, create other users and created new schema objects. |
| The SA user can be dropped by another user that has the DBA |
| role.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N117CE"></a>Access Rights</h3> |
| </div> |
| </div> |
| </div> |
| <p>By default, the objects in a schema can only be accessed by the |
| schema owner. But the schema owner can grant privileges (access rights) |
| on the objects to other users or roles.</p> |
| <p>Things can get far more complex, because the grant of privileges |
| can be made WITH GRANT OPTION. In this case, the role or user that has |
| been granted the privilege can grant the privilege to other roles and |
| users.</p> |
| <p>Privileges can also be revoked from users or roles.</p> |
| <p>The statements for granting and revoking privileges normally |
| specify which privileges are granted or revoked. However, there is a |
| shortcut, ALL PRIVILEGES, which means all the privileges that the |
| <code class="literal"><grantor></code> has on the schema object. The |
| <code class="literal"><grantor></code> is normally the CURRENT_USER of the |
| session that issues the statement.</p> |
| <p>The user or role that is granted privileges is referred to as |
| <code class="literal"><grantee></code> for the granted privileges.</p> |
| <p> |
| <span class="bold"><strong>Table</strong></span> |
| </p> |
| <p>For tables, including views, privileges can be granted with |
| different degrees of granularity. It is possible to grant a privilege on |
| all columns of a table, or on specific columns of the table.</p> |
| <p>The DELETE privilege applies to the table, rather than its |
| columns. It applies to all DELETE statements.</p> |
| <p>The SELECT, INSERT and UPDATE privileges may apply to all |
| columns or to individual columns. These privileges determine whether the |
| <code class="literal"><grantee></code> can execute SQL data statements on |
| the table.</p> |
| <p>The SELECT privilege designates the columns that can be |
| referenced in SELECT statements, as well as the columns that are read in |
| a DELETE or UPDATE statement, including the search condition.</p> |
| <p>The INSERT privilege designates the columns into which explicit |
| values can be inserted. To be able to insert a row into the table, the |
| user must therefore have the INSERT privilege on the table, or at least |
| all the columns that do not have a default value.</p> |
| <p>The UPDATE privilege simply designates the table or the |
| specific columns that can be updated.</p> |
| <p>The REFERENCES privilege allows the |
| <code class="literal"><grantee></code> to define a FOREIGN KEY constraint on |
| a different table, which references the table or the specific columns |
| designated for the REFERENCES privilege.</p> |
| <p>The TRIGGER privilege allows adding a trigger to the |
| table.</p> |
| <p> |
| <span class="bold"><strong>Sequence, Type, Domain, Character Set, |
| Collation, Transliteration,</strong></span> |
| </p> |
| <p>For these objects, only USAGE can be granted. The USAGE |
| privilege is needed when object is referenced directly in an SQL |
| statement.</p> |
| <p> |
| <span class="bold"><strong>Routine</strong></span> |
| </p> |
| <p>For routines, including procedures or functions, only EXECUTE |
| privilege can be granted. This privilege is needed when the routine is |
| used directly in an SQL statement.</p> |
| <p> |
| <span class="bold"><strong>Other Objects</strong></span> |
| </p> |
| <p>Other objects such as constraints and assertions are not used |
| directly and there is no grantable privilege that refers to |
| them.</p> |
| </div> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="N11810"></a>Statements for |
| Authorization and Access Control</h2> |
| </div> |
| </div> |
| </div> |
| <p>The statements listed below allow creation and destruction of USER |
| and ROLE objects. The GRANT and REVOKE statements allow roles to be |
| assigned to other roles or to users. The same statements are also used in |
| a different form to assign privileges on schema objects to users and |
| roles.</p> |
| <a name="N11816" class="indexterm"></a> |
| <p> |
| <a name="create_user-sql"></a><span class="bold"><strong>CREATE |
| USER</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>user definition (HyperSQL)</em></span> |
| </p> |
| <p> |
| <code class="literal"><user definition> ::= CREATE USER <user |
| name> PASSWORD <password> [ ADMIN ]</code> |
| </p> |
| <p>Define a new user and its password. <code class="literal"><user |
| name></code> is an SQL identifier. If it is double-quoted it is |
| case-sensitive, otherwise it is turned to uppercase. |
| <code class="literal"><password></code> is a string enclosed with single quote |
| characters and is case-sensitive. If <code class="literal">ADMIN</code> is |
| specified, the DBA role is granted to the new user. Only a user with the |
| DBA role can execute this statement.</p> |
| <a name="N11831" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>DROP USER</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>drop user statement (HyperSQL)</em></span> |
| </p> |
| <p> |
| <code class="literal"><drop user statement> ::= DROP USER <user |
| name></code> |
| </p> |
| <p>Drop (destroy) an existing user. If the specified user is the |
| authorization for a schema, the schema is destroyed. Only a user with the |
| DBA role can execute this statement.</p> |
| <a name="N11842" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>ALTER USER ... SET |
| PASSWORD</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>set the password for a user |
| (HyperSQL)</em></span> |
| </p> |
| <p> |
| <code class="literal"><alter user set password statement> ::= ALTER USER |
| <user name> SET PASSWORD <password></code> |
| </p> |
| <p>Change the password of an existing user. <code class="literal"><user |
| name></code> is an SQL identifier. If it is double-quoted it is |
| case-sensitive, otherwise it is turned to uppercase. |
| <code class="literal"><password></code> is a string enclosed with single quote |
| characters and is case-sensitive. Only a user with the DBA role can |
| execute this command.</p> |
| <a name="N11859" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>ALTER USER ... SET INITIAL |
| SCHEMA</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>set the initial schema for a user |
| (HyperSQL)</em></span> |
| </p> |
| <p> |
| <code class="literal"><alter user set initial schema statement> ::= |
| ALTER USER <user name> SET INITIAL SCHEMA <schema name> | |
| DEFAULT</code> |
| </p> |
| <p>Change the initial schema for a user. The initial schema is the |
| schema used by default for SQL statements issued during a session. If |
| <code class="literal">DEFAULT</code> is used, the default initial schema for all |
| users is used as the initial schema for the user. The SET SCHEMA command |
| allows the user to change the schema for the duration of the session. Only |
| a user with the DBA role can execute this statement.</p> |
| <a name="N1186D" class="indexterm"></a> |
| <p> |
| <a name="set_password-sql"></a><span class="bold"><strong>SET |
| PASSWORD</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>set password statement (HyperSQL)</em></span> |
| </p> |
| <p> |
| <code class="literal"><set password statement> ::= SET PASSWORD |
| <password></code> |
| </p> |
| <p>Set the password for the current user. |
| <code class="literal"><password></code> is a string enclosed with single quote |
| characters and is case-sensitive.</p> |
| <a name="N11882" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>SET INITIAL SCHEMA</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>set the initial schema for the current user |
| (HyperSQL)</em></span> |
| </p> |
| <p> |
| <code class="literal"><set initial schema statement> ::= SET INITIAL |
| SCHEMA <schema name> | DEFAULT</code> |
| </p> |
| <p>Change the initial schema for the current user. The initial |
| schema is the schema used by default for SQL statements issued during a |
| session. If <code class="literal">DEFAULT</code> is used, the default initial schema |
| for all users is used as the initial schema for the current user. The |
| separate SET SCHEMA command allows the user to change the schema for the |
| duration of the session. See also the <a class="link" href="sessions-chapt.html" title="Chapter 3. Sessions and Transactions">Sessions and Transactions</a> chapter.</p> |
| <a name="N1189B" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>SET DATABASE DEFAULT INITIAL |
| SCHEMA</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>set the default initial schema for all users |
| (HyperSQL)</em></span> |
| </p> |
| <p> |
| <code class="literal"><set database default initial schema statement> |
| ::= SET DATABASE DEFAULT INITIAL SCHEMA <schema |
| name></code> |
| </p> |
| <p>Sets the initial schema for new users. This schema can later be |
| changed with the <code class="literal"><set initial schema statement></code> |
| command.</p> |
| <a name="N118AF" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>CREATE ROLE</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>role definition</em></span> |
| </p> |
| <p> |
| <code class="literal"><role definition> ::= CREATE ROLE <role |
| name> [ WITH ADMIN <grantor> ]</code> |
| </p> |
| <p>Defines a new role. Initially the role has no rights, except |
| those of the PUBLIC role. Only a user with the DBA role can execute this |
| command.</p> |
| <a name="N118C0" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>DROP ROLE</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>drop role statement</em></span> |
| </p> |
| <p> |
| <code class="literal"><drop role statement> ::= DROP ROLE <role |
| name></code> |
| </p> |
| <p>Drop (destroy) a role. If the specified role is the authorization |
| for a schema, the schema is destroyed. Only a user with the DBA role can |
| execute this statement.</p> |
| <a name="N118D1" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>GRANTED BY</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>grantor determination</em></span> |
| </p> |
| <p> |
| <code class="literal">GRANTED BY <grantor></code> |
| </p> |
| <p> |
| <code class="literal"><grantor> ::= CURRENT_USER | |
| CURRENT_ROLE</code> |
| </p> |
| <p>The authorization that is granting or revoking a role or |
| privileges. The optional <code class="literal">GRANTED BY <grantor></code> |
| clause can be used in various statements that perform GRANT or REVOKE |
| actions. If the clause is not used, the authorization is CURRENT_USER. |
| Otherwise, it is the specified authorization.</p> |
| <a name="N118E8" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>GRANT</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>grant privilege statement</em></span> |
| </p> |
| <p> |
| <code class="literal"><grant privilege statement> ::= GRANT |
| <privileges> TO <grantee> [ { <comma> <grantee> |
| }... ] [ WITH GRANT OPTION ] [ GRANTED BY <grantor> |
| ]</code> |
| </p> |
| <p>Assign privileges on schema objects to roles or users. Each |
| <code class="literal"><grantee></code> is a role or a user. If <code class="literal">[ WITH |
| GRANT OPTION ]</code> is specified, then the |
| <code class="literal"><grantee></code> can assign the privileges to other |
| <code class="literal"><grantee></code> objects.</p> |
| <p> |
| <code class="literal"><privileges> ::= <object privileges> ON |
| <object name></code> |
| </p> |
| <p> |
| <code class="literal"><object name> ::= [ TABLE ] <table name> | |
| DOMAIN <domain name> | COLLATION <collation name> | CHARACTER |
| SET <character set name> | TRANSLATION <transliteration name> |
| | TYPE <user-defined type name> | SEQUENCE <sequence generator |
| name> | <specific routine designator> | ROUTINE <routine |
| name> | FUNCTION <function name> | PROCEDURE <procedure |
| name></code> |
| </p> |
| <p> |
| <code class="literal"><object privileges> ::= ALL PRIVILEGES | |
| <action> [ { <comma> <action> }... ]</code> |
| </p> |
| <p> |
| <code class="literal"><action> ::= SELECT | SELECT <left paren> |
| <privilege column list> <right paren> | DELETE | INSERT [ |
| <left paren> <privilege column list> <right paren> ] | |
| UPDATE [ <left paren> <privilege column list> <right |
| paren> ] | REFERENCES [ <left paren> <privilege column |
| list> <right paren> ] | USAGE | TRIGGER | |
| EXECUTE</code> |
| </p> |
| <p> |
| <code class="literal"><privilege column list> ::= <column name |
| list></code> |
| </p> |
| <p> |
| <code class="literal"><grantee> ::= PUBLIC | <authorization |
| identifier></code> |
| </p> |
| <p>The <code class="literal"><object privileges></code> that can be used |
| depend on the type of the <code class="literal"><object name></code>. These |
| are discussed in the previous section. For a table, if |
| <code class="literal"><privilege column list></code> is not specified, then |
| the privilege is granted on the table, which includes all of its columns |
| and any column that may be added to it in the future. For routines, the |
| name of the routine can be specified in two ways, either as the generic |
| name as the specific name. HyperSQL allows referencing all overloaded |
| versions of a routine at the same time, using its name. This differs from |
| the SQL Standard which requires the use of <code class="literal"><specific routine |
| designator></code> to grant privileges separately on each different |
| signature of the routine.</p> |
| <p>Each <code class="literal"><grantee></code> is the name of a role or |
| a user. Examples of GRANT statement are given below:</p> |
| <div class="informalexample"> |
| <pre class="programlisting">GRANT ALL ON SEQUENCE aSequence TO roleOrUser |
| GRANT SELELCT ON aTable TO roleOrUser |
| GRANT SELECT, UPDATE ON aTABLE TO roleOrUser1, roleOrUser2 |
| GRANT SELECT(columnA, columnB), UPDATE(columnA, columnB) ON TABLE aTable TO roleOrUser |
| GRANT EXECUTE ON SPECIFIC ROUTINE aroutine_1234 TO rolOrUser |
| </pre> |
| </div> |
| <p>As mentioned in the general discussion, it is better to define a |
| role for the collection of all the privileges required by an application. |
| This role is then granted to any user. If further changes are made to the |
| privileges of this role, they are automatically reflected in all the users |
| that have the role.</p> |
| <a name="N1192F" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>GRANT</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>grant role statement</em></span> |
| </p> |
| <p> |
| <code class="literal"><grant role statement> ::= GRANT <role name> |
| [ { <comma> <role name> }... ] TO <grantee> [ { |
| <comma> <grantee> }... ] [ WITH ADMIN OPTION ] [ GRANTED BY |
| <grantor> ]</code> |
| </p> |
| <p>Assign roles to roles or users. One or more roles can be assigned |
| to one or more <code class="literal"><grantee></code> objects. A |
| <code class="literal"><grantee></code> is a user or a role. If the <code class="literal">[ |
| WITH ADMIN OPTION ]</code> is specified, then each |
| <code class="literal"><grantee></code> can grant the newly assigned roles to |
| other grantees. An example of user and role creation with grants is given |
| below:</p> |
| <div class="informalexample"> |
| <pre class="programlisting">CREATE USER appuser |
| CREATE ROLE approle |
| GRANT approle TO appuser |
| GRANT SELECT, UPDATE ON TABLE atable TO approle |
| GRANT USAGE ON SEQUENCE asequence to approle |
| GRANT EXECUTE ON ROUTINE aroutine TO approle |
| </pre> |
| </div> |
| <a name="N1194F" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>REVOKE privilege</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>revoke statement</em></span> |
| </p> |
| <p> |
| <code class="literal"><revoke privilege statement> ::= REVOKE [ GRANT |
| OPTION FOR ] <privileges> FROM <grantee> [ { <comma> |
| <grantee> }... ] [ GRANTED BY <grantor> ] RESTRICT | |
| CASCADE</code> |
| </p> |
| <p>Revoke privileges from a user or role.</p> |
| <a name="N11960" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>REVOKE role</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>revoke role statement</em></span> |
| </p> |
| <p> |
| <code class="literal"><revoke role statement> ::= REVOKE [ ADMIN OPTION |
| FOR ] <role revoked> [ { <comma> <role revoked> }... ] |
| FROM <grantee> [ { <comma> <grantee> }... ] [ GRANTED BY |
| <grantor> ] RESTRICT | CASCADE</code> |
| </p> |
| <p> |
| <code class="literal"><role revoked> ::= <role |
| name></code> |
| </p> |
| <p>Revoke a role from users or roles.</p> |
| </div> |
| </div> |
| <HR xmlns:xi="http://www.w3.org/2001/XInclude"> |
| <P xmlns:xi="http://www.w3.org/2001/XInclude" class="svnrev">$Revision: 3601 $</P> |
| <div class="navfooter"> |
| <hr> |
| <table summary="Navigation footer" width="100%"> |
| <tr> |
| <td align="left" width="40%"><a accesskey="p" href="texttables-chapt.html"><img src="../images/db/prev.png" alt="Prev"></a> </td><td align="center" width="20%"> </td><td align="right" width="40%"> <a accesskey="n" href="dataaccess-chapt.html"><img src="../images/db/next.png" alt="Next"></a></td> |
| </tr> |
| <tr> |
| <td valign="top" align="left" width="40%">Chapter 5. Text Tables </td><td align="center" width="20%"><a accesskey="h" href="index.html"><img src="../images/db/home.png" alt="Home"></a></td><td valign="top" align="right" width="40%"> Chapter 7. Data Access and Change</td> |
| </tr> |
| </table> |
| </div> |
| </body> |
| </html> |