有没有办法在Oracle模式上为用户授予所有权限？我尝试了以下命令,但它只授予模式中特定表的权限.我想要的是为该用户授予给定模式的所有权限.
GRANT ALL ON MyTable TO MyUser;
解决方法
您可以在循环中执行此操作并通过动态sql进行授予：
BEGIN
FOR objects IN
(
SELECT 'GRANT ALL ON "'||owner||'"."'||object_name||'" TO MyUser' grantsql
FROM all_objects
WHERE owner = 'MY_SCHEMA'
AND object_type NOT IN
(
--Ungrantable objects. Your schema may have more.
'SYNONYM','INDEX','INDEX PARTITION','DATABASE LINK','LOB','TABLE PARTITION','TRIGGER'
)
ORDER BY object_type,object_name
) LOOP
BEGIN
EXECUTE IMMEDIATE objects.grantsql;
EXCEPTION WHEN OTHERS THEN
--Ignore ORA-04063: view "X.Y" has errors.
--(You could potentially workaround this by creating an empty view,-- granting access to it,and then recreat the original view.)
IF sqlCODE IN (-4063) THEN
NULL;
--Raise exception along with the statement that Failed.
ELSE
raise_application_error(-20000,'Problem with this statement: ' ||
objects.grantsql || CHR(10) || sqlERRM);
END IF;
END;
END LOOP;
END;
/
