SYS/SYSTEM Users and ORA-01031 (Prior to Oracle 9.2)
SYS and SYSTEM users have DBA roles so they manipulate any object on database, but if you are using an Oracle DB prior to 9.2, you’ll see that DBA role is not enough to manipulate the privileges of other users’ objects.
Because before Oracle 9.2, there was not a system privilege called “GRANT ANY OBJECT PRIVILEGE”. For example, you can not run the following query on Oracle 8i (even with SYS):
1 2 3 4 |
GRANT SELECT ON hr.employees TO gokhan; ERROR at line 1: ORA-01031: insufficient privileges |
Here’s a trick to overcome this problem: We create a temporary procedure under the target schema:
1 2 3 4 5 |
CREATE PROCEDURE hr.grant_priv AS BEGIN EXECUTE IMMEDIATE 'GRANT SELECT ON hr.employees TO gokhan'; END; EXEC hr.grant_priv; |
After we grant the required privileges, we can drop the procedure:
1 |
DROP PROCEDURE hr.grant_priv; |