Introduction to security and systems privileges

The database administrator is established with privileges to grant users access to databases and objects, remove users and tables, do backup etc. Essentially system privileges give the user access to the database while object privileges give the user the right to manipulate the objects within the database. Users may also be given the power to grant privileges themselves. A schema is owned by a user and is composed of objects including tables, views and sequences.

There are over 80 system privileges some of which include the right to create user, drop user, drop table, backup table.

To create myself as another user with ultimate privileges, I went into navigator (which is under Personal Oracle 7 for Windows 95). Then in the left menu, select Personal Oracle7, then Local Databases, right click on Users and select New. At this point you will be prompted to enter a username and password. Then I clicked on Role/Privilege and gave myself everything.

At this point I went back to the SQL that we have been using and instead of going in as scott and tiger, I went in with the new username and password. I now have DBA priviledges so I can do administrative tasks.

First I created another user in SQL. I then went in and granted privileges to the user. Specifically I gave the user the system privilege to create a table, create a sequence and create a view. Other creates that could have been granted are the right to create a session which is the right to connect to the database and the right to create procedure which is the write to create a procedure or function.

Note that REVOKE privilege list ON table FROM user can be used to take away privileges that were granted.

SQL CODE:

SQL> CREATE USER BRISTOL
2 IDENTIFIED BY bristol;

User created.

SQL> GRANT create table, create sequence, create view
  2  TO Bristol;

Grant succeeded.

A role is defined as a group of privileges that can be granted as a unit. A user can have several roles and multiple users can be given a role.

SQL CODE:

SQL> GRANT create table, create sequence, create view
  2  TO Bristol;

Grant succeeded.

SQL> CREATE ROLE create_table_view;

Role created.

SQL> GRANT create table, create view TO create_table_view;

Grant succeeded.

SQL> GRANT create_table_view to BRISTOL;

Grant succeeded.

To change the password identified with a user, the following alter statement can be used.

SQL CODE:

SQL> ALTER USER BRISTOL
  2  IDENTIFIED BY college;

User altered.

The right to do an action is an object privilege. Object privileges include the right to create, alter, delete, execute, index, insert, references, select, update when appropriate on tables, views, sequences, procedures, functions and packages. A user has rights to the schema object that they own and can grant privileges to other users. This is done with the GRANT privilege list ON table TO user command. If the original user gives the privilege to grant privileges by including the WITH GRANT OPTION then the privileges can be passed on to other users.

I logged in as BRISTOL with the password college. I created the following table testpriv and then did select * from cat to verify it was there. I then logged in as scott/tiger and did a select * from cat, testpriv does not appear on the list.

SQL CODE:

SQL> CREATE TABLE testpriv
  2  (idno NUMBER(3), name VARCHAR(20), amt NUMBER(6,2));

Table created.

SQL> INSERT INTO testpriv
  2  VALUES(111, 'Linda Higgins', 299.75);

1 row created.

SQL> INSERT INTO testpriv
  2  VALUES (222, 'William Rollins', 199.99);

1 row created.

SQL> SELECT * FROM testpriv;

     IDNO NAME                       AMT
--------- -------------------- ---------
      111 Linda Higgins           299.75
      222 William Rollins         199.99

SQL> SELECT * FROM cat;

TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
TESTPRIV                       TABLE