User Guide Getting Started Help Center Documentation Community Training
Looker
  
English
Français
Deutsch
日本語
Oracle

Encrypt Network Traffic

Looker strongly recommends encrypting network traffic between the Looker application and your database. Consider one of the options described here.

If you’re interested in using SSL encryption, see the Oracle documentation.

Create a Looker User

First, create a designated Looker user:

-- connect / as sysdba; CREATE USER LOOKER IDENTIFIED BY <password> DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;

Next, give the new Looker user the ability to create sessions:

GRANT CREATE SESSION TO LOOKER;

Finally, give the Looker user the appropriate SELECT permissions for the data tables you plan to access from Looker. If you want to access additional tables in the future, you will need to grant SELECT on those new tables as well.

GRANT SELECT ON -- <all tables that will be used by looker>;

Ensure Looker Can See All Tables

Looker may not be able to identify tables (especially empty tables) without first collecting statistics in Oracle. If tables you need do not appear in generated LookML or SQL Runner, try executing:

EXEC DBMS_STATS.GATHER_DATABASE_STATS;

For alternative methods, consult your Oracle documentation.

Set Up Main Database Objects

Your Oracle DBA must set up the following objects and permissions on Oracle. The following commands create LOOKER_SESSION and LOOKER_SQL as synonyms for V$SESSION and V$SQL.

Run the following commands as the root user to complete this setup. These examples assume that the Looker user’s name is LOOKER.

CREATE OR REPLACE VIEW LOOKER_SQL AS SELECT sql.SQL_ID, sql.SQL_TEXT FROM V$SQL sql ,v$session sess WHERE sess.SQL_ADDRESS = sql.ADDRESS AND sess.username='LOOKER'; CREATE OR REPLACE SYNONYM LOOKER.LOOKER_SQL FOR LOOKER_SQL; GRANT SELECT ON LOOKER.LOOKER_SQL TO LOOKER; CREATE OR REPLACE VIEW LOOKER_SESSION AS SELECT SID, USERNAME, TYPE, STATUS, SQL_ID, "SERIAL#", AUDSID FROM V$SESSION WHERE USERNAME='LOOKER'; CREATE OR REPLACE SYNONYM LOOKER.LOOKER_SESSION FOR LOOKER_SESSION; GRANT SELECT ON LOOKER.LOOKER_SESSION TO LOOKER;

Set Up Symmetric Aggregates

Your Oracle DBA must set up the LOOKER_HASH function to enable Symmetric Aggregates. The LOOKER_HASH function is a synonym for the Oracle dbms_crypto.hash function. The DBA must also create the associated synonym and privileges. The following commands assume that the Looker user’s name is LOOKER:

CREATE OR REPLACE FUNCTION LOOKER_HASH(bytes raw, prec number) RETURN raw AS BEGIN return(dbms_crypto.HASH(bytes, prec)); END; CREATE OR REPLACE SYNONYM LOOKER.LOOKER_HASH FOR LOOKER_HASH; GRANT EXECUTE ON LOOKER.LOOKER_HASH TO LOOKER; GRANT EXECUTE ON SYS.LOOKER_HASH TO LOOKER;

Depending on your Oracle database configuration, the SYS prefix may be SYSDBA, ADMIN, or unnecessary.

Set Up Persistent Derived Tables

In order to enable Persistent Derived Tables, give the Looker user the UNLIMITED TABLESPACE and CREATE TABLE permissions. The following commands assume that the Looker user’s name is LOOKER:

GRANT UNLIMITED TABLESPACE TO LOOKER; GRANT CREATE TABLE TO LOOKER;

Set Up Query Killing

Follow the instructions below to configure query killing for either a traditional Oracle deployment or an Amazon RDS deployment.

Traditional Oracle Deployments

To set up query killing in traditional Oracle deployments the Oracle DBA must create the LOOKER_KILL_QUERY procedure as a synonym of ALTER SYSTEM KILL SESSION. To do this, execute the following command:

Create or replace procedure LOOKER_KILL_QUERY(p_sid in varchar2, p_serial# in varchar2) is cursor_name pls_integer default dbms_sql.open_cursor; ignore pls_integer; BEGIN select count(*) into ignore from V$session where username = USER and sid = p_sid and serial# = p_serial#; if (ignore = 1) then dbms_sql.parse(cursor_name, 'alter system kill session ''' || p_sid || ',' || p_serial# || '''', dbms_sql.native); ignore := dbms_sql.execute(cursor_name); else raise_application_error(-20001, 'You do not own session ''' || p_sid || ',' || p_serial# || ''''); end if; END;

The DBA will also need to run these related commands:

CREATE OR REPLACE SYNONYM LOOKER.LOOKER_KILL_QUERY FOR SYS.LOOKER_KILL_QUERY; GRANT EXECUTE ON SYS.LOOKER_KILL_QUERY TO LOOKER;

Depending on your Oracle database configuration, the SYS prefix may be SYSDBA, ADMIN, or unnecessary.

Amazon RDS Deployments

In Amazon RDS Oracle deployments, the rdsadmin.rdsadmin_util.kill procedure is used to kill queries. In order to use this procedure the Looker database user must have the DBA role assigned. To give the Looker database user query killing abilities, run:

GRANT DBA TO LOOKER;

Note: Because DBA is an elevated database role, you might consider skipping this step and forgoing query killing in Looker.

Feature Support

Looker’s ability to provide some features depends on whether the database dialect can support them.

In the current Looker release, Oracle supports the following Looker features:

Next Steps

After completing the database configuration, you can connect to the database from Looker using these directions.

Top