Creating Users in Oracle Oracle Files Oracle Architecture Startup and Shutdown Datenbanken IIB: DBMS-Implementierung Chapter 2: Basic Oracle Architecture and Administration Prof. Dr. Stefan Brass Martin-Luther-Universit¨ at Halle-Wittenberg Wintersemester 2019/20 http://www.informatik.uni-halle.de/˜brass/dbi19/ Stefan Brass: DB IIB: DBMS-Implementation 2. Basic Oracle 1/88
Creating Users in Oracle Oracle Files Oracle Architecture Startup and Shutdown Objectives After completing this chapter, you should be able to: create users in Oracle. enumerate processes, files, memory structures of the Oracle architecture. explain why delayed writing of changed database blocks is a good idea, and how the logfile protects changes. start and stop the Oracle server, enumerate different system states of the server. Stefan Brass: DB IIB: DBMS-Implementation 2. Basic Oracle 2/88
Creating Users in Oracle Oracle Files Oracle Architecture Startup and Shutdown Inhalt Creating Users in Oracle 1 Oracle Files 2 Oracle Architecture 3 Startup and Shutdown 4 Stefan Brass: DB IIB: DBMS-Implementation 2. Basic Oracle 3/88
Creating Users in Oracle Oracle Files Oracle Architecture Startup and Shutdown Object Privileges (1) Access rights in standard SQL are a set of triples: Who can execute which command on which table? GRANT SELECT ON EMP TO SMITH REVOKE INSERT ON DEPT FROM MILLER Actually, they are quadruples: Who has given whom what right on which database object? This is important when rights are revoked. Rights can also be granted “ TO PUBLIC ”. All users, including users created in future. Rights can be given “ WITH GRANT OPTION ”. Then the grantee can grant the right to further users. The owner of a table (the user who created it) automatically holds all rights on it WITH GRANT OPTION . For views this is more complicated. Stefan Brass: DB IIB: DBMS-Implementation 2. Basic Oracle 4/88
Creating Users in Oracle Oracle Files Oracle Architecture Startup and Shutdown Object Privileges (2) USER_TAB_PRIVS : Grants on objects for which the current user is owner, grantor, or grantee. USER_TAB_PRIVS GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE PUBLIC SCOTT DEPT SCOTT SELECT N SMITH SCOTT EMP SCOTT SELECT Y MILLER SCOTT EMP SMITH SELECT N SMITH SCOTT EMP SCOTT INSERT N I.e. all users have read access to the table DEPT . SMITH got read access to EMP WITH GRANT OPTION , and has given the right to MILLER . In addition, SMITH can append rows to EMP . In contrast to other data dictionary tables, the prefix USER here does not mean that only tables owned by the current user are listed. Stefan Brass: DB IIB: DBMS-Implementation 2. Basic Oracle 5/88
Creating Users in Oracle Oracle Files Oracle Architecture Startup and Shutdown Object Privileges (3) Columns of USER_TAB_PRIVS : OWNER and TABLE_NAME identify the table. GRANTEE : The user who got the privilege. GRANTOR : The user who gave the privilege. Because of the grant option, not only the owner can be grantor. PRIVILEGE : The right, e.g. ’SELECT’ . GRANTABLE : ’YES’ if right includes grant option. In this way, the SQL GRANT commands are stored in the data dictionary. Stefan Brass: DB IIB: DBMS-Implementation 2. Basic Oracle 6/88
Creating Users in Oracle Oracle Files Oracle Architecture Startup and Shutdown Object Privileges (4) USER_TAB_PRIVS_MADE is the subset of USER_TAB_PRIVS with OWNER=USER . USER_TAB_PRIVS_RECD is the subset of USER_TAB_PRIVS with GRANTEE=USER . The user might also have access to database objects because of grants to PUBLIC , which are not listed in these tables (but see ALL_TAB_PRIVS ). Unless, of course, they are made by the current user or refer to tables of the current user. Otherwise, the name of the current user is neither OWNER , nor GRANTOR , nor GRANTEE , therefore the grant is not shown. Stefan Brass: DB IIB: DBMS-Implementation 2. Basic Oracle 7/88
Creating Users in Oracle Oracle Files Oracle Architecture Startup and Shutdown Object Privileges (5) The INSERT and UPDATE right can be given selectively for certain columns. An insert right for only part of the columns means that the other columns cannot be explicitly specified, and thus get their declared default value (or null). USER_COL_PRIVS : Grants that refer to single columns. USER_COL_PRIVS looks like USER_TAB_PRIVS , but has the additional column COLUMN_NAME . Grants for whole tables are not repeated here. USER_COL_PRIVS_MADE , USER_COL_PRIVS_RECD : Subsets with current user as owner/grantee (as above). Stefan Brass: DB IIB: DBMS-Implementation 2. Basic Oracle 8/88
Creating Users in Oracle Oracle Files Oracle Architecture Startup and Shutdown System Privileges (1) Commands like “ CREATE TABLE ” cannot be restricted with this standard security model. A user who is only supposed to enter data does not need to create new tables. For a secure system, every user should only be able to execute the commands he/she is supposed to execute. Therefore, Oracle has also “system privileges”. Every major DBMS vendor has a to the problem (all different). In contrast to “object privileges”, these refer to the execution of specific commands, not to DB objects. E.g. one needs the system privilege “ CREATE TABLE ” in order to be able to execute this command. Stefan Brass: DB IIB: DBMS-Implementation 2. Basic Oracle 9/88
Creating Users in Oracle Oracle Files Oracle Architecture Startup and Shutdown System Privileges (2) In order to log into Oracle, one needs the system privilege “ CREATE SESSION ”. An account can be locked by not granting (or revoking) this privilege. It is still possible to access tables, views, etc. under this account via synonyms or “ � User � . � Table � ” (if one has the necessary access rights). Many system privileges are only for DBAs, e.g.: “ SELECT ANY TABLE ” (read access to all tables), “ DROP ANY TABLE ” (delete data of arbitrary users), “ CREATE USER ” (create a new user). Stefan Brass: DB IIB: DBMS-Implementation 2. Basic Oracle 10/88
Creating Users in Oracle Oracle Files Oracle Architecture Startup and Shutdown System Privileges (3) Since the usual privileges of a DBA are separated into different system privileges, it is possible to have several DBAs with different responsibilities. Of course, one can still have one DBA with all privileges. There are currently 157 different system privileges. Basically, every administration command corresponds to a system privilege. Different kinds of CREATE commands also correspond to system privileges (since these commands could not be restricted otherwise). Most commands also have an ANY -version as a system privilege (allows one to apply the command to objects of any user). CREATE ANY TABLE : create tables in any schema. Stefan Brass: DB IIB: DBMS-Implementation 2. Basic Oracle 11/88
Creating Users in Oracle Oracle Files Oracle Architecture Startup and Shutdown System Privileges (4) If a user has a system privilege “ WITH ADMIN OPTION ”, he/she can give it to other users: GRANT CREATE TABLE TO SCOTT Adding “ WITH ADMIN OPTION ” gives SCOTT the right to grant “ CREATE TABLE ”, too. When a system privilege is revoked from a user A who had it “ WITH ADMIN OPTION ”, privileges are not recursively revoked from users B who got it from A . This might be the reason why it was not called “ GRANT OPTION ”. But it is very similar (“ GRANT OPTION ” can be used only for object privileges). Stefan Brass: DB IIB: DBMS-Implementation 2. Basic Oracle 12/88
Creating Users in Oracle Oracle Files Oracle Architecture Startup and Shutdown System Privileges (5) SYSTEM_PRIVILEGE_MAP : List of all system privileges. SYSTEM_PRIVILEGE_MAP PRIVILEGE NAME . . . . . . -5 CREATE SESSION . . . . . . -40 CREATE TABLE . . . . . . -47 SELECT ANY TABLE . . . . . . Stefan Brass: DB IIB: DBMS-Implementation 2. Basic Oracle 13/88
Creating Users in Oracle Oracle Files Oracle Architecture Startup and Shutdown System Privileges (6) USER_SYS_PRIVS : System privileges granted to the current user or to PUBLIC . Columns are: USERNAME (always the name of the current user, not very useful), PRIVILEGE (name of the system privilege, no join with SYSTEM_PRIVILEGE_MAP necessary), ADMIN_OPTION (similar to grant option for object privileges). DBA_SYS_PRIVS : System privileges for each user. For DBA only. It has the columns GRANTEE , PRIVILEGE , ADMIN_OPTION . Only directly granted privileges are listed. Additional system privileges might have been granted via roles (see below). Therefore, USER_SYS_PRIVS is often empty, although the user actually has many system privileges. Stefan Brass: DB IIB: DBMS-Implementation 2. Basic Oracle 14/88
Creating Users in Oracle Oracle Files Oracle Architecture Startup and Shutdown Roles (1) It is difficult to grant privileges to many users one by one. In one way or another, all modern DBMS support groups of users with similar privileges. Oracle has the concept of “roles”, which are sets of privileges that can be granted to users: CREATE ROLE MANAGEMENT This command requires DBA rights (system privilege “ CREATE ROLE ”). Access rights are granted to a role in the same way as they are granted to a user: GRANT SELECT ON EMP TO MANAGEMENT Stefan Brass: DB IIB: DBMS-Implementation 2. Basic Oracle 15/88
Recommend
More recommend
