GRANT { grant_system_privileges
| grant_object_privileges
} ;
grant_system_privileges::=
{ system_privilege
| role
| ALL PRIVILEGES
}
[, { system_privilege
| role
| ALL PRIVILEGES
}
]...
TO grantee_clause
[ WITH ADMIN OPTION ]
grant_object_privileges::=
{ object_privilege | ALL [ PRIVILEGES ] }
[ (column [, column ]...) ]
[, { object_privilege | ALL [ PRIVILEGES ] }
[ (column [, column ]...) ]
]...
on_object_clause
TO grantee_clause
[ WITH HIERARCHY OPTION ]
[ WITH GRANT OPTION ]
on_object_clause ::=
ON { [ schema. ] object
| DIRECTORY directory_name
| EDITION edition_name
| MINING MODEL [schema.] mining_model_name
| JAVA { SOURCE | RESOURCE } [ schema. ] object
}
grantee_clause ::=
{ user [ IDENTIFIED BY password ]
| role
| PUBLIC
}
[, { user [ IDENTIFIED BY password ]
| role
| PUBLIC
}
]...
Example
GRANT
CREATE ANY MATERIALIZED VIEW
, ALTER ANY MATERIALIZED VIEW
, DROP ANY MATERIALIZED VIEW
, QUERY REWRITE
, GLOBAL QUERY REWRITE
TO dw_manager
WITH ADMIN OPTION;
GRANT dw_manager
TO sh
WITH ADMIN OPTION;
GRANT SELECT ON sh.sales TO warehouse_user;
GRANT warehouse_user TO dw_manager;
GRANT READ ON DIRECTORY bfile_dir TO hr
WITH GRANT OPTION;
GRANT ALL ON bonuses TO hr
WITH GRANT OPTION;