The Role of Roles and Privileges

Carl Dudley – Tradba Ltd Carl Dudley UKOUG Official Oracle ACE Director carl.dudley@wlv.ac.uk The Role of Roles and Privileges

The Role of Roles and Privileges Granting System Privileges Granting Object Privileges Managing Roles Pre-created Roles Default, Non-default and Password Protected Roles Secure Application Roles Definers Rights and Invokers Rights Detecting Recent Grants of Privileges

Database Security • Oracle’s database security provides the ability to • Prevent unauthorized access to the database • Prevent unauthorized access to schema objects • Prevent unauthorized activity and audit user actions • Control disk storage and system-resource usage (profiles) • System security • Checks for user names and passwords • Connects authorization • Controls availability of disk space • Controls resource limits; enables and controls auditing • Specifies allowed system operations • Data security • Access to specific structures; e.g., tables, views, etc. • Types of access; e.g., SELECT, UPDATE, etc.

Privileges and Roles • Oracle has two main types of privileges • System • Object • System-level privileges control the use of DDL statements • Creation, alteration, and removal of objects • Connecting to the database • Execution of DBA functions • Object-level privileges provide access to database objects • Selection from a table • Update of view information • Execution of stored procedure code

10g System Privileges • Oracle has more than 100 system privileges • Allows precise specification of what users can and cannot do • Security strategy can become complex to manage • All available system privileges can be seen in system_privilege_map • System privileges convey much power to the recipients • Careful planning is required before granting such privileges • Grant only if absolutely necessary • Syntax is simple • If a user needs to create tables, issue the following: • This allows fred to build tables within the fred schema • Users can be created along with privileges by specifying a password GRANT system_privilege_name TO username; • GRANT CREATE TABLE TO fred; • GRANT CREATE SESSION TO amy IDENTIFIED BY amypw;

System Privileges (continued) • System privileges are granted only by DBAs or users with the GRANT ANY PRIVILEGE system privilege • To audit the system privileges granted to users, query dba_sys_privs SELECT grantee, privilege FROM dba_sys_privs WHERE grantee = 'FRED'; GRANTEE PRIVILEGE ------- ------------------- FRED CREATE TABLE FRED CREATE SESSION

Managing System Privileges • System privileges may be granted with the ADMIN OPTION • This allows smith to alter the structure of any table in any user schema (except sys) and to pass this privilege on to any other user • When smith logs in, he or she can issue the following: • Remember – the ‘GRANT ANY’ statements give access not only to objects already in the database but also objects created in the future • If O7_DICTIONARY_ACCESSIBILITY is set to true, the GRANT ANY statement gives access to sys objects! • O7 relates to Oracle7 behaviour • Default for O7_DICTIONARY_ACCESSIBILITY is false GRANT ALTER ANY TABLE TO smith WITH ADMIN OPTION; GRANT ALTER ANY TABLE TO brown [WITH ADMIN OPTION];

Revoking System Privileges • System privileges may be removed from users • Users who have received the privilege using the ADMIN OPTION will still have the privilege enabled • So brown will still be able to change any user’s tables REVOKE system_privilege_name FROM username; REVOKE ALTER ANY TABLE FROM smith;

Object Privileges • Allow users access to specific database objects • By default, only the user who owns (i.e., creates) a database object can perform any kind of activity or change on it • The owner has the full set of access rights on the object • Other users must be granted object privileges • This can be granted by the owner of the object or a user with the GRANT ANY OBJECT PRIVILEGE system privilege • Can also be accomplished via the grant of a system privilege GRANT UPDATE ON student TO fred; GRANT UPDATE ANY TABLE TO fred;

Granting Access to Tables • Object privileges cannot be granted along with system privileges or roles in the same GRANT statement • Some common uses for this statement are: Tables up to 11 privileges Views DELETE, INSERT, SELECT, UPDATE Sequences ALTER, SELECT Procedures EXECUTE Snapshots SELECT GRANT privilege(s) ON object TO user(s) | role(s) | PUBLIC [WITH GRANT OPTION]

Object Privileges • Oracle knows the nature of the object specified in the GRANT statement because all objects owned by a user must have unique names • The WITH GRANT option • Allows object privileges to be passed on by the grantee to other users • A form of delegation (assume student is owned by oral) • ora2 can now issue • Removal of the GRANT OPTION requires the privilege to be revoked and regranted GRANT DELETE ON student TO ora2 WITH GRANT OPTION; GRANT DELETE ON ora1.student TO ora3;

Summary of Commonly Used Privileges on Objects • Row-level security cannot be implemented at this level • Requires other means • Use of views • Fine-grained access (Virtual Private Database) • Application logic • Label security T = table, V = view, P = procedure, S = sequence, Sn = snapshot

Granting Access to Objects: Some Examples • UPDATE can be granted without SELECT to allow access via programs • The procedure insert_course_totals requires access to database tables • The procedure owner should have necessary privileges on the base objects • ora1 does not need privileges on those tables because the procedure executes with the privileges of its owner GRANT SELECT ON student TO PUBLIC; GRANT ALL ON company TO fred, managers; GRANT SELECT, INSERT, UPDATE(student_fname) ON student TO jones, adams, clerks; GRANT SELECT ON instructor TO ora2 WITH GRANT OPTION; GRANT EXECUTE ON insert_course_totals TO ora1;

Revoking Privileges • Revoke all privileges on an object from a user • You can also remove privileges on an individual basis • If a table is being referenced by foreign keys using the REFERENCES privilege, the CASCADE CONSTRAINTS option is needed • This revokes all access to student from ora1 and drops any constraints built by fred that reference the company table • Beware the impact of PUBLIC grants • Revoking a privilege does not give an absolute guarantee of denial • The user may have access using a PUBLICly granted privilege REVOKE ALL ON student FROM fred; REVOKE DELETE ON student FROM fred; REVOKE ALL ON company FROM fred CASCADE CONSTRAINTS;

Revoking Object Privileges Granted With GRANTOPTION check_obj_privs • The following statement issued by John will revoke access to his customers table from Mary, Steve, and Marc: REVOKE SELECT ON customers from mary; • The effect is immediate • This behavior is different from system privileges granted with theADMIN OPTION GRANT SELECT ON John customers TO mary WITH GRANT OPTION; JOHN.INSTRUCTOR GRANT SELECT ON Mary customers TO steve and marc; What happens if user mary is dropped? Steve Marc

Useful Dictionary Views user_object_privs • dba_tab_privs shows details of all table privileges in the database • Note : when the grantor is shown to be the owner of the table (mary) it could actually have been granted by any user withGRANT ANY OBJECT PRIVILEGE • Other useful views are dba_col_privs user_col_privs_recd user_col_privs_made GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE ------- ----- ---------- ------- ---------- --------- TOM MARY EMPLOYEE MARY SELECT YES AMY MARY EMPLOYEE TOM SELECT NO TOM MARY DEPARTMENT MARY DELETE NO

Table and Column Level Privileges • Consider the following dept table DEPTNO DNAME LOC ------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON • A table level grant will put one row in user_tab_privs • ‘Equivalent’ column level grant will put three rows in user_col_privs but NO rows in user_tab_privs • Why the difference? • Consider the effect of adding a new column to dept • Aside – individual column grants cannot be revoked • GRANT UPDATE ON dept TO joe; • GRANT UPDATE(dept,dname,loc) ON dept TO joe;

Managing Privileges With Roles • The complexity of privilege management can be reduced by using roles to group system and object privileges for easier control • Benefits of using roles • Reduce privilege administration by allowing a cohesive set of privileges to be granted to users through one role grant • Ease of maintenance • Changing privileges for a role affects all users in the role • Privileges lost when an object is dropped need to be regranted only to the role when the object is re-created • Can allow ad-hoc or preprogrammed access to tables through selective control of role availability

Privilege Management • In the above diagram, each line represents a grant of a specific privilege on an object Users Objects

User and Application Roles Users User roles Application roles Objects

User and Application Roles (continued) • Recommendation is to define “application” and “user” roles • This allows you to • Grant application roles to users’ roles rather than granting individual privileges • Grant both user roles and application roles to users • Roles do not belong to a schema (or user) • Creation of a role puts the creator in the role with the ADMIN OPTION

Creating and Granting Roles who_has_roles • Use the CREATE ROLE statement • Roles can be created by users with CREATE ROLE system privilege (No password is required for the cashiers role) • Grant roles to users or to other roles • fred now has the cashiers role • The cashiers role is now granted to the managers role • All users in the managers role also have the cashiers role • To grant a role, you must have the GRANT ANY ROLE system privilege or have been granted the role with the ADMIN OPTION • CREATE ROLE cashiers; • GRANT cashiers TO fred; • GRANT cashiers TO managers;

Revoking Roles deactivate_user • Roles are revoked in the same way as system privileges • Immediately disables any activity allowed by the role • If cashiers has CREATE SESSION system privilege • The user would stay connected, but would not be allowed to reconnect • To selectively REVOKE the ADMIN OPTION (user keeps the role) • The system privilege (or role) must be revoked and then regranted without the ADMIN OPTION • Beware of revoking from (and granting to) PUBLIC • May cause a large number of recompilations due to dependency tracking • REVOKE cashiers FROM fred;

Pre-Created Roles • Oracle11g has a set of roles pre-created with the database SELECT * FROM dba_roles; ROLE PASSWORD -------------------- ----------------- CONNECT NO RESOURCE NO DBA NO SELECT_CATALOG_ROLE NO EXECUTE_CATALOG_ROLE NO DELETE_CATALOG_ROLE NO AQ_ADMINISTRATOR_ROLE NO AQ_USER_ROLE NO IMP_FULL_DATABASE NO EXP_FULL_DATABASE NO RECOVERY_CATALOG_OWNER NO LOGSTDBY_ADMINISTRATOR NO HS_ADMIN_ROLE NO WM_ADMIN_ROLE NO GLOBAL_AQ_USER_ROLE GLOBAL : :

The CONNECT, RESOURCE and DBA roles • Throwbacks to Version 6 – do NOT use • On Oracle 9i, the CONNECT role gives a number of system privileges • On Oracle 11g it merely gives CREATE SESSION • RESOURCE is dangerous as it gives UNLIMITED TABLESPACE privilege • Does not have CREATE SESSION • SCOTT account has RESOURCE • The DBA role contains all system privileges • Granted roles and their system privileges are shown in role_sys_privs

RESOURCE Role • The UNLIMITED TABLESPACE privilege from the RESOURCE role does not show in role_sys_privs • BUT can be observed in user_sys_privs • If RESOURCE role is gained via some other role (r1), • The UNLIMITED TABLESPACE privilege shows in role_sys_privsas a privilege from r1, but not in user_sys_privs SELECT * FROM role_sys_privs WHERE role IN (‘CONNECT’,’RESOURCE’); ROLE PRIVILEGE ADM -------------- ------------------ --- RESOURCE CREATE SEQUENCE NO RESOURCE CREATE TRIGGER NO RESOURCE CREATE CLUSTER NO RESOURCE CREATE PROCEDURE NO RESOURCE CREATE TYPE NO CONNECT CREATE SESSION NO RESOURCE CREATE OPERATOR NO RESOURCE CREATE TABLE NO RESOURCE CREATE INDEXTYPE NO

Dictionary Information on Roles who_has_system_privs • A number of dictionary views are available

Finding Who Has the CONNECT Role SELECT * FROM dba_connect_role_grantees; GRANTEE PATH_OF_CONNECT_ROLE_GRANT ADM ---------------------- ------------------------------ --- APEX_030200 CONNECT/APEX_030200 YES BARNEY CONNECT/BARNEY NO IX CONNECT/IX NO MDDATA CONNECT/MDDATA NO MDSYS CONNECT/MDSYS NO OWBSYS CONNECT/OWBSYS YES PM CONNECT/PM NO SCOTT CONNECT/SCOTT NO SPATIAL_CSW_ADMIN_USR CONNECT/SPATIAL_CSW_ADMIN_USR NO SPATIAL_WFS_ADMIN_USR CONNECT/SPATIAL_WFS_ADMIN_USR NO SYS CONNECT/SYS YES WMSYS CONNECT/WMSYS NO

Default and non-default roles • By default, roles granted to users are enabled when the user logs in • Roles can be explicitly set to be default role(s) • This will cause all other roles granted to joe to be non-default roles • joe will have to enable them as required • Roles r1,r2,r3 will be enabled, but any other roles granted to joe are disabled ALTER USER joe DEFAULT ROLE r1; SET ROLE r1,r2,r3;

Default and non-default roles • Additional syntax • Causes all other roles granted to joe to become non-default ALTER USER joe DEFAULT ROLE <role,...,role>; ALTER USER joe DEFAULT ROLE ALL | NONE; ALTER USER joe DEFAULT ROLE ALL EXCEPT <role,...,role>; • Removal of password protection ALTER ROLE role_with_pwd NOT IDENTIFIED;

Password Protected Roles CREATE ROLE rp1 IDENTIFIED BY pwd1; • The pwd1 password will be required when setting the rp1 role • Password protected roles, when granted, are set up as default roles, but are not enabled • Must be enabled (SET) on login and therefore a password is required SET ROLE rp1 IDENTIFIED BY pwd1; GRANT rp1 TO joe;

Password Protected Roles • Non-default and default password-protected roles force the user to supply password • The role must always be set with the password • Note the omission of rp1 from the list of roles • If a password protected role (rp1) is granted to a non-password protected role (no_rp1), then users with the no_rp1 role are able to use rp1 without submission of a password ALTER USER joe DEFAULT ROLE r1,r2,rp2;

Default and non-Default roles ALTER USER joe DEFAULT ROLE r1_nopwd,r1_pwd; • Both roles show as default for joe in the dba_role_privs but r1_pwd is not set on login • joe will need to SET it with its password • Error if joe issues : SET ROLE ALL; ORA-01979: missing or invalid password for role 'r1_pwd'

Assigning Roles to Users • Assign roles to users but do not enable them by default • The role is then SET within the actual session using the application • Access is limited to a particular session but • Extra precautions should be taken • Password protected roles • Secure application roles GRANT selective_role TO joe; ALTER USER joe DEFAULT ROLE ALL EXCEPT selective_role;

Setting Password Protected Roles • In SQL*Plus • In a PL/SQL application SET ROLE pwd_role IDENTIFIED BY cool_password; BEGIN dbms_session.set_role('pwd_role' ||' IDENTIFIED BY cool_password); END; /

Securing the Role Password • Difficult to secure in code • Can be viewed by anyone with access to the code • Process can be compiled – obfuscates the code • May not protect from UNIX “strings” • If users are required to supply the password, they will know the password • Can use it outside of the application • Better to obtain password from an encrypted file • Role passwords may travel across networks in the clear • Setting SQLNET.ORA parameter TNSPING.TRACE_LEVEL to SUPPORT will capture all network packets • Use network encryption • Applications may need to share the same role • Many people will know the password • Security resides solely in the application • Database is compliant

Roles Set by Applications • Do not allow applications to grant and revoke roles • Allows the user to use the roles outside of the application CREATE OR REPLACE PROCEDURE grant_privs AS BEGIN EXECUTE IMMEDIATE 'GRANT SELECT ON scott.emp TO '||user; END; / CREATE OR REPLACE PROCEDURE revoke_privs AS BEGIN EXECUTE IMMEDIATE 'REVOKE SELECT ON scott.emp TO '|| user; END; / GRANT EXECUTE ON grant_privs TO fred; GRANT EXECUTE ON revoke_privs TO fred; • Dynamic SQL is required to allow this procedure-based dynamic privilege enablement • ANY user can connect in any way and gain access between any call of the grant_privs and revoke_privs procedures

Limiting Access to Users via Applications • Many ways to access Oracle data • ODBC, JDBC,HTTP, Oracle Net etc. • Wide attack surface, close them off if possible • Users typically access Oracle via applications • Applications can provide additional control and levels of security • Privileges should be based on the user AND the application in use • So, a users privileges vary with the application that they are using • Privileges should be based on EVERYTHING known • Application, user’s identity, where they are, how they are authenticated, time of day • If this is done, we have defence in depth

Privileges Based on User, Application, Location and Time • Consider a user accessing an application • within owned locked office in own guarded site • from an office on a different guarded site • via a wireless device Office All privileges Field Office Insert, Read Wireless Read Only • Could also add time constraints

Secure Application Roles • Roles that can be enabled only from within a PL/SQL program • User/application needs execute privilege on the PL/SQL program • The PL/SQL program can perform sophisticated checking • Based on time, user environment etc. • Using this method, the database decides whether the role is enabled • If new security rules are introduced, the program is simply changed • All applications are affected • Users cannot change the security domain within definer's rights procedures • Secure application roles can be enabled only within invoker's rights procedures

Secure Application Roles – Set up SCOTT> CONN sec_mgr/sec_mgr Connected. SEC_MGR> CREATE TABLE empsec AS SELECT * FROM scott.emp; Table created. SEC_MGR> CREATE ROLE sec_app_role IDENTIFIED USING sec_mgr.priv_mgr; Role created. The priv_mgr code will manage access to the role (it acts as a sentry) SEC_MGR> GRANT sec_app_role TO scott; Grant succeeded. SEC_MGR> GRANT SELECT ON empsec TO sec_app_role; Grant succeeded.

Secure Application Roles - The Procedure SEC_MGR> CREATE OR REPLACE PROCEDURE priv_mgr 2 AUTHID CURRENT_USER --invoker's rights 3 AS 4 BEGIN 5 IF (SYS_CONTEXT('userenv','db_name') = 'orcl' 6 /* 7 AND TO_CHAR(sysdate,'hh24') BETWEEN 8 AND 17 8 AND (SYS_CONTEXT('userenv','ip_address') = '127.0.0.1') 9 */ 10 ) 11 THEN dbms_session.set_role ('sec_app_role'); 12 END IF; 13 END; 14 / • This procedure could test for a number of criteria • Best to use a mix of parameter and non-parameter checks • Non-parameters are harder to fake – e.g. time

Secure Application Roles – Granting Access SEC_MGR> GRANT EXECUTE ON priv_mgr TO scott; Grant succeeded. SEC_MGR> CONN scott/tiger Connected. SCOTT> SELECT SYS_CONTEXT ('userenv','db_name') FROM dual; SYS_CONTEXT('USERENV','DB_NAME') ------------------------------------------------------------ orcl SCOTT> SELECT * FROM session_roles; ROLE ------------------------------ CONNECT RESOURCE

Secure Application Roles – Setting the Role SCOTT tries (unsuccessfully) to set the role SCOTT> BEGIN 2 dbms_session.set_role('sec_app_role'); 3 END; 4 / BEGIN * ERROR at line 1: ORA-28201: Not enough privileges to enable application role 'SEC_APP_ROLE' ORA-06512: at "SYS.DBMS_SESSION", line 132 ORA-06512: at line 2 SCOTT> BEGIN 2 sec_mgr.priv_mgr; 3 END; 4 / PL/SQL procedure successfully completed. The role has now been set by the procedure (which SCOTT is able to execute) same functionality as SET ROLE sec_app_role;

Secure Application Roles – Using the Role secure_roles SCOTT> SELECT * FROM sec_mgr.empsec; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ---- --------- ---- ---- ------ 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 : : : : : : : 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 14 rows selected. SCOTT> SELECT * FROM session_roles; ROLE ------------------------------ SEC_APP_ROLE • Note only the sec_app_role is now enabled

The Role of Roles and Privileges