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;

/