向用户授予Oracle模式上的所有特权 [英] Grant all privileges to user on Oracle schema

问题描述

是否可以在Oracle模式上向用户授予所有特权?我尝试了以下命令，但它仅授予对架构中特定表的权限.我想要给该用户所有给定架构的权限.

Is there a way to grant all privileges to a user on Oracle schema? I tried the following command but it only grants permission on specific tables in a schema. What I want is to give this user all permissions on a given schema.

GRANT ALL ON MyTable TO MyUser;

推荐答案

您可以循环执行并通过动态SQL授予权限:

You can do it in a loop and grant by dynamic 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;
/

这篇关于向用户授予Oracle模式上的所有特权的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！