Oracle DB 18 c-撤销用户访问其他用户的PUBLIC模式的权限

8aqjt8rx  于 2023-06-22  发布在  Oracle
关注(0)|答案(1)|浏览(204)

我按照以下脚本为Oracle db(18c,版本18.0.0.0.0)创建了db用户:

CREATE USER username  IDENTIFIED BY username  ;
-- ROLES
GRANT "CONNECT" TO username  WITH ADMIN OPTION;
ALTER USER username  DEFAULT ROLE "CONNECT";
-- SYSTEM PRIVILEGES
GRANT DROP ANY TRIGGER TO username  WITH ADMIN OPTION;
GRANT DROP ANY SEQUENCE TO username  WITH ADMIN OPTION;
GRANT CREATE TRIGGER TO username  WITH ADMIN OPTION;
GRANT ALTER ANY PROCEDURE TO username  WITH ADMIN OPTION;
GRANT ADMINISTER DATABASE TRIGGER TO username  WITH ADMIN OPTION;
GRANT CREATE ANY PROCEDURE TO username  WITH ADMIN OPTION;
GRANT EXECUTE ANY INDEXTYPE TO username  WITH ADMIN OPTION;
GRANT DEBUG CONNECT SESSION TO username  WITH ADMIN OPTION;
GRANT ALTER SESSION TO username  WITH ADMIN OPTION;
GRANT CREATE MATERIALIZED VIEW TO username  WITH ADMIN OPTION;
GRANT MERGE ANY VIEW TO username  WITH ADMIN OPTION;
GRANT CREATE ANY INDEX TO username  WITH ADMIN OPTION;
GRANT ALTER ANY MATERIALIZED VIEW TO username  WITH ADMIN OPTION;
GRANT CREATE INDEXTYPE TO username  WITH ADMIN OPTION;
GRANT DEBUG ANY PROCEDURE TO username  WITH ADMIN OPTION;
GRANT CREATE ANY SEQUENCE TO username  WITH ADMIN OPTION;
GRANT CREATE VIEW TO username  WITH ADMIN OPTION;
GRANT ALTER ANY TABLE TO username  WITH ADMIN OPTION;
GRANT CREATE SESSION TO username  WITH ADMIN OPTION;
GRANT SELECT ANY TABLE TO username  WITH ADMIN OPTION;
GRANT DELETE ANY TABLE TO username  WITH ADMIN OPTION;
GRANT ALTER ANY SEQUENCE TO username  WITH ADMIN OPTION;
GRANT DROP ANY MATERIALIZED VIEW TO username  WITH ADMIN OPTION;
GRANT CREATE TABLE TO username  WITH ADMIN OPTION;
GRANT DROP ANY TABLE TO username  WITH ADMIN OPTION;
GRANT SELECT ANY SEQUENCE TO username  WITH ADMIN OPTION;
GRANT MANAGE TABLESPACE TO username  WITH ADMIN OPTION;
GRANT CREATE TYPE TO username  WITH ADMIN OPTION;
GRANT CREATE TABLESPACE TO username  WITH ADMIN OPTION;
GRANT SELECT ANY TRANSACTION TO username  WITH ADMIN OPTION;
GRANT LOCK ANY TABLE TO username  WITH ADMIN OPTION;
GRANT DROP ANY TYPE TO username  WITH ADMIN OPTION;
GRANT QUERY REWRITE TO username  WITH ADMIN OPTION;
GRANT EXECUTE ANY PROCEDURE TO username  WITH ADMIN OPTION;
GRANT CREATE SYNONYM TO username  WITH ADMIN OPTION;
GRANT DEBUG CONNECT ANY TO username  WITH ADMIN OPTION;
GRANT CREATE SEQUENCE TO username  WITH ADMIN OPTION;
GRANT CREATE ANY INDEXTYPE TO username  WITH ADMIN OPTION;
GRANT DROP ANY INDEX TO username  WITH ADMIN OPTION;
GRANT UPDATE ANY TABLE TO username  WITH ADMIN OPTION;
GRANT DROP ANY VIEW TO username  WITH ADMIN OPTION;
GRANT ALTER TABLESPACE TO username  WITH ADMIN OPTION;
GRANT ALTER ANY TRIGGER TO username  WITH ADMIN OPTION;
GRANT CREATE ANY VIEW TO username  WITH ADMIN OPTION;
GRANT IMPORT FULL DATABASE TO username  WITH ADMIN OPTION;
GRANT READ ANY TABLE TO username  WITH ADMIN OPTION;
GRANT INSERT ANY TABLE TO username  WITH ADMIN OPTION;
GRANT CREATE ANY MATERIALIZED VIEW TO username  WITH ADMIN OPTION;
GRANT ALTER ANY TYPE TO username  WITH ADMIN OPTION;
GRANT DROP ANY PROCEDURE TO username  WITH ADMIN OPTION;
GRANT DROP ANY INDEXTYPE TO username  WITH ADMIN OPTION;
GRANT UNLIMITED TABLESPACE TO username  WITH ADMIN OPTION;
GRANT CREATE ANY TRIGGER TO username  WITH ADMIN OPTION;
GRANT CREATE ANY TABLE TO username  WITH ADMIN OPTION;
GRANT CREATE ANY TYPE TO username  WITH ADMIN OPTION;
GRANT CREATE PROCEDURE TO username  WITH ADMIN OPTION;
GRANT ALTER DATABASE TO username  WITH ADMIN OPTION;
GRANT DROP TABLESPACE TO username  WITH ADMIN OPTION;

问题是他们中的每一个都可以选择模式浏览器并导航到所有其他用户的模式,此外还可以选择,更新等。我应该取消哪项特权来避免这种情况?
我试过这个

REVOKE select on SYS.ALL_USERS from PUBLIC

但没有结果

s4chpxco

s4chpxco1#

就像其他人已经对你的问题发表评论一样,不要在创建时使用所有的GRANT语句。

CREATE USER username  IDENTIFIED BY username  ;
-- ROLES
GRANT "CONNECT" TO username  WITH ADMIN OPTION;
ALTER USER username  DEFAULT ROLE "CONNECT";

您可能需要添加:

GRANT UNLIMITED TABLESPACE TO username;

但肯定不是所有其他的特权。

相关问题