WARNING: This web is not used anymore. Please use PDBService.UserAccounts instead!
 

Database User Accounts at CERN

In the examples below we use the application name CMS_DAQ. This name follows the NamingConvention.

Owner / Human / Development account

  • name:
    • same name of the application (ex: CMS_DAQ)
    • suffixed with state, login (ex: CMS_DAQ_SC3, CMS_DAQ_MANJO)
  • System privileges: ALTER SESSION, CREATE CLUSTER, CREATE MATERIALIZED VIEW, CREATE PROCEDURE, CREATE ROLE, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE TRIGGER, CREATE TYPE, CREATE VIEW, QUERY REWRITE, ADVISOR
  • Profile Limit:
    • max simultaneous sessions: 10
    • password life time: 365 days
    • max login attemps: 5 (then lock for 1 minute)
    • idle time: 2880 minutes (2 days)
    • password function: min 8 chars, at least 2 different kinds: letters, digits, symbols

Reader / Writer / Application account

  • name:
    • reader / writer approach: suffix _R and _W (ex: CMS_DAQ_R, CMS_DAQ_W)
    • application + roles approach: suffix _APP (ex: CMS_DAQ_APP)
  • System privileges: CREATE SESSION, ALTER SESSION, CREATE SYNONYM, CREATE VIEW, QUERY REWRITE
  • Profile limit:
    • max simultaneous sessions: limited by maximum database sessions
    • password life time: unlimited (for now)
    • max login attemps: 10 (then lock for 1 minute)
    • idle time: 2880 minutes (2 days)
    • password function: min 8 chars, at least 2 different kinds: letters, digits, symbols

Scripts: Grant privileges

By using the scripts below you will be able to have your application to connect to its account (with _R, _W or _APP suffix) and work the same way as within the owner account.

Give the mininal set of necessary privileges to your application accounts. Don't give any delete/update privilege where not needed.

--
-- Run in the owner account and execute the output.
--
set pages 200
set lines 999
set wrap off
 
define USER = 'CMS_DAQ' -- name of the owner account
define GRANTEE = 'CMS_DAQ_W' -- name of the account to be granted privileges, necessary to do for _R also

select 'GRANT SELECT[, INSERT, UPDATE, DELETE] ON ' || object_name || ' TO &GRANTEE;'
  from ALL_OBJECTS
  where object_type in ('TABLE','VIEW')
    and owner = upper('&USER')
union
select 'GRANT SELECT ON ' || object_name || ' TO &GRANTEE;'
  from ALL_OBJECTS
  where object_type in ('SEQUENCE')
    and owner = upper('&USER');

How to access the schema owner's objects

  • the recommended method to access objects on a different schema is to use full qualified names
    • Ex: select col1, col2, ... from schema_owner.table_name where ...
    • this method has better scalability and makes the code more readable (among other advantages)
  • a second choice (to avoid when possible) is to create and use private synonyms on the schema owner objects
  • the use of public synonyms is a third option and should be avoided

Edit | Attach | Watch | Print version | History: r14 < r13 < r12 < r11 < r10 | Backlinks | Raw View | WYSIWYG | More topic actions
Topic revision: r14 - 2008-05-08 - MiguelAnjo
 
    • Cern Search Icon Cern Search
    • TWiki Search Icon TWiki Search
    • Google Search Icon Google Search

    PSSGroup All webs login

This site is powered by the TWiki collaboration platform Powered by PerlCopyright & 2008-2022 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
or Ideas, requests, problems regarding TWiki? use Discourse or Send feedback