Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE TABLE DOCHOD (NRP int, NRT int, KWOTA int);
- CREATE TABLE TEMAT (NRT int, NAZWA varchar(255), NRKT int);
- CREATE TABLE ZESPOL (NRZ int, NRKZ int, NAZWA varchar(255));
- CREATE TABLE PRACOWNIK (NRP int, NAZWISKO varchar(255), IMIE varchar(255), ADRES varchar(255));
- CREATE TABLE STANOWISKO (NRS int, STANOWISKO varchar(255));
- 1.
- 1. CREATE USER admin IDENTIFIED BY admin DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
- SELECT * FROM all_users;
- USERNAME USER_ID CREATED
- ------------------------------ ---------------------- -------------------------
- PROJEKTANT 89 03-MAR-12
- ADMIN 88 03-MAR-12
- BI 87 26-NOV-11
- PM 86 26-NOV-11
- SH 85 26-NOV-11
- IX 84 26-NOV-11
- OE 83 26-NOV-11
- SCOTT 81 15-OCT-07
- OWBSYS 77 15-OCT-07
- FLOWS_030000 76 15-OCT-07
- APEX_PUBLIC_USER 75 15-OCT-07
- FLOWS_FILES 74 15-OCT-07
- MGMT_VIEW 73 15-OCT-07
- SYSMAN 71 15-OCT-07
- WK_TEST 70 15-OCT-07
- WKPROXY 68 15-OCT-07
- WKSYS 67 15-OCT-07
- SPATIAL_CSW_ADMIN_USR 65 15-OCT-07
- SPATIAL_WFS_ADMIN_USR 62 15-OCT-07
- HR 82 26-NOV-11
- MDDATA 60 15-OCT-07
- OLAPSYS 56 15-OCT-07
- MDSYS 52 15-OCT-07
- SI_INFORMTN_SCHEMA 51 15-OCT-07
- ORDPLUGINS 50 15-OCT-07
- ORDSYS 49 15-OCT-07
- ANONYMOUS 42 15-OCT-07
- XS$NULL 2147483638 15-OCT-07
- CTXSYS 39 15-OCT-07
- EXFSYS 38 15-OCT-07
- WMSYS 28 15-OCT-07
- DBSNMP 27 15-OCT-07
- XDB 41 15-OCT-07
- ORACLE_OCM 20 15-OCT-07
- TSMSYS 14 15-OCT-07
- DIP 13 15-OCT-07
- OUTLN 9 15-OCT-07
- SYSTEM 5 15-OCT-07
- SYS 0 15-OCT-07
- 39 rows selected
- SELECT * FROM dba_ts_quotas;
- TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DROPPED
- ------------------------------ ------------------------------ ---------------------- ---------------------- ---------------------- ---------------------- -------
- SYSAUX OLAPSYS 16318464 -1 1992 -1 NO
- SYSAUX WK_TEST 14155776 -1 1728 -1 NO
- SYSAUX SYSMAN 129826816 -1 15848 -1 NO
- SYSAUX FLOWS_FILES 458752 -1 56 -1 NO
- 4 rows selected
- 2. CREATE USER projektant IDENTIFIED BY admin DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp ACCOUNT LOCK;
- 3. Brak uprawnien session connect
- Jako system:
- GRANT create session TO admin;
- Jako admin:
- ALTER USER projektant QUOTA 100M ON users;
- Brak uprawnien
- Jako system:
- ALTER USER projektant QUOTA 100M ON users;
- GRANT create succeeded.
- ALTER USER projektant succeeded.
- 4. SELECT * FROM dba_ts_quotas;
- TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DROPPED
- ------------------------------ ------------------------------ ---------------------- ---------------------- ---------------------- ---------------------- -------
- SYSAUX OLAPSYS 16318464 -1 1992 -1 NO
- SYSAUX WK_TEST 14155776 -1 1728 -1 NO
- SYSAUX SYSMAN 129826816 -1 15848 -1 NO
- SYSAUX FLOWS_FILES 458752 -1 56 -1 NO
- USERS PROJEKTANT 0 104857600 0 12800 NO
- 5 rows selected
- CREATE USER ksiegowa IDENTIFIED BY ksiegowa;
- GRANT create session TO ksiegowa;
- SELECT * FROM user_users;
- USERNAME USER_ID ACCOUNT_STATUS LOCK_DATE EXPIRY_DATE DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED INITIAL_RSRC_CONSUMER_GROUP EXTERNAL_NAME
- ------------------------------ ---------------------- -------------------------------- ------------------------- ------------------------- ------------------------------ ------------------------------ ------------------------- ------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- KSIEGOWA 90 OPEN 30-AUG-12 USERS TEMP 03-MAR-12 DEFAULT_CONSUMER_GROUP
- 1 rows selected
- ALTER USER ksiegowa DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
- SQL Error: ORA-01031: insufficient privileges
- 01031. 00000 - "insufficient privileges"
- d)
- ALTER USER ksiegowa DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
- jako system
- 6. create user pracownik identified by pracownik DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
- alter user pracownik identified by pracownik password expire;
- 7. create user student identified by student DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA 10M ON appl_data;
- SQL Error: ORA-00959: tablespace 'APPL_DATA' does not exist
- 00959. 00000 - "tablespace '%s' does not exist"
- 8.
- SELECT USERNAME,PROFILE,ACCOUNT_STATUS FROM DBA_USERS;
- SELECT * FROM DBA_TS_QUOTAS;
- USERNAME PROFILE ACCOUNT_STATUS
- ------------------------------ ------------------------------ --------------------------------
- MGMT_VIEW DEFAULT OPEN
- SYS DEFAULT OPEN
- SYSTEM DEFAULT OPEN
- DBSNMP MONITORING_PROFILE OPEN
- SYSMAN DEFAULT OPEN
- KSIEGOWA DEFAULT OPEN
- ADMIN DEFAULT OPEN
- SCOTT DEFAULT EXPIRED
- PRACOWNIK DEFAULT EXPIRED
- PROJEKTANT DEFAULT LOCKED
- OUTLN DEFAULT EXPIRED & LOCKED
- FLOWS_FILES DEFAULT EXPIRED & LOCKED
- MDSYS DEFAULT EXPIRED & LOCKED
- ORDSYS DEFAULT EXPIRED & LOCKED
- EXFSYS DEFAULT EXPIRED & LOCKED
- WMSYS DEFAULT EXPIRED & LOCKED
- WKSYS WKSYS_PROF EXPIRED & LOCKED
- WK_TEST DEFAULT EXPIRED & LOCKED
- CTXSYS DEFAULT EXPIRED & LOCKED
- ANONYMOUS DEFAULT EXPIRED & LOCKED
- XDB DEFAULT EXPIRED & LOCKED
- WKPROXY DEFAULT EXPIRED & LOCKED
- ORDPLUGINS DEFAULT EXPIRED & LOCKED
- FLOWS_030000 DEFAULT EXPIRED & LOCKED
- OWBSYS DEFAULT EXPIRED & LOCKED
- SI_INFORMTN_SCHEMA DEFAULT EXPIRED & LOCKED
- OLAPSYS DEFAULT EXPIRED & LOCKED
- ORACLE_OCM DEFAULT EXPIRED & LOCKED
- TSMSYS DEFAULT EXPIRED & LOCKED
- XS$NULL DEFAULT EXPIRED & LOCKED
- BI DEFAULT EXPIRED & LOCKED
- PM DEFAULT EXPIRED & LOCKED
- MDDATA DEFAULT EXPIRED & LOCKED
- IX DEFAULT EXPIRED & LOCKED
- SH DEFAULT EXPIRED & LOCKED
- DIP DEFAULT EXPIRED & LOCKED
- OE DEFAULT EXPIRED & LOCKED
- APEX_PUBLIC_USER DEFAULT EXPIRED & LOCKED
- HR DEFAULT EXPIRED & LOCKED
- SPATIAL_CSW_ADMIN_USR DEFAULT EXPIRED & LOCKED
- SPATIAL_WFS_ADMIN_USR DEFAULT EXPIRED & LOCKED
- 41 rows selected
- TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DROPPED
- ------------------------------ ------------------------------ ---------------------- ---------------------- ---------------------- ---------------------- -------
- SYSAUX OLAPSYS 16318464 -1 1992 -1 NO
- SYSAUX WK_TEST 14155776 -1 1728 -1 NO
- SYSAUX SYSMAN 129826816 -1 15848 -1 NO
- SYSAUX FLOWS_FILES 458752 -1 56 -1 NO
- USERS PROJEKTANT 0 104857600 0 12800 NO
- 5 rows selected
- CZESC II
- 1. GRANT create session, create user, alter user, drop user, create role, grant any role, grant any privilege TO admin WITH ADMIN OPTION;
- 2.
- SELECT * FROM user_sys_privs;
- USERNAME PRIVILEGE ADMIN_OPTION
- ------------------------------ ---------------------------------------- ------------
- ADMIN ALTER USER YES
- ADMIN GRANT ANY ROLE YES
- ADMIN CREATE SESSION YES
- ADMIN CREATE ROLE YES
- ADMIN CREATE USER YES
- ADMIN GRANT ANY PRIVILEGE YES
- ADMIN DROP USER YES
- 7 rows selected
- SELECT * FROM session_privs;
- PRIVILEGE
- ----------------------------------------
- CREATE SESSION
- CREATE USER
- ALTER USER
- DROP USER
- CREATE ROLE
- GRANT ANY ROLE
- GRANT ANY PRIVILEGE
- 7 rows selected
- 3. CREATE USER ad1 IDENTIFIED BY ad1;
- GRANT create session, create user, alter user, drop user, create role, grant any role, grant any privilege TO ad1 WITH ADMIN OPTION;
- 4.
- SELECT * FROM user_sys_privs;
- USERNAME PRIVILEGE ADMIN_OPTION
- ------------------------------ ---------------------------------------- ------------
- AD1 CREATE SESSION YES
- AD1 GRANT ANY ROLE YES
- AD1 CREATE ROLE YES
- AD1 GRANT ANY PRIVILEGE YES
- AD1 ALTER USER YES
- AD1 DROP USER YES
- AD1 CREATE USER YES
- 7 rows selected
- 5.
- REVOKE drop user FROM admin;
- 6. DROP USER ad1;
- SQL Error: ORA-01031: insufficient privileges
- 01031. 00000 - "insufficient privileges"
- 7. SELECT * FROM user_sys_privs;
- USERNAME PRIVILEGE ADMIN_OPTION
- ------------------------------ ---------------------------------------- ------------
- AD1 CREATE SESSION YES
- AD1 GRANT ANY ROLE YES
- AD1 CREATE ROLE YES
- AD1 GRANT ANY PRIVILEGE YES
- AD1 ALTER USER YES
- AD1 CREATE USER YES
- 8.
- GRANT create session, create table, alter any table, select any table TO projektant;
- 9.
- ALTER USER projektant QUOTA 15k ON users;
- 10.
- aCCOUNT IS LOCKED
- ALTER USER projektant ACCOUNT unLOCK;
- CREATE TABLE s1Nowa1(p1 number(4), p2 number(8), p3 varchar2(20));
- SQL Error: ORA-01536: space quota exceeded for tablespace 'USERS'
- 01536. 00000 - "space quota exceeded for tablespace '%s'"
- Nie zostan utworzone
- 11.
- select grantee, privilege from DBA_SYS_PRIVS where ADMIN_OPTION = 'YES' UNION select grantee, granted_role from dba_role_privs where ADMIN_OPTION = 'YES';
- z uprawnieniami system!!!
- UPRAWNIENIA OBIEKTOWE
- 1.
- GRANT SELECT, UPDATE, INSERT ON DOCHODY TO ksiegowa;
- GRANT SELECT, UPDATE, INSERT ON PRACOWNIK TO ksiegowa;
- GRANT SELECT, UPDATE, INSERT ON ZESPOL TO ksiegowa;
- GRANT SELECT, UPDATE, INSERT ON TEMAT TO ksiegowa;
- GRANT SELECT, succeeded.
- GRANT SELECT, succeeded.
- GRANT SELECT, succeeded.
- GRANT SELECT, succeeded.
- 2.
- GRANT create session TO pracownik;
- GRANT SELECT ON pracownik TO pracownik;
- GRANT SELECT ON temat TO pracownik;
- GRANT update(nrp, nrt) ON dochod TO pracownik with grant option;
- 3.
- Zmiana hasla
- alter user pracownik identified by pracownik;
- select * from user_tab_privs;
- GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY
- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------- --------- ---------
- PRACOWNIK SYSTEM TEMAT SYSTEM SELECT NO NO
- PRACOWNIK SYSTEM PRACOWNIK SYSTEM SELECT NO NO
- 2 rows selected
- select * from user_col_privs;
- GRANTEE OWNER TABLE_NAME COLUMN_NAME GRANTOR PRIVILEGE GRANTABLE
- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------- ---------
- PRACOWNIK SYSTEM DOCHOD NRT SYSTEM UPDATE YES
- PRACOWNIK SYSTEM DOCHOD NRP SYSTEM UPDATE YES
- 2 rows selected
- 4.
- select * from pracownik;
- SQL Error: ORA-00942: table or view does not exist
- 00942. 00000 - "table or view does not exist"
- 5.
- create user nowy IDENTIFIED BY nowy DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
- 6.
- GRANT SELECT, UPDATE, INSERT ON DOCHODY TO pracownik;
- Brak tabeli
- GRANT create session TO nowy;
- SQL Error: ORA-01031: insufficient privileges
- 01031. 00000 - "insufficient privileges"
- 7.
- revoke select on pracownik from pracownik;
- 8.
- brak session privilages
- 9.
- select grantee, privilege, table_name from DBA_tab_PRIVS where grantable = 'YES' UNION select grantee, privilege, table_name from DBA_col_PRIVS where grantable = 'YES';
- ROLE
- 1.
- create role admin_role;
- grant create session, create user, alter user, drop user, create role, grant any role, grant any privilege to admin_role;
- 2.
- select * from dba_role_privs;
- select * from dba_roles;
- select * from dba_sys_privs where granted_role = 'admin_role';
- select * from dba_role_privs where grantee = 'SYSTEM';
- GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE
- ------------------------------ ------------------------------ ------------ ------------
- SYSTEM AQ_ADMINISTRATOR_ROLE YES YES
- SYSTEM ADMIN_ROLE YES YES
- SYSTEM DBA YES YES
- SYSTEM MGMT_USER NO YES
- 3. grant admin_role to admin;
- select * from user_role_privs;
- select * from dba_roles;
- 4.
- select * from session_privs;.
- select * from session_roles;
- pusty
- select * from user_role_privs;
- pusty
- select * from role_sys_privs;
- pusty
- 5. create role szuk_tab;
- grant select any table to szuk_tab;
- 6.
- create role tw_tab;
- grant create table, drop any table to tw_tab;
- 7. CREATE USER asystent IDENTIFIED BY asystent DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
- grant create session to asystent;
- 8.
- select * from stanowisko;
- SQL Error: ORA-00942: table or view does not exist
- 00942. 00000 - "table or view does not exist"
- 9.
- grant szuk_tab to tw_tab;
- 10. grant tw_tab to asystent;
- 11.select * from PRACOWNIK;
- SQL Error: ORA-00942: table or view does not exist
- 00942. 00000 - "table or view does not exist"
- 12. grant szuk_tab to asystent;
- create table test (tp int);
- no privilages on table user
- CZESC III
- 1.
- CREATE PROFILE projekt_prac LIMIT
- SESSIONS_PER_USER 5
- CPU_PER_CALL UNLIMITED
- IDLE_TIME 60;
- SELECT * FROM dba_profiles where profile = 'projekt_prac';
- PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT
- ------------------------------ -------------------------------- ------------- ----------------------------------------
- 0 rows selected
- SELECT * FROM sys.dba_profiles where profile = 'projekt_prac';
- PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT
- ------------------------------ -------------------------------- ------------- ----------------------------------------
- 0 rows selected
- 2.
- ALTER PROFILE projekt_prac LIMIT
- SESSIONS_PER_USER 1
- CPU_PER_CALL 7
- IDLE_TIME 1
- LOGICAL_READS_PER_SESSION 20;
- set timing on;
- ALTER PROFILE projekt_prac succeeded.
- 3.
- ALTER USER projektant IDENTIFIED BY projektant
- PROFILE projekt_prac;
- ALTER USER projektant succeeded.
- SELECT * FROM user_resource_limits;
- RESOURCE_NAME LIMIT
- -------------------------------- ----------------------------------------
- COMPOSITE_LIMIT UNLIMITED
- SESSIONS_PER_USER 1
- CPU_PER_SESSION UNLIMITED
- CPU_PER_CALL 7
- LOGICAL_READS_PER_SESSION 20
- LOGICAL_READS_PER_CALL UNLIMITED
- IDLE_TIME 1
- CONNECT_TIME UNLIMITED
- PRIVATE_SGA UNLIMITED
- 9 rows selected
- ALTER SYSTEM SET RESOURCE_LIMIT = TRUE
- ALTER SYSTEM SET succeeded.
- e) ni9c sie nie stalo
- f) nie udalo sie polaczyc
- g i h ) nie mozna sie juz polaczyc poniewaz zostal przekroczony limit sesji , ktory zostal ustawiony na 1 w zad. 2
- KONIEC !!!
Add Comment
Please, Sign In to add comment