การกำหนดให้ Developer ไม่สามารถแก้ไขโครงสร้างตาราง

ขอบคุณคุณ Somritt Thiendej สำหรับคำถาม
"โปรแกรมเมอร์ใช้ toad ต่อ oracle db ทำอย่างไรจะไม่ให้สามารถแก้ไข ฟิล เพิ่มฟิล ในตารางได้?"

ปกติ User บนฐานข้อมูลจะมีสิทธิ์สร้างตาราง และเปลี่ยนแปลงตารางที่เขาสร้างได้ ถ้าเราต้องการให้ User หรือ Developer มีสิทธิ์เฉพาะการจัดการข้อมูลในตาราง แต่ไม่มีสิทธิ์ในการแก้ไขโครงสร้าง เราจะต้องให้ User หรือ Developer ล็อกอินด้วย User อื่น แล้วกำหนดสิทธิ์เฉพาะการจัดการข้อมูลในตารางให้เขาดังตัวอย่างข้างล่างนี้ครับ

สมมติว่าตารางที่ต้องให้ Developer ใช้อยู่ใน Schema "account" สังเกตว่าถ้าเราล็อกอินด้วย User "account" เราจะสามารถสร้างตาราง,เปลี่ยนแปลงโครงสร้างตารางที่สร้าง และเปลี่ยนแปลงข้อมูลในตารางได้

===========================================================
Listing1: User "account" สามารถสร้างและจัดการโครงสร้างตารางที่ตนเองสร้างขึ้นได้เสมอ เราไม่สามารถห้ามได้

SQL> conn system
Connected.

SQL> create user account2 identified by account;

User created.

SQL> grant connect,resource to account2;

Grant succeeded.

SQL> create table table1 (id number,name varchar2(20));

Table created.

SQL> alter table table1 add address varchar2(50);

Table altered.

SQL> desc table1;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(20)
ADDRESS VARCHAR2(50)

SQL> drop table table1;

Table dropped.

SQL> create table table2(id number,name varchar2(30));

Table created.

SQL> insert into table2 values (1,'Somchai');

1 row created.

SQL> insert into table2 values (2,'Somkid');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from table2;

ID NAME
---------- ------------------------------
1 Somchai
2 Somkid

ต่อมาเราสร้าง User ชื่อ accapp ที่จะให้ Developer ใช้ในการ Access ฐานข้อมูล จากนั้นเรา Grant สิทธิ์ให้ Connect ได้ และสามารถใช้ทรัพยากรของระบบได้ (เช่นสามารถสร้างตารางของตัวเองได้) Resource เป็นสิทธิ์ที่ให้พื้นที่กับ User แบบไม่จำกัด จากนั้นเราก็ ล็อกอินด้วย User ที่มีสิทธิ์ DBA หรือล็อกอินด้วย User "account" แล้ว Grant สิทธิ์ทุกอย่างบนตาราง Table2 ให้กับ accapp เมื่อเราล็อกอินเป็น accapp แล้วคิวรีดูจาก USER_TAB_PRIVS ซึ่งแสดงสิทธิ์ของ User ที่ล็อกอินอยู่ว่ามีสิทธิ์อะไรบนตารางไหนบ้าง เนื่องจากเราได้ Grant All ไว้เราจะเห็นว่า accapp ได้สิทธิ์ทุกสิทธิ์บนตาราง Table2 ของ "account" เราสามารถจะลบสิทธิ์บางตัวที่ไม่ต้องการออก โดยการใช้คำสั่ง Revoke หรือจะใช้วิธีการ Grant เฉพาะสิทธิ์ที่ต้องการให้กับ Developer ก็ได้ เช่นถ้าต้องการให้ Developer จัดการกับข้อมูลในตารางได้เท่านั้นก็ใช้คำสั่ง "grant insert,update,delete on table2 to accapp" แทนที่จะใช้คำสั่ง grant all เป็นต้น ข้อสำคัญอย่าไป grant สิทธิ์ DBA ให้กับ User ก็แล้วกันนะครับ :)

===========================================================
Listing2: User "accapp" จะสามารถจัดการตารางของ User (หรือ Schema) อื่น ได้เท่าที่ได้รับสิทธิ์

SQL> conn system
Connected.
SQL> create user accapp identified by accapp;

User created.

SQL> grant connect,resource to accapp;

Grant succeeded.

SQL> grant all on account.table2 to accapp;

Grant succeeded.

SQL> conn accapp
Connected.
SQL> column owner format a15
SQL> column table_name format a15
SQL> column privilege format a30
SQL> select owner ,table_name ,privilege from user_tab_privs;

OWNER TABLE_NAME PRIVILEGE
--------------- --------------- ------------------------------
ACCOUNT TABLE2 FLASHBACK
ACCOUNT TABLE2 DEBUG
ACCOUNT TABLE2 QUERY REWRITE
ACCOUNT TABLE2 ON COMMIT REFRESH
ACCOUNT TABLE2 REFERENCES
ACCOUNT TABLE2 UPDATE
ACCOUNT TABLE2 SELECT
ACCOUNT TABLE2 INSERT
ACCOUNT TABLE2 INDEX
ACCOUNT TABLE2 DELETE
ACCOUNT TABLE2 ALTER

11 rows selected.

SQL> select * from account.table2;

ID NAME
---------- ------------------------------
1 Somchai
2 Somkid

-- คำสั่งข้างล่างแสดงให้เห็นว่าตอนนี้ accapp สามารถเปลี่ยนโครงสร้างของ table2 ได้
SQL> alter table account.table2 add test number;

Table altered.

SQL> conn account
Connected.
SQL> revoke alter on table2 from accapp;

Revoke succeeded.

SQL> conn accapp
Connected.
--หลังจากที่เรา Revoke สิทธิ์ในการแก้ไขตารางออกไปแล้ว accapp ไม่สามารถแก้ไขตารางได้
SQL> alter table account.table2 drop column test;
alter table account.table2 drop column test
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> alter table account.table2 add testcol varchar2(30);
alter table account.table2 add testcol varchar2(30)
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> drop table account.table2;
drop table account.table2
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> conn accapp
Connected.

SQL> desc account.table2;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(30)
TEST NUMBER

SQL> show user
USER is "ACCAPP"
SQL> conn system
Connected.
SQL> alter table account.table2 drop column TEST;

Table altered.

SQL> conn accapp
Connected.
SQL> select * from account.table2;

ID NAME
---------- ------------------------------
1 Somchai
2 Somkid

SQL> insert into account.table2 values (3,'Somkiat');

1 row created.

SQL> select * from account.table2 ;

ID NAME
---------- ------------------------------
1 Somchai
2 Somkid
3 Somkiat

SQL> update account.table2 set name = 'XXXX' where id = 2;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from account.table2;

ID NAME
---------- ------------------------------
1 Somchai
2 XXXX
3 Somkiat

SQL> delete account.table2 where id = 3;

1 row deleted.

SQL> commit;

Commit complete.

คุณอาจจะพบว่าการ Grant สิทธิ์ทีละตารางนี้เป็นเรื่องยากถ้ามีเป็นจำนวนมาก เราอาจจะใช้วิธีการใช้ SQL สร้างคำสั่งเป็นสคริปต์แล้วไปรันอีกทีเช่น

SQL> conn account
connected.

SQL> set heading off
SQL> select 'grant all on ' || table_name || ' to accapp;' from user_tables;