oracle的权限授予,在Oracle模式上授予用户所有权限

您可以在循环中执行此操作并通过动态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;

/