Skip to main content

REVOKE statement

REVOKE statement

Use the REVOKE statement to remove privileges from a specific user or role, or from all users, to perform actions on database objects. You can also use the REVOKE statement to revoke a role from a user, from PUBLIC, or from another role.
The following types of privileges can be revoked:
  • Delete data from a specific table.
  • Insert data into a specific table.
  • Create a foreign key reference to the named table or to a subset of columns from a table.
  • Select data from a table, view, or a subset of columns in a table.
  • Create a trigger on a table.
  • Update data in a table or in a subset of columns in a table.
  • Run a specified routine (function or procedure).
  • Use a sequence generator or a user-defined type.
The derby.database.sqlAuthorization property must be set to true before you can use the GRANT statement or the REVOKE statement. The derby.database.sqlAuthorization property enables SQL Authorization mode.
You can revoke privileges for an object if you are the owner of the object or the database owner.
The syntax that you use for the REVOKE statement depends on whether you are revoking privileges to a schema object or revoking a role.
For more information on using the REVOKE statement, see "Using SQL standard authorization" in the Java DB Developer's Guide.

Syntax for tables

REVOKE privilege-type ON [ TABLE ] { table-Name | view-Name } FROM grantees
Revoking a privilege without specifying a column list revokes the privilege for all of the columns in the table.

Syntax for routines

REVOKE EXECUTE ON { FUNCTION | PROCEDURE } routine-designator FROM grantees  RESTRICT
You must use the RESTRICT clause on REVOKE statements for routines. The RESTRICT clause specifies that the EXECUTE privilege cannot be revoked if the specified routine is used in a view, trigger, or constraint, and the privilege is being revoked from the owner of the view, trigger, or constraint.

Syntax for sequence generators

REVOKE USAGE ON SEQUENCE [ schemaName. ] SQL92Identifier FROM grantees RESTRICT
In order to use a sequence generator, you must have the USAGE privilege on it. This privilege can be revoked from users and roles. Only RESTRICTed revokes are allowed. This means that the REVOKE statement cannot make a view, trigger, or constraint unusable by its owner. The USAGE privilege cannot be revoked from the schema owner. See CREATE SEQUENCE statement for more information.
The sequence name is composed of an optional schemaName and a SQL92Identifier. If a schemaName is not provided, the current schema is the default schema. If a qualified sequence name is specified, the schema name cannot begin with SYS.

Syntax for user-defined types

REVOKE USAGE ON TYPE  [ schemaName. ] SQL92Identifier FROM grantees RESTRICT
In order to use a user-defined type, you must have the USAGE privilege on it. This privilege can be revoked from users and roles. Only RESTRICTed revokes are allowed. This means that the REVOKE statement cannot make a view, trigger, or constraint unusable by its owner. The USAGE privilege cannot be revoked from the schema owner. See CREATE TYPE statement for more information.
The type name is composed of an optional schemaName and a SQL92Identifier. If a schemaName is not provided, the current schema is the default schema. If a qualified type name is specified, the schema name cannot begin with SYS.

Syntax for roles

REVOKE roleName [ {, roleName }* ] FROM grantees
Only the database owner can revoke a role.

privilege-types

  ALL PRIVILEGES |
  privilege-list

privilege-list

  table-privilege {, table-privilege }*

table-privilege

  DELETE |
  INSERT |
  REFERENCES [column list] |
  SELECT [column list] |
  TRIGGER |
  UPDATE [column list]

column list

  ( column-identifier {, column-identifier}* )
Use the ALL PRIVILEGES privilege type to revoke all of the privileges from the user or role for the specified table. You can also revoke one or more table privileges by specifying a privilege-list.
Use the DELETE privilege type to revoke permission to delete rows from the specified table.
Use the INSERT privilege type to revoke permission to insert rows into the specified table.
Use the REFERENCES privilege type to revoke permission to create a foreign key reference to the specified table. If a column list is specified with the REFERENCES privilege, the permission is revoked on only the foreign key reference to the specified columns.
Use the SELECT privilege type to revoke permission to perform SELECT statements on a table or view. If a column list is specified with the SELECT privilege, the permission is revoked on only those columns. If no column list is specified, then the privilege is valid on all of the columns in the table.
Use the TRIGGER privilege type to revoke permission to create a trigger on the specified table.
Use the UPDATE privilege type to revoke permission to use the UPDATE statement on the specified table. If a column list is specified, the privilege is revoked only on the specified columns.

grantees

{ AuthorizationIdentifier | roleName | PUBLIC } 
[,{ AuthorizationIdentifier | roleName | PUBLIC } ] *
You can revoke the privileges from specific users or roles or from all users. Use the keyword PUBLIC to specify all users. The privileges revoked from PUBLIC and from individual users or roles are independent privileges. For example, a SELECT privilege on table t is granted to both PUBLIC and to the authorization ID harry. The SELECT privilege is later revoked from the authorization ID harry, but the authorization ID harry can access the table t through the PUBLIC privilege.
You can revoke a role from a role, from a user, or from PUBLIC.
Restriction: You cannot revoke the privileges of the owner of an object.

routine-designator

  {
   qualified-name [ signature ]
  }

sequenceName

[ schemaName. ] SQL92Identifier
If schemaName is not provided, the current schema is the default schema. If a qualified sequence name is specified, the schema name cannot begin with SYS.

Prepared statements and open result sets/cursors

Checking for privileges happens at statement execution time, so prepared statements are still usable after a revoke action. If sufficient privileges are still available for the session, prepared statements will be executed, and for queries, a result set will be returned.
Once a result set has been returned to the application (by executing a prepared statement or by direct execution), it will remain accessible even if privileges or roles are revoked in a way that would cause another execution of the same statement to fail.

Cascading object dependencies

For views, triggers, and constraints, if the privilege on which the object depends on is revoked, the object is automatically dropped. Derby does not try to determine if you have other privileges that can replace the privileges that are being revoked. For more information, see "Using SQL standard authorization" and "Privileges on views, triggers, and constraints" in the Java DB Developer's Guide.

Limitations

The following limitations apply to the REVOKE statement:
Table-level privileges
All of the table-level privilege types for a specified grantee and table ID are stored in one row in the SYSTABLEPERMS system table. For example, when user2 is granted the SELECT and DELETE privileges on table user1.t1, a row is added to the SYSTABLEPERMS table. The GRANTEE field contains user2 and the TABLEID contains user1.t1. The SELECTPRIV and DELETEPRIV fields are set to Y. The remaining privilege type fields are set to N.When a grantee creates an object that relies on one of the privilege types, the Derby engine tracks the dependency of the object on the specific row in the SYSTABLEPERMS table. For example, user2 creates the view v1 by using the statement SELECT * FROM user1.t1, the dependency manager tracks the dependency of view v1 on the row in SYSTABLEPERMS for GRANTEE(user2), TABLEID(user1.t1). The dependency manager knows only that the view is dependent on a privilege type in that specific row, but does not track exactly which privilege type the view is dependent on.
When a REVOKE statement for a table-level privilege is issued for a grantee and table ID, all of the objects that are dependent on the grantee and table ID are dropped. For example, if user1 revokes the DELETE privilege on table t1 from user2, the row in SYSTABLEPERMS for GRANTEE(user2), TABLEID(user1.t1) is modified by the REVOKE statement. The dependency manager sends a revoke invalidation message to the view user2.v1 and the view is dropped even though the view is not dependent on the DELETE privilege for GRANTEE(user2), TABLEID(user1.t1).
Column-level privileges
Only one type of privilege for a specified grantee and table ID are stored in one row in the SYSCOLPERMS system table. For example, when user2 is granted the SELECT privilege on table user1.t1 for columns c12 and c13, a row is added to the SYSCOLPERMS. The GRANTEE field contains user2, the TABLEID contains user1.t1, the TYPE field contains S, and the COLUMNS field contains c12, c13.When a grantee creates an object that relies on the privilege type and the subset of columns in a table ID, the Derby engine tracks the dependency of the object on the specific row in the SYSCOLPERMS table. For example, user2 creates the view v1 by using the statement SELECT c11 FROM user1.t1, the dependency manager tracks the dependency of view v1 on the row in SYSCOLPERMS for GRANTEE(user2), TABLEID(user1.t1), TYPE(S). The dependency manager knows that the view is dependent on the SELECT privilege type, but does not track exactly which columns the view is dependent on.
When a REVOKE statement for a column-level privilege is issued for a grantee, table ID, and type, all of the objects that are dependent on the grantee, table ID, and type are dropped. For example, if user1 revokes the SELECT privilege on column c12 on table user1.t1 from user2, the row in SYSCOLPERMS for GRANTEE(user2), TABLEID(user1.t1), TYPE(S) is modified by the REVOKE statement. The dependency manager sends a revoke invalidation message to the view user2.v1 and the view is dropped even though the view is not dependent on the column c12 for GRANTEE(user2), TABLEID(user1.t1), TYPE(S).
Roles
Derby tracks any dependencies on the definer's current role for views, constraints, and triggers. If privileges were obtainable only via the current role when the object in question was defined, that object depends on the current role. The object will be dropped if the role is revoked from the defining user or from PUBLIC, as the case may be. Also, if a contained role of the current role in such cases is revoked, dependent objects will be dropped. Note that dropping may be too pessimistic. This is because Derby does not currently make an attempt to recheck if the necessary privileges are still available in such cases.

http://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljrevoke.html

Comments

Post a Comment

Popular posts from this blog

Oracle SQL92_SECURITY parameter

The Oracle SQL92_SECURITY parameter must be set to TRUE. The configuration option SQL92_SECURITY specifies whether table-level SELECT privileges are required to execute an update or delete that references table column values. If this option is disabled (set to FALSE), the UPDATE privilege can be used to determine values that should require SELECT privileges. The SQL92_SECURITY setting of TRUE prevents the exploitation of user credentials with only DELETE or UPDATE privileges on a table from being able to derive column values in that table by performing a series of update/delete statements using a where clause, and rolling back the change.  In the following example, with SQL92_SECURITY set to FALSE, a user with only delete privilege on the scott.emp table is able to derive that there is one employee with a salary greater than 3000. With SQL92_SECURITY set to TRUE, that user is prevented from attempting to derive a value.  SQL92_SECURITY = FALSE SQL> delete from s...
Post a Comment
Read more

Ofruesit e sistemeve të menaxhimit të bazës së të dhënave

Ofruesit e sistemeve të menaxhimit të bazës së të dhënave Cila është mënyra më e mirë për të përcaktuar se cili lloj i shërbimit të bazës së të dhënave ose bazës së të dhënave është më e mira për ndërmarrjen tuaj? E gjitha varet nga lloji i rastit të përdorimit që ju nevojitet. Zbuloni më shumë në këtë artikull , dhe artikujt vijues. Sistemi I menaxhimit të të dhenave Në thelb të gjitha informacionet digjitale që përdorim në baza ditore janë në një sistem të menaxhimit të bazës së të dhënave ose një grupi ruajtës diku në botë. Këto mund të shkojnë nga një pajisje e vogël e ruajtjes si një smartphone   ose a aq i madh sa një sistem ruajtje relativisht i pakufizuar i cloud. Më së miri është të zbuloni se cila DBMS është për ndërmarrjen tuaj? A duhet të abonoheni në një shërbim në AWS, Azure, Google ose ofrues tjetër cloud ose duhet të blini magazinimin e qendrës së të dhënave dhe serverat dhe ta ekzekutoni atë vetë? E gjitha varet nga lloji i rastit të përdorimit që ju ...
Post a Comment
Read more

Autentifikimi, Autorizimi dhe Llogaritja (AAA)- Authentication, Authorization, and Accounting (AAA)

Autentifikimi, Autorizimi dhe Llogaritja (AAA)- Authentication, Authorization, and Accounting (AAA) Autentifikimi, autorizimi dhe llogaritja (AAA) është një term për një kornizë për kontrollin inteligjent të qasjes në burimet kompjuterike, zbatimin e politikave, përdorimin e auditimit dhe sigurimin e informacionit të nevojshëm për të faturuar shërbimet. Këto procese të kombinuara konsiderohen të rëndësishme për menaxhimin efektiv të rrjetit dhe sigurinë. Si proces i parë, autentifikimi siguron një mënyrë për identifikimin e një përdoruesi, zakonisht duke i dhënë përdoruesit një emër përdoruesi të vlefshëm dhe një fjalëkalim të vlefshëm përpara se qasja të jepet. Procesi i legalizimit bazohet në secilin përdorues që ka një grup të kritereve unike për të fituar akses. Serveri AAA krahason kredencialet e identifikimit të përdoruesit me kredencialet e tjera të përdoruesit të ruajtura në një bazë të dhënash. Nëse kredencialet përputhen, përdoruesit i jepet aksesi në rrjet. Nëse kre...
Post a Comment
Read more