Assigning and removing rights. Setting permissions grant command Using views to filter privileges

GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...] ON (tbl_name | * | *.* | db_name.*) TO user_name "password"] [, user_name ...] ] ] ] ] REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...] ON (tbl_name | * | *.* | db_name.*) FROM user_name [, user_name ...]

GRANT is included in MySQL version 3.22.11 and higher. In earlier versions of MySQL, the GRANT statement does nothing.

The GRANT and REVOKE commands allow system administrators to create MySQL users and grant or revoke rights to users at four levels of privilege:

Global level Global privileges apply to all databases on the specified server. These privileges are stored in the mysql.user table. Database level Database privileges apply to all tables in the specified database. These privileges are stored in the mysql.db and mysql.host tables. Table level Table privileges apply to all columns of the specified table. These privileges are stored in the mysql.tables_priv table. Column level Column privileges apply to individual columns in the specified table. These privileges are stored in the mysql.columns_priv table.

If privileges are granted to a user that does not exist, then that user is created. For examples of the GRANT command, see section 4.3.5 Adding New Users to MySQL.

The table shows a list of possible values ​​for the priv_type parameter for the GRANT and REVOKE statements:

ALL	Sets all simple privileges except WITH GRANT OPTION
ALTER	Allows the use of ALTER TABLE
CREATE	Allows the use of CREATE TABLE
CREATE TEMPORARY TABLES	Allows the use of CREATE TEMPORARY TABLE
DELETE	Allows the use of DELETE
DROP	Allows the use of DROP TABLE.
EXECUTE	Allows the user to run stored procedures (for MySQL 5.0)
FILE	Allows the use of SELECT ... INTO OUTFILE and LOAD DATA INFILE .
INDEX	Allows the use of CREATE INDEX and DROP INDEX
INSERT	Allows the use of INSERT
LOCK TABLES	Allows the use of LOCK TABLES on tables that have the SELECT privilege.
PROCESS	Allows the use of SHOW FULL PROCESSLIST
REFERENCES	Reserved for future use
RELOAD	Allows the use of FLUSH
REPLICATION CLIENT	Grants the user the right to query the location of the master and slave servers.
REPLICATION SLAVE	Necessary for slave servers during replication (for reading information from the binary logs of the head server).
SELECT	Allows the use of SELECT
SHOW DATABASES	SHOW DATABASES Lists all databases.
SHUTDOWN	Allows the use of mysqladmin shutdown
SUPER	Allows you to establish one connection (one time), even if max_connections is reached, and run CHANGE MASTER , KILL thread , mysqladmin debug , PURGE MASTER LOGS and SET GLOBAL commands
UPDATE	Allows the use of UPDATE
USAGE	Synonym for ``without privileges''.

The USAGE value can be specified if you need to create a user without privileges.

The CREATE TEMPORARY TABLES, EXECUTE, LOCK TABLES, REPLICATION ..., SHOW DATABASES, and SUPER privileges are new in version 4.0.2. To take advantage of these new privileges after upgrading to version 4.0.2, you must run the mysql_fix_privilege_tables script.

In older versions of MySQL, the PROCESS privilege grants the same rights as the new SUPER privilege.

To revoke a user's privileges granted by the GRANT command, use the priv_type value in the GRANT OPTION:

Mysql> REVOKE GRANT OPTION ON ... FROM ...;

The only priv_type values ​​that can be specified for a table are: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT, IDEX, and ALTER.

The only priv_type values ​​that can be specified for a column (when using the column_list operator) are SELECT , INSERT , and UPDATE .

Global privileges can be set using the ON *.* syntax, and database privileges can be set using the ON db_name.* syntax. If you specify ON * while the current database is open, privileges will be set for that database. ( Warning: if you specify ON * when absence the current database is open, this will affect global privileges!)

In order to be able to define rights for users on specific computers, MySQL provides the ability to specify the user name (user_name) in the form. If you need to specify a user string that contains special characters (such as `-"), or a host string that contains special or wildcard characters (such as `%"), you can enclose the name remote computer or user in quotes (for example, "test-user"@"test-hostname").

You can also include wildcards in the remote computer name. For example, "%.loc.gov" refers to the user of all remote computers in the loc.gov domain, and "144.155.166.%" refers to the user of all remote computers in the class C subnet 144.155.166.

The simple form user is a synonym for "%" .

MySQL does not support wildcards in usernames. Anonymous users are defined by inserting User="" records into the mysql.user table or by creating a user with an empty name using the GRANT command.

Note: If anonymous users are allowed to connect to the MySQL server, you must also grant privileges to all local users as , because otherwise, when a user tries to log into MySQL from the local computer, the mysql.user table will use the anonymous user login!

To check if this happens on your computer, run the following query:

Mysql> SELECT Host,User FROM mysql.user WHERE User="";

Currently, the GRANT command supports names of remote computers, tables, databases, and columns of up to 60 characters. The username must contain no more than 16 characters.

Privileges for a table or column are formed using the logical OR operator from the privileges at each of the four levels. For example, if the mysql.user table indicates that the user has the global SELECT privilege, that privilege is not revoked at the database, table, or column level.

The privileges for a column can be calculated as follows:

Global privileges OR (database privileges AND remote computer privileges) OR table privileges OR column privileges

In most cases, user rights are defined at only one privilege level, so this procedure is usually not as complex as described above. detailed information The sequence of actions for checking privileges is presented in section 4.2 General security issues and the MySQL access privilege system.

If privileges are granted to a user/remote combination that is not in the mysql.user table, an entry is added to the mysql.user table and remains in the table until deleted using the DELETE command. In other words, the GRANT command can create user records in the table, but the REVOKE command cannot delete them. This must be done using the DELETE command.

If you have database privileges, an entry is created in the mysql.db table if necessary. This entry is deleted after all privileges for this database are removed with the REVOKE command.

If a user does not have any privileges on a table, then the table is not displayed when the user requests a list of tables (for example, using the SHOW TABLES statement).

The WITH GRANT OPTION statement gives a user the ability to grant other users any privileges that he himself has at a specified privilege level. Care must be taken when granting the GRANT privilege, as two users with different privileges may combine their privileges!

The MAX_QUERIES_PER_HOUR # , MAX_UPDATES_PER_HOUR # and MAX_CONNECTIONS_PER_HOUR # options are new in MySQL version 4.0.2. These settings limit the number of requests, updates, and logins that a user can make in one hour. If set to 0 (the default), this means that there are no restrictions for this user. See section.

You cannot give another user a privilege that you do not have. The GRANT privilege allows you to grant only those privileges that you have.

Note that if a user is assigned the GRANT privilege at a particular privilege level, then all privileges that that user already has (or will be assigned in the future!) at that level can also be assigned to that user. Let's assume that a user has been granted INSERT privilege on a database. If you then grant the SELECT privilege in the database and specify WITH GRANT OPTION, the user can grant not only the SELECT privilege, but also the INSERT privilege. If you then grant the user the UPDATE privilege in the database, the user can then issue INSERT, SELECT, and UPDATE.

ALTER privileges should not be assigned to regular users. This gives the user the ability to break the privilege system by renaming tables!

Please note that if table or column privileges are used for even one user, the server checks the table and column privileges for all users and this slows down MySQL somewhat.

When mysqld starts, all privileges are read into memory. Database, table, and column privileges take effect immediately, while user-level privileges take effect the next time the user connects. Changes to the privilege assignment tables that are made using the GRANT and REVOKE commands are processed immediately by the server. If you modify privilege assignment tables manually (using INSERT , UPDATE , etc.), you must run the FLUSH PRIVILEGES or mysqladmin flush-privilege s statement to instruct the server to reload the privilege assignment tables. See section 4.3.3 When privilege changes take effect.

The most significant differences between the ANSI SQL and MySQL versions of the GRANT command are as follows:

• In MySQL, privileges are assigned to the combination of username + remote computer, not just the username.
• ANSI SQL does not have global or database-level privileges, and ANSI SQL does not support all MySQL privilege types. MySQL, on the other hand, does not support the ANSI SQL TRIGGER, EXECUTE, or UNDER privileges.
• The ANSI SQL privilege structure is hierarchical. If you delete a user, all privileges assigned to that user are revoked. In MySQL, assigned privileges are not automatically revoked; you must remove them yourself if necessary.
• In MySQL, a user can INSERT a table if they have INSERT privilege on only a few columns in that table. Columns that do not have the INSERT privilege will be set to their default values. ANSI SQL requires INSERT privilege on all columns.
• When you drop a table in ANSI SQL, all privileges for that table will be revoked. If you revoke a privilege in ANSI SQL, all privileges that were assigned based on that privilege are also revoked. In MySQL, privileges can only be removed using the REVOKE command or by changing the MySQL privilege assignment tables.

For a description of using REQUIRE, see See section 4.3.9 Using Secure Connections.

User Comments

Posted by Frank Wortner

[Delete] [Edit]

I had no problems with ld. DEC (Compaq) might
have fixed ld in a patch kit. You might want to
install the latest patch kit for your Digital Unix
(Tru64 Unix) before building MySQL. Patch kits
are available at
href=http://ftp.support.compaq.com/public/unix/ >
http://ftp.support.compaq.com/public/unix/

Posted by on Saturday February 16 2002, @10:21pm

[Delete] [Edit]

For source installations, these instructions refer to the directory structure presuming "usr/local" was used (default) with configure. But the preceding page"s instructions (for compilation/installation) suggest you use:

./configure --prefix=/usr/local/mysql

To be consistent (and this is causing me some hassles with Perl, so it"s not purely semantic), the instructions on this page should presume /usr/local/mysql was specified as the installation directory with configure.

Posted by Linda Wright on Saturday February 16 2002, @10:21pm

[Delete] [Edit]

This is probably the most important and least
appreciated sections of all of the mySQL
documentation for first time mySQL users. IMHO,
reading this page in conjunction with
http://www.mysql.com/doc/P/r/Privileges.html is a
must for planning anyone secure database systems
of any real sophistication.

Posted by Christopher Raymond on Saturday February 16 2002, @10:21pm

[Delete] [Edit]

I am trying to install MySQL under OS X Public Beta. When I run the mysql_install_db script, I get an error message:

Dyld: ./bin/mysqld can"t open library: /usr/lib/libpthread.A.dylib (No such file or directory, errno = 2)
Installation of grant tables failed!

I am assuming that the script is looking for a directory that doesn't exist because Apple has a little bit different directory naming structure. Maybe this script needs to be modified for the OS X distribution.

Can anyone help?

Posted by Mark Zieg on Saturday February 16 2002, @10:21pm

[Delete] [Edit]

It would be nice if there was an option to log connections, but not queries.

Posted by Bennett Haselton on Saturday February 16 2002, @10:21pm

[Delete] [Edit]

If you"re logged on as the mysql root user, without a current database selected, and you try to
grant all privileges to a user with the command:

GRANT ALL PRIVILEGES ON * TO bhaselto

Then the RELOAD, SHUTDOWN, PROCESS, FILE and GRANT will not be granted, as can be
verified by checking the "user" table of the "mysql" database. (This is presumably by design,
since these privileges can make a user "too powerful".)

Posted by DC Hill on Saturday February 16 2002, @10:21pm

[Delete] [Edit]

NOTE: If you have granted privileges to a user on a particular database, or at any lower level than that, invoking "REVOKE ALL ON *.* FROM ;" will NOT revoke privileges at those levels. The *.* in the above statement means "global", not "all (individual) tables on all (individual) databases. That statment will ONLY revoke global privileges, as stored in the mysql.user table. You MUST revoke any more specific privileges in the same manner as they were granted, if you wish them to be removed from the privilege tables. (i.e. - GRANT ALL ON foo.* TO ; => REVOKE ALL ON foo.* FROM ;) I hope this saves some of you a little time and frustration.

Posted by Cris Perdue on Saturday February 16 2002, @10:21pm

[Delete] [Edit]

"If you have the process privilege, you can see
all threads.
Otherwise, you can see only your own threads."

Posted by FreeBSD Forums on Saturday February 16 2002, @10:21pm

[Delete] [Edit]

You can use phpMyAdmin web based tool to do a lot
of mySQL admin functions. href="http://www.freebsdforums.org"
>FreeBSD forums

Posted by on Monday February 25 2002, @6:03am

[Delete] [Edit]

Verified on MySQL 3.23.36 on Red Hat Linux 7.1:
Note that if you type
use a_c;
grant select on * to ;
you will be given access to any
database matching "a_c" where the underscore is a
wildcard. (Rarely a problem, I suppose).
Rectify with
update mysql.db set db="a\_c" where db="a_c";

Posted by jan behrens on Tuesday July 9 2002, @1:31am

[Delete] [Edit]

the aformentioned bug from DAN ELIN in x.x.41 is
apperently still valid in x.x.51,i cannot log in to a
database after GRANTing privileges and given a
password to a new user(yes, i flushed
privileges).............only root access is possible

Posted by Dan Egli on Thursday April 4 2002, @8:33pm

[Delete] [Edit]

There seems to be a bug in 3.23.41 using Grant.
Only root can access the mysql database, even
after using Grant to grant privs on whatever
database/table/column/ect.. you always get
permission denied, regardless.

Posted by Lars Aronsson on Saturday June 8 2002, @11:16am

["%". When I try to delete them I"m told certian database, but no global privileges, the CREATE TEMPORARY TABLE privilege on that database is denied. You have to give global CREATE __and__ global CREATE TEMPORARY TABLES to the user. IOW: GRANT CREATE, CREATE TEMPORARY TABLES ON *.* TO ; Needless to say, this affects security grately. Posted by on Sunday August 25 2002, @9:17am [Delete] [Edit] Temporary files are a great idea but even with Create and Create Temporary File rights in the user (global rights) file it still doesn't work. This appears to be badly designed. Posted by Brad Bulger on Monday September 2 2002, @4:09am [Delete] [Edit] It should be noted that WITH GRANT OPTION only allows the user to pass on privileges to users who already exist. The automagical creation of user records does not apply - you get an error saying that the user with the GRANT OPTION privilege does don't have access to the "mysql" database. This is probably a good thing, but it needs to be documented. Posted by Michael Babcock on Friday November 8 2002, @1:00pm [Delete] [Edit] SHOW MASTER STATUS requires PROCESS privileges. Other such odd combinations should be documented. Posted by Dee Kintaudi on Thursday November 21 2002, @12:42pm [Delete] [Edit] Okay I got a question and a problem with Mysql and passwords:). I tried to use several of the options and most of them have not worked. However one soloution did work and I tested it out twice and it was solid. Of course I lost the little piece of paper I wrote it out on and I can"t seem to find this soloution anywhere, as if it did not exist or maybe I imagined it. The soloution that worked for me, before I lost the little slip of paper I wrote it down on goes something like this..... Insert into user root Password "my password" and then something with "Y", "Y", "Y", (about a dozen or 15 times or so) However, I can't find this soloution anywhere can someone help me out here? I think it would be so nice if they just put this throughout their documentation instead of trying to hide it. I think this would solve many problems. Just put password = "Y", "Y", "Y", its like them ashamed of it or something. Posted by AJIT DIXIT on Monday November 25 2002, @6:56am [Delete] [Edit] When I work on multi-table update with root user it works fine When I work with non-root user I get error Sql: update Stockists, areas set a_nm = aname where acd = area

In this chapter, you will learn how to work with privileges. As discussed in Chapter 2, SQL is typically used in environments that require user recognition and distinction between different users of systems. In general, database administrators themselves create users and give them privileges. On the other hand, users who create tables themselves have rights to manage these tables. Privileges are what determine whether a specified user can execute a given command. There are several types of privileges corresponding to several types of operations. Privileges are granted and revoked using two SQL commands: - GRANT and REVOKE. This chapter will show you how these commands are used.

USERS

Each user in a SQL environment has a special identification name or number. The terminology is different everywhere, but we have chosen (following ANSI) to refer to it or the number as the Access Identifier (ID). A command sent to the database is associated with a specific user; or otherwise, a special Access Identifier. As it relates to a SQL database, the permission ID is the username, and SQL can use the special keyword USER, which refers to the Access ID associated with the current command. The command is interpreted and allowed (or denied) based on information associated with the Access ID of the user issuing the command.

REGISTRATION

In systems with numerous users, there is some kind of login procedure that the user must complete in order to gain access to the computer system. This procedure determines which access ID will be associated with the current user. Typically, each person using the database must have their own access ID and upon registration becomes a valid user. However, often users with many tasks can be registered under different access IDs, or, conversely, one access ID can be used by several users. From a SQL perspective there is no difference between these two cases; it treats the user simply as their access ID. The SQL database may use its own login procedure, or it may allow another program, such as operating system(the main program that runs on your computer), process the registration file and obtain the access ID from this program. One way or another, SQL will have an access ID to associate with your actions, and the USER keyword will be relevant to you.

PROVIDING PRIVILEGES

Each user in a SQL database has a set of privileges. This is what the user is allowed to do (perhaps it is a log file, which can be considered a minimum privilege). These privileges can change over time - new ones are added, old ones are removed. Some of these privileges are defined in ANSI SQL, but there are additional privileges that are also required. SQL privileges as defined by ANSI are not sufficient in most real-life situations. On the other hand, the types of privileges that are needed may vary with the type of system you are using - for which ANSI makes no recommendations. Privileges that are not part of the SQL standard may use syntax that is similar and not entirely consistent with the standard.

STANDARD PRIVILEGES

SQL privileges defined by ANSI are object privileges. This means that the user has the privilege to execute a given command only on a specific object in the database. Obviously, privileges must distinguish between these objects, but a privilege system based solely on an object's privileges cannot address everything that SQL needs, as we will see later in this chapter. An object's privileges are associated with both users and tables. That is, the privilege is given to a specific user in a specified table, or underlying table or view. You must remember that the user who created the table (of any kind) is the owner of this table.

This means that the user has all the privileges in this table and can transfer privileges to other users in this table. Privileges that can be assigned to a user:

SELECT A user with this privilege can run queries on the table.

INSERT A user with this privilege can issue an INSERT command on a table.

UPDATE A user with this privilege can issue an UPDATE command on a table. You can restrict this privilege to specific table columns.

DELETE A user with this privilege can issue a DELETE command on a table.

REFERENCES A user with this privilege can define a foreign key that uses one or more columns of this table as a parent key. You can restrict this privilege to certain columns. (See Chapter 19 for details regarding foreign key and parent key.)

In addition, you will encounter non-standard object privileges, such as INDEX, which gives the right to create an index on a table, SYNONYM, which gives the right to create a synonym for an object, which will be explained in Chapter 23, and ALTER, which gives the right to execute ALTER TABLE command on a table. The SQL engine assigns these privileges to users using the GRANT command.

GRANT TEAM

Let's assume that the user Diane has a Customers table and wants to allow the user Adrian to query it. Diane should then enter the following command:

GRANT INSERT ON Salespeople TO Diane;

Now Adrian can run queries against the Customers table. Without other privileges, he can only select values; but cannot perform any action that would affect the values ​​in the Customers table (including using the Customers table as the parent table of the foreign key, which limits the changes that can be made to the value in the Customers table).

When SQL receives a GRANT command, it checks the privileges of the user issuing the command to determine whether the GRANT command is valid. Adrian cannot issue this command on his own. It also cannot grant SELECT permission to another user: the table still belongs to Diane (we'll show later how Diane can grant Adrian SELECT permission to other users).

The syntax is the same as for granting other privileges. If Adrian is the owner of the Sellers table, then he can allow Diane to enter rows into it using the following clause

GRANT INSERT ON Salespeople TO Diane; Diane now has the right to place a new seller in the table.

PRIVILEGES GROUPS, USER GROUPS

You should not limit yourself to granting a single privilege to an individual user with the GRANT command. Comma-separated lists of privileges or users are perfectly acceptable. Stephen can provide both SELECT and INSERT in the Order table for Adrian

GRANT SELECT, INSERT ON Orders TO Adrian; or for both Adrian and Diane GRANT SELECT, INSERT ON Orders TO Adrian, Diane;

When privileges and users are listed in this way, the entire list of privileges is granted to all specified users. In strict ANSI interpretation, you cannot grant privileges on many tables at once with a single command, but some implementations can relax this restriction by allowing you to specify multiple tables, separated by commas, so that the entire list of privileges can be granted for all specified tables. .

LIMITING PRIVILEGES ON SPECIFIC COLUMNS

All object privileges use the same syntax, except for the UPDATE and REGERNCES commands, which do not require column names. The UPDATE privilege can be granted like other privileges:

GRANT UPDATE ON Salespeople TO Diane;

This command will allow Diane to change the values ​​in any or all columns of the Vendors table. However, if Adrian wants to restrict Diane from changing, for example, commissions, he can enter

GRANT UPDATE (comm) ON Salespeople TO Diane;

In other words, it simply must specify the specific column on which the UPDATE privilege should be applied, in parentheses after the table name. The names of multiple table columns can be specified in any order, separated by commas:

GRANT UPDATE (city, comm) ON Salespeople TO Diane;

REFERENCES follows the same rule. When you grant the REFERENCES privilege to another user, they will be able to create foreign keys that reference columns in your table as parent keys. Like UPDATE, the REFERENCES privilege can be specified as a list of one or more columns for which that privilege is restricted. For example, Diane could grant Stephen the right to use the Customer table as the parent key table with the following command:

GRANT REFERENCES (cname, cnum) ON Customers TO Stephen; This command gives Stephen the right to use the cnum and cname columns as parent keys to any foreign keys in his tables. Stephen can control how this is done. It can define (cname, cnum) or, in our case, (cnum, cname), as a two-column parent key matched by a foreign key to two columns in one of its own tables. Or it can create separate foreign keys to refer to gender individually, thereby ensuring that Diane has a parent key assigned to it (see Chapter 19).

With no restrictions on foreign key numbers, it must be based on these parent keys, and the parent keys of different foreign keys must be allowed to overlap.

As with the UPDATE privilege, you can exclude a list of columns and thus allow all columns to be used as parent keys. Adrian can grant Diane the right to do this with the following command:

GRANT REFERENCES ON Salespeople TO Diane;

Naturally, the privilege will only be usable on columns that have the restrictions required by the parent keys.

USING ALL AND PUBLIC ARGUMENTS

SQL supports two arguments to the GRANT command that have a special meaning: ALL PRIVILEGES, or simply ALL and PUBLIC. ALL is used instead of privilege names in the GRANT command to grant all privileges in a table. For example, Diane could give Stephen the entire set of privileges in the Customers table with the following command:

GRANT REFERENCES ON Salespeople TO Diane;

(UPDATE and REFERENCES privileges naturally apply to all columns.) Here's another way to say the same thing:

GRANT ALL ON Customers TO Stephen;

PUBLIC is more like a catch-all argument type than a user privilege. When you grant publishing privileges, all users automatically receive them. Most often, this is used for the SELECT privilege on certain underlying tables or views that you want to make available to any user. To allow any user to see the Order table, you could, for example, enter the following:

GRANT SELECT ON ORDERS TO PUBLIC;

Of course, you can grant any or all of the privileges to society, but this is probably not advisable. All privileges except SELECT allow the user to change (or, in the case of REFERENCES, restrict) the contents of the table. Allowing all users to change the content of your tables will cause a problem.

Even if you have a small company and all of your current users are capable of executing modification commands on a given table, it would be better to grant privileges to each user individually than to grant the same privileges to everyone. PUBLIC is not limited to transferring it to current users only. Any New user added to your system will automatically receive all the privileges previously assigned to everyone, so if you want to limit table access to everyone, now or in the future, it is best to grant privileges other than SELECT to individual users.

GRANTING PRIVILEGES USING WITH GRANT OPTION

Sometimes, the creator of a table wants other users to be able to gain privileges on his table. This is typically done in systems where one or more people create several (or all) of the base tables in a database and then delegate responsibility for them to those who will actually work with them. SQL allows you to do this using the WITH GRANT OPTION clause. If Diane wanted Adrian to be able to grant the SELECT privilege on the Customers table to other users, she would grant him the SELECT privilege using the WITH GRANT OPTION clause:

GRANT SELECT ON Customers TO Adrian WITH GRANT OPTION; Adrian then acquired the right to transfer the SELECT privilege to third parties; it can issue the command GRANT SELECT ON Diane.Customers TO Stephen; or even GRANT SELECT ON Diane.Customers TO Stephen WITH GRANT OPTION; A user with a GRANT OPTION on a particular privilege on a given table can, in turn, grant that privilege on the same table, with or without a GRANT OPTION, to any other user. This does not change the ownership of the table itself; as before, the table belongs to its creator. (Therefore, granted users must prefix the owner access ID when referencing these tables. The next chapter will show you this method.) A user using the GRANT OPTION on all privileges for a given table will have full authority in that table.

CANCELLATION OF PRIVILEGES

Just as ANSI provides the CREATE TABLE command to create a table, rather than the DROP TABLE command to get rid of it, the GRANT command allows you to give privileges to users without providing a way to take them back. The need to remove privileges comes down to the REVOKE command, which is actually a standard tool with a fairly clear form of entry. The syntax of the REVOKE command is similar to GRANT, but has the opposite meaning. To remove the INSERT privilege for Adrian in the Order table, you can enter

REVOKE INSERT ON Orders FROM Adrian;

Using lists of privileges and users is allowed here as with GRANT, so you can enter the following command:

REVOKE INSERT, DELETE ON Customers FROM Adrian, Stephen; However, there is some ambiguity here. Who has the right to revoke privileges? When does a user with the right to transfer privileges to others lose that right? Will the users to whom he granted these privileges also lose them? Since this is not a standard feature, there are no authoritative answers to these questions, but the most common approach is this: * Privileges are revoked by the user who granted them, and the revoking will cascade, that is, it will automatically propagate to all users who received the privilege from him .

USING VIEWS TO FILTER PRIVILEGES

You can make privilege actions more precise by using views. Whenever you grant a privilege on a base table to a user, it is automatically propagated to all rows and, when using the possible UPDATE and REFERENCES exceptions, to all columns of the table. By creating a view that references the underlying table and then transfers the privilege to the view rather than the table, you can restrict those privileges to any expressions in the query contained in the view. This greatly improves the basic capabilities of the GRANT command.

WHO CAN MAKE SUBMISSIONS?

To create a view, you must have SELECT privilege on all tables that you reference in the view. If the view is modifiable, any INSERT, UPDATE, and DELETE privileges you have on the base table will automatically be transferred to the view. If you lack modification privileges on the base tables, you won't be able to have them on the views you create, even if those views themselves are modifiable. Because foreign keys are not used in views, the REFERENCES privilege is never used when creating views. All these restrictions are defined by ANSI. Non-standard system privileges (discussed later in this chapter) can also be enabled. In the following sections, we will assume that the creators of the views we discuss have private or appropriate privileges on all underlying tables.

LIMITING SELECT PRIVILEGE ON SPECIFIC COLUMNS

Let's say you want to give the user Claire the ability to see only the snum and sname columns of the Sales table. You can do this by putting the names of these columns in the view

CREATE VIEW Clairesview AS SELECT snum, sname FROM Salespeople; and grant Claire the SELECT privilege on the view rather than on the Sellers table itself: GRANT SELECT On Clairesview to Claire; You can create privileges specifically for columns, like using other privileges, but for an INSERT command this will insert default values, and for a DELETE command, the column constraint will have no effect. The REFERENCES and UPDATE privileges can, of course, make columns specific without resorting to a view.

LIMITING PRIVILEGES FOR SPECIFIC STRINGS Typically, a more useful way to filter privileges with views is to use the view to make the privilege apply only to certain rows. You do this naturally by using a predicate on the view that will determine which rows are included. To give the user Adrian the UPDATE privilege on the Customers table for all customers located in London, you could create a view like this:

CREATE VIEW Londoncust AS SELECT * FROM Customers WHERE city = "London" WITH CHECK OPTION; You must then grant the UPDATE privilege on this table to Adrian: GRANT UPDATE ON Londoncust TO Adrian; This is the difference between the privilege for certain rows and the UPDATE privilege for certain columns, which applies to all columns of the Customers table, but not to rows, among which rows with a city gender value other than London will not be taken into account. The WITH CHECK OPTION clause prevents Adrian from changing the city gender value to anything other than London. PROVIDING ACCESS ONLY TO EXTRACTED DATA Another possibility is to offer users access to the data that has already been retrieved, rather than the actual values ​​in the table. Aggregate functions can be very convenient in using this method. You can create a view that gives the count, average, and total for orders for each day of the order: CREATE VIEW Datetotals AS SELECT odate, COUNT (*), SUM (amt), AVG (amt) FROM Orders GROUP BY odate; You now grant the user Diane the SELECT privilege on the Datetotals view: GRANT SELECT ON Datetotals TO Diane; USING REPRESENTATIONS AS AN ALTERNATIVE TO CONSTRAINTS One of the latest applications in the series, described in Chapter 18, is the use of views with WITH CHECK OPTION as an alternative to constraints. Let's say you wanted to make sure that all city gender values ​​in the Vendors table were in one of the cities where your company currently has an office. You can set a CHECK constraint directly on the city column, but it may become difficult to change it later if your company opens other departments there, for example. Alternatively, you can create a view that excludes invalid city values: CREATE VIEW Curcities AS SELECT * FROM Salespeople WHERE city IN ("London", "Rome", "San Jose", "Berlin") WITH CHECK OPTION; Now, instead of granting users modification privileges in the Merchants table, you can grant them in the Curcities view. The advantage of this approach is that if you need to make a change, you can delete that view, create a new one, and grant privileges to users in that new view, which is easier than changing restrictions. The disadvantage is that the owner of the Sales table must also use this view if he does not want his own commands to be rejected. On the other hand, this approach allows the table owner and anyone else to gain modification privileges on the table itself, rather than on the view, to make exceptions to constraints.

This is often desirable, but not feasible if you are using constraints on the base table. Unfortunately, these exceptions will not be visible in the view. If you choose this approach, you'll want to create a second view containing only exceptions: CREATE VIEW Othercities AS SELECT * FROM Salespeople WHERE city NOT IN ("London", "Rome", "San Jose", "Berlin") WITH CHECK OPTION; You should choose to give users only the SELECT privilege on this view so that they can see the excluded rows but cannot put invalid city values ​​into the underlying table. In fact, users could query both views in a union and see all the rows at once.

OTHER TYPES OF PRIVILEGES

Of course, you want to know who has the right to create the table first. This privilege area is not ANSI, but cannot be ignored. All standard ANSI privileges are derived from this privilege; privileges of table creators that can transfer object privileges. If all your users create base tables in the system with different sizes this will lead to redundancy in them and to inefficiency of the system. Other questions also attract attention:

Who has the right to change, delete, or limit tables?

Should the rights to create base tables be different from the rights to create views?

Should there be a superuser - a user who is responsible for maintaining the database and therefore has most, or all, privileges that are not granted individually?

Until ANSI is involved and SQL is used in a variety of environments, we cannot give a definitive answer to these questions. We propose to consider here a portion of the most general conclusions.

Privileges that are not defined in terms of special data objects are called system privileges, or database rights. At a basic level, these will likely include the right to create data objects, likely other than base tables (usually created by a few users) and views (usually created by a majority of users). System privileges for creating views should be in addition to, and not in lieu of, the object privileges that ANSI requires of view creators (described earlier in this chapter). In addition, in a system of any size there are always some types of superusers - users who automatically have most or all privileges - and who can transfer their superuser status to someone else through a privilege or group of privileges. Database Administrator, or DBA, is the term most commonly used for such a superuser and the privileges it has.

TYPICAL SYSTEM PRIVILEGES

In a general approach, there are three basic system privileges: - CONNECT, - RESOURCE, and - DBA (Database Administrator). In simpler terms, CONNECT can be said to consist of the right to register and the right to create views and synonyms (see Chapter 23) if the object's privileges are passed. RESOURCE consists of the right to create base tables. DBA is a superuser privilege that gives the user high authority in the database. One or more users with database administrator functionality may have this privilege. Some systems also have a special user, sometimes called SYSADM or SYS (System Database Administrator), who has the highest authority; it is special to them, and not just a user with special DBA privilege. In fact, only one person is allowed to register with the name SYSADM, which is their access ID. The distinction is quite subtle and functions differently in different systems. For our purposes, we will refer to a highly privileged user who develops and manages a database with DBA privileges, understanding that in fact these privileges are the same privilege. The GRANT command, in modified form, is usable with object privileges as well as system privileges. To begin with, transfer of rights can be done using a DBA. For example, a DBA could grant table creation privilege to Rodriguez as follows: GRANT RESOURCE TO Rodriguez;

CREATE AND DELETE USERS

Naturally, the question arises: where does a user named Rodriguez come from? How to determine its clearance ID? In most implementations, the DBA creates the user by automatically granting him the CONNECT privilege. In this case, an IDENTIFIED BY clause is usually added to indicate the password. (If not, the operating system must determine whether you can log into the database with the given access ID.) The DBA could, for example, issue GRANT CONNECT TO Thelonius IDENTIFIED BY Redwagon; which will create a user named Thelonius, give him the right to register, and assign him the password Redwagon, all in one sentence. Since Thelonious is already an authenticated user, he or the DBA can use this same command to change Redwagon's password. Although this is convenient, there are still limitations in this approach. This is the impossibility of having a user who could not register, at least temporarily. If you want to prevent a user from logging in, you must use the CONNECT privilege on REVOKE, which "removes" that user. Some implementations allow you to create and delete users, regardless of their login privileges. When you grant the CONNECT privilege to a user, you create that user. Moreover, to do this yourself, you must have DBA privilege. If this user will be creating base tables (not just views), he must also be granted the RESOURCE privilege. But this immediately gives rise to another problem. If you attempt to remove the CONNECT privilege of a user who has tables created by him, the command will be rejected because it would leave the table without an owner, which is not allowed. You must first drop all tables created by this user before removing his CONNECT privilege. If these tables are not empty, then you will probably want to pass their data to other tables using the INSERT command, which uses a query. You do not need to remove the RESOURSE privilege separately; It is enough to delete CONNECT to delete the user. While the above is a fairly standard approach to system privileges, it also has significant limitations. Alternative approaches have emerged that are more specifically defined and control system privileges more accurately.

These conclusions take us somewhat beyond the SQL standard as currently defined, and, in some implementations, may go beyond the SQL standard entirely. These things probably won't concern you too much unless you're a DBA or user high level. Regular users simply need to be familiar with the system's privileges in principle, consulting their documentation only in the event of special messages.

SUMMARY

Privileges give you the ability to see SQL from a new perspective when SQL performs actions through special users on a special database system. The GRANT command itself is quite simple: with its help, you grant certain privileges of an object to one or more users. If you grant the WITH GRANT OPTION privilege to a user, that user can in turn grant that privilege to others. You now understand the hints about using privileges on views—to enhance privileges in base tables, or as an alternative to constraints—and some of the advantages and disadvantages of this approach. System privileges that are required but not within the scope of the SQL standard have been discussed in their most general form and so you will learn them through practice. Chapter 23 will continue the discussion about inference in SQL, such as saving or restoring changes, creating your own names for tables owned by other people, and understanding what happens when different users try to access the same object at the same time.

WORKING WITH SQL

1. Give Janet the right to change the customer's rating.

2. Give Stephan the right to grant other users the right to make queries in the Orders table.

3. Remove the INSERT privilege in the Vendors table from Claire and from all users to whom it was granted.

4. Give Jerry the right to insert or modify the Customers table while retaining his ability to evaluate values ​​in the range of 100 to 500.

5. Allow Janet to query the Customer table, but prevent her from decrementing ratings in the same Customer table.

The SQL Server platform uses the REVOKE statement as a way to revoke the permission settings assigned to a given user. This point is important because SQL Server supports an additional DENY statement that explicitly denies a user access to a specified resource. In SQL Server, the REVOKE statement can be used to revoke privileges assigned to a user using the GRANT statement. If you want to explicitly deny a user a certain privilege, you should use the DENY statement.

The SQL Server platform does not support the ANSI HIERARCHY OPTION and ADMIN OPTION clauses. Although the ADMIN OPTION clause is not supported, the SQL Server version of the REVOKE command has two administrative privileges (CREATE and BACKUP). The instruction syntax is as follows.

REVOKE ([object_privilege] [, ...] | [system_privilege]) [(column [, ...])]]| (TO | FROM) (recipient_name [, …] | role [, …] | PUBLIC | GUEST) ]

GRANT OPTION FOR

The user is deprived of the right to assign specific privileges to other users.

object privilege

Access rights to various instructions, which can be combined in any order, are revoked.

ALL

All privileges assigned in currently specified users and/or for specified database objects. This suggestion is discouraged because it promotes programming ambiguity.

(SELECT | INSERT DELETE UPDATE)

The specified user is denied the specified access privilege on the specified object (for example, a table or view). To revoke column-level privileges, use a list of columns enclosed in parentheses.

REFERENCES

The right to create and delete foreign key constraints that reference a database object as a parent object is revoked.

The user's right to create or delete a rule in a table or view is revoked.

The right to execute a stored procedure, user-defined function, or extended stored procedure is revoked.

system privilege

The right to execute the following statements is revoked: CREATE DATABASE, CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TABLE, CREATE VIEW, BACKUP DATABASE, and BACKUP LOG.

ON [object] [(column [, ...])]

The user's access right to the specified object is revoked. If the object is a table or view, you can revoke access privileges on individual columns. You can revoke SELECT, INSERT, UPDATE, DELETE, and REFERENCES privileges on a table or view. On table or view columns, you can only revoke SELECT and UPDATE privileges. You can revoke EXECUTE privileges in a stored procedure, user-defined function, or extended stored procedure.

[TO | FROM] recipient name | role | PUBLIC | GUEST

Specifies the users or roles that lose the specified privilege. You can use the PUBLIC keyword to revoke privileges assigned to the PUBLIC role (which includes all users). You can list multiple recipients, separating their names with commas. Also supported in SQL Server Account GUEST, which is used by all users who do not have their own entry in the database.

The privileges of users who received their rights through the WITH GRANT OPTION clause are removed. This clause is required when using the GRANT OPTION FOR clause.

AS (group_name role_name)

The rights under which the privilege is revoked are indicated. In some cases, a user may temporarily need the rights of a specific group to override the specified privileges. In this case, you can use the AS clause to obtain such rights.

The two forms of the REVOKE statement, REVOKE object privilege and REVOKE system_privilege, are mutually exclusive. Don't try to do both operations in one statement. The key syntactic difference between the two is that you should not use the ON clause when removing system privileges. For example, to remove a system privilege, you can use the following command.

REVOKE CREATE DATABASE, BACKUP DATABASE FROM dylan, katie

If privileges were assigned to a user using the WITH GRANT OPTION clause, then these privileges should be revoked using the simultaneous use of two clauses - WITH GRANT OPTION and CASCADE. For example:

REVOKE GRANT OPTION FOR SELECT, INSERT, UPDATE, DELETE ON titles TO editors CASCADE GO

The REVOKE command can only be used on the current database. Accordingly, the ANSI standard options CURRENTJJSER and CURRENTROLE are always implicitly assumed. The REVOKE statement is also used to cancel all DENY options.

The SQL Server platform also supports an additional DENY statement. The syntax of the DENY statement is identical to the syntax of the REVOKE statement. However, in essence, they differ in that REVOKE neutralizes the user's privileges, while DENY explicitly prohibits them. Use the DENY statement to deny a user or role access to a privilege, even if the privilege is granted explicitly or through role assignment.

The REVOKE statement must be used to remove previously granted or DENY privileges. For example, user kelly went on extended maternity leave. During this time, her access to the employee table was denied. She returned and we re-allowed privileges.

DENY ALL ON employee TO Kelly GO

REVOKE ALL ON employees TO Kelly GO

In this example, the REVOKE command does not remove her privileges; it overrides the DENY command.

Creating a user does not in itself give the user any rights to access database objects.

Permissions are granted by the GRANT command. It should be remembered that the user issuing the GRANT command can transfer or, if you prefer, delegate to other users only those rights that he himself has.

GRANT sets rights on database objects to users, roles, or other database objects. When an object is created, only its creator has rights to it, and only he can grant rights to other users or objects.

To access a table or view, the user or object needs SELECT, INSERT, UPDATE, DELETE, or REFERENCES rights. All rights can be given with the ALL option.

To call a procedure in an application, the user must have EXECUTE rights.

Users can obtain permission to grant rights to other users by transferring rights according to the list , which is specified by the WITH GRANT OPTION option. The user can grant to others only those rights that he himself has.

Permissions can be given to all users by using the PUBLIC option in place of the list of usernames. Specifying the PUBLIC option affects only users, not database objects.

The list of rights is given in table. 8.5.

Table 8.5. List of rights

Rights can be revoked by the user who granted them using the REVOKE command. If rights were issued using ALL, then they can only be liquidated in ALL mode; if rights were issued using PUBLIC, then they can only be liquidated in PUBLIC mode.

Syntax:

GRANT (all /PRIVILEGES] / LJST_ ) ON (TABLE ]

(tablename/viewname)

TO(