Dictionary & User privileges

Dictionary

SELECT * FROM dict WHERE table_name LIKE 'USER_%';

// prefix is set to 'user_'

// prefix examples
// 'ALL_' - info accessable to the current user
// 'DBA_' - info accessable to the admin
// 'USER_' - info from the scheme of the current user

// examples include 'USER_TRIGGERS'

Create Users

CREATE USER userName IDENTIFIED BY password
	Default tablespace users
	Quota 5M ON users # resource limit this is 5mb
	Temporary tablespace temp
	Profile profileName;

Alter User

CREATE USER userName IDENTIFIED BY password;

Create profile

CREATE PROFILE profileName
LIMIT SESSIONS_PER_USER 2
CPU_PER_SESSION unlimited
CPU_PER_CALL 6000
IDLE_TIME 60 #IN MINS
CONNECT_TIME 120 #IN MINS
LIMIT PASSWORD_REUSE_MAX 10 #HOW MANY CHANGES BEFORE YOU CAN USE OLD PASSWORD
PASSWORD_REUSE_TIME 30 # HOW MANY DAYS UNTIL YOU CAN USE OLD PASSWORD

Grant access to table

GRANT SELECT, DELETE, INSERT, UPDATE ON tableName TO userName1, userName2;
#      ^ to select all ALL PRIVILEGES                 ^ must be user ID

GRANT SELECT ON tableName TO userName1 WITH GRANT OPTION;
# means they can give thoses privlages to other users
# if they get their powers revoked, the users with powers passed to them
# will also be revoked

# userName1 will refure to tableName as user that made the object . tablename
# n0928518.tableName



# access to columns
GRANT SELECT (col1, col2) ON tableName TO userName1 WITH GRANT OPTION;

Revoke access

REVOKE SELECT, DELETE, INSERT, UPDATE ON tableName FROM userName1, userName2;
#      ^ to select all ALL PRIVILEGES                    ^ must be user ID

# revoke columns
REVOKE SELECT (col1, col2) ON tableName TO userName1;

Roles

Note: treat a role like a user

# step 1: create role
CREATE ROLE roleName;

GRANT /* look at the image below */ create, delete, update TO roleName;

# step 2: assign role to...
GRANT SELECT ON tableName TO roleName; # treat it like a user

GRANT roleName TO userName;

Restrict access to rows/columns via views

Note: user returns the current users name

Example

GRANT SELECT ON viewName TO userName;

How other users can select your tables?

SELECT * FROM yourUsername.tableName;