Version: Next

User rights

Oracle

This document describes which Oracle system privileges are required for the inPoint server.

System privileges for inPoint default schema (without multischema support)

User who is defined as the default schema for inPoint should have the following system privileges in Oracle:

CREATE SESSION
ALTER SESSION
CREATE TABLE
CREATE PROCEDURE
CREATE SEQUENCE
CREATE SYNONYM
CREATE TRIGGER
CREATE VIEW
CREATE JOB TO
EXECUTE ON DBMS_SCHEDULER
UNLIMITED TABLESPACE

Execute the following statements to grant these rights to the user:

GRANT CREATE SESSION TO <inPoint default schema>;
GRANT ALTER SESSION TO <inPoint default schema>;
GRANT CREATE TABLE TO <inPoint default schema>;
GRANT CREATE PROCEDURE TO <inPoint default schema>;
GRANT CREATE SEQUENCE TO <inPoint default schema>;
GRANT CREATE SYNONYM TO <inPoint default schema>;
GRANT CREATE TRIGGER TO <inPoint default schema>;
GRANT CREATE VIEW TO <inPoint default schema>;
GRANT CREATE JOB TO <inPoint default schema>;
GRANT EXECUTE ON DBMS_SCHEDULER TO <inPoint default schema>;
GRANT UNLIMITED TABLESPACE TO <inPoint default schema>;

, where <inPoint default schema> is replaced with the inPoint default schema.

Additional system privileges for inPoint default schema for multischema support

For multischema support the following additional system privileges are required for the default schema of the inPoint server:

INSERT ANY TABLE
UPDATE ANY TABLE
DELETE ANY TABLE
SELECT ANY TABLE
SELECT ANY SEQUENCE
CREATE ANY TABLE
CREATE ANY INDEX
CREATE ANY SEQUENCE
CREATE ANY SYNONYM
CREATE ANY TRIGGER
CREATE ANY VIEW
ALTER ANY TABLE
ALTER ANY INDEX
ALTER ANY SEQUENCE
ALTER ANY TRIGGER
DROP ANY TABLE
DROP ANY INDEX
DROP ANY SEQUENCE
DROP ANY SYNONYM
DROP ANY TRIGGER
DROP ANY VIEW
ALTER ANY PROCEDURE
CREATE ANY PROCEDURE
DROP ANY PROCEDURE
EXECUTE ANY PROCEDURE

Execute the following statements to grant these rights to the user:

GRANT INSERT ANY TABLE TO <inPoint default schema>;
GRANT UPDATE ANY TABLE TO <inPoint default schema>;
GRANT DELETE ANY TABLE TO <inPoint default schema>;
GRANT SELECT ANY TABLE TO <inPoint default schema>;
GRANT SELECT ANY SEQUENCE TO <inPoint default schema>;
GRANT CREATE ANY TABLE TO <inPoint default schema>;
GRANT CREATE ANY INDEX TO <inPoint default schema>;
GRANT CREATE ANY SEQUENCE TO <inPoint default schema>;
GRANT CREATE ANY SYNONYM TO <inPoint default schema>;
GRANT CREATE ANY TRIGGER TO <inPoint default schema>;
GRANT CREATE ANY VIEW TO <inPoint default schema>;
GRANT ALTER ANY TABLE TO <inPoint default schema>;
GRANT ALTER ANY INDEX TO <inPoint default schema>;
GRANT ALTER ANY SEQUENCE TO <inPoint default schema>;
GRANT ALTER ANY TRIGGER TO <inPoint default schema>;
GRANT DROP ANY TABLE TO <inPoint default schema>;
GRANT DROP ANY INDEX TO <inPoint default schema>;
GRANT DROP ANY SEQUENCE TO <inPoint default schema>;
GRANT DROP ANY SYNONYM TO <inPoint default schema>;
GRANT DROP ANY TRIGGER TO <inPoint default schema>;
GRANT DROP ANY VIEW TO <inPoint default schema>;
GRANT ALTER ANY PROCEDURE TO <inPoint default schema>;
GRANT CREATE ANY PROCEDURE TO <inPoint default schema>;
GRANT DROP ANY PROCEDURE TO <inPoint default schema>;
GRANT EXECUTE ANY PROCEDURE TO <inPoint default schema>;

System privileges for different schema

Each user included into the "multischema support" must have the following system privileges:

CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE PROCEDURE
CREATE SEQUENCE
CREATE SYNONYM
CREATE TRIGGER
CREATE VIEW
EXECUTE ANY PROCEDURE
SELECT ANY TABLE
DELETE ANY TABLE

Execute the following statements to grant these rights to the user:

GRANT CREATE SESSION TO <different schema>;
GRANT UNLIMITED TABLESPACE TO <different schema>;
GRANT CREATE TABLE TO <different schema>;
GRANT CREATE PROCEDURE TO <different schema>;
GRANT CREATE SEQUENCE TO <different schema>;
GRANT CREATE SYNONYM TO <different schema>;
GRANT CREATE TRIGGER TO <different schema>;
GRANT CREATE VIEW TO <different schema>;
GRANT EXECUTE ANY PROCEDURE TO <different schema>;
GRANT SELECT ANY TABLE TO <different schema>;
GRANT DELETE ANY TABLE TO <different schema>;

, where <different schema> is replaced with the requested schema.

Minimum system privileges for the inPoint connection user not containing the inPoint objects

We need to grant object privileges for all objects owned by the INPOINT_OWNER to inPoint connection user.

There is no schema level grant command in Oracle (using a single SQL statement.), something like:

GRANT SELECT ON INPOINT_OWNER TO INPOINT_CONNECTION_USER;

Instead we need to build a GRANT statement for each object owned by INPOINT_OWNER to grant :

SELECT/INSERT/UPDATE/DELETE object privilege on all TABLES which belong to a INPOINT_OWNER schema
SELECT/INSERT/UPDATE/DELETE object privilege on all VIEWS which belong to a INPOINT_OWNER schema
SELECT object privilege on all SEQUENCES which belong to a INPOINT_OWNER schema
EXECUTE object privilege on all 'PACKAGE','FUNCTION','PROCEDURE' which belong to a INPOINT_OWNER schema
etc

to INPOINT_CONNECTION_USER user.

These grants are obviously point in time grants, which means objects created after running these statements will not be picked up. In order to keep the schema level grant up to date (objects are being created and changed), use the following script, which checks if there has been any objects modified, will determine a list of missing privileges and only grant those.

CREATE OR REPLACE PROCEDURE SCHEMA_GRANT(powning_schema varchar2, precipient varchar2, pcomplete boolean default false) is
     errs_found boolean := false;

     cursor c_all is
        select
           owner owner
          ,object_name
          ,object_type
          ,decode(object_type
                            ,'TABLE'   ,
                                decode(external,'YES','SELECT','SELECT,INSERT,UPDATE,DELETE,REFERENCES')
                            ,'VIEW'    ,'SELECT,INSERT,UPDATE,DELETE'
                            ,'SEQUENCE','SELECT'
                            ,'EXECUTE') priv
        from
          ( select o.owner,o.object_name,o.object_type,'NO' external
            from   dba_objects o
            where  o.owner = upper(powning_schema)
            and    o.object_type in ('FUNCTION','PACKAGE','PROCEDURE','SEQUENCE','TYPE','VIEW')
            and    o.generated = 'N'
            and    o.secondary = 'N'
            and    o.object_name not like 'AQ$%'
            union all
            select o.owner,o.object_name,o.object_type,'NO' external
            from   dba_objects o,
                   dba_tables t
            where  o.owner = upper(powning_schema)
            and    o.object_type  = 'TABLE'
            and    o.generated = 'N'
            and    o.secondary = 'N'
            and    o.object_name not like 'AQ$%'
            and    o.owner = t.owner
            and    o.object_name = t.table_name
          )
        order by decode(object_type  -- the order is only so views are granted after any likely
                        ,'VIEW', 1     -- objects referenced by them have already been granted
                        , 0) asc       -- as the grant would else fail due to view invalidity.
                ,owner
                ,object_name;

     cursor c_partial is
        with objs as
        (
        select /*+ materialize */ owner,object_name,object_type,priv_count
        from
          (
          select /*+ materialize */ owner,object_name,object_type,decode(object_type,'VIEW',4,1) priv_count
          from   dba_objects
          where  owner = upper(powning_schema)
          and    object_type in ('FUNCTION','PACKAGE','PROCEDURE','SEQUENCE','TYPE','VIEW')
          and    generated = 'N'
          and    secondary = 'N'
          and    object_name not like 'AQ$%'
          and    status != 'INVALID'
          union all
          select o.owner,o.object_name,o.object_type, decode('NO','YES',1,5) priv_count
          from   dba_objects o,
                 dba_tables t
          where  o.owner = upper(powning_schema)
          and    o.object_type  = 'TABLE'
          and    o.generated = 'N'
          and    o.secondary = 'N'
          and    o.object_name not like 'AQ$%'
          and    o.owner = t.owner
          and    o.object_name = t.table_name
          and    o.status != 'INVALID'
          )
        ),
        obj_privs as (
          select o.owner,o.object_name table_name,o.object_type,p.privilege,p.grantee
          from   dba_tab_privs p,
                 dba_objects o
          where  o.owner = p.owner
          and    o.object_name = p.table_name
          and    o.owner != 'SYS'
        )
        select
                 owner owner
                ,object_name
                ,object_type
                ,decode(object_type
                                  ,'TABLE'   ,'SELECT,INSERT,UPDATE,DELETE,REFERENCES'
                                  ,'VIEW'    ,'SELECT,INSERT,UPDATE,DELETE'
                                  ,'SEQUENCE','SELECT'
                                  ,'EXECUTE') priv
              from
              (
                select owner,object_name,object_type
                from
                (   select owner,object_name,object_type,priv_count
                    from   objs
                    minus
                    select owner, table_name, object_type,
                           least(count(*),decode(object_type,'TABLE',5,'VIEW',4,1))
                    from   obj_privs
                    where  grantee = upper(precipient)
                    and    privilege in ('SELECT','INSERT','UPDATE','DELETE','REFERENCES','EXECUTE')
                    and    owner = upper(powning_schema)
                    group by owner, table_name, object_type
                )
              )
              order by decode(object_type  -- the order is only so views are granted after any likely
                              ,'VIEW', 1     -- objects referenced by them have already been granted
                              , 0) asc       -- as the grant would else fail due to view invalidity.
                      ,owner
                      ,object_name;

     type t_grant_list is table of c_all%rowtype;
     r    t_grant_list;

     l_ddl_indicator number;

     procedure logger(m varchar2) is
     begin
       dbms_output.put_line(m);
     end;

  begin
    if pcomplete then
      logger('Starting complete run');

      open c_all;
      fetch c_all bulk collect into r;
      close c_all;
    else
      logger('Starting partial run');

      begin
        select 1
        into   l_ddl_indicator
        from dba_objects
        where owner = upper(powning_schema)
        and last_ddl_time > sysdate-1/24
        and rownum = 1;

        open c_partial;
        fetch c_partial bulk collect into r;
        close c_partial;
      exception
        when no_data_found then
          logger('Finished, no ddl in past 60 mins');
          return;
      end;
    end if;

    for i in 1 .. r.count loop
        --dbms_output.put_line(rpad(r(i).object_type,20)||r(i).owner||'.'||r(i).object_name)   ;

        begin
          logger('grant '||r(i).priv||' on '||r(i).owner||'.'||r(i).object_name||' to '||precipient||';');
          execute immediate 'grant '||r(i).priv||' on '||r(i).owner||'.'||r(i).object_name||' to '||precipient;

        exception
          when others then
             logger('ERROR: '||sqlerrm);
             errs_found := true;
        end;
    end loop;
    if errs_found then
      logger('**** ERRORS FOUND ****');
    end if;
    logger('Finished, record count = '||r.count);
  end;

Oracle​

System privileges for inPoint default schema (without multischema support)​

Additional system privileges for inPoint default schema for multischema support​

System privileges for different schema​

Minimum system privileges for the inPoint connection user not containing the inPoint objects​

Oracle

System privileges for inPoint default schema (without multischema support)

Additional system privileges for inPoint default schema for multischema support

System privileges for different schema

Minimum system privileges for the inPoint connection user not containing the inPoint objects