Setting permissions using the grant command. SQL GRANT Commands Revoke

REVOKE - revoke access rights

Syntax

REVOKE [ GRANT OPTION FOR ] ( ( SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER ) [, ...] | ALL [ PRIVILEGES ] ) ON ( [ TABLE ] table_name[, ...] | ALL TABLES IN SCHEMA schema_name[, ...] ) FROM ( [ GROUP ] role_name| PUBLIC ) [, ...] [ CASCADE | RESTRICT ] REVOKE [ GRANT OPTION FOR ] ( ( SELECT | INSERT | UPDATE | REFERENCES ) ( column_name[, ...]) [, ...] | ALL [PRIVILEGES] ( column_name[, ...]) ) ON [ TABLE ] table_name[, ...] FROM ( [ GROUP ] role_name| PUBLIC ) [, ...] [ CASCADE | RESTRICT ] REVOKE [ GRANT OPTION FOR ] ( ( USAGE | SELECT | UPDATE ) [, ...] | ALL [ PRIVILEGES ] ) ON ( SEQUENCE sequence_name[, ...] | ALL SEQUENCES IN SCHEMA schema_name[, ...] ) FROM ( [ GROUP ] role_name| PUBLIC ) [, ...] [ CASCADE | RESTRICT ] REVOKE [ GRANT OPTION FOR ] ( ( CREATE | CONNECT | TEMPORARY | TEMP ) [, ...] | ALL [ PRIVILEGES ] ) ON DATABASE database_name[, ...] FROM ( [ GROUP ] role_name| PUBLIC ) [, ...] [ CASCADE | RESTRICT ] REVOKE [ GRANT OPTION FOR ] ( USAGE | ALL [ PRIVILEGES ] ) ON DOMAIN domain_name[, ...] FROM ( [ GROUP ] role_name| PUBLIC ) [, ...] [ CASCADE | RESTRICT ] REVOKE [ GRANT OPTION FOR ] ( USAGE | ALL [ PRIVILEGES ] ) ON FOREIGN DATA WRAPPER third-party_data_wrapper_name[, ...] FROM ( [ GROUP ] role_name| PUBLIC ) [, ...] [ CASCADE | RESTRICT ] REVOKE [ GRANT OPTION FOR ] ( USAGE | ALL [ PRIVILEGES ] ) ON FOREIGN SERVER server_name[, ...] FROM ( [ GROUP ] role_name| PUBLIC ) [, ...] [ CASCADE | RESTRICT ] REVOKE [ GRANT OPTION FOR ] ( EXECUTE | ALL [ PRIVILEGES ] ) ON ( FUNCTION function_name ([ [ argument_mode ] [ argument_name ] argument_type[, ...] ]) [, ...] | ALL FUNCTIONS IN SCHEMA schema_name[, ...] ) FROM ( [ GROUP ] role_name| PUBLIC ) [, ...] [ CASCADE | RESTRICT ] REVOKE [ GRANT OPTION FOR ] ( USAGE | ALL [ PRIVILEGES ] ) ON LANGUAGE language_name[, ...] FROM ( [ GROUP ] role_name| PUBLIC ) [, ...] [ CASCADE | RESTRICT ] REVOKE [ GRANT OPTION FOR ] ( ( SELECT | UPDATE ) [, ...] | ALL [ PRIVILEGES ] ) ON LARGE OBJECT oid_BO[, ...] FROM ( [ GROUP ] role_name| PUBLIC ) [, ...] [ CASCADE | RESTRICT ] REVOKE [ GRANT OPTION FOR ] ( ( CREATE | USAGE ) [, ...] | ALL [ PRIVILEGES ] ) ON SCHEMA schema_name[, ...] FROM ( [ GROUP ] role_name| PUBLIC ) [, ...] [ CASCADE | RESTRICT ] REVOKE [ GRANT OPTION FOR ] ( CREATE | ALL [ PRIVILEGES ] ) ON TABLESPACE table_space[, ...] FROM ( [ GROUP ] role_name| PUBLIC ) [, ...] [ CASCADE | RESTRICT ] REVOKE [ GRANT OPTION FOR ] ( USAGE | ALL [ PRIVILEGES ] ) ON TYPE type_name[, ...] FROM ( [ GROUP ] role_name| PUBLIC ) [, ...] [ CASCADE | RESTRICT ] REVOKE [ ADMIN OPTION FOR ] role_name[, ...] FROM role_name[, ...] [ CASCADE | RESTRICT]

Description

The REVOKE command revokes one or more roles that were previously assigned rights. The PUBLIC keyword denotes an implicitly defined group of all roles.

Note that any given role receives the sum of the rights given directly to it, the rights given to any role in which it is included, and the rights given to the PUBLIC group. Therefore, for example, depriving PUBLIC of the SELECT right will not necessarily mean that all roles will lose the SELECT right for a given object: it will remain with those roles to which it was granted directly or indirectly through another role. Likewise, revoking the SELECT right from a user may not affect that user's ability to use the SELECT right if that right is granted to the PUBLIC group or other role to which he or she is a member.

If GRANT OPTION FOR is specified, only the right to transfer the right is revoked, not the right itself. Without this instruction, both the right and the right to dispose of it are revoked.

If a user has a transferable right and has given it to other users, the latter right is considered dependent. When the first user is deprived of the right itself or the transfer right and there are dependent rights, those dependent rights are also revoked if CASCADE is additionally specified; otherwise the operation fails. This recursive revocation affects only rights acquired through a chain of users that can be traced back to the user who is the subject of the REVOKE command. Thus, users may end up retaining this right if it was also acquired through other users.

When an access right to a table is revoked, the corresponding rights for each table column (if such rights are set) are automatically revoked along with it. On the other hand, if a role has been given rights for a table, revoking the same rights at the individual column level has no effect.

When a user is deprived of membership in a role, the GRANT OPTION changes to ADMIN OPTION , otherwise the behavior of the command is similar. Note also that this form of the command does not accept the redundant GROUP word.

Notes

To view the permissions assigned to existing tables and columns, you can use the \dp command in psql. Its output format is discussed in the description of GRANT. For other, non-tabular objects, other \d commands are provided, which can also show the rights assigned to them.

A user can only revoke rights that he has given to another directly. If, for example, user A gave a transferable right to user B, and user B in turn gave that right to user C, then user A would not be able to revoke that right directly from C. Instead, user A could revoke the transferable right from user B and use the CASCADE parameter so that user C is deprived of this right along the chain. Or, for example, if both A and B gave the same right to C, then A will be able to revoke the right that he gave, but not user B, so that as a result C will still have this right.

If someone other than the owner of the object tries to revoke access to an object (using REVOKE), the command will fail if the user does not have any rights to that object. If the user has some rights, the command will be executed, but the user will be able to revoke only those rights that were given to him with the right to dispose of them. The REVOKE ALL PRIVILEGES forms will issue a warning if the user does not have such rights at all, while other forms will issue a warning if the user does not have the right to control the exact rights specified in the command. (In principle, these statements apply to the owner of an object, but he is allowed to control all rights, so such situations are not possible.)

If a superuser executes a GRANT or REVOKE command, the command is executed as if it were executed by the owner of the affected object. Since all rights ultimately come from the object's owner (perhaps indirectly through a chain or through a disposition right), a superuser can revoke all rights, but this may require CASCADE mode as described above.

REVOKE can also be performed by a role that is not the owner of the specified object, but is a member of the owning role, or a member of a role that has WITH GRANT OPTION rights on that object. In this case, the command will be executed as if it were executed by the containing role that actually owns the object or has WITH GRANT OPTION rights. For example, if table t1 is owned by role g1, of which role u1 is a member, then u1 can revoke rights to use t1 that are written as given by role g1. These may include rights granted by the role u1, as well as by other members of the role g1.

If the role executing the REVOKE command has been granted the specified rights indirectly through multiple role membership paths, which role will be selected to execute the command is undetermined. In such cases, it is recommended to use the SET ROLE command and switch to the role that you want to see as performing REVOKE. If this is not done, rights may not be revoked as planned, or rights may not be revoked at all.

Examples

Removing the public group from adding data to the films table:

REVOKE INSERT ON films FROM PUBLIC;

Removing the user manuel from all rights to view kinds:

REVOKE ALL PRIVILEGES ON kinds FROM manuel;

Notice what this actually means « take away all the rights that I gave» .

Removing joe from the admins role:

REVOKE admins FROM joe;

Compatibility

The compatibility notes given for the GRANT command also apply to REVOKE. The standard requires the RESTRICT or CASCADE keyword to be specified, but PostgreSQL assumes RESTRICT by default.

By default, the account does not have any rights in the Oracle database. It is not even possible to create connections without assigned rights. And even after receiving connection rights, the account cannot do anything useful (or dangerous) without obtaining the appropriate rights. Rights are assigned using the GRANT command and removed using the REVOKE command. Additional command directives are used to allow an account to share the rights it has with other users. By default, only administrator accounts (SYS and SYSTEM) have assignment rights. A user who assigns rights to another user is called grantor while the recipient of rights is called grantee. Rights are divided into two groups: system rights, which roughly speaking allow the user to perform actions affecting the data dictionary, and rights over objects, which allow the user to perform actions affecting the data.

System rights

In total, about two hundred system rights are available. Most of them affect actions affecting the data (such as creating tables or users). The rest affect the instance or database (creating table spaces, changing database parameters and creating sessions). The most commonly used permissions are

• CREATESESSION – connection rights. Without these rights you won't even be able to connect to the database
• RESTRICTEDSESSION – If the database was launched with the STARTUPRESTRICT directive or the ALTERSYSTEMENABLERESTRICTEDSESSION command was used, then only users with these rights will be able to connect to the database
• ALTERDATABASE – allows you to execute commands that affect physical structures
• ALTERSYSTEM – allows changing instance parameters and memory structure
• CREATETABLESPACE – together with ALTERTABLESPACE and DROPTABLESPACE allow the user to manage tablespaces
• CREATETABLE – allows gratee to create tables in its schema; includes the ability to create, modify, and drop tables, execute DML and select commands, and manage indexes
• GRANTANYOBJECTPRIVILEGE – allows grantee to manage the rights of objects that do not belong to it, but does not give rights to itself
• CREATEANYTABLE – grantee can create tables that belong to other accounts
• DROPANYTABLE – grantee allows you to delete tables that belong to other accounts
• INSERTANYTABLE, UPDATEANYTABLE, DELETEANYTABLE – gives grantee the right to execute DML commands on objects that do not belong to it
• SELECTANYTABLE – Gives grantee permission to perform SELECT on any tables.

Syntax for assigning rights

GRANT privilege [,privilege…] TO username;

After creating an account, rights frequently used by users who are involved in the development of the application are usually assigned.

grant create session, alter session,

create table, create view, create synonym, create cluster,

create database link, create sequence,

create trigger, create type, create procedure, create operator

to username;

These rights allow you to connect and configure a session, create objects, and store PL/SQL objects. Objects can only be created in the account schema; no rights to schemes of other accounts. Also, object creation is limited by tablespace limits.

Another option for assigning rights would be to assign grantee access to reassign rights to other accounts. For example

connect system/oracle;

grant create table to scott with admin option;

connect scott/tiger;

grant create table to jon;

Running these commands will allow SCOTT to create tables in its schema and issue the GRANT command. SCOTT gives the user JON rights to create tables - but JON will only be able to create tables in the JON schema. Figure 6-5 shows the user rights in Database Control; the same information can be obtained by querying the DBA_SYS_PRIVS view.

If system permissions are revoked, all actions you performed while you had permissions remain in effect. If you had rights with ADMIN OPTION, then all users to whom you assigned rights will retain rights, despite the fact that your rights have been revoked. There is no record of who exactly assigned system privileges, so it is impossible to take away CASCADE rights as shown in Figure 6-6

Revocation of a system privilege will not cascade (unlike

revocation of an object privilege).

ANY rights give access to all objects in the database. Thus

grant select any table to scott

Allow the SCOTT account to perform a SELECT query on all tables in all database schemas. This assignment of rights is considered bad manners and ANY rights are assigned only to DBAs.

In fact, ANY is not as dangerous now as with earlier releases. It's no longer

includes tables in the SYS schema, so the data dictionary is still protected. But

ANY should still be used with extreme caution, as it removes all protection

from user tables.

Object rights

Object rights give access to execute DML and SELECT commands on corresponding objects and execute PL/SQL objects. These rights do not exist for objects in the account schema; if a user has CREATE TABLE system rights, this means that he can perform SELECT and DML queries on the tables he created without additional rights.

The ANY privileges, that grant permissions against objects in

every user account in the database, are not object privileges-they are

system privileges.

Object rights apply to different groups of objects

Command Syntax

GRANT privilege ON object TO username ;

For example

You can use ALL to apply rights to all operations, or you can use a specific table or view column reference.

grant select on store.orders to scott;

grant update (order_status) on store.orders to scott;

grant all on store.regions to scott;

These commands will allow the SCOTT account to perform a SELECT query on all columns of the ORDERS table in the STORE schema but update the data in only one column. The SCOTT account also has access to all operations on the REGIONS table. Figure 6-7 shows the result of assigning rights when viewing in Database Control

Granting privileges at the column level is often said to be bad practice

because of the massive workload involved. If it is necessary to restrict people’

access to certain columns, creating a view that shows only those columns will

often be a better alternative.

Using the WITH GRANT OPTION directive will allow the user to transfer his rights to other accounts. Oracle stores information about who gave access to whom at the object level; this allows rights to be revoked given this information. Let's look at an example

connect store/admin123;

grant select on customers to sales with grant option;

connect sales/sales;

grant select on store.customers to webapp with grant option;

conn webapp/oracle;

grant select on store.customers to scott;

connect store/admin123;

revoke select on customers from sales;

After executing these commands, neither the SALES user nor the WEBAPP user nor the SCOTT user has permission to issue SELECT commands on the STORE.CUSTOMERS table.

Revocation of an object privilege will cascade (unlike revocation of

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 the operating system (the main program that runs on your computer), to process the log file and obtain the access ID from that 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 the 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 rather than granting 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 restrict 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 in 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 a high-level user. Regular users simply need to be familiar with the system's privileges in principle, consulting their documentation only in the event of special messages.

RESUME

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.

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(