DBA_USERS - describes all users of the database.
DBA_SYS_PRIVS - describes system privileges granted to users and roles.
DBA_ROLE_PRIVS - describes the roles granted to all users and roles in the database.
DBA_TAB_PRIVS describes all object grants in the database.
USER_SYS_PRIVS - describes system privileges granted to the current user.
USER_ROLE_PRIVS - describes the roles granted to the current user.
USER_TAB_PRIVS - describes the object grants for which the current user is the object owner, grantor, or grantee.
SESSION_PRIVS - describes the system privileges that are currently available to the user.
- About whole dictionary information of Oracle 19C
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/database-reference.pdf
Oracle 19C database-reference.pdf
10.42MB
--the user demo can know his privileges by using this query
--see that the user demo has all these system privileges.
# You can verify the privileges assigned in the previous blog.
select * from session_privs;
/*
PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE SYNONYM
CREATE VIEW
CREATE SEQUENCE
*/
--now if he have create table privileges then he can insert,update,delete,
--select, alter, index on any table he create
create table emp
( empid number constraint emp_pk primary key,
ename varchar2(100)
);
/*
Table EMP created.
*/
insert into emp values (1,'seoul');
/*
1 row inserted.
*/
select * from emp;
/*
EMPID ENAME
---------- --------
1 seoul
*/
alter table emp add (salary number);
/*
Table EMP altered.
*/
col ename for a15
select * from emp;
/*
EMPID ENAME SALARY
---------- --------------- ----------
1 seoul
*/
create sequence emp_s;
/*
Sequence EMP_S created.
*/
--he can create index for the table he create
create index ename_ind on emp (ename);
/*
Index ENAME_IND created.
*/
create or replace view emp_v
as
select empid, ename
from emp;
/*
View EMP_V created.
*/
--now the user demo he want to change his password , because the dba create for
--his default password demo1234
alter user demo identified by demo_green;
/*
User DEMO altered.
*/
select * from hr.employees;
select EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL from hr.employees;
/*
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL
----------- -------------------- ------------------------- -------------------------
198 Donald OConnell DOCONNEL
199 Douglas Grant DGRANT
200 Jennifer Whalen JWHALEN
201 Michael Hartstein MHARTSTE
202 Pat Fay PFAY
...
*/
--the demo user can make select * from employees without hr.
--only if there is public syonym for hr.employees
select * from all_synonyms
where table_name='EMPLOYEES'
/*
no rows selected
*/
update hr.employees
set department_id =null
where employee_id=1;
/*
demo only has update for salary field on the employees table.
Error starting at line : 106 in command -
update hr.employees
set department_id =null
where employee_id=1
Error at Command Line : 106 Column : 11
Error report -
SQL Error: ORA-01031: insufficient privileges
01031. 00000 - "insufficient privileges"
*Cause: An attempt was made to perform a database operation without
the necessary privileges.
*Action: Ask your database administrator or designated security
administrator to grant you the necessary privileges
*/
update hr.employees
set salary =500
where employee_id=1;
/*
0 rows updated.
*/
-- 2개의 결과가 같은 이유는 아직 role을 생성하지 않아서 같은결과를 보여줌.
select * from session_privs;
--This is contain all the privileges granted to the user regardless
--if these privileges direct or from a role.
/*
PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE SYNONYM
CREATE VIEW
CREATE SEQUENCE
6 rows selected.
*/
col username for a20
--system privileges granted to the user
--But in this table it contains only the system privileges
--that come direct to the user. not from role.
select * from user_sys_privs;
/*
USERNAME PRIVILEGE ADM COM INH
-------------------- ---------------------------------------- --- --- ---
DEMO CREATE SESSION NO NO NO
DEMO CREATE TABLE NO NO NO
DEMO UNLIMITED TABLESPACE NO NO NO
DEMO CREATE SEQUENCE NO NO NO
DEMO CREATE VIEW NO NO NO
DEMO CREATE SYNONYM NO NO NO
6 rows selected.
*/
# DBA 딕셔너리를 이용해서 'DEMO' 사용자에게 할당된 시스템 권한을 조회
select * from DBA_SYS_PRIVS where GRANTEE = 'DEMO';
/*
GRAN PRIVILEGE ADM COM INH
---- ---------------------------------------- --- --- ---
DEMO CREATE TABLE NO NO NO
DEMO CREATE VIEW NO NO NO
DEMO UNLIMITED TABLESPACE NO NO NO
DEMO CREATE SESSION NO NO NO
DEMO CREATE SEQUENCE NO NO NO
DEMO CREATE SYNONYM NO NO NO
6 rows selected.
*/
--Now, if you go to the presentation, you can see
--that the user tab privs record object privileges granted to the user.
col owner for a10
col TABLE_NAME for a10
col GRANTOR for a10
col PRIVILEGE for a20
col grantable for a10
col hierarchy for a10
col common for a10
col type for a10
col inherited for a10
--현재 사용자가 다른 사용자에게 부여한 권한 정보를 알려준다
select * from user_tab_privs_made order by 2;
--자신에게 부여된 사용자 권한을 알고싶을 때
select * from user_tab_privs_recd order by 2;
/*
OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY COMMON TYPE INHERITED
---------- ---------- ---------- -------------------- ---------- ---------- ---------- ---------- ----------
HR EMPLOYEES HR DELETE NO NO NO TABLE NO
HR EMPLOYEES HR SELECT NO NO NO TABLE NO
HR JOBS HR INSERT NO NO NO TABLE NO
HR JOBS HR SELECT NO NO NO TABLE NO
HR LOCATIONS HR SELECT NO NO NO TABLE NO
HR LOCATIONS HR UPDATE NO NO NO TABLE NO
HR LOCATIONS HR REFERENCES NO NO NO TABLE NO
HR LOCATIONS HR READ NO NO NO TABLE NO
HR LOCATIONS HR ON COMMIT REFRESH NO NO NO TABLE NO
HR LOCATIONS HR QUERY REWRITE NO NO NO TABLE NO
HR LOCATIONS HR DEBUG NO NO NO TABLE NO
HR LOCATIONS HR INSERT NO NO NO TABLE NO
HR LOCATIONS HR INDEX NO NO NO TABLE NO
HR LOCATIONS HR FLASHBACK NO NO NO TABLE NO
HR LOCATIONS HR ALTER NO NO NO TABLE NO
HR LOCATIONS HR DELETE NO NO NO TABLE NO
16 rows selected.
*/
# DBA 딕셔너리를 이용해서 'DEMO' 사용자에게 할당된 시스템 권한을 조회
select * from DBA_TAB_PRIVS where GRANTEE = 'DEMO';
/*
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE COM TYPE INH
--------- ------ ------------- ----------- --------------------- --- --- --- ------- ---
DEMO HR LOCATIONS HR ALTER NO NO NO TABLE NO
DEMO HR LOCATIONS HR DELETE NO NO NO TABLE NO
DEMO HR EMPLOYEES HR DELETE NO NO NO TABLE NO
DEMO HR LOCATIONS HR INDEX NO NO NO TABLE NO
DEMO HR LOCATIONS HR INSERT NO NO NO TABLE NO
DEMO HR JOBS HR INSERT NO NO NO TABLE NO
DEMO HR LOCATIONS HR SELECT NO NO NO TABLE NO
DEMO HR JOBS HR SELECT NO NO NO TABLE NO
DEMO HR EMPLOYEES HR SELECT NO NO NO TABLE NO
DEMO HR LOCATIONS HR UPDATE NO NO NO TABLE NO
DEMO HR LOCATIONS HR REFERENCES NO NO NO TABLE NO
DEMO HR LOCATIONS HR READ NO NO NO TABLE NO
DEMO HR LOCATIONS HR ON COMMIT REFRESH NO NO NO TABLE NO
DEMO HR LOCATIONS HR QUERY REWRITE NO NO NO TABLE NO
DEMO HR LOCATIONS HR DEBUG NO NO NO TABLE NO
DEMO HR LOCATIONS HR FLASHBACK NO NO NO TABLE NO
16 rows selected.
*/
select * from user_col_privs_recd;
/*
OWNER TABLE_NAME COLUMN_NAME GRANTOR PRIVILEGE GRANTABLE COMMON INHERITED
---------- ---------- --------------- ---------- -------------------- ---------- ---------- ----------
HR EMPLOYEES SALARY HR UPDATE NO NO NO
*/
grant select on emp to hr;
/*
Grant succeeded.
*/
select * from user_tab_privs_made;
--1번째 라인의 정보가 상기 쿼리의 결과임.
--2번째 권한은 기본권한암. 자기 스키마에서 생선된 모든 테이블을 퍼블릭으로 줄수 있다는 의미.
/*
GRANTEE TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY COMMON TYPE INHERITED
----------- ---------- ---------- -------------------- ---------- ---------- ---------- ---------- ----------
HR EMP DEMO SELECT NO NO NO TABLE NO
PUBLIC DEMO DEMO INHERIT PRIVILEGES NO NO NO USER NO
*/
# DBA 딕셔너리를 이용해서 'DEMO' 사용자가 할당한 객체 권한을 조회
select * from DBA_TAB_PRIVS where GRANTOR = 'DEMO';
/*
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE COM TYPE INH
--------- -------- ------------ -------- -------------------- --- --- --- ------- ---
HR DEMO EMP DEMO SELECT NO NO NO TABLE NO
PUBLIC SYS DEMO DEMO INHERIT PRIVILEGES NO NO NO USER NO
*/
grant update (ename) on emp to hr;
/*
Grant succeeded.
*/
select * from user_col_privs_made;
/*
GRANTEE TABLE_NAME COLUMN_NAME GRANTOR PRIVILEGE GRANTABLE COMMON INHERITED
--------- ---------- -------------- ---------- ----------- ---------- ---------- ----------
HR EMP ENAME DEMO UPDATE NO NO NO
*/
'Database > Oracle' 카테고리의 다른 글
| 66 - [Oracle 19C] Common users and common privileges (0) | 2023.12.05 |
|---|---|
| 65 - [Oracle 19C] Creating private roles examples (0) | 2023.12.05 |
| 63 - [Oracle 19C] Grant Privileges of system and objects (0) | 2023.12.03 |
| 62 - [Oracle 19C] DB Link (0) | 2023.12.01 |
| 61 - [Oracle 19C] Oracle Net Manager (netmgr) (0) | 2023.12.01 |
