Oracle DBMS_NETWORK_ACL_ADMIN – How to View Settings

acldbms-network-acl-adminoracle

How do I view the contents of a network ACL? For example, if I create this ACL, how can I view what settings have been applied to it?

  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
     acl => 'www.xml',
     description => 'WWW ACL',
     principal => 'SCOTT',
     is_grant => true,
     privilege => 'connect'
  );

Best Answer

In order to see also implicit privileges you can use this query:

SELECT PRINCIPAL, HOST, lower_port, upper_port, acl, 'connect' AS PRIVILEGE, 
    DECODE(DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, PRINCIPAL, 'connect'), 1,'GRANTED', 0,'DENIED', NULL) PRIVILEGE_STATUS
FROM DBA_NETWORK_ACLS
    JOIN DBA_NETWORK_ACL_PRIVILEGES USING (ACL, ACLID)  
UNION ALL
SELECT PRINCIPAL, HOST, NULL lower_port, NULL upper_port, acl, 'resolve' AS PRIVILEGE, 
    DECODE(DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, PRINCIPAL, 'resolve'), 1,'GRANTED', 0,'DENIED', NULL) PRIVILEGE_STATUS
FROM DBA_NETWORK_ACLS
    JOIN DBA_NETWORK_ACL_PRIVILEGES USING (ACL, ACLID);

Related Solutions

Can you have multiple Oracle ACL files

Check the Creating an Access Control List for External Network Services documentation (look at step 2, after the parameter descriptions):

Only one access control list can be assigned to any host computer, domain, or IP subnet, and if specified, the TCP port range.

Your second assignment simply replaces the first. You can only have one ACL file per host/domain.

Oracle Schema Privileges – Managing Permissions for Other Users

RESOURCE role has the following system privileges.

SQL> select privilege from role_sys_privs where  role='RESOURCE';

PRIVILEGE
----------------------------------------
CREATE SEQUENCE
CREATE TRIGGER
CREATE CLUSTER
CREATE PROCEDURE
CREATE TYPE
CREATE OPERATOR
CREATE TABLE
CREATE INDEXTYPE

8 rows selected.

And has no table privileges.

SQL> select privilege from role_tab_privs where  role='RESOURCE';

no rows selected

After the observation we can know that the user, with RESOURCE role, can create certain objects such as tables and procedures.

But in order to SELECT from tables belong to another schema, another user needs to grant SELECT object privilege on tables explicitly.

SQL> conn user1/password
SQL> grant select on table to user2

Now user2 can create view(user should have CREATE VIEW system privilege) by selecting from the tables of user1 schema.

Demo(Based on Oracle 11.2.0.4):

SQL> create user user2 identified by user2;

User created.

SQL> grant resource, connect to user2;

Grant succeeded.

SQL> conn user2/user2
Connected.
SQL> create view v1 as select * from user1.mytest;
create view v1 as select * from user1.mytest
                                    *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> conn user1/user1
Connected.
SQL> grant select on mytest to user2;

Grant succeeded.

SQL> conn user2/user2
Connected.
SQL> create view v1 as select * from user1.mytest;
create view v1 as select * from user1.mytest
            *
ERROR at line 1:
ORA-01031: insufficient privileges --Now the user has no `CREATE VIEW` system privilege

SQL> conn / as sysdba
Connected.
SQL> grant create view to user2;

Grant succeeded.

SQL> conn user2/user2
Connected.
SQL> create view v1 as select * from user1.mytest;

View created.

Best Answer

Related Solutions

Can you have multiple Oracle ACL files

Oracle Schema Privileges – Managing Permissions for Other Users

Related Question