Управление привилегиями базы данных, создание ролей, страница 5

7.  Подсоединились к базе данных как bert и просмотрели представления: SESSION_PRIVS, SESSION_ROLES, USER_SYS_PRIVS, USER_ROLE_PRIVS и ROLE_SYS_PRIVS.

CONNECT BERT/BERT

Connected.

SELECT * FROM SESSION_PRIVS

PRIVILEGE                              

----------------------------------------

CREATE SESSION                         

ALTER SESSION                          

CREATE TABLE                           

CREATE ANY TABLE                       

CREATE CLUSTER                         

CREATE ANY INDEX                       

CREATE SYNONYM                         

CREATE VIEW                            

CREATE ANY VIEW                        

CREATE SEQUENCE                        

CREATE ANY SEQUENCE                    

CREATE DATABASE LINK                   

12 rows selected.

SELECT * FROM SESSION_ROLES

ROLE                         

---------------------

CONNECT                      

DEVELOPER                    

2 rows selected.

SELECT * FROM USER_SYS_PRIVS

USERNAME                PRIVILEGE                             ADM

--------------------------- ------------------------------------  -----------

BERT                           CREATE ANY INDEX            NO

BERT                           CREATE ANY SEQUENCE    NO

BERT                           CREATE ANY TABLE            NO

BERT                           CREATE SESSION                   NO

BERT                           CREATE TABLE                      NO

5 rows selected.

SELECT * FROM USER_ROLE_PRIVS

USERNAME               GRANTED_ROLE       ADM     DEF      OS_

-----------------------       -------------------------     --------   --------   ------

BERT                           CONNECT                    NO        YES      NO

BERT                           DEVELOPER               NO         YES      NO

2 rows selected.

SELECT * FROM ROLE_SYS_PRIVS

ROLE                           PRIVILEGE                                          ADM

-------------------------   -------------------------------------------      --------

CONNECT                        ALTER SESSION                            NO

CONNECT                        CREATE CLUSTER                        NO

CONNECT                        CREATE DATABASE LINK          NO

CONNECT                        CREATE SEQUENCE                     NO

CONNECT                        CREATE SESSION                          NO

CONNECT                        CREATE SYNONYM                      NO

CONNECT                        CREATE TABLE                              NO

CONNECT                        CREATE VIEW                                NO

DEVELOPER                      CREATE ANY TABLE                  NO

DEVELOPER                      CREATE ANY VIEW                     NO

DEVELOPER                      CREATE CLUSTER                       NO

11 rows selected.

8.  Как bert предоставили все привилегии для доступа и обновления таблицы ЕМР роли SECURITY ADM1N. Вывели информацию представления USER_TAB_PRIVS, относящуюся к таблице ЕМР.

GRANT SELECT, UPDATE ON EMP TO SECURITY_ADMiN

Statement processed.

SELECT * FROM USER_TAB_PRIVS

GRANTEE                        OWNER     TABLE_NAME        GRANTOR       PRIVILEGE       GRA

------------------------------ -------------    -----------------------   ------------------   -----------------   ---------

ERNIE                              BERT            EMP                         BERT                  SELECT           NO

SECURITY_ADMIN       BERT           EMP                         BERT                   SELECT           NO

SECURITY_ADMIN       BERT           EMP                         BERT                   UPDATE          NO

3 rows selected.

9.  Подсоединились к базе данных как bigbird и создали роль END_USER

CONNECT BIGBIRD/BIGBIRD

Connected.

CREATE ROLE END_USER

Statement processed.

10.  Как bert предоставили роли END_USER возможность выполнять следующие команды SQL, относящиеся к таблице ЕМР: SELECT, UPDATE (столбцы ENAME и JOB), INSERT (столбцы EMPNO, ENAME, H1REDATE, JOB и DEPTNO).

GRANT SELECT,

UPDATE (ENAME, JOB),

INSERT (EMPNO, ENAME, HIREDATE, JOB, DEPTNO)

ON EMP TO end_USER

Statement processed.