Appendix A: Setting Up the Security Manager

Separation of duty is an important security principle. It means that different users will perform different actions. The objective is to create and manage users and assign privileges in a way that maximizes security. The least privilege principle serves as a guide. For this book, the SEC_MGR user performs most of the security-related administration tasks. This appendix lists the privileges given to the SEC_MGR user to fulfill the mission of security manager for the examples given herein.

The privileges are granted directly to the user. This is done to allow the user to create procedures using the default definer rights model. In the definer rights model, roles are disabled. The SEC_MGR was created originally with only the CREATE SESSION privilege. Privileges were added as needed on a case-by-case basis. No default roles—DBA, RESOURCE, and CONNECT—were ever granted. This ensures that the only privileges granted were the ones needed to perform a specific action. This is the least privilege principle and it should serve as a guide for you in creating administrators for your databases.

This schema isn’t meant to serve as the template for your database security administrators. These privileges are shown here to allow you to follow along with the examples. The privileges for the examples used throughout the book could have been granted when the examples were presented, which would have ensured that any example could be run as is (autonomously). However, doing so was both a distraction to the example and redundant because many of the same privileges were needed for many different examples.

Many of the grants have to be made from SYS since the objects are dictionary protected objects. Comments are provided whenever possible to explain when and where the privileges are referenced.

-- connect as SYS to run this script CONN sys/oracle as sysdba /***  Create the Security Manager ***/ -- Create the security manager. Password is simple. -- CHANGE this password for a production environment. CREATE USER sec_mgr IDENTIFIED BY oracle10g   DEFAULT TABLESPACE users   TEMPORARY TABLESPACE temp; -- Grant privileges to place data in the tablespace. -- This is necessary for creating tables. ALTER USER sec_mgr     QUOTA UNLIMITED     ON "USERS"; -- Privilege to log on to the database. -- ADMIN given to allow sec_mgr to grant priv to others. GRANT CREATE SESSION TO sec_mgr WITH ADMIN OPTION; -- Privilege to change database session GRANT ALTER SESSION TO sec_mgr; /***        Base Privileges       ***/ -- The following grants form the basis of the privileges -- needed for performing many of the examples. GRANT CREATE PROCEDURE TO sec_mgr; GRANT CREATE TABLE TO sec_mgr; GRANT CREATE VIEW TO sec_mgr; GRANT CREATE TYPE TO sec_mgr; GRANT CREATE TRIGGER TO sec_mgr; GRANT CREATE OPERATOR TO sec_mgr; GRANT CREATE SEQUENCE TO sec_mgr; /***        User Management       ***/ -- Privileges to create and manage users. GRANT CREATE USER TO sec_mgr; GRANT ALTER USER TO sec_mgr; GRANT DROP USER TO sec_mgr; -- Privilege to inspect user passwords GRANT SELECT ON dba_users TO sec_mgr; -- Privilege to inspect proxy users GRANT SELECT ON proxy_users TO sec_mgr; /***        Role Privileges       ***/ -- Role administration privileges GRANT CREATE ROLE TO sec_mgr; GRANT ALTER ANY ROLE TO sec_mgr; GRANT GRANT ANY ROLE TO sec_mgr; GRANT DROP ANY ROLE TO sec_mgr; -- Privileges to inspect and validate role privileges. GRANT SELECT ON SYS.dba_role_privs TO sec_mgr; GRANT SELECT ON SYS.role_role_privs TO sec_mgr; /*** Privileges for misc examples ***/ -- Privileges to manage synonyms GRANT CREATE SYNONYM TO sec_mgr; GRANT CREATE PUBLIC SYNONYM TO sec_mgr; GRANT DROP PUBLIC SYNONYM TO sec_mgr; GRANT CREATE DATABASE LINK TO sec_mgr; -- Privilege to create a database logon trigger GRANT ADMINISTER DATABASE TRIGGER TO sec_mgr; GRANT CREATE ANY TRIGGER TO sec_mgr; -- Privileges to create security profiles GRANT CREATE PROFILE TO sec_mgr; GRANT DROP PROFILE TO sec_mgr; -- Privilege to query database initialization parameters GRANT SELECT ON v_$parameter TO sec_mgr; /***    Chapter specific examples ***/ -- Used to create all_user_Priv_path in Chapter 7 GRANT SELECT ON sysauth$ TO sec_mgr WITH GRANT OPTION; GRANT SELECT ON user$ TO sec_mgr WITH GRANT OPTION; GRANT SELECT ON dba_tab_privs TO sec_mgr WITH GRANT OPTION; GRANT SELECT ON dba_sys_privs TO sec_mgr WITH GRANT OPTION; GRANT SELECT ON v_$process TO sec_mgr WITH GRANT OPTION; GRANT SELECT ON v_$session TO sec_mgr WITH GRANT OPTION; GRANT SELECT ON v_$parameter TO sec_mgr WITH GRANT OPTION; GRANT SELECT ON v_$thread TO sec_mgr WITH GRANT OPTION; GRANT SELECT ON v_$mystat TO sec_mgr WITH GRANT OPTION; GRANT SELECT ON v_$statname TO sec_mgr; /***       Audit Privileges       ***/ GRANT AUDIT ANY TO sec_mgr; GRANT AUDIT SYSTEM TO sec_mgr; GRANT SELECT ON dba_common_audit_trail TO sec_mgr; GRANT SELECT ON dba_obj_audit_opts TO sec_mgr; GRANT SELECT ON dba_priv_audit_opts TO sec_mgr; GRANT SELECT ON dba_stmt_audit_opts TO sec_mgr; -- Privilege to establish Fine-Grained Auditing GRANT EXECUTE ON DBMS_FGA TO sec_mgr; -- Flashback privileges used in auditing chapter. GRANT SELECT ON flashback_transaction_query TO sec_mgr; GRANT FLASHBACK ANY TABLE to sec_mgr; /***       FGAC Privileges        ***/ -- Privileges to create and delete application context GRANT CREATE ANY CONTEXT TO sec_mgr; GRANT DROP ANY CONTEXT TO sec_mgr; GRANT SELECT ON dba_context TO sec_mgr; -- Privilege to establish VPD policies GRANT EXECUTE ON DBMS_RLS TO sec_mgr; -- Inspect VPD policies GRANT SELECT ON dba_policies TO sec_mgr; -- Use SLEEP procedure in a VPD caching example  GRANT EXECUTE ON DBMS_LOCK TO sec_mgr; /***   Encryption Privileges      ***/ -- Execute privileges for the encryption package: Ch. 13 GRANT EXECUTE ON dbms_crypto TO sec_mgr;



Effective Oracle Database 10g Security by Design
Effective Oracle Database 10g Security by Design
ISBN: 0072231300
EAN: 2147483647
Year: 2003
Pages: 111

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net