| Security Advisory |
Object privileges specific to actions that can be taken with respect to a single named object.
Individually, they are, in general, far less dangerous than are system privileges as an object privilege can, unless there are triggers or replication, only damage a single object.
Granting an object privilege for a DBA is the same as punching a hole in the firewall for a network admin. Every object grant made has an associated risk. |
| Recommended Security Rules |
NEVER
- grant connection access to an application schema, instead grant access to an empty schema that has privileges to the application schema
- grant with the grantable option
- grant with admin rights
- grant UPDATE on a table if updating any column, for example a primary key, could result in a loss of data integrity
WITH GREAT CARE
- grant SELECT on a table. If SELECT FOR UPDATE locking is not required grant READ instead
CAUTIONS
- In the overwhelming majority of databases granting DELETE is not necessary. Carefully evaluate every request.
|
| Data Dictionary Objects |
| ALL_COL_PRIVS |
CDB_TAB_PRIVS |
TABLE_PRIVILEGE_MAP |
| ALL_COL_PRIVS_MADE |
COLUMN_PRIVILEGES |
USER_COL_PRIVS |
| ALL_COL_PRIVS_RECD |
DBA_COL_PRIVS |
USER_COL_PRIVS_MADE |
| ALL_TAB_PRIVS |
DBA_TAB_PRIVS |
USER_COL_PRIVS_RECD |
| ALL_TAB_PRIVS_MADE |
OBJAUTH$ |
USER_TAB_PRIVS |
| ALL_TAB_PRIVS_RECD |
OBJPRIV$ |
USER_TAB_PRIVS_MADE |
| CDB_COL_PRIVS |
TABLE_PRIVILEGES |
USER_TAB_PRIVS_RECD |
|
| |
| WITH GRANT OPTION Vulnerability Screening |
The code at right should be run daily or weekly to identify existing or newly created vulnerabilities utilizing the WITH GRANT OPTION clause.
To screening code is followed by a demo of how easily this clause can be exploited to weaken database security. |
col grantee format a30
col obj_name format a30
col privilege format a30
col grantable format a9
For a legacy architecture database
SELECT dtp.grantee, dtp.table_name AS OBJ_NAME, dtp.privilege, dtp.grantable
FROM dba_tab_privs dtp
WHERE dtp.grantee NOT IN ('PUBLIC')
AND dtp.grantable = 'YES'
ORDER BY 1,2,3;
For a container database
SELECT ctp.grantee, ctp.table_name AS OBJ_NAME, ctp.privilege, ctp.grantable, ctp.con_id
FROM cdb_tab_privs ctp
WHERE ctp.grantee NOT IN ('PUBLIC')
AND ctp.grantable = 'YES'
ORDER BY 1,2,3, 4;
Any additions to these listings should be cause for concern.
Change the above statements to filter for rather than exclude PUBLIC as the grantee and you see a very different picture
SELECT dtp.grantee, dtp.table_name, dtp.privilege, dtp.grantable
FROM dba_tab_privs dtp
WHERE dtp.grantee IN ('PUBLIC')
AND dtp.grantable = 'YES'
ORDER BY 1,2,3;
Oracle has given the WITH GRANT OPTION to more than one thousand objects to PUBLIC.
This is certainly nonsensical as the underlying privileges have already been granted to PUBLIC so there is no new vulnerability created by being able to grant them further because every connected user is PUBLIC?
But this does illustrate how easily a major risk factor can slip under the radar.
Would any sane DBA would want PUBLIC to be able to grant privs to other users?
Further explore the WITH GRANT OPTION as a security risk using the following demo. In this demo SYS creates two tables and grants READ to what should be the least privileged account in any database:
SCOTT (though SCOTT should be dropped in any database not used for education/training). The privilege on the first table is properly granted.
The same privilege on the second table gives SCOTT the ability to further grant it to anyone with the CREATE SESSION privilege.
SQL> conn / as sysdba
SQL> CREATE TABLE more_secure AS SELECT * FROM dual;
SQL> GRANT READ ON more_secure TO scott;
SQL> CREATE TABLE less_secure AS SELECT * FROM dual;
SQL> GRANT READ ON less_secure TO scott WITH GRANT OPTION;
SQL> SQL> conn scott/tiger
Connected.
SQL> grant read on sys.more_secure to sh;
grant read on sys.more_secure to sh
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> grant read on sys.less_secure to csh;
Grant succeeded.
Use the SQL statements above to detect any unapproved usages of the WITH GRANT OPTION clause. |
| |
| INHERITED Privileges Vulnerability Screening |
The code at right should be run daily or weekly to identify existing or newly created vulnerabilities utilizing the WITH GRANT OPTION clause.
To screening code is followed by a demo of how easily this clause can be exploited to weaken database security. |
col grantee format a30
col owner format a30
col obj_name format a30
col grantor format a30
For a legacy architecture database
SELECT dtp.grantee, dtp.owner, dtp.table_name AS OBJ_NAME, dtp.grantor
FROM dba_tab_privs dtp
WHERE dtp.privilege LIKE '%INHERIT%'
ORDER BY 1,2,3;
For a container database
SELECT ctp.grantee, ctp.owner, ctp.table_name AS OBJ_NAME, ctp.grantor, ctp.con_id
FROM cdb_tab_privs ctp
WHERE ctp.privilege LIKE '%INHERIT%'
ORDER BY 6,1,2,3;
Any additions to these listings should be cause for concern.
Change the above statements to filter for rather than exclude PUBLIC as the grantee and you see a very different picture
SELECT dtp.grantee, dtp.table_name, dtp.privilege, dtp.grantable
FROM dba_tab_privs dtp
WHERE dtp.grantee IN ('PUBLIC')
AND dtp.grantable = 'YES'
ORDER BY 1,2,3;
Oracle has given the WITH GRANT OP
Use the SQL statements above to detect any unapproved usages of the WITH GRANT OPTION clause. |
| |
| UPDATE Privileges Vulnerability Screening |
The ability to any column in a row contains an inherent high risk. If the primary key is changed referential integrity will be severely compromised.
And not just compromised in your primary production environment but also in your next backup, in DR, in exports, and in records transferred through replication.
The demo at right will help you gain a better understanding of how this risk might play out in your database and how dangerous the UPDATE privilege is.
The testing code at the bottom of the dmeo should be run daily or weekly to identify existing or newly created vulnerabilities created by granting the UPDATE privilege.
|
Oracle has given the WITH GRANT OP
conn / as sysdba
CREATE USER app IDENTIFIED BY app
DEFAULT TABLESPACE users
QUOTA 1M ON users;
GRANT create session, create table TO app;
CREATE USER appuser IDENTIFIED BY appuser;
GRANT create session TO appuser;
conn app/app@pdbdev
CREATE TABLE master (
tx_id NUMBER,
tx_dt DATE);
ALTER TABLE master ADD PRIMARY KEY (tx_id);
CREATE TABLE detail (
tx_id NUMBER,
part_id NUMBER);
ALTER TABLE detail ADD PRIMARY KEY (tx_id, part_id);
ALTER TABLE detail ADD FOREIGN KEY
(tx_id) REFERENCES master(tx_id);
INSERT INTO master (tx_id, tx_dt) VALUES (1, SYSDATE-1);
INSERT INTO master (tx_id, tx_dt) VALUES (2, SYSDATE+1);
INSERT INTO detail (tx_id, part_id) VALUES (1, 100);
INSERT INTO detail (tx_id, part_id) VALUES (1, 200);
INSERT INTO detail (tx_id, part_id) VALUES (1, 300);
INSERT INTO detail (tx_id, part_id) VALUES (2, 400);
COMMIT;
SELECT * FROM master;
SQL> SELECT * FROM master;
TX_ID TX_DT
----- --------------------
1 24-MAY-2019 10:17:16
2 26-MAY-2019 10:17:25
SQL> SELECT * FROM detail;
TX_ID PART_ID
----- -------
1 100
1 200
1 300
2 400
GRANT read, update ON detail TO app_user;
USER_TAB_PRIVS_MADE
conn appuser/appuser@pdbdev
SQL> SELECT * FROM app.detail;
TX_ID PART_ID
----- -------
1 100
1 200
1 300
2 400
UPDATE sys.detail SET tx_id = 2, part_id = part_id+1;
COMMIT;
SQL> SELECT * FROM app.detail;
TX_ID PART_ID
----- -------
2 101
2 201
2 301
2 401
The part numbers were updated but clearly data integrity has been destroyed because transaction 1 no longer has any detail records. Let's go back and fix the privileges and do it correctly.
conn app/app@pdbdev
SQL> REVOKE update ON detail FROM appuser;
SQL> GRANT update(part_id) ON detail TO appuser;
And, as the application user, try the original update statement again: it will fail.
conn appuser/appuser@pdbdev
UPDATE sys.detail SET tx_id = 2, part_id = part_id+1;
<demo of failure from Linux>
Granting minimum required privilege means granting update at the column level and protecting the referential integrity of your data.
SQL> SELECT * FROM app.detail;
TX_ID PART_ID
----- -------
2 102
2 202
2 302
2 402
The following SQL statement can be used to monitor for granting of the UPDATE statement so that you can evaluate whether security has been compromised, revoke the grant and replace it with an column based update privilege.
SELECT dtp.grantee, dtp.owner, dtp.table_name
FROM dba_tab_privs dtp
WHERE dtp.privilege = 'UPDATE'
AND owner NOT IN ('ANONYMOUS', 'APPQOSSYS', 'AUDSYS', 'CTXSYS', 'DBSFWUSER', 'DBSNMP', 'DIP', 'DVF', 'DVSYS', 'GGSYS',
'GSMADMIN_INTERNAL', 'GSMCATUSER', 'GSMUSER', 'LBACSYS', 'MDDATA', 'MDSYS', 'OJVMSYS', 'OLAPSYS', 'ORACLE_OCM', 'ORDDATA',
'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'REMOTE_SCHEDULER_AGENT', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYS$UMF', 'SYSBACKUP', 'SYSDG',
'SYSKM', 'SYSRAC', 'SYSTEM', 'WMSYS', 'XDB', 'XS$NULL')
ORDER BY 1,2,3;
If rows are returned you should open a ticket and investigate each one. |
| |
| PRIVILEGE |
APPLIES TO |
| ALTER |
- ANALYTIC VIEW
- ATTRIBUTE DIMENSION
- HIERARCHY
- MINING MODEL
- OLAP
- SCHEDULER
- SEQUENCE
- SQL TRANSLATION PROFILE
- TABLE
|
| AUDIT |
Present in TABLE_PRIVILEGE_MAP but apparently not in use as an object privilege |
| COMMENT |
- AUDIT UNIFIED POLICY
- COLUMN
- EDITION
- INDEXTYPE
- MATERIALIZED VIEW
- OPERATOR
- TABLE
|
| CREATE |
Present in TABLE_PRIVILEGE_MAP but apparently not in use as an object privilege |
| DEBUG |
- FUNCTION
- PACKAGE
- FUNCTION
- TABLE
- VIEW
|
| DELETE |
|
| DEQUEUE |
|
| ENQUEUE |
|
| EXECUTE |
- DIRECTORY
- FUNCTION
- INDEXTYPE
- LIBRARY
- JAVA CLASS
- JAVA RESOURCE
- JOB CLASS
- OBJECT TYPE
- OPERATOR
- PACKAGE
- PROCEDURE
- RESOURCE CONSUMER GROUP
- SCHEDULER
- TYPE
- UNKNOWN
|
| FLASHBACK |
|
| FLASHBACK ARCHIVE |
|
| GRANT |
Present in TABLE_PRIVILEGE_MAP but apparently not in use as an object privilege |
| INDEX |
|
| INHERIT PRIVILEGES |
|
| INHERIT REMOTE PRIVILEGES |
|
| INSERT |
|
| KEEP SEQUENCE |
|
| LOCK |
Present in TABLE_PRIVILEGE_MAP but apparently not in use as an object privilege |
| MERGE VIEW |
|
| ON COMMIT REFRESH |
|
| QUERY REWRITE |
|
| READ |
- ANALYTIC VIEW
- DIRECTORY
- HIERARCHY
- MATERIALIZED VIEW
- TABLE
- VIEW
|
| REFERENCE |
|
| RENAME |
Present in TABLE_PRIVILEGE_MAP but apparently not in use as an object privilege |
| SELECT |
- ANALYTIC VIEW
- HIERARCHY
- MATERIALIZED VIEW
- MINING MODEL
- OLAP
- SEQUENCE
- TABLE
- VIEW
|
| TRANSLATE SQL |
|
| UNDER |
|
| UPDATE |
|
| USE |
- EDITION
- SCHEDULER
- SQL TRANSLATION PROFILE
|
| WRITE |
|
| |
| Syntax & Demos: Granting Object Privileges |
| Grant A Single Privilege |
GRANT <privilege_name> ON <object_name> TO <schema_name>; |
conn uwclass/uwclass@pdbdev
CREATE TABLE test (
testcol VARCHAR2(20));
GRANT SELECT ON test TO abc;
set linesize 100
col grantee format a30
col table_name format a30
col privilege format a20
SELECT grantee, table_name, privilege
FROM user_tab_privs_made;
conn abc/abc
SELECT grantor, table_name, privilege
FROM user_tab_privs_recd; |
| Grant Multiple Privileges |
GRANT <privilege_name_list> ON <object_name> TO <schema_name>; |
conn uwclass/uwclass@pdbdev
GRANT INSERT, DELETE ON test TO abc;
SELECT grantee, table_name, privilege
FROM user_tab_privs_made;
conn abc/abc
SELECT grantor, table_name, privilege
FROM user_tab_privs_recd; |
| Grant All Privileges |
GRANT ALL ON <object_name> TO <schema_name>; |
conn abc/abc@pdbdev
GRANT ALL ON test TO uwclass;
SELECT grantee, table_name, privilege
FROM user_tab_privs_made;
conn uwclass/uwclass
SELECT grantor, table_name, privilege
FROM user_tab_privs_recd; |
| Grant EXECUTE |
GRANT EXECUTE ON <object_name> TO <schema_name>; |
conn uwclass/uwclass@pdbdev
GRANT EXECUTE ON getosuser TO abc;
SELECT grantee, table_name, privilege
FROM user_tab_privs_made;
conn abc/abc
SELECT grantor, table_name, privilege
FROM user_tab_privs_recd; |
| |
| Syntax & Demos: Revoking Object Privileges |
| Revoke A Single Privilege |
REVOKE <privilege_name> ON <object_name> FROM <schema_name>; |
conn uwclass/uwclass@pdbdev
REVOKE SELECT ON test FROM abc;
SELECT grantee, table_name, privilege
FROM user_tab_privs_made;
conn abc/abc
SELECT grantor, table_name, privilege
FROM user_tab_privs_recd; |
| Revoke Multiple Privileges |
REVOKE <privilege_name_list> ON <object_name> FROM <schema_name>; |
conn uwclass/uwclass@pdbdev
REVOKE INSERT, DELETE ON test FROM abc;
SELECT grantee, table_name, privilege
FROM user_tab_privs_made;
conn abc/abc
SELECT grantor, table_name, privilege
FROM user_tab_privs_recd; |
| Revoke All Privileges |
REVOKE ALL ON <object_name> FROM <schema_name>; |
conn uwclass/uwclass@pdbdev
REVOKE ALL ON test FROM abc;
SELECT grantee, table_name, privilege
FROM user_tab_privs_made;
conn abc/abc
SELECT grantor, table_name, privilege
FROM user_tab_privs_recd; |
| Revoke EXECUTE |
REVOKE EXECUTE ON <object_name> FROM <schema_name>; |
conn uwclass/uwclass@pdbdev
REVOKE EXECUTE ON getosuser FROM abc;
SELECT grantee, table_name, privilege
FROM user_tab_privs_made;
conn abc/abc
SELECT grantor, table_name, privilege
FROM user_tab_privs_recd; |
| |
| Syntax & Demos: Column Level Privileges |
| Grant Column Privileges |
GRANT <privilege_name> (<column_name>) ON <table_name> TO <schema_name>; |
GRANT UPDATE (first_name, last_name) ON person TO uwclass; |
| Revoking Column Privileges |
REVOKE <privilege_name> (<column_name>) ON <table_name> FROM <schema_name>; |
REVOKE UPDATE (first_name, last_name) ON person FROM uwclass; |
| |
| Related Queries |
| Show privileges by object granted to users |
set linesize 141
col select_priv format a11
col insert_priv format a11
col update_priv format a11
col delete_priv format a11
col execute_priv format a12
SELECT table_name, grantee,
MAX(DECODE(privilege, 'SELECT', 'SELECT')) AS select_priv,
MAX(DECODE(privilege, 'DELETE', 'DELETE')) AS delete_priv,
MAX(DECODE(privilege, 'UPDATE', 'UPDATE')) AS update_priv,
MAX(DECODE(privilege, 'INSERT', 'INSERT')) AS insert_priv,
MAX(DECODE(privilege, 'EXECUTE', 'EXECUTE')) AS execute_priv
FROM dba_tab_privs
WHERE grantee IN (
SELECT username
FROM dba_users)
GROUP BY table_name, grantee
ORDER BY 2,1; |
| Show privileges by object granted to roles |
set linesize 141
col select_priv format a11
col insert_priv format a11
col update_priv format a11
col delete_priv format a11
col execute_priv format a12
SELECT table_name, grantee,
MAX(DECODE(privilege, 'SELECT', 'SELECT')) AS select_priv,
MAX(DECODE(privilege, 'DELETE', 'DELETE')) AS delete_priv,
MAX(DECODE(privilege, 'UPDATE', 'UPDATE')) AS update_priv,
MAX(DECODE(privilege, 'INSERT', 'INSERT')) AS insert_priv,
MAX(DECODE(privilege, 'EXECUTE', 'EXECUTE')) AS execute_priv
FROM dba_tab_privs
WHERE grantee IN (
SELECT role
FROM dba_roles)
GROUP BY table_name, grantee
ORDER BY 2,1; |