Changing own user permissions with Oracle SQL not being the DBA

oracleoracle-11gpermissions

I have been making changes to a couple of schemas of different environments (they belong to the same application). Sometimes I'll want to copy data from one environment to the other, and sometimes I'll want, for instance, to copy information from production to a development environment. When doing this kind of stuff, it always scares me to think of what would happen were I to copy or merge or whatever in the wrong direction, then destroying the production database.

I'm currently connecting to the schemas as a regular user with read/write permissions (I'm not the DBA here).

The ideal would be to have some way of being able to decrease in a fine grained way my own permissions (or having some system akin to having to typesudo in Linux. It'd be cool that any kind of write action to DEV would make me have to write my password).

I imagine this is a recurrent situation. How do people generally solve this kind of issues? I could of course ask the DBA to create different users with different kinds of permissions, but that gets tiresome, as their permissions end-up not being fine-grained and there are like 5 or 6 different environments where I'd have to create new users to. Maybe he could grant me rights to create new users (being those users always limited to have in the best case having exactly the same rights as my original user?)

Thanks

Best Answer

You mentioned as one of your examples as "from production to development" .. so I'll start there, and with that, assume that your different "environments" are, in fact, different instances/databases as well.

Always "PULL" data ... rather than "PUSH". So in other words, login to where you the data will be INSERTED. That id should have INSERT access to the target tables. That same id should ONLY HAVE READ access to the source (ie production). You can set that up via a db link or such.

You then

INSERT into <local table>
  select * from <remote table>@dblink;

and you can't go wrong, since you don't have access.

This works for any two environments ... if you setup those db links using READ ONLY ids, and you do the INSERT locally with a user with local INSERT privs .. you're safe.

Related Solutions

Sql-server – Managing Access for DBA and IT Support

Regarding providing DBA access, you might be better-off creating an Active Directory group and adding the DBAs into that group. That way, you can provide group-level permissions and give sysadmin to the group rather than individual users, meaning that when a DBA leaves (or a new one gets hired), you just need to adjust Active Directory rights rather than going into each SQL Server and altering logins. The same applies (though without granting sysadmin!) for other groups of internal users. Windows authentication is more secure than SQL authentication, and if you can use it, you probably should.

Regarding auditing, you can still have individual-level auditing when users are in a group. You can use SUSER_NAME() to get the username in whatever process you're going to use to log activity. Note that somebody could do an EXECUTE AS to switch their user context, but you should be able to log that as well as the IP address from which the query came, so you'd still be able to correlate activity to a person.

The idea of using a jumpstation (in your case, you mentioned a server) is possible. It would make administration a bit easier, especially if the laptop domain does not have a full trust relationship with the domain upon which the SQL Server instances are located. It would probably be a bit annoying for the support personnel, honestly, but if you go that route, I'd recommend a virtualized desktop for each IT support team member. That way, they don't have to deal with server contention issues and you can minimize the pain. If you do go down that route, then the IT support team members could have domain accounts and you'd create relevant Windows groups the same way as the DBA group.

PostgreSQL user permissions

This should work (as superuser):

CREATE ROLE rwc;    -- read/write/create user
CREATE SCHEMA foo;
ALTER  SCHEMA foo OWNER TO rwc;
-- GRANT ALL ON SCHEMA foo TO rwc;  -- alternatively

CREATE ROLE r;      -- read user
GRANT USAGE ON SCHEMA foo TO r;

CREATE ROLE rw;     -- read/write user

GRANT r TO rw;
GRANT rw TO rwc;

ALTER DEFAULT PRIVILEGES FOR ROLE rwc IN SCHEMA foo GRANT SELECT ON TABLES TO r;
ALTER DEFAULT PRIVILEGES FOR ROLE rwc IN SCHEMA foo GRANT USAGE ON SEQUENCES TO r;

ALTER DEFAULT PRIVILEGES FOR ROLE rwc IN SCHEMA foo
GRANT INSERT, UPDATE, DELETE ON TABLES TO rw;
ALTER DEFAULT PRIVILEGES FOR ROLE rwc IN SCHEMA foo
GRANT SELECT, UPDATE ON SEQUENCES TO rw;

You probably missed one of these steps.
You may need do adjust existing objects additionally since default privileges are only granted to new objects.

Best Answer

Related Solutions

Sql-server – Managing Access for DBA and IT Support

PostgreSQL user permissions

Related Question