授权用户在Oracle模式上拥有所有权限

Question

有没有一种方法可以授予用户在Oracle模式上的所有权限？我尝试了以下命令，但它只授予特定模式中特定表的权限。我想要的是在给定模式上给予这个用户所有权限。

GRANT ALL ON MyTable TO MyUser;

- Frank Martin

Answer 1

你可以使用循环和动态 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;
/

- Wernfried Domscheit

---

最好循环遍历ALL_TABLES ... UNION ALL ... ALL_VIEWS UNION ALL ... ALL_SNAPSHOTS。 - Wernfried Domscheit

Answer 2

如果您想授予特定模式中所有表的权限：

    BEGIN
    FOR x IN (select *from all_tables where OWNER = 'schema name')
    LOOP   
     EXECUTE IMMEDIATE 'GRANT SELECT ON '||x.OWNER||'.'|| x.table_name || TO 'user name'; 
    END LOOP;
    END;

- user2372670

Answer 3

begin
  for x in (select *from all_tables where owner = 'SYS')
  loop   
    execute immediate 'grant select on '||x.owner||'.'|| x.table_name || ' to ' || 'your_user';
  end loop;
end;

- Lucas Caminha

---

您的回答可以通过提供额外的支持性信息来改善。请 [编辑] 添加更多细节，例如引用或文档，以便他人可以确认您的答案正确。您可以在 帮助中心 中找到有关如何撰写良好答案的更多信息。 - Community