GRANT
Use the GRANT statement to grant:
System privileges to users and roles. Table 18-1 lists the system privileges (organized by the database object operated upon).
Object privileges for a particular object to users and roles. Table 18-2 lists the object privileges (organized by the database object operated upon).
Global roles (created with IDENTIFIED GLOBALLY ) are granted through enterprise roles and cannot be granted using the GRANT statement.
Notes on Authorizing Database Users You can authorize database users through means other than the database and the GRANT statement.
Many Oracle Database privileges are granted through supplied PL/SQL and Java packages. For information on those privileges, refer to the documentation for the appropriate package.
Note on Editionable Objects A GRANT operation to grant object privileges on an editionable object actualizes the object in the current edition. See Oracle Database Development Guide for more information about editions and editionable objects.
CREATE USER and CREATE ROLE for definitions of local, global, and external privileges
Oracle Database Security Guide for information about other authorization methods and for information about privileges
REVOKE for information on revoking grants
You must have been granted the GRANT ANY PRIVILEGE system privilege. In this case, if you grant the system privilege to a role, then a user to whom the role has been granted does not have the privilege unless the role is enabled in user’s session.
To grant a role to a program unit in your own schema :
For Oracle Database 12 c Release 1 (12.1.0.1), you must have been directly granted the role, or you must have created the role.
Description of the illustration »grant.gif»
Description of the illustration »grant_system_privileges.gif»
Grantable oracle что это
Use the GRANT statement to grant:
System privileges to users and roles. Table 18-1 lists the system privileges (organized by the database object operated upon).
Object privileges for a particular object to users and roles. Table 18-2 lists the object privileges (organized by the database object operated upon).
Global roles (created with IDENTIFIED GLOBALLY ) are granted through enterprise roles and cannot be granted using the GRANT statement.
Notes on Authorizing Database Users
You can authorize database users through means other than the database and the GRANT statement.
Many Oracle Database privileges are granted through supplied PL/SQL and Java packages. For information on those privileges, refer to the documentation for the appropriate package.
Note on Oracle Automatic Storage Management
Note on Editionable Objects
A GRANT operation to grant object privileges on an editionable object actualizes the object in the current edition. See Oracle Database Development Guide for more information about editions and editionable objects.
CREATE USER and CREATE ROLE for definitions of local, global, and external privileges
Oracle Database Security Guide for information about other authorization methods and for information about privileges
REVOKE for information on revoking grants
You must have been granted the GRANT ANY PRIVILEGE system privilege. In this case, if you grant the system privilege to a role, then a user to whom the role has been granted does not have the privilege unless the role is enabled in user’s session.
GRANT statement
Use the GRANT statement to give privileges to a specific user or role, or to all users, to perform actions on database objects. You can also use the GRANT statement to grant a role to a user, to PUBLIC, or to another role.
You can grant privileges on an object if you are the owner of the object or the database owner. See the CREATE statement for the database object that you want to grant privileges on for more information.
The syntax that you use for the GRANT statement depends on whether you are granting privileges to a schema object or granting a role.
Syntax for tables
Syntax for routines
Syntax for sequence generators
In order to use a sequence generator, you must have the USAGE privilege on it. This privilege can be granted to users and to roles. 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
In order to use a user-defined type, you must have the USAGE privilege on it. This privilege can be granted to users and to roles. 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
Before you can grant a role to a user or to another role, you must create the role using the CREATE ROLE statement. Only the database owner can grant a role.
A role A contains another role B if role B is granted to role A, or is contained in a role C granted to role A. Privileges granted to a contained role are inherited by the containing roles. So the set of privileges identified by role A is the union of the privileges granted to role A and the privileges granted to any contained roles of role A.
privilege-types
privilege-list
table-privilege
column list
Use the ALL PRIVILEGES privilege type to grant all of the privileges to the user or role for the specified table. You can also grant one or more table privileges by specifying a privilege-list.
Use the DELETE privilege type to grant permission to delete rows from the specified table.
Use the INSERT privilege type to grant permission to insert rows into the specified table.
Use the REFERENCES privilege type to grant permission to create a foreign key reference to the specified table. If a column list is specified with the REFERENCES privilege, the permission is valid on only the foreign key reference to the specified columns.
Use the SELECT privilege type to grant permission to perform SELECT statements or SelectExpressions on a table or view. If a column list is specified with the SELECT privilege, the permission is valid on only those columns. If no column list is specified, then the privilege is valid on all of the columns in the table.
For queries that do not select a specific column from the tables involved in a SELECT statement or SelectExpression (for example, queries that use COUNT(*) ), the user must have at least one column-level SELECT privilege or table-level SELECT privilege.
Use the TRIGGER privilege type to grant permission to create a trigger on the specified table.
Use the UPDATE privilege type to grant permission to use the UPDATE statement on the specified table. If a column list is specified, the permission applies only to the specified columns. To update a row using a statement that includes a WHERE clause, you must have the SELECT privilege on the columns in the row that you want to update.
grantees
Either the object owner or the database owner can grant privileges to a user or to a role. Only the database owner can grant a role to a user or to another role.
routine-designator
Examples
Как я могу перечислить ВСЕ гранты, полученные пользователем?
Мне нужно увидеть все гранты по БД Oracle.
Я использовал функцию TOAD для сравнения схем, но она не показывает заманчивые гранты и т. Д., Поэтому есть мой вопрос:
Как я могу перечислить все гранты в базе данных Oracle?
Если вам нужно больше, чем просто прямые гранты таблицы (например, гранты через роли, системные привилегии, такие как выбор любой таблицы и т. Д.), Вот несколько дополнительных запросов:
Системные привилегии для пользователя:
Прямые гранты для таблиц / представлений:
Косвенные гранты для таблиц / представлений:
Это не вернет объекты, принадлежащие пользователю. Если они вам нужны, используйте all_tab_privs вместо этого просмотр.
Извините, ребята, но выбор из all_tab_privs_recd, где grantee = ‘your user’ не даст никаких результатов, кроме публичных грантов и текущих пользовательских грантов, если вы запустите выбор от другого (скажем, SYS) пользователя. Как говорится в документации,
ALL_TAB_PRIVS_RECD описывает следующие типы грантов:
Итак, если вы администратор базы данных и хотите перечислить все гранты объектов для определенного (не самого SYS) пользователя, вы не можете использовать это системное представление.
В этом случае вы должны выполнить более сложный запрос. Вот пример, взятый (отслеженный) из TOAD для выбора всех грантов объекта для конкретного пользователя:
В нем будут перечислены все гранты объекта (включая гранты столбцов) для вашего (указанного) пользователя. Если вам не нужны гранты на уровне столбца, удалите всю часть выбора, начинающуюся с предложения union.
UPD: Изучая документацию, я нашел еще одно представление, в котором все гранты перечислены гораздо проще:
Имейте в виду, что в Oracle нет представления DBA_TAB_PRIVS_RECD.
How To Grant SELECT Object Privilege On One or More Tables to a User
Summary: in this tutorial, you will learn how to use the Oracle GRANT statement to grant SELECT object privilege on one or more tables to a user.
Grant SELECT on a table to a user
To grant the SELECT object privilege on a table to a user or role, you use the following statement:
The following example illustrates how to grant the SELECT object privilege on a table to a user.
First, create a new user called DW and grant the CREATE SESSION to the user:
Second, grant the SELECT object privilege on the ot.customers table to the dw user:
Finally, use the dw user to log in to the Oracle Database and query data from the ot.customers table:
Here is the output:
Grant SELECT on all tables in a schema to a user
Sometimes, you want to grant SELECT on all tables which belong to a schema or user to another user. Unfortunately, Oracle doesn’t directly support this using a single SQL statement.
To work around this, you can select all table names of a user (or a schema) and grant the SELECT object privilege on each table to a grantee.
The following stored procedure illustrates the idea:
This example grants the SELECT object privileges of all tables that belong to the user OT to the user DW :
When you use the user DW to login to the Oracle Database, the user DW should have the SELECT object privilege on all tables of the OT ‘s schema.
In this tutorial, you have learned how to grant the SELECT object privilege on one or more tables to a user.
