Postgres grant select access on all tables in a database

It is not possible to create a user with read only access on a particular database. Instead we can do it at schema level.

1) Grant connect to database

GRANT CONNECT ON DATABASE <db name> to <user name>;

2) Grant usage on schema

GRANT USAGE ON SCHEMA <schema name> TO <user name>;

3) Grant select on all tables in a schema using

GRANT SELECT ON ALL TABLES IN SCHEMA <schema name> TO <user name>;

4) Also make this privilege available for new tables added in future using

ALTER DEFAULT PRIVILEGES IN SCHEMA <schema name> GRANT SELECT ON TABLES TO <user name>;

 

So, if we have many schemas we can simply generate ‘GRANT’ statements as mentioned below : Read More

Difference between undo segment and redo logs

A point of confusion for a DBA would be the distinction between undo and redo.

–> Basically undo tablespace stores the before values of changed data blocks whenever we issue a insert, update or delete statement (DML operation) . When we issue any DML statement the changed blocks are stored in buffer cache and the before values for those changed blocks in UNDO segments. Whenever we issue a ROLLBACK command it uses the undo segment to rollback to previous value and even UNDO provides the read consistency to a user as he can only see the previous values until a transaction is committed, for example a user 1 may run select statement on a table being modified by user 2, while user 1 can only see the unchanged values until user 2 commits his DML statement.

How long a undo data stored in the database?
Oracle provides flexibility of how long should undo data be stored with the help of undo_retention parameter. We can set undo_management parameter to automatic for oracle to manage undo retention(default), or even set this value manually and it’s value should be greater than the time taken by the longest running query in your database. Read More

Create disk partitions and disk lables for ASM instance

Creating ASM disks and adding to existing diskgroups

As a root user, check for available disks :

fdisk -l
Disk /dev/vdb: 107.4 GB, 107374182400 bytes
16 heads, 63 sectors/track, 208050 cylinders
Units = cylinders of 1008 * 512 = 516096 bytes

Create new partitions

fdisk /dev/vdb
Command (m for help): p
Command (m for help): n
Command action
 e extended
 p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-208050, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-208050, default 208050): +30G
Command (m for help): p
Command (m for help): n
Command action
 e extended
 p primary partition (1-4)
p
Partition number (1-4): 2
First cylinder (31-208050, default 31):
Using default value 31
Last cylinder, +cylinders or +size{K,M,G} (31-208050, default 208050): +30G
Command (m for help): w
The partition table has been altered!

Install following rpms : Read More

Cloning Oracle home and creating a new database

Oracle database Installation using binaries copied from other server (Perl clone on RHEL/Centos):

1. Make sure following rpms are installed :

binutils 
compat-libcap1 
compat-libstdc++-33
gcc 
gcc-c++ 
glibc 
glibc-devel 
ksh 
libgcc 
libstdc++ 
libstdc++-devel 
libaio 
libaio-devel 
make 
sysstat

2. Create a new user and group who will be the owner of oracle binaries.

groupadd dba
useradd -g dba oracle --> -g to add dba as primary group

3. Check ulimits (/etc/security/limits.conf) Read More

Oracle data dictionary view does not exist error

Error while accessing catalog view DBA_DATA_FILES.

SQL> desc DBA_DATA_FILES
ERROR:
ORA-04043: object DBA_DATA_FILES does not exist
SQL> desc DBA_segments
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 SEGMENT_NAME                                       VARCHAR2(81)
 PARTITION_NAME                                     VARCHAR2(30)
 SEGMENT_TYPE                                       VARCHAR2(18)
 SEGMENT_SUBTYPE                                    VARCHAR2(10)
 TABLESPACE_NAME                                    VARCHAR2(30)
 HEADER_FILE                                        NUMBER
 HEADER_BLOCK                                       NUMBER
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 EXTENTS                                            NUMBER
 INITIAL_EXTENT                                     NUMBER
 NEXT_EXTENT                                        NUMBER
 MIN_EXTENTS                                        NUMBER
 MAX_EXTENTS                                        NUMBER
 MAX_SIZE                                           NUMBER
 RETENTION                                          VARCHAR2(7)
 MINRETENTION                                       NUMBER
 PCT_INCREASE                                       NUMBER
 FREELISTS                                          NUMBER
 FREELIST_GROUPS                                    NUMBER
 RELATIVE_FNO                                       NUMBER
 BUFFER_POOL                                        VARCHAR2(7)
 FLASH_CACHE                                        VARCHAR2(7)
 CELL_FLASH_CACHE                                   VARCHAR2(7)

While we are able to access all other dictionary views, unable to access only dba_data_files .

FIX :

Read More

xtrabackup fails with error number 23

innobackupex fails with below error:

InnoDB: Operating system error number 23 in a file operation.
InnoDB: Error number 23 means 'Too many open files in system'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html
InnoDB: Error: could not open single-table tablespace file

Issue :

Innobackupex is failing due to server restriction on open file descriptors for ‘mysql’ user.

[email protected][/mysql/data] ulimit -Hn
32000

Ulimit hard limit is only set to 32k, we can estimate the ulimit required by Mysql user by looking at the number of partitions/tables we have.

[email protected][/mysql/data] find . -name "*.ibd"|wc -l
185293

Or

mysql> select count(*) from information_schema.partitions;
+----------+
| count(*) |
+----------+
|   185433 |
+----------+
1 row in set (1.68 sec)

Fix :

Read More

Oracle db link usage

Way to find out db link usage by database objects :

 

  • Below query can be used to find all DB links in oracle database
SQL> select * from dba_db_links;

OWNER DB_LINK USERNAME HOST CREATED
------------------------------ ------------------------------ ------------------------------ -------------------------------------------------- ---------
PUBLIC TESTDB.WORLD LINKUSER testdb 24-MAY-11

 

  • Find DB link usage in synonyms
SQL> SELECT * FROM dba_synonyms WHERE TABLE_OWNER NOT IN('SYSTEM','SYS') AND DB_LINK IS NOT NULL;

OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK
------------------------------ ------------------------------ ------------------------------ ------------------------------ ----------------------------------------
PRODUSER COUNTS APPDEV COUNTS TESTDB.WORLD
PRODUSER COUNTS_S APPDEV COUNTS_S TESTDB.WORLD

 

  • DB link usage in VIEWS

Oracle internal view ‘dba_views.text‘ column contains view ddl. Below pl/sql code is helpful in finding views which use this DB link.

declare
 cursor text is select text,view_name from dba_views where owner not in('SYS','SYSTEM');
 v clob;
 vn varchar2(30);
begin
 for c in text loop
 v:=c.text;
 vn:=c.view_name;
 if ( v like '%TESTDB.WORLD%') then
 dbms_output.put_line( 'view is '||vn);
 end if;
 end loop;
end;

 

  • DB link usage by PROCEDURES or FUNCTIONS:

We can verify db link usage in any procedures or package body using data dictionary view dba_source.

SQL> select OWNER,NAME,TYPE from dba_source where TEXT like '%@TESTDB%' or TEXT like '%@testdb%';

OWNER NAME TYPE
------------------------------ ------------------------------ ------------
KHAN ACTIVITY_PULL_TESTDB PACKAGE BODY

 

Mysql Galera WSREP_SST: [ERROR] xtrabackup_checkpoints missing

Adding new node to galera fails with following error :

WSREP_SST: [INFO] Waiting for SST streaming to complete! (20160607 12:21:20.988)
160607 12:21:23 [Note] WSREP: (3d7453d3, 'tcp://0.0.0.0:4567') turning message relay requesting off
WSREP_SST: [ERROR] xtrabackup_checkpoints missing, failed innobackupex/SST on donor (20160607 12:21:30.966)
WSREP_SST: [ERROR] Cleanup after exit with status:2 (20160607 12:21:30.968)
160607 12:21:30 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '10.103.34.27' --auth 'sstuser:dev2' --datadir '/var/untd/mysql/data/' --defaults-file '/etc/my.cnf' --parent '4858': 2 (No such file or directory)
160607 12:21:30 [ERROR] WSREP: Failed to read uuid:seqno from joiner script.
160607 12:21:30 [ERROR] WSREP: SST failed: 2 (No such file or directory)
160607 12:21:30 [ERROR] Aborting

160607 12:21:30 [Warning] WSREP: 1 (hadoop-dn1qa.cs1cmates.com): State transfer to 0 (node2) failed: -22 (Invalid argument)
160607 12:21:30 [ERROR] WSREP: gcs/src/gcs_group.cpp:long int gcs_group_handle_join_msg(gcs_group_t*, const gcs_recv_msg_t*)():717: Will never receive state. Need to abort.
160607 12:21:30 [Note] WSREP: gcomm: terminating thread
160607 12:21:30 [Note] WSREP: gcomm: joining thread
160607 12:21:30 [Note] WSREP: gcomm: joining thread
160607 12:21:30 [Note] WSREP: gcomm: closing backend
160607 12:21:31 [Note] WSREP: gcomm: closed
160607 12:21:31 [Note] WSREP: /usr/sbin/mysqld: Terminated.
160607 12:21:32 mysqld_safe mysqld from pid file /var/untd/mysql/log/mysqld.pid ended

Fix :

Read More

Basic User Administration commands in NETEZZA

  • CONNECTING AS A USER
nzsql -h `hostname` -u testuser -pw testuser
  • GRANT/ REVOKE SELECT ON DATABASE ‘COMMON’
system(admin)=> grant list on common to testuser;
GRANT
common(admin)=> grant select on common to testuser;
common(admin)=> revoke all on common from testuser;
  • DISPLAY PERMISSIONS
common(admin)=> \dpu testuser
  • GRANT LIST/SELECT ON ALL TABLES IN A DATABASE ‘COMMON’
system(admin)=> \c common
common(admin)=> grant list on table to testuser;
common(admin)=> GRANT SELECT ON TABLE TO TESTUSER;

MORE -
grant all on table to <user>;
grant all on view to <user>;
grant all on procedure to <user>;
grant all on function to <user>;
grant all on sequence to <user>;
grant all on synonym to <user>;

Read More

Creating or Dropping a database in Oracle 11g

CREATING A NEW DATABASE

  • Creating a initialization parameter file at location $ORACLE_HOME/dbs which is the default location where oracle looks for a init file .

Lets create a database by name ‘test’ :

[[email protected]]$ cat $ORACLE_HOME/dbs/inittest.ora
db_name=test
db_block_size=8192
control_files=(/home/oracle/app/oracle/oradata/test/control01.ctl,/home/oracle/app/oracle/oradata/test/control02.ctl)
  • Startup instance in nomount mode and create a database
[[email protected] ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 18 23:55:15 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 150667264 bytes
Fixed Size 1335080 bytes
Variable Size 92274904 bytes
Database Buffers 50331648 bytes
Redo Buffers 6725632 bytes

SQL> select status from v$instance;

STATUS
------------
NOMOUNT

SQL> CREATE DATABASE test
LOGFILE
GROUP 1 ('/home/oracle/app/oracle/oradata/test/redo01a.log', '/home/oracle/app/oracle/oradata/test/redo01b.log') SIZE 10M,
GROUP 2 ('/home/oracle/app/oracle/oradata/test/redo02a.log', '/home/oracle/app/oracle/oradata/test/redo02b.log') SIZE 10M
DATAFILE
 '/home/oracle/app/oracle/oradata/test/system01.dbf' SIZE 200M
SYSAUX DATAFILE '/home/oracle/app/oracle/oradata/test/sysaux01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 2000M
UNDO TABLESPACE "UNDOTBS1" DATAFILE '/home/oracle/app/oracle/oradata/test/undotbs01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/home/oracle/app/oracle/oradata/test/temp01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 500M
DEFAULT TABLESPACE USERS DATAFILE '/home/oracle/app/oracle/oradata/test/users01.dbf' SIZE 50M AUTOEXTEND ON MAXSIZE 200M
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16;

Database created.


SQL> select status from v$instance;

STATUS
------------
OPEN

Read More