SHA INFOTECH - Privileges

-- Get the current tablespace quota information of an user
set lines 299
select TABLESPACE_NAME,BYTES/1024/1024 "UTILIZIED_SPACE" ,MAX_BYTES/1024/1024 "QUOTA_ALLOCATED" from dba_ts_quotas where username='&USER_NAME';

TABLESPACE_NAME UTILIZIED_SPACE QUOTA_ALLOCATED
------------------------------ --------------------------- ------------------------
USERS .0625 1024

--- Change the tablespace quota for the user to 5G

ALTER USER SCOTT QUOTA 5G ON USERS;

--- Grant unlimited tablespace quota:

ALTER USER SCOTT QUOTA UNLIMITED ON USERS;

View Privileges granted to an user

-- System privileges granted to an user ( scott)

SELECT * FROM DBA_SYS_PRIVS where grantee='SCOTT';

-- Roles granted to an user ( scott)

SELECT * FROM DBA_ROLE_PRIVS where grantee='SCOTT';

-- Object privileges granted to an user ( SCOTT)

SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE='SCOTT';

-- Column specific privileges granted

SELECT * FROM DBA_COL_PRIVS WHERE WHERE GRANTEE='SCOTT';

Grant table/column privilege to user

-- Table privileges

GRANT READ ANY TABLE TO SCOTT;

GRANT SELECT ANY TABLE TO SCOTT;

GRANT INSERT, UPDATE, DELETE ON TESTUSER1.EMPTABL on SCOTT;
GRANT ALL ON TESTUSER1.EMPTABL on SCOTT;

-- Grant privilege on few columns of a table
--Only INSERT,UPDATE can be granted at COLUMN level.

GRANT insert (emp_id) ON TESTUSER1.EMPTABL TO SCOTT;
GRANT UPDATE(emp_id) ON TESTUSER1.EMPTABL TO SCOTT;

Page updated

Google Sites

Report abuse