오라클 INSERT 권한 확인 - olakeul INSERT gwonhan hwag-in

GRANT ANY OBJECT PRIVILEGE 시스템 권한(1, 2번 SQL로 확인 가능)을 가지고 있는 경우 타 사용자의 모든 객체에 대한 권한 부여 가능(DBA도 해당 권한이 있음)

제공하는 System Privilege 중 ‘TRUNCATE TABLE’ Privilege라는 것은 따로 존재하지 않으며, Table을 Truncate하기 위해서는 ‘DROP TABLE’ Privilege가 있으면 가능.

CREATE OR REPLACE PROCEDURE XXX_DBA.SP_TRUNC (pTable IN VARCHAR2, reuse IN VARCHAR2 DEFAULT 'N')
IS
cmd	VARCHAR2 (4000) := 'truncate table XXX.' || pTable; BEGIN
IF UPPER (pTable) IN (TB_RC_XXX, 'TB_CM_XXX') -- Truncate이 허용되는 테이블 목록
THEN
CASE
WHEN reuse = 'N' THEN
-- DROP STORAGE
cmd := cmd || ' DROP STORAGE'; 
WHEN reuse = 'Y'
THEN
-- REUSE STORAGE
cmd := cmd || ' REUSE STORAGE'; 
END CASE;
-- DBMS_OUTPUT.put_line (cmd); EXECUTE IMMEDIATE cmd;
ELSE
RAISE_APPLICATION_ERROR (-20101, 'Requested Table is not allowed truncate operation.');
END IF;
END SP_TRUNC;
/

1. DROP STORAGE (디폴트) : 결과 테이블에 할당 된 익스텐트 수를 MINEXTENTS의 원래 설정으로 줄입니다.

1. DROP ALL STORAGE는 세그먼트 삭제.

1. REUSE STORAGE : 기존 공간을 줄이지 않고 유지토록함.

1.7 시스템 권한

Enables a user to perform administrative tasks including checkpointing, backups, migration, and user creation and deletion.

Note: There is no ALTER INDEX statement.

Note: There is no ALTER MATERIALIZED VIEW statement.

Note: There is no ALTER SEQUENCE statement.

Note: There is no ALTER VIEW statement.

Enables a user to perform operations related to cache groups.

Enables a user to create a cache group owned by that user.

Enables a user to create a materialized view owned by that user.

Enables a user to create a PL/SQL procedure, function or package owned by that user.

Enables a user to create a public synonym.

Enables a user to create a sequence owned by that user.

Enables a user to create a connection to the database.

Enables a user to create a private synonym.

Enables a user to create a table owned by that user.

Enables a user to create a view owned by that user.

Enables a user to drop a public synonym.

Enables a user to connect to a database as an XLA reader.

1.8 오브젝트 권한

Table||

Enables a user to delete from a table.

PL/SQL package, procedure or function||

Enables a user to execute a PL/SQL package, procedure or function directly.

Cache group||

Enables a user to flush a cache group.

Table or materialized view||

Enables a user to create an index on a table or materialized view.

Table or synonym||

Enables a user to insert into a table or into the table through a synonym.

Cache group||

Enables a user to load a cache group.

Enables a user to create a foreign key dependency on a table or materialized view.||

Cache group||

Enables a user to refresh a cache group.

Cache group||

Enables a user to unload a cache group.

Table||

Enables a user to update a table.

1.9 Privilege hierarchy

All other privileges including CACHE_MANAGER

CREATE MATERIALIZED VIEW

CREATE PROCEDURE

CREATE SEQUENCE

CREATE SYNONYM

CREATE TABLE

CREATE VIEW

Cache group privileges have a separate hierarchy except that ADMIN confers the CACHE_MANAGER privilege.

The CACHE_MANAGER privilege confers these privileges:

CREATE ANY CACHE GROUP

ALTER ANY CACHE GROUP

DROP ANY CACHE GROUP

FLUSH ANY CACHE GROUP

LOAD ANY CACHE GROUP

UNLOAD ANY CACHE GROUP

REFRESH ANY CACHE GROUP

FLUSH (object)

LOAD (object)

UNLOAD (object)

REFRESH (object)

The CACHE_MANAGER privilege also includes the ability to start and stop the cache agent and the replication agent and to perform cache grid operations.

CREATE ANY CACHE GROUP confers the CREATE CACHE GROUP privilege for any cache group.

1.10 PUBLIC role

All users of the database have the PUBLIC role. In a newly created TimesTen database, by default PUBLIC has SELECT and EXECUTE privileges on various system tables and views and PL/SQL functions, procedures and packages. You can see the list of objects by using this query:

SELECT table_name, privilege FROM sys.dba_tab_privs WHERE grantee='PUBLIC';

Privileges that are granted to PUBLIC as part of database creation cannot be revoked. To see a list of these privileges, use this query: