Как проверить grant в oracle sql

4урок по oracle sql, пользователи, роли, привилегии

Продолжаем знакомство с возможностями запросов в ORACLE , это четвертый урок.

—Работа с пользователями, ролями и привилегиями

—Создание пользователей с аутентификацией по паролю
CREATE USER CU_1 IDENTIFIED BY cupass;

—Изменение пароля
ALTER USER CU_1 IDENTIFIED BY new_cupass;

—Предоставление привилегий
—Создавать сессию с сервером
GRANT CREATE SESSION TO CU_1;

—Можно так

grant connect to CU_1;

—Создание основных лбъектов базы данных
GRANT
CREATE TABLE,
CREATE PROCEDURE,
CREATE TRIGGER,
CREATE VIEW,
CREATE SEQUENCE
TO CU_1;

—Предоставление права создавать базовые таблицы

grant resource to CU_1;

—Предоставление табличного пространства по умолчанию

alter user CU_1 default tablespace users;

—Права на ALTER(изменение объектов)
GRANT ALTER ANY TABLE TO CU_1;
GRANT ALTER ANY PROCEDURE TO CU_1;
GRANT ALTER ANY TRIGGER TO CU_1;
GRANT ALTER PROFILE TO CU_1;
—Права на удаление объектов и записей
GRANT DELETE ANY TABLE TO CU_1;
GRANT DROP ANY TABLE TO CU_1;
GRANT DROP ANY PROCEDURE TO CU_1;
GRANT DROP ANY TRIGGER TO CU_1;
GRANT DROP ANY VIEW TO CU_1;
GRANT DROP PROFILE TO CU_1;

—Создание ролей
CREATE ROLE cu_role;
—ПРедоставление привилегий роли
GRANT
CREATE TABLE,
CREATE PROCEDURE,
CREATE TRIGGER,
CREATE VIEW,
CREATE SEQUENCE
TO cu_role;
—Связь роли с пользователем
GRANT cu_role TO CU_1;

—Предоставление объектных привилегий
—На оператор SELECT для пользователя и роли
GRANT SELECT ON HR.EMPLOYEES TO CU_1, cu_role;
—На оператор UPDATE к определенным столбцам
GRANT UPDATE(FIRST_NAME, LAST_NAME) ON HR.EMPLOYEES TO CU_1, cu_role;
—На INSERT с возможностью пользователя передавать другим эту привилегию
GRANT INSERT ON HR.EMPLOYEES TO CU_1 WITH GRANT OPTION;
—Для всех пользователей на чтение
GRANT SELECT ON HR.EMPLOYEES TO PUBLIC;

—Системные привилегии для ролей
SELECT * FROM ROLE_SYS_PRIVS;
—Привилегии на таблицы для ролей
SELECT * FROM ROLE_TAB_PRIVS;
—Роли, доступные пользователю
SELECT * FROM USER_ROLE_PRIVS;
—Объектные привилегии доступные пользователю
SELECT * FROM USER_TAB_PRIVS_RECD;

—Отмена привилегий
REVOKE CREATE VIEW FROM CU_1;
REVOKE INSERT ON HR.EMPLOYEES FROM CU_1;

—Удаление роли
DROP ROLE cu_role;

—Удаление пользователя
DROP USER CU_1;

—Роли, доступные определенному пользователю
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = ‘CU_1’;

—Отнять роль у пользователя
REVOKE CU_ROLE FROM CU_1;

На этом — все, видео можно посмотреть на моем канале в YouTube

Показывать гранты для пользователя в MySQL

В MySQL вы можете использовать команду SHOW GRANTS для отображения всей информации о грантах для пользователя. Это отобразит привилегии, которые были назначены пользователю с помощью команды GRANT.

Синтаксис

Синтаксис команды SHOW GRANTS в MySQL:

SHOW GRANTS [ FOR username ]

Параметры или аргументы

user_name — имя учетной записи базы данных, для которой будет отображаться информация о грантах.

Примечание

Чтобы просмотреть привилегии user (то есть не CURRENT_USER), вы должны иметь привилегию SELECT в базе данных MySQL.

Пример

Рассмотрим пример использования команды SHOW GRANTS в MySQL для отображения информации о гранте для пользователя.

SHOW GRANTS FOR ‘trizor’ ;

В этом примере будет отображаться вся информация о грантах для пользователя, называемого ‘trizor’ . Каждая строка, возвращаемая командой SHOW GRANTS, является оператором GRANT, который может использоваться для пересоздания привилегий. Это отличный способ захватить привилегии, которые вы захотите сохранить позже.

В этом первом примере, когда вы не указываете хост для имени пользователя, MySQL принимает в качестве хоста % . Таким образом, приведенный выше пример будет эквивалентен следующей команде SHOW GRANTS.
Например:

Управление доступом к базе данных Oracle:

Полномочия – это право на выполнение конкретного типа SQL-оператора или на доступ к объекту базы данных, принадлежащему другому пользователю. В базе данных Oracle необходимо явно предоставить пользователю полномочия для выполнения любых действий, включая подключение к базе данных или выборку, изменение и обновление данных в любой таблице, кроме собственной.

Существуют два основных типа полномочий Oracle: системные полномочия и объектные полномочия. Для предоставления пользователям как системных, так и объектынх полномочий служит оператор GRANT.

Системные полномочия:

Системные полномочия позволяют пользователю выполнить конкретное действие в базе данных либо действие с любым объектом схемы, конкретного типа. Хороший пример первого типа системных полномочий – полномочия, которые позволяют подключаться к базе данных, носящие название полномочий CONNECT. Другими полномочиями этого типа являются полномоичия CREATE TABLESPACE, CREATE USER, DROP USER и ALTER USER.

Второй класс системных полномоичий предоставляет пользователям право на выполнение операций, которыевлияют на объекты в любой схеме. Примерами этого типа системных полномочий служат ANALYZE ANY TABLE, GRANT ANY PRIVILEGE, INSERT ANY TABLE, DELETE ANY TABLE и т.п. Системные полномочия являются очень мощным средством и выдача их не тому пользователю может оказать разруши тельное влияние на базу данных.

Ниже перечислены некоторые наиболее часто используемые полномочия базы данных Oracle:

• ADVISOR
• ALTER DATABASE
• ALTER SYSTEM
• AUDIT SYSTEM
• CREATE DATABASE LINK
• CREATE TABLE
• CREATE ANY INDEX
• CREATE SESSION
• CREATE TABLESPACE
• CREATE USER
• DROP USER
• INSERT ANY TABLE

Пример:

GRANT CREATE SESSION TO scott; 

Объектыные полномочия:

Объектыне полномочия – это полномочия по отношению к различным типам объектов базы данных. Объектыные полномочия дают пользователю возможность выполнять действия с конкретной таблицей, представлением, материализованным представлением, последовательностью, процедурой, функцией или пакетом. Следовательно, всем пользователям базы данных нужны объектные полномочия.

Для выдачи объектных полномочий можно использовать следующие SQL-операторы.

Пример:

GRANT SELECT, UPDATE ON table_name TO scott; 

Основные представления привелегий пользователей:

ROLE_SYS_PRIVS - Системные привилегии, предоатавленные ролям. ROLE_TAB_PRIVS - Привилегии на таблицы, предоставленные ролям. USER_ROLE_PRIVS - Роли, доступные пользователю. USER_TAB_PRIVS_MADE - Объектыне привилегии, которые пользователь предоставил на свои объекты. USER_TAB_PRIVS_RECD - Объектыне привилегии, предоставленные пользователю. USER_COL_PRIVS_MADE - объектные привилегии, которые пользователь предоставил на столбцы своих объектов. USER_COL_PRIVS_RECD - Объектыне привилении, предоставленные пользователю на столбцы чужих объектов. USER_SYS_PRIVS - Перечень системынх привилегий предоставленных пользователю.

Получить список всех ролей, системных и объектных привилегий пользователя. (Запускаетс под учетной записью пользователя)

Способ 1:

SET feedback off SET serveroutput ON BEGIN dbms_output.enable(100000); dbms_output.put_line('-- Fetching roles'); FOR i IN (SELECT username, granted_role FROM user_role_privs) LOOP dbms_output.put_line('grant '||i.granted_role||' to '||i.username||';'); END LOOP; dbms_output.put_line('-- Fetching system privileges'); FOR i IN (SELECT username, privilege FROM user_sys_privs) LOOP dbms_output.put_line('grant '||i.privilege||' to '||i.username||';'); END LOOP; dbms_output.put_line('-- Fetching object privileges'); FOR i IN (SELECT grantee, owner, table_name, privilege FROM user_tab_privs) LOOP dbms_output.put_line('grant '||i.privilege||' on '||i.owner||'.'||i.table_name||' to '||i.grantee||';'); END LOOP; END; / 

Result:

grant UNLIMITED TABLESPACE to PLSQL_PROJECT; grant CREATE SESSION to PLSQL_PROJECT; grant CREATE TABLE to PLSQL_PROJECT; 

Способ 2:

Our task is get all privileges granted for user and create report based on this information. As you know privileges gives on user directly or on his role. Privileges divided by 2 part: system and object.

Let see example, create report for user Scott

1.Create repository table for store data:

create table vm_user_privs(privilege varchar2(100),user_name varchar2(100),object_name varchar2(100)); 

2. Main script:

declare cursor c_user is select distinct(a.username) from dba_users a where a.username='SCOTT'; p_user varchar2(100); begin open c_user; loop fetch c_user into p_user; insert into vm_user_privs select /*+ rule */ a.privilege,p_user,a.table_name from dba_tab_privs a where a.grantee=p_user union all select /*+rule */ b.privilege,p_user,b.table_name from dba_tab_privs b where b.grantee in (select b1.granted_role from dba_role_privs b1 where b1.grantee=p_user) union all select /*+ rule */ c.privilege,p_user,null from dba_sys_privs c where c.grantee=p_user union all select /*+rule */ d.privilege,p_user,null from dba_sys_privs d where d.grantee in (select b2.granted_role from dba_role_privs b2 where b2.grantee=p_user); commit; EXIT WHEN c_user%NOTFOUND; end loop; close c_user; end; 

3. See result:

select * from vm_user_privs 

Thank’s to
http://ocp.community.ge/post/Script-generate-all-user-privileges-in-one-report.aspx

Tags: Oracle Database, Управление доступом

Oracle DBA

Собираем также материалы по: SQL & PL/SQL

Лучше потратить какое-то количество времени, чтобы записать успешный опыт, чем потом повторно воспроизводить его по памяти.

Все материалы обновляются по мере нахождения лучших практик и апгрейда знаний. Если будут желающие добавлять свои знания или исправлять ошибки и неточности, пишите в телеграм чате. Если будет учавствовать больше людей, качество материалов будет улучшаться и обновляться быстрее. Ссылки на ваши профили в соц. сетях будут добавлены в статьях, в которых вы учавствуете.

Как проверить grant в oracle sql

Не знаю, чем у вас закончилась история с нашим новым пользователем DUMMY, а у меня он все же остался. Если кто-то из вас создал своего пользователя, то можете воспользоваться своим. А, вот сейчас давайте поговорим о том, как могут взаимодействовать разные схемы БД. И как это все возможно осуществить. Запускайте SQL*Plus и подключайтесь пользователем DUMMY (если вы его все-таки пристрелили, реанимируйте его согласно шагу 101). А теперь, находясь в схеме DUMMY дайте такой запрос:

SELECT * FROM SALESREPS /

SQL> SELECT * FROM SALESREPS 2 / SELECT * FROM SALESREPS * ошибка в строке 1: ORA-00942: таблица или представление пользователя не существует

Неудача «ORA-00942: таблица или представление пользователя не существует»! Говорит само за себя. Теперь попробуем:

SELECT * FROM MILLER.SALESREPS /

SQL> SELECT * FROM MILLER.SALESREPS 2 / SELECT * FROM MILLER.SALESREPS * ошибка в строке 1: ORA-01031: привилегий недостаточно

В чем же причина? Да просто у пользователя DUMMY нет прав производить чтение из таблицы схемы MILLER! Как его предоставить? Очень просто. Подключаемся к схеме MILLER:

SQL> CONNECT MILLER/KOLOBOK@PROBA Соединено.

А теперь записываем следующее:

SQL> GRANT SELECT ON SALESREPS TO DUMMY 2 / Привилегии предоставлены.

Меняем подключение на DUMMY:

SQL> CONNECT DUMMY/DUMB@PROBA Соединено.

Снова повторяем запрос вот так, чтобы было меньше столбцов:

SELECT NAME FROM MILLER.SALESREPS /

Получаем в результате:

SQL> SELECT NAME FROM MILLER.SALESREPS 2 / NAME ------------------------------ Вася Пупкин Маша Распутина Филип Киркоров Света Разина Наташа Королева Игорь Николаев Крис Кельми Игорь Петров Дима Маликов Маша Сидорова Максим Галкин 11 строк выбрано.

Теперь результат операции GRANT SELECT ON SALESREPS TO DUMMY виден на практике. Давайте более подробно рассмотрим операторы DDL — GRANT (предоставить) и REVOKE (отменить). Эти операторы нельзя использовать непосредственно в PL/SQL. Они предназначены для возможности выполнения других операторов SQL. Например, чтобы выполнить над таблицей Oracle некоторую операцию — INSERT или DELETE, необходимо иметь полномочия предоставляемые операторам GRANT. Существуют привилегии двух различных видов: объектные и системные. Объектная привилегия (object privilege) разрешает выполнение определенной операции над конкретным объектом (например над таблицей). В то время как системная привилегия (system privilege) разрешает выполнение операций над целым классом объектов. Существует множество системных привилегий, соответствующих практически всем возможным операциям DDL. Например, системная привилегия CREATE TABLE, позволяет ее обладателю создавать таблицы. А, вот системная привилегия CREATE ANY TABLE дает возможность создавать таблицы в других схемах. Давайте кратко насколько это, возможно остановимся на операторе GRANT. Синтаксис для предоставления пользователям или ролям системных полномочий и ролей:

------- --------- , ------------ ---- , ----- ------------------------ ------- GRANT --- system_privilege --- TO --- user --- WITH ADMIN OPTION ------ ------- --- role --------------- --- role --- ------------------------ --- PUBLIC --------------------------

• system_privilege — предоставляемое системное полномочие.
• role — предоставляемая роль.
• TO — определяет пользователей или роли, которым предоставляются системные полномочия.
• PUBLIC — указывает что, системные полномочия определяемые администратором предоставляются всем пользователям.
• WITH ADMIN OPTION — позволяет получившему системные полномочия или роль предоставлять их в дальнейшем другими пользователям или ролям. Такое решение в частности включает и возможность изменение или удаления роли.

Давайте посмотрим какие системные полномочия могут предоставляться. Основных операций в языке DDL три — это CREATE, ALTER, DROP.

• ALTER DATABASE — Позволяет изменять саму БД.
• ALTER USER — Позволяет изменять пользователя и его параметры (пароль, профиль, роль и т.д.)
• ALTER PROFILE — Позволяет изменять профили.
• ALTER TABLESPACE — Позволяет изменять табличные пространства.

• ALTER ANY PROCEDURE — Разрешает изменение любой хранимой функции процедуры или пакета в любой схеме.
• ALTER ANY ROLE — Разрешает изменение любой роли БД.
• ALTER ANY SEQUENCE — Разрешает изменение любой последовательности в БД.
• ALTER ANY TABLE — Разрешает изменение любой таблицы или вида в схеме БД.
• ALTER ANY TRIGGER — Позволяет разрешать, запрещать компилировать любой триггер в любой схеме БД.
• ALTER ANY INDEX — Разрешает изменение любого индекса в любой схеме.

Группа CREATE:

Позволяет создавать в любой схеме соответствующий объект:

CREATE ANY PROCEDURE; CREATE ANY SEQUENCE; CREATE ANY TABLE; CREATE ANY TRIGGER; CREATE ANY VIEW; CREATE ANY INDEX;

Позволяет создавать в конкретной схеме соответствующий объект:

CREATE PROCEDURE; CREATE SEQUENCE; CREATE TABLE; CREATE TRIGGER; CREATE VIEW; CREATE INDEX; CREATE SESSION CREATE ROLE;

Удаление объектов в любой схеме, а так же очистка таблиц:

DELETE ANY TABLE; DROP ANY PROCEDURE; DROP ANY SEQUENCE; DROP ANY TABLE; DROP ANY TRIGGER; DROP ANY VIEW; DROP ANY INDEX;

Удаление объектов в схеме:

DROP PROCEDURE; DROP SEQUENCE; DROP TABLE; DROP TRIGGER; DROP VIEW; DROP INDEX;

И еще полезные системные привилегии:

EXECUTE ANY PROCEDURE - Выполнить любую процедуру. GRANT ANY PRIVILEGE; GRANT ANY ROLE; INSERT ANY TABLE - Вставка в любую таблицу. LOCK ANY TABLE; SELECT ANY TABLE - Чтение любой таблицы. SELECT ANY SEQUENCE; - Чтение любой последовательности.

Вот далеко не полный список системных привилегий, которые предоставляются оператором GRANT. Для начала я думаю хватит. А дальше все зависит от вас. Давайте теперь рассмотрим предоставление объектных привилегий. Здесь все выглядит вот так:

------ --------- , ------------ ------ GRANT --- object_privilege --- ------------------- ON -- schema.object --- TO --- User ----- ------ --- ALL ---------------- ---------- , ------ --- Role -----> --- PRIVILEGES - -- ( COLUMN ) ----- --- PUBLIC --- ------> WITH ADMIN OPTION ------------------

object_privilege — предоставляемая привилегия — одна из:

:ALTER :SELECT :UPDATE :DELETE :INSERT :EXECUTE (только для процедур функций и пакетов) :INDEX (только для таблиц) :REFERENCES (только для таблиц).

COLUMN — определяет столбец таблицы или вида, на который распространяется предоставляемая привилегия.

ON — определяет объект (таблицу, вид, и т.д.)

TO — указывает кому предоставляется привилегия.

WITH ADMIN OPTION — позволяет имеющему эту привилегию предоставлять их в дальнейшем другими пользователям или ролям.

Как с работать с этим типом мы с вами уже пробовали в начале этого шага! Можете, например добавить еще что-нибудь к вышеизложенному примеру. И наконец, давайте рассмотрим как привилегии изымаются или удаляются. Для этого необходимо применять оператор REVOKE. Его синтаксис аналогичен первым двум операторам за небольшим исключением:

----- --------- , ------------ ----- REVOKE --- object_privilege --- ------------------- ON -- schema.object --- FROM --- User ----- ----- --- ALL ---------------- ---------- , ------ --- Role -----> --- PRIVILEGES - --- PUBLIC --- ------> CASCADE CONSTRAINTS ------------------

Например, чтобы изъять привилегию на выборку из таблицы SALESREPS для схемы DUMMY введите следующее находясь в схеме MILLER:

REVOKE SELECT ON SALESREPS FROM DUMMY /

Получим примерно следующее:

SQL> REVOKE SELECT ON SALESREPS FROM DUMMY 2 / Привилегии изъяты.

Вот таким образом применяя операторы GRANT и REVOKE, можно строить взаимоотношение схем и строить политику доступа к объектам БД. Попробуйте создать в новом пользователе несколько объектов и разрешить обращаться к ним из схемы MILLER. Если что не получится пишите!