Oracle - Χρήστες/Users Βάσης Δεδομένων

Στο άρθρο αυτό θα μιλήσουμε για τους χρήστες μιας βάσης Oracle. Θα δοθούν χρήσιμα scripts που αφορούν την δημιουργία χρηστών και την διαχείρισή τους.

1. Δημιουργία Χρήστη

SQL>create user db_user
          identified by <password>
          default tablespace users
          temporary tablespace temp
          profile default
          account unlock;

Όταν θέλουμε να δημιουργήσουμε έναν καινούργιο χρήστη θα πρέπει πρώτα να ορίζουμε το όνομα του χρήστη μαζί με το password του καθώς και το default tablespace στο οποίο θα μπορεί να γράφει. Μαζί ορίζουμε και το προφίλ ασφαλείας του χρήστη είμαστε έτοιμοι να προχωρήσουμε στην δημιουργία του.

SQL>drop user db_user cascade;

Για να σβήσουμε έναν χρήστη μαζί με τα δεδομένα του τρέχουμε την παραπάνω εντολή.

2. Δικαιώματα Χρηστών 

SQL>grant execute on SYS.DBMS_MONITOR to db_user;

Όταν θέλουμε να δώσουμε την δυνατότητα σε ένα χρήστη να μπορεί να εκτελεί ενέργειες σε αντικείμενα (πίνακες, functions,procedure) άλλου χρήστη, εκτελούμε την παραπάνω εντολή και εκχωρούμε στον χρήστη το δικαίωμα να εκτελεί συγκεκριμένη ενέργεια. Οι ενέργειες που μπορούμε να εκχωρήσουμε είναι: ALTER, CREATE, DROP, DELETE, INSERT, SELECT, UPDATE, EXECUTE. 

SQL>revoke drop any table from db_user;

Για να αφαιρέσουμε κάποιο δικαίωμα από έναν χρήστη τρέχουμε την πιο πάνω εντολή.

3.Ρόλοι Χρηστών 

SQL>create role connect not identified;
          grant create session to connect;

Όταν θέλουμε να ομαδοποιήσουμε δικαιώματα τα οποία στην συνέχεια θα δοθούν σε μια ομάδα χρηστών τότε χρήσιμη είναι η δημιουργία ενός ρόλου. Δημιουργούμε τον ρόλο με την πρώτη εντολή και στην συνέχεια εκχωρούμε στον ρόλο δικαιώματα όπως στο 2.8.2 εκχωρούμε δικαιώματα σε χρήστη. Όπως περιγράφεται στο 2.8.2 μπορούμε να αφαιρέσουμε και δικαιώματα από έναν ρόλο. 

SQL>drop role connect;
Για να σβήσουμε έναν ρόλο τρέχουμε την παραπάνω εντολή.

4. Αλλαγή password χρήστη

SQL>alter user db_user identified by test

Όταν θέλουμε να αλλάξουμε το password ενός χρήστη εκτελούμε την παραπάνω εντολή όπου db_user είναι ο χρήστης που θέλουμε να αλλάξουμε το password και test είναι το νέο password που θέλουμε να του δώσουμε.


5. Script δημιουργίας χρηστών μαζί με τους κωδικούς πρόσβασης (Oracle 11g)

Για να μπορέσουμε στην 11g να πάρουμε από την βάση το script δημιουργίας των χρηστών της βάσης μαζί με τα κωδικοποιημένα passwords τρέχουμε με έναν λογαριασμό με ισχυρά δικαιώματα το παρακάτω:

SQL> set head off
           set pages 0
           set long 9999999
           select dbms_metadata.get_ddl('USER', username) || ';' usercreate from dba_users;


6. Στοιχεία Χρηστών Βάσης

Με το παρακάτω query βλέπουμε όλα τα στοιχεία των χρηστών-σχημάτων της βάσης (το τρέχουμε με ισχυρά δικαιώματα).

SQL> select * from sys.user$

Διαβασε Περισσοτερα

Oracle Temporary Tablespace - Σβήσιμο και ξανά δημιουργία

Στο άρθρο αυτό θα μιλήσουμε για το temporary tablespace μιας βάσης δεδομένων oracle. Δημιουργούμε ένα καινούργιο TEMP1 (default temporary tablespace). Σβήνουμε το παλιό (TEMP), το ξαναδημιουργούμε πιο μικρό και το κάνουμε default temporary για την βάση. Τέλος σβήνουμε το παλιό TEMP1. Έτσι καταλήγουμε με ένα μικρότερο temporary tablespace με το ίδιο όνομα αλλά με πολύ μικρότερο μέγεθος.

SQL> create temporary tablespace temp1 tempfile '/u02/dbfiles/aylo/temp101_u02.dbf' size 2000m 

SQL> alter database default temporary tablespace TEMP1

SQL> drop tablespace TEMP including contents and datafiles

SQL> create temporary tablespace temp tempfile '/u02/dbfiles/aylo/temp.dbf' size 2000m 

SQL> alter database default temporary tablespace TEMP

SQL> drop tablespace TEMP1 including contents and datafiles

Σε περίπτωση που σκάει η εντολή σβησίματος του TEMP tablespace, τρέχουμε το παρακάτω query και με βάση τα αποτελέσματα σκοτώνουμε το session-s που δημιουργούν το πρόβλημα και προχωράμε στην οριστική διαγραφή.

SQL> select se.username username,se.SID sid, se.serial# serial#, se.status status, se.sql_hash_value, se.prev_hash_value,se.machine machine, su.TABLESPACE 

tablespace,su.segtype,su.CONTENTS CONTENTS
from v$session se, v$sort_usage su
where se.saddr=su.session_addr;

SQL> alter system kill session 'sid,serial'; (πχ ‘23,342’ )

Διαβασε Περισσοτερα

Oracle Database - Δημιουργία/Διαγραφή Index Πίνακα

Στο άρθρο αυτό θα μιλήσουμε για την δημιουργία index πίνακα δεδομένων σε μια βάση Oracle. Ακολουθεί ένα ενδεικτικό παράδειγμα και η ανάλυσή του. 

SQL> create index όνομα χρήστη.όνομα index on όνομα χρήστη.όνομα πίνακα
           (πεδίο 1 πίνακα, πεδίο 2 πίνακα)
           logging
           tablespace mm_idx
           pctfree 10
           initrans 2
           maxtrans 255
           storage (
           initial 304k
           next 1m
           minextents 1
           maxextents unlimited
           pctincrease 0
          buffer_pool default
          flash_cache default
         cell_flash_cache default
          )
         noparallel;

Όταν θέλουμε να δημιουργήσουμε έναν index θα πρέπει πρώτα να ορίζουμε το όνομα του index-ευρετηρίου μαζί με το σχήμα στο οποίο θα ανήκει (full name – σχήμα.όνομα index). Στην συνέχεια ορίζουμε τα πεδία του πίνακα στα οποία θα δημιουργηθεί o index. Αφού γίνει αυτό ορίζουμε το tablespace στο οποίο θα αποθηκεύεται ο index και άλλα χαρακτηριστικά που αφορούν αν θα γίνεται log, αν θα είναι parallel κτλ.

SQL>drop index nikos.mm_bf_day_stats;

Για να σβήσουμε έναν index τρέχουμε την παραπάνω εντολή.

Διαβασε Περισσοτερα

Oracle Database - Δημιουργία Partition Πίνακα

Στο άρθρο αυτό θα μιλήσουμε για την δημιουργία partition πίνακα δεδομένων σε μια βάση Oracle. Ακολουθεί ένα ενδεικτικό παράδειγμα και η ανάλυσή του. 

SQL>create tablespace mm_data_cy_2011d
          logging
          datafile '/oradata2/dbfiles/surv/mm_data_cy_2011d.dbf' size 50m
          autoextend on next 100m
          extent management local
          uniform size 500k
          segment space management auto

Πρώτα φτιάχνω τα Tablespaces και μετά τρέχω τις ακόλουθες εντολές ανάλογα με τον πίνακα που θέλω να προσθέσω το συγκεκριμένο partition.

SQL>alter table nikos.mm_bf_day_stats add partition part_date_2012d 
          values less than (to_date(' 2013-01-01 00:00:00', 'syyyy-mm-dd 
          hh24:mi:ss','nls_calendar=gregorian'))
          logging
          nocompress
          tablespace mm_data_cy_2011d
          pctfree 10
          initrans 1
          maxtrans 255
          storage (
          initial 2m
          next 2m
          minextents 1
          maxextents unlimited
          pctincrease 0
          buffer_pool default
          )

Διαβασε Περισσοτερα

Oracle Database - Δημιουργία/Διαγραφή πίνακα

Στο άρθρο αυτό θα μιλήσουμε για την δημιουργία πίνακα δεδομένων σε μια βάση Oracle. Ακολουθεί ένα ενδεικτικό παράδειγμα και η ανάλυσή του. 

SQL> create table όνομα χρήστη.όνομα πίνακα (
           stocks_code number(10),
           companies_code number(10),
           ashscode char(25 byte),
           en_abbreviation varchar2(40 byte),
           listing_date date,
           share_type varchar2(4 byte),
           adts number(25,2),
           a number(5,2),
          smss number(10,2),
          )
          tablespace mm_data
          result_cache (mode default)
          pctused 0
          pctfree 10
          initrans 1
         maxtrans 255
         storage (
            initial 40k
            next 1m
            minextents 1
            maxextents unlimited
           pctincrease 0
           buffer_pool default
           flash_cache default
          cell_flash_cache default
            )
         logging
         nocompress
         nocache
         noparallel
         monitoring;

Όταν θέλουμε να δημιουργήσουμε ένα πίνακα θα πρέπει πρώτα να ορίζουμε το όνομα του πίνακα μαζί με το σχήμα στο οποίο θα ανήκει (full name – σχήμα.όνομα πίνακα). Στην συνέχεια ορίζουμε τα πεδία του πίνακα και τι τύπου θα είναι ανάλογα με το τι θα αποθηκεύσουμε μέσα (αριθμό, αλφαριθμητικό, ημερομηνία). Αφού γίνει αυτό ορίζουμε το tablespace στο οποίο θα αποθηκεύεται ο πίνακας και άλλα χαρακτηριστικά που αφορούν αν θα γίνεται log, αν θα είναι compress κτλ.

SQL>drop table nikos.mm_bf_day_stats cascade constraints purge;

Για να σβήσουμε ένα πίνακα τρέχουμε την παραπάνω εντολή είτε προσθέτοντας την επιλογή cascade constraints (σβήνει όλα τα referential contraints) είτε την επιλογή purge (συμπιέζει και αποδεσμεύει το χώρο που καταλάμβανε ο πίνακας).

Διαβασε Περισσοτερα

Oracle Tablespaces - Χρήσιμα scripts

Στο άρθρο αυτό θα μιλήσουμε για το πως μπορούμε να δημιουργήσουμε oracle tablespaces (λογικός χώρος όπου θα αποθηκεύοντε τα δεδομένα μας) και πως μπορούμε να τα διαχειριστούμε και να τα τροποποιήσουμε κατάλληλα. 

Δημιουργία Oracle Tablespace

             SQL> create tablespace BO_REP_DATA datafile
                       '/oradata/dbfiles/SSP/bo_rep_data.dbf'
                       size 50M
                       autoextend on next 50M
                       maxsize 30000M
                       logging
                       online
                       extent management local autoallocate
                       blocksize 8K
                       segment space management auto
                       flashback on;

Το μονοπάτι στο δίσκο όπου θα αποθηκεύονται τα δεδομένα πρέπει να οριστεί για linux (/oradata/dbfiles/SSP/bo_rep_data.dbf) ή για windows (H:\ORADATA\DBFILES\MPWDB\HERDS_APEX.DBF) λειτουργικό αναλόγως.

Προσθήκη Datafile σε Tablespace

         SQL> alter tablespace USERS add datafile                                                                                '/oradata2/dbfiles/PRTRS/TRS_TRANS_DATA_2009.dbf'
                    size 500M
                    autoextend on
                    next 50M
                    maxsize 20000M

Προσθήκη καινούργιου datafile στο tablespace USERS μεγέθους 500ΜΒ το οποίο γίνεται autoextend όταν φτάσει στο όριό του αυτόματα κατά 50 ΜΒ κάθε φορά και μπορεί να φτάσει μέχρι τα 20GB.

Αλλαγή θέσης Datafile στο file system

         SQL> alter database rename file 
                  '/oradata/dbfiles/PRTRS/trs_trans_data_2007.dbf' to                                                   '/oradata2/dbfiles/PRTRS/trs_trans_data_2007.dbf'

Βάζουμε την βάση σε mount state (περιγράφετε στο 2.1) και εκτελούμε την παρακάτω εντολή με κάποιον χρήστη που να έχει δικαίωμα να το κάνει (DBA privileges)

Αφαίρεση δυνατότητας εγγραφής στοιχείων από χρήστη σε tablespace

        SQL> revoke resourse role;
                 (για να μην μπορεί να γράψει σε όλα τα tablespaces εκτός από τα defaults)
       SQL> alter user DERIVPROD quota 0M on system;
                (για να μην μπορεί να γράψει στο συγκεκριμένο tablespace)

Διαβασε Περισσοτερα

Oracle Audits - Χρήσιμα Queries

Στο άρθρο αυτό θα μιλήσουμε για χρήσιμα queries που αφορούν το auditing μιας βάσης oracle. 

1. Με το παρακάτω query μπορούμε να δούμε πότε αλλάχτηκε τελευταία φορά το password όλων των χρηστών της βάσης:

SQL>select name,ptime passwd_change_time from sys.user$ where type#='1';

2. Με το παρακάτω query μπορούμε να δούμε πότε συνδέθηκαν τελευταία φορά οι χρήστες στην βάση:

SQL>select userid, max(nvl(ntimestamp#, sysdate)) last_logon from sys.aud$ where action#=100 and returncode=0 group by userid;

3. Με το επόμενο query μπορούμε να δούμε όλες τις εγγραφές που έχουν καταγραφεί στον audit table: 

SQL>select * from SYS.AUD$ where LOGOFF$TIME > to_date('2010/07/12', 'yyyy/mm/dd') and
rownum<10;

Με το παρακάτω query μπορούμε να δούμε όλες τις IPs και τα τερματικά που έχουν συνδεθεί στην βάση με χρήση auditing και sys λογαριασμό (από τα πιο χρήσιμα). 

SQL>select userid, terminal, spare1, substr(s1,1,instr(s1,')')-1) IP,returncode, count(*) from
(select A.*, substr(comment$text,instr(comment$text,'HOST=')+5,100) s1 from aud$ a where action# in (100,101) )
group by userid, terminal, spare1, substr(s1,1,instr(s1,')')-1), returncode
order by IP;

Με το ακόλουθο query μπορούμε να δούμε όλα τα τερματικά που έχουν συνδεθεί στην βάση με χρήση auditing και sys λογαριασμό και έχουν κάνει delete ή select (βλέπουμε ότι ορίσουμε στο ACTION_NAME και για το χρονικό διάστημα που θέλουμε να δούμε). 

SQL>select os_username,username,timestamp,owner,obj_name,action_name
from dba_audit_trail
where extended_timestamp > to_date('2011/07/19
17:56:00', 'yyyy/mm/dd hh24:mi:ss') and extended_timestamp <
to_date('2011/07/19 18:07:00', 'yyyy/mm/dd hh24:mi:ss')and
action_name='delete';

Τέλος με τα παρακάτω queries μπορούμε να δούμε όλα τα audits options που έχουν ενεργοποιηθεί στην βάση μας:

SQL>select * from dba_priv_audit_opts;
SQL>select * from dba_stmt_audit_opts;

Διαβασε Περισσοτερα