SQLD_ROLE ,CONSTRAINT

ROLE

GRANT 로 권한을 부여하고

SQL> grant resource, connect to test1;

Granted.

SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'TEST1';

GRANTEE    GRANTED_ROLE         ADMIN_OPTION DEFAULT_ROLE
---------- -------------------- ------------ ------------
TEST1      CONNECT              NO           YES
TEST1      RESOURCE             NO           YES

2 rows selected.

REVOKE 로 권한을 뺏어감

SQL> revoke resource, connect from test1;

Revoked.

SQL>  SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'TEST1';

0 row selected.

ROLE 조회 관련 쿼리

-- 롤 조회
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'sys';

-- 시스템 권한 조회
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'sys';

-- 객체 권한 조회
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = 'sys';

-- 롤에 포함된 권한 조회
SELECT * FROM ROLE_SYS_PRIVS 
WHERE ROLE IN (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'sys');

CONSTRAINT

TABLE 생성할 때 제약조건 추가하기

conn test1/tibero

create table test1
( no number primary key,
 name varchar(24) not null,
 gender number
);

PK 제약조건 추가, 제거

ALTER TABLE test1 DROP PRIMARY KEY

ALTER TABLE test1 ADD CONSTRAINT pk_no PRIMARY KEY (no);

NOT NULL 제약조건 추가, 제거

alter table test1 modify name varchar2(24) null;
alter table test1 modify name varchar2(24) not null;

UNIQUE 제약조건 추가

select *
FROM all_cons_columns
WHERE table_name = 'TEST1'

--unique 제약조건 추가 (중복 허용 안함)
ALTER TABLE test1
ADD CONSTRAINT unique_num UNIQUE (num);

INSERT INTO test1 (num, name) VALUES (11, 'wtebf');
TBR-10007: UNIQUE constraint violation ('EUN1'.'UNIQUE_NUM').

제약조건 조회 쿼리

select *
from DBA_CONSTRAINTS;

select *
from USER_CONSTRAINTS;

select *
from ALL_CONSTRAINTS;

select *
from DBA_CONS_COLUMNS;

select *
from USER_CONS_COLUMNS;

select *
from ALL_CONS_COLUMNS;

Privileges/ Role 조회

1. User에게 부여된 Privileges 조회

SELECT C.USERNAME, B.NAME FROM _DD_SYSAUTH A, SYSTEM_PRIVILEGES B,DBA_USERS C
          WHERE A.PRIV_NO = B.PRIV_NO
          AND A.GRANTEE_ID = C.USER_ID
          AND C.USERNAME IN ('SYS');

2. User에게 부여된 Role 조회

SELECT GRANTEE, GRANTED_ROLE FROM DBA_ROLE_PRIVS
          WHERE GRANTEE IN ('SYS');

3. Role에 부여된 Privileges 조회

SELECT GRANTEE, PRIVILEGE FROM DBA_SYS_PRIVS
          WHERE GRANTEE IN ('DBA');

4. User의 객체에 대한 다른 User들의 Privileges Script 생성

 SELECT 'GRANT '||PRIVILEGE||' ON '||OWNER||'.'||TABLE_NAME||' TO '||GRANTEE||';'
           FROM DBA_TAB_PRIVS
           WHERE OWNER IN ('USER1')
           ORDER BY OWNER, TABLE_NAME, GRANTEE, PRIVILEGE;