There are two of privileges are available in Oracle.
- Object Privileges: Object privileges can be granted as Object Level.
- Example, You will able to provide delete privileges for particular table. User will able to delete records from that particular table only.
- System Privileges: System Privileges are very powerful privileges as you can’t grant at object level like Object Privileges. You have to provide the privileges as database level.
- Let say, DBA need to grant a user the drop or truncate table means user will able to drop or truncate any table from any schema in database.
- Don’t recommended to provide this kind of privileges to any user as these are very risky privileges. But sometimes need to provide for business criticality.
I will show how can control these kind of access:
Create two users – MIKE and JOHN. If need to drop any table from MIKE’s Schema by John, then John should has this system privileges ‘ DROP ANY TABLE’ otherwise John will not able to drop table from Mike’s Schema.
Once John has this privileges (DROP ANY TABLE), John will able to drop any table from any schema in database and this is the main concern.
Create User Mike and Grant him to create table.
SQL> CREATE USER MIKE IDENTIFIED BY mike5487 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK; User created. SQL> GRANT CREATE SESSION TO MIKE; Grant succeeded. SQL> ALTER USER MIKE DEFAULT ROLE ALL; Grant succeeded. SQL> GRANT SELECT ANY TABLE TO MIKE; Grant succeeded. SQL> GRANT CREATE TABLE TO MIKE; Grant succeeded. SQL> ALTER USER JOHN QUOTA UNLIMITED ON USERS; User altered.
Create another user John and he should able create table and also Select Any Table from database.
SQL> CREATE USER JOHN IDENTIFIED BY JOHN587 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK; User created. SQL> GRANT CREATE SESSION TO JOHN; Grant succeeded. SQL> ALTER USER JOHN DEFAULT ROLE ALL; Grant succeeded. SQL> GRANT SELECT ANY TABLE TO MIKE; Grant succeeded. SQL> GRANT CREATE TABLE TO JOHN; Grant succeeded. SQL> ALTER USER JOHN QUOTA UNLIMITED ON USERS; User altered.
Let see what can be done by John once he has ‘DROP ANY TABLE’ privileges. We see that John is able to drop table from Mike and HR Schemas in database.
SQL> SHOW USER
USER is "JOHN"
SQL> DROP TABLE MIKE.TEST;
DROP TABLE MIKE.TEST
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> DROP TABLE JOHN.TEST;
Table dropped.
SQL> conn / as sysdba
Connected.
SQL> GRANT DROP ANY TABLE TO JOHN;
Grant succeeded.
SQL> conn JOHN/JOHN587
Connected.
SQL> DROP TABLE MIKE.TEST;
Table dropped.
SQL> DROP TABLE HR.EMPLOYEES;
Table dropped.
SQL> conn / as sysdba
Connected.
SQL> REVOKE DROP ANY TABLE FROM JOHN;
Create a table within privileged schema like SYS or SYSTEM to make sure no one else has access to insert data into this table. In this Example, I created a table called TBL_DROP_CONTROL in SYSTEM schema.
- SYSTEM.TBL_DROP_CONTROL
- SCHEMA_NAME – Owner of the table which table need to drop by a user
- TABLE_NAME – Table Name which table need to drop by a user
- DROP_ACCESS_SCHEMA – User or Schema name who need to has the privilege to drop the table.
SQL> CONN / AS SYSDBA Connected. SQL> CREATE TABLE SYSTEM.TBL_DROP_CONTROL(SCHEMA_NAME VARCHAR (30), TABLE_NAME VARCHAR (30), DROP_ACCESS_SCHEMA VARCHAR (30)); Table created. SQL> ALTER TABLE SYSTEM.TBL_DROP_CONTROL ADD CONSTRAINT system_tbl_drop_control_constr UNIQUE (DROP_ACCESS_SCHEMA,SCHEMA_NAME,TABLE_NAME); Table altered. SQL> INSERT INTO SYSTEM.TBL_DROP_CONTROL VALUES( 'MIKE', 'TBL_TEST', 'JOHN'); 1 row created. SQL> COMMIT; Commit complete. SQL> SET LINESIZE 200 SQL> SELECT * FROM SYSTEM.TBL_DROP_CONTROL; SCHEMA_NAME TABLE_NAME DROP_ACCESS_SCHEMA ------------------------------ ------------------------------ ------------------------------ MIKE TBL_TEST JOHN
Create a procedure which will check the control table (created by above step) and if match the request it will allow to drop the table.
CREATE OR REPLACE PROCEDURE MIKE.DROP_CONTROL (pSCHEMA_NAME VARCHAR, pTABLE_NAME VARCHAR)
IS
V_COUNT NUMBER;
BEGIN
SELECT TO_NUMBER(NVL(COUNT(*),0)) INTO V_COUNT
FROM SYSTEM.TBL_DROP_CONTROL
WHERE UPPER(DROP_ACCESS_SCHEMA) = sys_context('USERENV','SESSION_USER') AND UPPER(SCHEMA_NAME)= UPPER(pSCHEMA_NAME) AND UPPER(TABLE_NAME)=UPPER(pTABLE_NAME);
IF (V_COUNT = 0) THEN
DBMS_OUTPUT.PUT_LINE('TABLE DID NOT MATCH TO DROP OR HAS NO PRIVILEGE');
ELSIF (V_COUNT = 1) THEN
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE '||UPPER(TRIM(pSCHEMA_NAME))||'.'||UPPER(TRIM(pTABLE_NAME));
DBMS_OUTPUT.PUT_LINE('TABLE ' || UPPER(TRIM(pSCHEMA_NAME))||'.'||UPPER(TRIM(pTABLE_NAME)) || ' DROPPED SUCCESSFULLY!!!');
END;
END IF;
END;
/
Procedure created.
SQL> GRANT EXECUTE ON MIKE.DROP_CONTROL TO JOHN;
Grant succeeded.
There are two tables (tbl_test and tbl_test_mike) on Mike Schema. Table tbl_test has been included on drop control table so john should able to drop this table only.
SQL> SHOW USER
USER is "JOHN"
SQL> SELECT * FROM MIKE.TBL_TEST;
ID
----------
1
SQL> SELECT * FROM MIKE.TBL_TEST_MIKE;
ID NAME
---------- --------------------
1 JASON
SQL> SELECT * FROM SYSTEM.TBL_DROP_CONTROL;
SCHEMA_NAME TABLE_NAME DROP_ACCESS_SCHEMA
------------------------------ ------------------------------ ------------------------------
MIKE TBL_TEST JOHN
Lets try to drop the table – MIKE.TBL_TEST_MIKE from John user but John should not able to drop this table as this table information has not included on TBL_DROP_CONTROL Table. But Table TBL_TEST should be dropped by John.
SQL> SET SERVEROUT ON
SQL> EXEC MIKE.DROP_CONTROL ('MIKE','TBL_TEST_MIKE');
TABLE DID NOT MATCH TO DROP OR HAS NO PRIVILEGE
PL/SQL procedure successfully completed.
SQL> SELECT * FROM MIKE.TBL_TEST_MIKE;
ID NAME
---------- --------------------
1 JASON
SQL> SET SERVEROUT ON
SQL> EXEC MIKE.DROP_CONTROL ('MIKE','TBL_TEST');
TABLE MIKE.TBL_TEST DROPPED SUCCESSFULLY!!!
PL/SQL procedure successfully completed.
SQL> SELECT * FROM MIKE.TBL_TEST;
SELECT * FROM MIKE.TBL_TEST
*
ERROR at line 1:
ORA-00942: table or view does not exist
NOTE::: if you want to give drop privileges for other schema’s table (i.e. MIKE need to drop some HR schema table) then need to create same procedure for that schema (i.e. HR schema) and insert table and schema information into SYSTEM.TBL_DROP_CONTROL Table.
