Standard SQL:
GRANT SELECT|UPDATE|INSERT ON schema.object TO user/role
to allow the grantee to create views based off an object you need to also add with grant option
which only works for a specific user (not roles).
###Remove privs
Standard SQL:
REVOKE SELECT|UPDATE|INSERT ON schema.object FROM user
###View privs
####ora
SELECT * FROM SYS.table_privileges;
###sql
slightly more complicated answer...
USE master;
SELECT dp.NAME AS principal_name
,dp.type_desc AS principal_type_desc
,o.NAME AS object_name
,p.permission_name
,p.state_desc AS permission_state_desc
FROM sys.database_permissions p
LEFT OUTER JOIN sys.all_objects o ON p.major_id = o.OBJECT_ID
INNER JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id
###Further reading
- https://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=222
- http://www.dba-oracle.com/t_convent_sql_server_tsql_oracle_plsql.htm
###Oracle
- dbms_jobs is deprecated, but you'll keep finding it all over the place. jog interval and date arithmetic is a pita to get right. so use this page as an aid: http://dbataj.blogspot.co.uk/2007/04/dbmsjob.html
####Evaluating dbms_job interval strings
SET SERVEROUTPUT ON;
DECLARE
V_DATE_STRING VARCHAR2(200);
V_DATE_VALUE VARCHAR2(200);
BEGIN
-- this could easily be a table i.e. select bar into V_DATE_STRING from foo where fizz=buzz;
V_DATE_STRING := 'TRUNC(SYSDATE-1)';
EXECUTE IMMEDIATE 'SELECT ' || V_DATE_STRING || ' FROM DUAL'
INTO V_DATE_VALUE;
DBMS_OUTPUT.PUT_LINE('date string: ' || V_DATE_STRING || ' actual value: ' || V_DATE_VALUE);
END;
####interval
SELECT TO_CHAR(SYSDATE, 'HH24:Mi:SS') "nao", TO_CHAR(SYSDATE + INTERVAL '10' SECOND , 'HH24:MI:SS') "future", 'seconds' "period" FROM DUAL
UNION ALL
SELECT TO_CHAR(SYSDATE, 'HH24:Mi:SS') "nao", TO_CHAR(SYSDATE + INTERVAL '5' MINUTE, 'HH24:MI:SS') "future", 'minutes' "period" FROM DUAL
UNION ALL
SELECT TO_CHAR(SYSDATE, 'HH24:Mi:SS') "nao", TO_CHAR(SYSDATE + INTERVAL '1' HOUR, 'HH24:MI:SS') "future", 'hours' "period" FROM DUAL
UNION ALL
SELECT TO_CHAR(SYSDATE, 'DD-MON-YY HH24:Mi:SS') "nao", TO_CHAR(SYSDATE + INTERVAL '1' DAY, 'DD-MON-YY HH24:MI:SS') "future", 'day' "period" FROM DUAL
UNION ALL
SELECT TO_CHAR(SYSDATE, 'DD-MON-YY HH24:Mi:SS') "nao", TO_CHAR(SYSDATE + INTERVAL '1' MONTH, 'DD-MON-YY HH24:MI:SS') "future", 'month' "period" FROM DUAL
UNION ALL
SELECT TO_CHAR(SYSDATE, 'DD-MON-YY HH24:Mi:SS') "nao", TO_CHAR(SYSDATE + INTERVAL '1' YEAR, 'DD-MON-YY HH24:MI:SS') "future", 'year' "period" FROM DUAL;
###SQL Server
-- view all jobs enabled
exec msdb.dbo.sp_help_job @enabled = 1
-- view steps in a specific job
exec msdb.dbo.sp_help_job @job_name = N'world domination', @job_aspect = N'ALL'
###Further reading
- http://msdn.microsoft.com/en-us/library/ms187763(v=sql.105).aspx
- http://stackoverflow.com/questions/4152111/dbms-job-vs-dbms-scheduler#4152120
- http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_job.htm
- http://docs.oracle.com/cd/B19306_01/server.102/b14231/jobtosched.htm#ADMIN037
- http://www.orafaq.com/wiki/DBMS_JOB
- http://www.orafaq.com/wiki/DBMS_SCHEDULER
- http://www.orafaq.com/wiki/DBMS_JOB_complex_scheduling
###Deletion or modification where profile has to be changed
####Oracle
drop user FOO cascade;
###Creation or modification where profile has to be changed
if this is a brand new user, then you'll need to create a new role and assign privs (you are using roles right?). also don't enable a password unless you hate yourself. as sys:
create role SPECIAL_ROLE not identified;
grant create synonym to SPECIAL_ROLE;
as sys (asumes that table spaces and profiles already exist):
create user FOO profile "service_account" identified by SEKRItPASSWORD
default tablespace FOO_DATA
temporary tablespace temp
quota unlimited on "FOO_DATA"
quota unlimited on "FOO_IND"
account unlock;
grant create session to FOO;
grant SPECIAL_ROLE to FOO;
finally (re)create grants or synonyms as the new user. top
##meta data###oracle
desc TABLE|VIEW; -- returns column names and details
desc PACKAGE; -- returns sprocs and their parameters
desc SPROC; -- returns their parameters
###sql
sp_help 'TABLE|VIEW|SPROC' -- gives you multiple rowsets of data including column info
sp_helptext 'VIEW|SPROC' -- gives you DDL