Latihan User - Hak Akses

MELIHAT USER YANG SUDAH ADA

template1=# select * from pg_catalog.pg_user;

usename | usesysid | usecreatedb | usesuper | usecatupd | userepl | passwd | valuntil | useconfig

----------+----------+-------------+----------+-----------+---------+----------+----------+-----------

postgres | 10    |  t  |   t  |  t  |  t  |  ********  | |

zahra    | 16529 |  t  |   f  |  f  |  f  |  ********  | |

fatimah  | 16531 |  t  |   f  |  f  |  f  |  ********  | |

(3 rows)

MEMBUAT USER AHMAD, UMAR, ALI

template1=# create user ahmad with password 'ahmad' createdb;

CREATE ROLE

template1=# create user umar with password 'umar' createdb;

CREATE ROLE

template1=# create user ali with password 'ali' createdb;

CREATE ROLE

MELIHAT GRUP YANG SUDAH ADA

template1=# select * from pg_catalog.pg_group;

                groname | grosysid | grolist

      -----------+----------+---------

      marketing | 16546 | {}

      (1 row)

MEMASUKAN USER KEDALAM GRUP

template1=# alter group marketing add user ahmad, fatimah, umar, ali;

ALTER ROLE

template1=# select * from pg_catalog.pg_group;

           groname | grosysid | grolist

          -----------+----------+---------------------------

          marketing | 16546 | {16543,16531,16544,16545}

          (1 row)

MENAMPILKAN LIST USER YANG ADA DI GRUP MARKETING

template1=# select groname, usename from pg_group, pg_user where usesysid = any(grolist);

                     groname | usename

                    -----------+---------

                    marketing | fatimah

                    marketing | ahmad

                    marketing | umar

                    marketing | ali

                    (4 rows)

Melihat user yang ada didatabase

template1=# select usename,usesysid from pg_catalog.pg_user;

            usename | usesysid          ----------+----------           postgres | 10               zahra| 16529             fatimah| 16531               ahmad| 16543               umar | 16544                ali | 16545                      (6 rows)

Melihat group yang ada di database

template1=# select * from pg_catalog.pg_group;

groname   | grosysid | grolist

-----------+----------+---------------------------

marketing | 16546 | {16543,16531,16544,16545}

(1 row)

melihat user yang ada digroup marketing

template1=# select groname,usename from pg_group,pg_user where usesysid = any (grolist);

groname    | usename

-----------+---------

marketing | fatimah

marketing | ahmad

marketing | umar

marketing | ali

(4 rows)

MENJADIKAN SEBUAH USER YANG AWALNYA TIDAK BISA MEMBUAT SEBUAH DATABASE MENJADI BISA MEMBUAT DATABASE

-Melihat apakah user bisa membuat database atau tidak 

template1=# select usename,usesysid,usecreatedb from pg_catalog.pg_user;

usename | usesysid | usecreatedb

----------+----------+-------------

postgres| 10       | t

zahra   | 16529    | t

fatimah | 16531    | t

ahmad   | 16543    | t

umar    | 16544    | t

ali     | 16545    | t

(6 rows)

RUMUSNYA:

template=# alter user bowo with createdb;

KALO SEBALIKNYA,  DARI BISA KE TIDAK BISA, RUMUSNYA:

template=# alter user bowo with nocreatedb;

MELIHAT GROUP

template1=# SELECT * FROM pg_catalog.pg_group;

groname | grosysid | grolist

-----------+----------+---------------------------------------

dosen   | 16547    | {16545,16549}

marketing | 16546  | {16543,16531,16544,16548,16545,16549}

(2 rows)

KELUAR DARI TEMPLATE 1

template1=# \q

AHMAD Masuk kedalam dblatihan

postgres@henny-K84L:~$ psql -U ahmad dblatihan

Password for user ahmad:

psql (9.1.9)

Type "help" for help.

MELIHAT OWNER PEMILIK TABLE

dblatihan=> \d

List of relations

Schema | Name | Type | Owner

--------+----------------+----------+----------

public | agama | table | postgres

public | pegawai | table | postgres

public | pegawai_id_seq | sequence | postgres

(3 rows)

MEMBUAT TABEL DEPARTEMEN

dblatihan=> create table departemen (

dblatihan(> id serial primary key, nama varchar(30)

dblatihan(> );

NOTICE: CREATE TABLE will create implicit sequence "departemen_id_seq" for serial column "departemen.id"

NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "departemen_pkey" for table "departemen"

CREATE TABLE

MELIHAT TABEL AGAMA, NAMUN KARENA OWNERNYA POSTGRES (LIHAT DIATAS YG KUNING) MAKA PERMISSION DENIED

dblatihan=> select * from agama;

ERROR: permission denied for relation agama

dblatihan=> \q

MASUK KE DBLATIHAN MENGGUNAKAN POSTGRES

postgres@henny-K84L:~$ psql -U postgres dblatihan;

Password for user postgres:

psql (9.1.9)

Type "help" for help.

MEMBERIKAN HAK AKSES SEPENUHNYA KE AHMAD

dblatihan=# grant all on agama,pegawai to ahmad;

GRANT

dblatihan-# \d

List of relations

Schema | Name | Type | Owner

--------+-------------------+----------+----------

public | agama | table | postgres

public | departemen | table | ahmad

public | departemen_id_seq | sequence | ahmad

public | pegawai | table | postgres

public | pegawai_id_seq | sequence | postgres

(5 rows)

dblatihan-# \q

MASUK KE DBLATIHAN USER AHMAD

postgres@henny-K84L:~$ psql -U ahmad dblatihan;

Password for user ahmad:

psql (9.1.9)

Type "help" for help.

dblatihan=> \d

List of relations

Schema | Name | Type | Owner

--------+-------------------+----------+----------

public | agama | table | postgres

public | departemen | table | ahmad

public | departemen_id_seq | sequence | ahmad

public | pegawai | table | postgres

public | pegawai_id_seq | sequence | postgres

(5 rows)

KARENA SUDAH DIBERIKAN HAK AKSES, MAKA AKAN TAMPIL

dblatihan=> select * from agama;

id | nama

----+----------

1 | Islam

2 | Kristen

3 | Khatolik

(3 rows)

MEMBERIKAN HAK AKSES KE ALI (HANYA SELECT)

dblatihan=> grant select on departemen to ali;

GRANT

dblatihan=> \q

postgres@henny-K84L:~$ psql -U ali dblatihan;

Password for user ali:

psql (9.1.9)

Type "help" for help.

dblatihan=> drop table departemen;

ERROR: must be owner of relation departemen

AHMAD MASUK KE DBLATIHAN

postgres@henny-K84L:~$ psql -U ahmad dblatihan;

Password for user ahmad:

psql (9.1.9)

Type "help" for help.

AHMAD MEMBERIKAN HAK AKSES KE GROUP

dblatihan=> grant all on departemen to group marketing;

GRANT

AHMAD MENCABUT HAK AKSES DARI GROUP

dblatihan=> revoke all on departemen from group marketing;

REVOKE